Topic 8: Registration Form (DB Connect)

📖 14 min read · 🎯 advanced · 🧭 Prerequisites: tags-list, javascript-events

Why this matters

Up until now, you've probably built forms that look good but don't actually do anything — you hit Submit and the data just disappears. That's frustrating, and it's the gap we're closing today. In this lesson, we wire a real registration form directly to a MySQL database using PHP and Apache. When someone fills in their name and email and clicks Submit, that data gets saved — permanently — in a proper database table. This is the moment your web page stops being a pretty picture and starts being a working application.

What You'll Learn

• Install and configure a LAMP stack (Linux, Apache, MySQL, PHP) for local development
• Create the InnovatorsSummit database with Participants, Events, and Registrations tables
• Build the HTML registration form that posts to a PHP handler script
• Write a secure PHP backend using mysqli prepared statements to insert form data
• Test the full form-to-database flow and identify paths to enhance the system

The Analogy

Think of the registration form as a paper slip a conference attendee fills out at the door. The PHP script is the volunteer who takes that slip, checks it for completeness, and files it in the right drawer of a locked cabinet — that cabinet is the MySQL database. Without the volunteer (PHP) and the cabinet (MySQL), the slip just blows away in the wind. The LAMP stack is the entire filing office: the building (Linux), the reception desk (Apache), the cabinet (MySQL), and the volunteer (PHP) — all working in concert so no registration ever gets lost.

Chapter 1: Setting Up the LAMP Stack

Before any form can talk to a database, the development environment needs to be ready. The class chose the LAMP stack — Linux, Apache, MySQL, PHP — the classic server-side quartet.

Step 1 — Install Apache

Apache serves the web pages to the browser.

sudo apt-get update
sudo apt-get install apache2

Step 2 — Install MySQL

MySQL manages the relational database where registration data will live.

sudo apt-get install mysql-server
sudo mysql_secure_installation

mysql_secure_installation walks through hardening steps: setting a root password, removing anonymous users, disabling remote root login, and dropping the test database. Run it every time on a fresh install.

Step 3 — Install PHP

PHP handles the server-side scripting that bridges the HTML form and the MySQL database.

sudo apt-get install php libapache2-mod-php php-mysql

libapache2-mod-php lets Apache process .php files. php-mysql gives PHP the mysqli extension it needs to talk to MySQL.

Chapter 2: Creating the Database

With the stack running, the class created the InnovatorsSummit database and its three tables: Participants, Events, and Registrations.

CREATE DATABASE InnovatorsSummit;

USE InnovatorsSummit;

CREATE TABLE Participants (
    ParticipantID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    ContactNumber VARCHAR(15) NOT NULL,
    Track VARCHAR(50) NOT NULL,
    DietaryPreferences VARCHAR(255)
);

CREATE TABLE Events (
    EventID INT AUTO_INCREMENT PRIMARY KEY,
    EventName VARCHAR(100) NOT NULL,
    Description TEXT,
    EventDate DATE NOT NULL,
    EventTime TIME NOT NULL,
    Location VARCHAR(100) NOT NULL
);

CREATE TABLE Registrations (
    RegistrationID INT AUTO_INCREMENT PRIMARY KEY,
    ParticipantID INT,
    EventID INT,
    RegistrationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ParticipantID) REFERENCES Participants(ParticipantID),
    FOREIGN KEY (EventID) REFERENCES Events(EventID)
);

Key design decisions to note:

• Email on Participants is marked UNIQUE — no duplicate registrations for the same address.
• RegistrationDate defaults to CURRENT_TIMESTAMP — the database records when a registration happened automatically, without the PHP script needing to supply it.
• Registrations links participants to events via two foreign keys, enabling one participant to attend multiple events.

erDiagram
    Participants {
        int ParticipantID PK
        varchar Name
        varchar Email
        varchar ContactNumber
        varchar Track
        varchar DietaryPreferences
    }
    Events {
        int EventID PK
        varchar EventName
        text Description
        date EventDate
        time EventTime
        varchar Location
    }
    Registrations {
        int RegistrationID PK
        int ParticipantID FK
        int EventID FK
        timestamp RegistrationDate
    }
    Participants ||--o{ Registrations : "registers via"
    Events ||--o{ Registrations : "listed in"

Chapter 3: Building the HTML Registration Form

The HTML form collects five pieces of information from the participant. The action attribute points to register.php — the PHP script that will handle the submission. The method is post so that form data is sent in the HTTP body, not exposed in the URL.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Innovators' Summit Registration</title>
</head>
<body>
    <h1>Innovators' Summit Registration Form</h1>

    <form action="register.php" method="post">
        <label for="name">Full Name:</label>
        <input type="text" id="name" name="name" required>
        <br>

        <label for="email">Email:</label>
        <input type="email" id="email" name="email" required>
        <br>

        <label for="contact">Contact Number:</label>
        <input type="tel" id="contact" name="contact" required>
        <br>

        <label for="track">Preferred Track:</label>
        <select id="track" name="track" required>
            <option value="frontend">Frontend Development</option>
            <option value="backend">Backend Development</option>
            <option value="fullstack">Full Stack Development</option>
        </select>
        <br>

        <label for="diet">Dietary Preferences:</label>
        <input type="text" id="diet" name="diet">
        <br>

        <button type="submit">Register</button>
    </form>
</body>
</html>

The name attributes on each input (name, email, contact, track, diet) are the keys that $_POST will use in the PHP script. They must match exactly.

Chapter 4: Building the PHP Backend Script

register.php is where the real work happens. It connects to MySQL, receives the posted form data, and uses a prepared statement to insert that data safely.

<?php

// Database configuration
$servername = "localhost";
$username   = "root";
$password   = "";
$dbname     = "InnovatorsSummit";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Get form data
$name    = $_POST['name'];
$email   = $_POST['email'];
$contact = $_POST['contact'];
$track   = $_POST['track'];
$diet    = $_POST['diet'];

// Prepare and bind
$stmt = $conn->prepare(
    "INSERT INTO Participants (Name, Email, ContactNumber, Track, DietaryPreferences)
     VALUES (?, ?, ?, ?, ?)"
);
$stmt->bind_param("sssss", $name, $email, $contact, $track, $diet);

// Execute the statement
if ($stmt->execute()) {
    echo "Registration successful!";
} else {
    echo "Error: " . $stmt->error;
}

// Close connection
$stmt->close();
$conn->close();

?>

How each part works

The type string "sssss" tells bind_param all five values are strings. Other type codes are i (integer), d (double), and b (blob).

Chapter 5: Testing the Registration Form

The class verified the integration end-to-end:

1. Opened http://localhost/registration.html in a browser.
2. Filled in a name, email, contact number, selected a track, and optionally entered dietary preferences.
3. Clicked Register.
4. Confirmed the browser showed Registration successful!.
5. Opened phpMyAdmin (or ran SELECT * FROM Participants; in the MySQL CLI) to confirm the row appeared in the Participants table of InnovatorsSummit.

If connect_error fires instead, the most common culprits are a wrong $username/$password, MySQL service not running (sudo service mysql start), or the database name being misspelled.

Chapter 6: Enhancing the System

With the basic pipeline working, the class mapped out three enhancements worth building next:

1. Email Confirmation — Send a confirmation email to participants upon successful registration. PHP's mail() function or a library like PHPMailer can send transactional email with the participant's details as a receipt.

2. Event Selection — Allow participants to register for specific events and insert a row into the Registrations table linking their ParticipantID to the chosen EventID. This activates the relational structure already in the schema.

3. Admin Panel — Create an admin interface for managing participants and events — listing all registrants, updating records, or removing them. This would use SELECT, UPDATE, and DELETE queries against the same database.

🧪 Try It Yourself

Task: Set up the LAMP stack locally, create the InnovatorsSummit database, and get a registration from the HTML form to land in the Participants table.

Steps:

1. Install Apache, MySQL, and PHP using the commands from Chapter 1.
2. Log into MySQL and run the CREATE DATABASE and CREATE TABLE statements from Chapter 2.
3. Save the HTML form as registration.html and the PHP script as register.php inside /var/www/html/.
4. Open http://localhost/registration.html, fill in the form, and submit.

Success criterion: After submitting, you see Registration successful! in the browser, and running the following query in MySQL returns your test row:

SELECT * FROM Participants;

Starter snippet — quick connection test you can run before wiring up the form:

<?php
$conn = new mysqli("localhost", "root", "", "InnovatorsSummit");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully!";
$conn->close();
?>

Save it as test_connection.php in /var/www/html/ and visit http://localhost/test_connection.php.

🔍 Checkpoint Quiz

Q1. Why does register.php use a prepared statement (prepare + bind_param) instead of building the SQL string by concatenating $_POST values directly?

A) Prepared statements are faster to type
B) They prevent SQL injection by separating query structure from user data
C) mysqli doesn't support string concatenation in queries
D) $_POST values can't be used in regular SQL strings

Q2. Given this snippet, what happens when the form is submitted with an email address that already exists in the Participants table?

$stmt = $conn->prepare(
    "INSERT INTO Participants (Name, Email, ContactNumber, Track, DietaryPreferences)
     VALUES (?, ?, ?, ?, ?)"
);
$stmt->bind_param("sssss", $name, $email, $contact, $track, $diet);
if ($stmt->execute()) {
    echo "Registration successful!";
} else {
    echo "Error: " . $stmt->error;
}

A) The duplicate row is inserted silently
B) PHP throws a fatal error and crashes
C) execute() returns false and $stmt->error describes the duplicate-key violation
D) The existing row is overwritten with the new data

Q3. What does the type string "sssss" in bind_param("sssss", $name, $email, $contact, $track, $diet) signify, and what would you use instead if one of the values were an integer?

Q4. A teammate adds a sixth field, age (an integer), to the Participants table and the HTML form. Which two lines of register.php must be updated to include age?

A) The $conn = new mysqli(...) line and the if ($conn->connect_error) check
B) The prepare(...) SQL string and the bind_param(...) call
C) The die(...) error handler and $conn->close()
D) The $_POST reads and the $stmt->close() call

A1. B — Prepared statements keep the SQL query structure separate from user-supplied data. If you concatenate $_POST values directly into a query string, a malicious user can inject SQL commands (e.g., '; DROP TABLE Participants; --). With ? placeholders, the database treats the bound values as pure data, not as executable SQL.

A2. C — Because Email is defined as UNIQUE, MySQL rejects the duplicate insert. execute() returns false, and $stmt->error will contain a message like Duplicate entry 'test@example.com' for key 'Email'. The script then outputs "Error: ...".

A3. Each character in the type string maps to one bound variable. s = string, i = integer, d = double/float, b = blob. If age were an integer, its position in the type string would be i instead of s — e.g., "sssssi" for five strings followed by one integer.

A4. B — The SQL INSERT statement inside prepare() needs a new column and ? placeholder, and bind_param() needs $age appended to its variable list with i added to the type string (e.g., "sssssi").

🪞 Recap

• A LAMP stack (Linux + Apache + MySQL + PHP) is the classic environment for server-side PHP applications.
• The InnovatorsSummit database holds three tables — Participants, Events, and Registrations — linked by foreign keys.
• The HTML form uses action="register.php" and method="post" to route submissions to the PHP handler.
• new mysqli() opens a database connection; always check connect_error before proceeding.
• Prepared statements (prepare + bind_param + execute) insert form data safely, blocking SQL injection at the source.

📚 Further Reading

• PHP mysqli documentation — the source of truth for every mysqli method used in this lesson
• MySQL CREATE TABLE reference — full syntax for column types, constraints, and foreign keys
• OWASP SQL Injection Prevention Cheat Sheet — why prepared statements exist and what they protect against
• ⬅️ Previous: Calculate, Logical Operation, Table Print
• ➡️ Next: Database Structuring, Table Designing