Aim

To create a table and insert a few records into it using form data, via PDO prepared statements.

Theory

CREATE TABLE defines each column as name + type + constraints: here id INTEGER PRIMARY KEY AUTOINCREMENT (SQLite's spelling — MySQL writes id INT AUTO_INCREMENT PRIMARY KEY) gives every row an automatic unique number, and NOT NULL on name, email and course refuses empty inserts. The PRIMARY KEY is the row's identity — unique and non-null by definition.

Form values are untrusted input, so they must never be concatenated into SQL — "... VALUES ('$name')" breaks the moment someone types O'Brien, and a crafted value becomes an attack (SQL injection). Prepared statements fix this structurally: prepare() sends the SQL template with placeholders, execute() sends the values separately, and the driver guarantees a bound value can never be re-parsed as SQL. Placeholders come in positional (?) and named (:name) flavours; the snippet uses named ones, matched by the keys of the array passed to execute().

On a real page each submission arrives in $_POST — one request, one row. To make the whole flow observable in a single run, the snippet simulates three submissions with the $formSubmissions array, each element standing in for one $_POST. After an insert, $pdo->lastInsertId() would return the auto-generated id — the standard companion call, though this script verifies with a final SELECT instead.

Requirements

• XAMPP/WAMP with Apache and PHP 8.x (MySQL + phpMyAdmin when using the mysql: DSN)
• PHP PDO extension (pdo_sqlite / pdo_mysql)
• Code editor (VS Code); browser or terminal

Procedure

1. Start Apache (and MySQL if you point the DSN at mysql:host=localhost;dbname=wbp_lab_db).
2. Save the snippet as p25_insert_form.php in C:\xampp\htdocs\wbplab.
3. Open http://localhost/wbplab/p25_insert_form.php, or run php p25_insert_form.php — either way the script creates the table, replays the three simulated submissions and lists the rows.
4. To make it a real form page, add an HTML form with name/email/course inputs and replace $formSubmissions with a single $_POST read guarded by $_SERVER["REQUEST_METHOD"] === "POST" — each submission then inserts one row.

Explanation of the Code

• ERRMODE_EXCEPTION makes any failed SQL throw a PDOException instead of failing silently.
• Step 1 creates students with four columns; AUTOINCREMENT will hand out ids 1, 2, 3 without the inserts mentioning id at all.
• Step 2 defines $formSubmissions — three associative arrays with exactly the keys a form POST would deliver.
• The INSERT is prepared once with :name, :email, :course placeholders and executed three times — cheaper than re-parsing SQL per row, and injection-proof by construction.
• Inside the loop trim() cleans each value (classic form hygiene) and an Inserted: ... confirmation is echoed per row.
• Step 3 verifies with SELECT ... ORDER BY id via fetchAll(PDO::FETCH_ASSOC), printing #id | name | email | course for every row.

Expected Output

The run prints Table 'students' created., a blank line, then Inserted: Aman (BCA), Inserted: Riya (BBA), Inserted: Rohit Kumar (BCA), then All records: followed by #1 | Aman | aman@example.com | BCA, #2 | Riya | riya@example.com | BBA and #3 | Rohit Kumar | rohit@example.com | BCA — the ids 1–3 supplied automatically by AUTOINCREMENT.

🎯 Viva Questions

1. What is SQL injection? Crafting input so that, when concatenated into SQL, it changes the query's structure — e.g. ' OR '1'='1 making a condition always true.
2. How do prepared statements prevent it? The query template and the data travel separately; bound values are treated purely as data and can never become SQL syntax.
3. ? versus :name placeholders? Both bind safely; ? is positional (order matters), :name is named and matched by array key — clearer with many columns.
4. What does AUTOINCREMENT / AUTO_INCREMENT do? The database assigns the next unique integer to id automatically on each insert.
5. How would you obtain the id just generated? $pdo->lastInsertId() immediately after the execute().
6. Where would the three rows come from in the real lab? From $_POST — one form submission per request, each handled by the same prepared insert.

CO Mapping

CO1, CO2