Using PDO to CREATE TABLE

Using PDO to CREATE TABLE

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

The Silent Failure in CREATE TABLE Logic

The primary issue preventing your script from working lies in how the table definition is being constructed and executed within the PDO context. You are attempting to concatenate a table name directly into a raw SQL string, but the logic surrounding that string construction contains two critical flaws that will stop execution before any data reaches the database engine.

First, look closely at this line:
$createTable = $db->exec("CREATE TABLE IF NOT EXISTS mydb.$table ($columns)");

You are prefixing the table name with mydb. inside the query string. However, your connection configuration already specifies the database:
new PDO("mysql:dbname=mydb;host=localhost", "root", "")

When you connect to a specific database using the DSN (Data Source Name), the default scope of that connection is that database. You do not need to repeat mydb. in the SQL statement. In fact, doing so often causes ambiguity or errors depending on your MySQL server version and configuration, as it attempts to treat mydb as a schema name while you are already connected to it. The correct approach is to simply use the table variable:
CREATE TABLE IF NOT EXISTS $table ($columns)

Secondly, there is a fundamental misunderstanding of what PDO::exec() returns compared to PDO::query(). While both methods return a PDOStatement object when successful, the behavior regarding success and failure is distinct. PDO::exec() is designed for statements that do not return result sets, like INSERT, UPDATE, or DROP. However, it does not automatically return a boolean true or false in a way that you can reliably check against an integer-like truth value without checking the number of rows affected.

If you run CREATE TABLE IF NOT EXISTS on a table that already exists, MySQL does not throw an error; it simply returns 0 affected rows because no new data was inserted or modified. If you then check $createTable in your PHP logic, you might find that the variable holds an object representing zero rows changed. Depending on how strict your PHP version is with type juggling, this can lead to confusion regarding whether the operation succeeded.

To handle this robustly, you should explicitly check the number of affected rows using rowCount(). This method returns an integer: 1 if a new table was created, and 0 if the table already existed. Your logic should look like this:

$sql = "CREATE TABLE IF NOT EXISTS $table ($columns)";
$createTable = $db->exec($sql);

if ($createTable->rowCount() > 0) {
    echo "Table $table - Created!<br><br>";
} else {
    echo "Table $table already exists or creation failed.<br><br>";
}

Handling Dynamic Column Lists

Your current $columns variable relies on string concatenation to build the schema. While this works for simple cases, it is brittle and difficult to maintain. If you add a new column later, you must remember to update the string exactly. Furthermore, string-based SQL building is prone to syntax errors if spacing or commas are misplaced.

A more professional approach involves using an associative array to define columns. This allows PDO's prepare() method to handle the query structure safely and separates data from logic. While CREATE TABLE statements generally cannot use prepared statement placeholders for column definitions (you can't say "Prename ?" because the database needs to know the type), you can still store the column list in an array and implode it. This makes your code self-documenting and easier to extend.

$columns = [
    'ID' => 'INT(11) AUTO_INCREMENT PRIMARY KEY',
    'Prename' => 'VARCHAR(50) NOT NULL',
    'Name' => 'VARCHAR(250) NOT NULL',
    'StreetA' => 'VARCHAR(150) NOT NULL',
    'StreetB' => 'VARCHAR(150) NOT NULL',
    'StreetC' => 'VARCHAR(150) NOT NULL',
    'County' => 'VARCHAR(100) NOT NULL',
    'Postcode' => 'VARCHAR(50) NOT NULL',
    'Country' => 'VARCHAR(50) NOT NULL'
];

$sql = "CREATE TABLE IF NOT EXISTS $table (" . implode(', ', $columns) . ")";

Security Considerations

Finally, be aware that your code is vulnerable to SQL injection if the $table variable contains user input. Even though you are using a connection with a specific database name, allowing dynamic table names via user input bypasses prepared statements entirely. Prepared statements cannot sanitize identifiers like table names or column names. If this script runs in a production environment, ensure that the table name is hardcoded or strictly validated against an allowlist of known tables.

For application-level database management, frameworks like Laravel provide robust migrations that handle schema evolution safely. If you are starting a new project and want to avoid raw PDO logic for schema changes, consider using php artisan make:migration which generates safe, version-controlled migration files. You can find more resources on modern PHP practices at https://laravelcompany.com, where they discuss best practices for database abstraction in enterprise applications.

By correcting the query string construction, verifying row counts instead of relying on boolean execution results, and structuring your column definitions as arrays, you will create a script that is both functional and maintainable.

Our network