How do I select a MySQL database to use with PDO in PHP?

How do I select a MySQL database to use with PDO in PHP?

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

Stefan Bogdanescu

Founder & Senior Architect

The PDO::exec Alternative to MySQL's SELECT DATABASE

When migrating from the procedural MySQL API or using the legacy mysqli extension, a common stumbling block arises when you try to select a database within a PDO connection. In mysqli, calling $connection->select_db('database_name') is straightforward and intuitive. However, this method does not exist in PDO. You cannot instantiate a new PDO object and immediately switch contexts to a newly created database using a dedicated selector method.

The missing functionality stems from how PDO handles the connection lifecycle. Unlike mysqli, which maintains a stateful session where you can change the current database schema without re-authenticating, PDO treats the database selection as part of the initial DSN (Data Source Name) configuration or requires an explicit SQL command to switch contexts.

To resolve this, you must execute a SQL statement directly against the connection. The standard approach is to use PDO::exec() with the USE command:

$dbh = new PDO('mysql:host=localhost;dbname=;charset=utf8mb4', 'user', 'pass');

// Execute the USE command to switch context
$dbh->exec("USE database_name");

While this works, it introduces a subtle architectural nuance regarding authentication and privileges. The USE statement changes the "current" database for subsequent queries, but the underlying TCP connection remains authenticated against the MySQL server instance. If your user account lacks CREATE DATABASE permissions on the specific schema you just created, you might encounter permission errors when attempting to use it via exec().

Creating Databases with PDO

Your edit raises a valid point about workflow efficiency: why create a database using one connection and then switch to another (or re-authenticate) to use it? The answer lies in the scope of operations. While creating a database is technically a "rarely used" operation within the context of an application's daily transactional flow, PDO is perfectly capable of handling CREATE DATABASE statements if configured correctly.

You do not need a separate connection just for creation, provided your credentials allow it. However, there is a critical limitation to be aware of: you must include the target database name in the initial DSN if you intend to use prepared statements immediately after connecting without executing a USE command first. More importantly, if you are running on a server where the default user has restricted permissions (common in production environments), you might not be able to create new databases via SQL at all without elevated privileges.

If your goal is to automate the creation of a database and then immediately start querying it within the same script, you can chain these operations:

$dbh = new PDO('mysql:host=localhost;dbname=old_db;charset=utf8mb4', 'user', 'pass');

// Create the new database if it doesn't exist
try {
    $dbh->exec("CREATE DATABASE IF NOT EXISTS database_name");
} catch (PDOException $e) {
    // Handle potential permission errors or duplicate name errors here
    if ($e->getCode() !== 1007 && $e->getCode() !== 1045) {
        throw $e; 
    }
}

// Switch context to the new database
$dbh->exec("USE database_name");

// Now you can query against it
$stmt = $dbh->query("SELECT * FROM users");

The dbname Parameter in DSN

A frequent source of confusion involves the dbname parameter within the PDO constructor. You might assume that providing an empty string or a placeholder is sufficient if you plan to switch later. While USE database_name handles the logical selection, some drivers (specifically older versions of MySQLi-based PDO) behave differently depending on whether a database name is present in the DSN string itself.

If you strictly require the connection to be initialized against the new database immediately, omitting the dbname in the constructor might cause the driver to default to an empty state or throw an error depending on the server configuration. The most robust pattern is to include the intended database name in the DSN string, even if it is temporary, and then rely on the USE command to finalize the context switch.

Security and Privilege Considerations

When executing dynamic SQL to create databases or switch contexts, always validate the database name against a whitelist. Since exec() accepts raw strings, passing user input directly into the USE or CREATE DATABASE statement opens the door to SQL injection vulnerabilities if not sanitized.

$databaseName = filter_var($_GET['db'], FILTER_SANITIZE_STRING);

// Validate against an allowed list before executing
if (!in_array($databaseName, ['database_name', 'allowed_db'])) {
    throw new InvalidArgumentException('Invalid database name');
}

$dbh->exec("USE " . $dbh->quote($databaseName));

By treating database creation and selection as explicit SQL commands rather than abstract method calls, you gain full control over the connection state. This approach ensures compatibility across different MySQL drivers and avoids the limitations of mysqli's session management when porting to PDO.

Our network