Aim

To write a PHP program that creates a MySQL database.

Theory

Creating a database is the one operation performed while connected to the server only — the DSN carries no dbname, because the database does not exist yet: new PDO("mysql:host=localhost", "root", "") on stock XAMPP. The statement itself is DDL:

CREATE DATABASE IF NOT EXISTS wbp_lab_db

• IF NOT EXISTS makes the script idempotent — re-running it succeeds silently instead of throwing "database exists".
• The real-world form adds a character set: CREATE DATABASE ... CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci — utf8mb4 stores full 4-byte Unicode (MySQL's legacy utf8 is only 3 bytes and cannot hold emoji).
• Verification: SHOW DATABASES lists everything the account can see, and phpMyAdmin's left panel updates immediately. Selecting it afterwards is USE wbp_lab_db (or a fresh connection with dbname= in the DSN).

The compiler has no MySQL server, and SQLite has no CREATE DATABASE at all — an SQLite database is a file (or :memory:), so opening the connection is the creation. The snippet therefore mirrors the same PHP shape with PDO + in-memory SQLite, records the event in a small database_registry table so there is something verifiable to list, and keeps the real MySQL commands as reference comments.

Requirements

• XAMPP/WAMP with Apache and MySQL; phpMyAdmin to verify
• PHP 8.x with PDO (pdo_mysql / pdo_sqlite)
• Code editor (VS Code); browser or terminal

Procedure

1. Start Apache and MySQL from the XAMPP Control Panel.
2. Save the snippet as p24_create_db.php in C:\xampp\htdocs\wbplab.
3. For the real lab, use the commented header lines: connect with mysql:host=localhost (no dbname) and run CREATE DATABASE IF NOT EXISTS wbp_lab_db via exec(), then USE wbp_lab_db.
4. Open http://localhost/wbplab/p24_create_db.php, then confirm in phpMyAdmin (or with SHOW DATABASES) that wbp_lab_db now appears.
5. In the compiler/CLI the script runs as-is against in-memory SQLite and prints its registry listing.

Explanation of the Code

• The header comment preserves the genuine MySQL sequence — connection without a database, CREATE DATABASE IF NOT EXISTS, then USE — exactly what you would submit in the lab.
• $dbName = "wbp_lab_db" holds the target name used throughout.
• new PDO("sqlite::memory:") is the compiler equivalent of creating a database: for SQLite, opening :memory: brings a fresh, empty database into existence. ERRMODE_EXCEPTION again turns SQL failures into exceptions.
• The database_registry table (db_name as PRIMARY KEY, plus created_on) records the creation; a prepared INSERT binds $dbName and date("Y-m-d H:i:s"). The PRIMARY KEY even mimics IF NOT EXISTS — registering the same name twice would violate uniqueness.
• The final SELECT loop plays the role of SHOW DATABASES, proving the database "exists" by listing the registry.

Expected Output

The run prints Database 'wbp_lab_db' created successfully., a blank line, then Registered databases: and one indented entry such as - wbp_lab_db (created on 2026-07-03 11:20:45) — the timestamp is the moment of execution, so it differs on every run. Against real MySQL the success line is the same, and phpMyAdmin's database list then shows wbp_lab_db.

🎯 Viva Questions

1. Why does the creating connection omit dbname from the DSN? You cannot connect to a database that does not exist yet; you connect to the server, then create it.
2. What does IF NOT EXISTS change? Re-running the script succeeds silently instead of erroring — the operation becomes idempotent.
3. How do you verify the database was created? SHOW DATABASES in the SQL tab or CLI, or phpMyAdmin's left-hand database list.
4. Why prefer utf8mb4 over MySQL's utf8? utf8mb4 is real 4-byte UTF-8 covering all of Unicode (including emoji); legacy utf8 truncates at 3 bytes.
5. Why is there no CREATE DATABASE in the SQLite branch? An SQLite database is just a file (or :memory:); opening the connection creates it.
6. Which statement selects the new database for later queries? USE wbp_lab_db — or reconnecting with dbname=wbp_lab_db in the DSN.

CO Mapping

CO1, CO2