PHP can execute CREATE DATABASE and CREATE TABLE, but those operations belong to controlled setup or migration code. A public request handler should not create a schema whenever someone visits the site. The schema defines what data can exist: column types, required values, unique keys, and relationships. This reference uses MySQL DDL to create a small product table and explains privileges, repeatable migrations, and rollback limits. PDO remains the main course API; MySQLi can run the same fixed DDL as an optional track. No database is created on your machine by reading this lesson.
Distinguish DDL from ordinary data changes
Data definition language changes the schema: CREATE, ALTER, and DROP describe database structures. Data manipulation changes rows inside those structures. MySQL treats many DDL statements differently from ordinary transactional INSERT or UPDATE operations, including implicit commits. Wrapping CREATE TABLE in a PHP transaction therefore does not guarantee a later rollback will undo the schema change. Keep migrations separate from business transactions and verify the exact server behavior for the statements you use. Creating a database requires suitable administrative privileges; an ordinary runtime account may correctly be denied. A setup script should run under an explicit command-line or deployment procedure, not through a hidden unauthenticated web URL. Controlled execution makes the action reviewable and easier to audit.
Choose types according to meaning
Use a numeric surrogate ID when you need a generated internal key, and keep product codes as text when leading zeros matter. A name can use VARCHAR with a considered maximum rather than an arbitrary huge length. Store prices in a DECIMAL column sized for the permitted range, with currency handled explicitly. Stock can be a nonnegative integer enforced by a supported constraint. Choose utf8mb4 for the database and relevant tables so text can represent the expected characters. Type choices influence storage, comparisons, indexes, and validation. Avoid using strings for every column because they appear flexible; that moves important consistency rules into every query and makes invalid sorting or arithmetic easier.
Use constraints to protect all writers
NOT NULL prevents missing required values, while UNIQUE makes a code or email unique under the database's comparison rules. A primary key identifies each row. Foreign keys express relationships when the engine and schema support them. CHECK constraints express rules such as stock >= 0 on supported MySQL versions, but legacy MySQL installations historically had different enforcement behavior. Verify the server version rather than assuming a copied definition is enforced. Application validation gives useful messages; constraints protect the data when another application, migration, or concurrent request writes it. Defaults should express an intended business value, not hide a required field that callers forgot to send. Explain nullability explicitly because NULL and an empty string are different states.
Keep identifiers controlled
Prepared-statement placeholders represent data values and cannot generally replace a database name, table name, or column definition. A migration should use fixed reviewed identifiers or an application-owned allowlist. Do not accept a schema name from an ordinary request and join it into SQL. Even a quoted identifier needs correct dialect-specific escaping, so avoid arbitrary dynamic identifiers when they are unnecessary. An administrative tool may require dynamic names, but then it needs a documented validation policy and permissions appropriate to that tool. Keep schema DDL in version control so reviewers can inspect it. Credentials and environment-specific host details belong in protected configuration rather than the migration source.
Track schema versions instead of guessing
CREATE TABLE IF NOT EXISTS can avoid one creation error, but it does not prove an existing table has the right columns or constraints. A table with the same name may have an outdated definition. Use a migrations table or established migration tool to record applied changes in order. Each migration should have a purpose, prerequisites, and an operational rollback or recovery plan. Avoid editing an already-applied migration as if deployments will automatically replay it. Add a new migration for the new change. Test upgrades against a realistic previous schema, not only installation from empty. For large tables, ALTER operations can lock or rebuild data depending on MySQL version and operation, so assess impact before production execution.
Plan destructive changes and recovery
Dropping a table or database removes structures and data; do not include such operations in a casual setup retry. Review backups and restore procedures before migrations that discard or rewrite valuable information. A rollback script cannot recover data that was destroyed unless a recoverable copy exists. Prefer staged changes when deploying code and schema together: add a compatible column, deploy readers and writers, backfill data, then remove old fields after compatibility is no longer needed. Separate development fixtures from production records. Use a database account whose scope is appropriate to the target environment, and print the target environment clearly in controlled tooling without exposing secrets. This reduces mistakes when several similar database names exist.
Practice task and worked solution
Design a two-step schema migration for adding a required product category. The solution first creates a categories table and adds a nullable category_id foreign key, preserving compatibility with existing rows. Backfill categories using a reviewed rule and verify unmatched products. Only after application writers supply the field should a later migration make it required. Record applied migration versions and test the upgrade from the original products schema. Explain why IF NOT EXISTS does not validate an old table definition and why MySQL DDL cannot be treated as a universal rollback-safe transaction. Keep the runtime account separate from the migration account.
Continue learning
Review PDO, CRUD, and security for ordinary row operations and privilege boundaries.
The PDO transaction documentation describes DDL implicit-commit caveats.
Creating Databases and Tables with PHP Examples
Each example shows the code, the output it produces and what each part does.
Fixed MySQL product-table DDL
Run only in a deliberately selected disposable development schema using a configured PDO connection with CREATE TABLE permission. This is MySQL syntax and assumes a version that enforces CHECK constraints.
On the first successful execution in an empty target schema, output is Products table created. Repeating it without a migration guard fails because the table exists. No row is inserted, and actual execution requires the stated connection and privileges.
<?php
$sql = <<<'SQL'
CREATE TABLE products (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
code VARCHAR(30) NOT NULL,
name VARCHAR(120) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY uq_products_code (code),
CONSTRAINT chk_products_stock CHECK (stock >= 0),
CONSTRAINT chk_products_price CHECK (price >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
SQL;
$pdo->exec($sql);
echo 'Products table created.';First successful execution in an empty development schema: Products table created.Creating Databases and Tables with PHP FAQ
Should each page request create tables?
No. Running CREATE TABLE on every request wastes time, needs elevated privileges and can fail halfway on busy sites. Use controlled setup or migrations, scripts that run once during deployment and record which schema changes have been applied.
Can placeholders bind table names?
No. Placeholders represent values, not schema structure, so a table or column name can't be bound. If a name genuinely varies, choose it from an allowlist of known names in your code, and never take it directly from user input.
Does IF NOT EXISTS upgrade a table?
No. It only avoids creation when a name exists; it doesn't compare columns or add new ones. Changing an existing table needs ALTER TABLE, ideally in a migration that runs once and is tracked, so every environment ends up with the same structure.
Can rollback undo all MySQL DDL?
No. In MySQL, many statements such as CREATE TABLE, ALTER TABLE and DROP TABLE commit implicitly, ending any open transaction. Plan schema changes so a failure can be repaired, and take a backup before risky migrations.
Creating Databases and Tables with PHP Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
An old table needs a new column. What is the correct migration approach?
Creating Databases and Tables with PHP Quiz
2 questions to check what you learned.
What is the right place for schema creation?
- Every anonymous page load
- A browser cookie
- An unprotected upload filename
- A controlled setup or migration procedure
What does IF NOT EXISTS guarantee?
- Creation is skipped when the named object already exists
- The existing schema is correct
- All data is backed up
- Every constraint is enforced on all versions