Aim

To modify a table by performing delete, modify and add operations.

Theory

"Modifying a table" happens at two different levels. DDL (Data Definition Language) changes the table's shape: ALTER TABLE students ADD COLUMN phone VARCHAR(20) bolts a new column on, and every existing row receives NULL in it (MySQL additionally offers MODIFY COLUMN and DROP COLUMN). DML (Data Manipulation Language) changes the rows: INSERT adds, UPDATE ... SET ... WHERE edits, DELETE FROM ... WHERE removes.

The WHERE clause is the safety catch on both destructive verbs: an UPDATE without a WHERE rewrites every row, and a bare DELETE FROM students empties the whole table — the classic irreversible lab accident. After any DML, rowCount() on the statement reports how many rows were actually affected — the standard confirmation that the WHERE matched what you intended (0 means it touched nothing). Values are bound through prepared statements exactly as in inserts — UPDATE and DELETE are just as injectable as INSERT when concatenated.

A final SELECT is the habit that closes every modification session: it proves the state you think you produced is the state the table is actually in.

Requirements

• XAMPP/WAMP with Apache and PHP 8.x (or PHP CLI for the compiler run)
• PHP PDO extension (pdo_sqlite; pdo_mysql when pointed at MySQL)
• Code editor (VS Code); browser or terminal

Procedure

1. Start Apache (plus MySQL if you switch the DSN to mysql:host=localhost;dbname=wbp_lab_db).
2. Save the snippet as p27_modify_table.php in C:\xampp\htdocs\wbplab.
3. Open http://localhost/wbplab/p27_modify_table.php or run php p27_modify_table.php — the script seeds two rows, then performs the four operations and prints the final table.
4. Follow the printed confirmations against the code, in order: ALTER → UPDATE → INSERT → DELETE → final SELECT.

Explanation of the Code

• Setup: students is created and seeded with two rows via a prepared insert — Aman (BCA) and Riya (BBA), ids 1 and 2.
• 1) ADD column — ALTER TABLE students ADD COLUMN phone VARCHAR(20) is DDL run through exec(); both existing rows now hold NULL for phone.
• 2) MODIFY — a prepared UPDATE with named placeholders :n, :p, :old renames Aman to Aman Verma and fills his phone; $upd->rowCount() confirms exactly 1 row changed.
• 3) ADD row — a prepared INSERT adds Karan with all four values; he receives id 3 automatically.
• 4) DELETE — DELETE FROM students WHERE name = :n removes Riya; rowCount() again reports 1. Note that both destructive statements carry a WHERE.
• Verification: the final SELECT fetches every column including phone; the echo uses $r['phone'] ?? "NULL" so a row without a phone would print a visible NULL instead of an empty gap.

Expected Output

Four confirmation lines print in order: ADD: column 'phone' added to 'students'., MODIFY: updated 1 row (Aman -> Aman Verma)., ADD: inserted new student 'Karan'., DELETE: removed 1 row (Riya). — then Final 'students' table: with exactly two rows: #1 | Aman Verma | aman@example.com | BCA | 9876543210 and #3 | Karan | karan@example.com | BCA | 9999999999. The jump from #1 to #3 is the deletion made visible: Riya's id 2 is gone, and AUTOINCREMENT never hands it out again.

🎯 Viva Questions

1. Which statements here are DDL and which are DML? ALTER TABLE is DDL (changes structure); UPDATE, INSERT and DELETE are DML (change rows).
2. What happens to an UPDATE or DELETE that omits its WHERE clause? It applies to every row — rewriting or emptying the entire table.
3. What does rowCount() return? The number of rows the last statement actually affected — 1 here for both the update and the delete.
4. What value do existing rows hold in a freshly added column? NULL, until an UPDATE fills it in.
5. Why does the final listing show ids 1 and 3 but no 2? Row 2 (Riya) was deleted; auto-increment ids are not reused, which leaves a visible gap.
6. How would MySQL change a column's type or remove it? ALTER TABLE ... MODIFY COLUMN ... and ALTER TABLE ... DROP COLUMN ....

CO Mapping

CO1, CO2