How to Create a Database Using Pdo in PHP

Can I create a database using PDO in PHP?

Yes, you can.

The dsn part, which is the first parameter of the PDO constructor, does not have to have a database name. You can simply use mysql:host=localhost. Then, given you have the right privilege, you can use regular SQL commands to create a database and users, etc.

Following is an example from an install.php file. It logs in with root, create a database, a user, and grant the user all privilege to the new created database:

<?php

    $host = "localhost";

    $root = "root";
    $root_password = "rootpass";

    $user = 'newuser';
    $pass = 'newpass';
    $db = "newdb";

    try {
        $dbh = new PDO("mysql:host=$host", $root, $root_password);

        $dbh->exec("CREATE DATABASE `$db`;
                CREATE USER '$user'@'localhost' IDENTIFIED BY '$pass';
                GRANT ALL ON `$db`.* TO '$user'@'localhost';
                FLUSH PRIVILEGES;")
        or die(print_r($dbh->errorInfo(), true));

    }
    catch (PDOException $e) {
        die("DB ERROR: " . $e->getMessage());
    }
?>

PDO creating database and tables

You're only executing the last statement. You keep assigning to $sql, but not executing those statements.

try {
    $conn = new PDO("mysql:host=$servername", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "CREATE DATABASE IF NOT EXISTS musicDB";
    $conn->exec($sql);
    $sql = "use musicDB";
    $conn->exec($sql);
    $sql = "CREATE TABLE IF NOT EXISTS ARTISTS (
                ID int(11) AUTO_INCREMENT PRIMARY KEY,
                artistname varchar(30) NOT NULL)";
    $conn->exec($sql);
    echo "DB created successfully";
}
catch(PDOException $e)
{
    echo $sql . "<br>" . $e->getMessage();
}

Creating a new database with php/PDO using a variable as the database name

You can use string concat and remove backticks

"CREATE DATABASE IF NOT EXISTS  " . $db_name . " DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

you can also avoid concat and using var inside quote (this could be useful if you need backticks for allow reserved word)

"CREATE DATABASE IF NOT EXISTS  '$db_name' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

Create Database If Not Exists with PDO

Slightly more sensible and safe code.

$pdo = new PDO("mysql:host=localhost", $dbusername, $dbpassword);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$dbname = "`".str_replace("`","``",$dbname)."`";
$pdo->query("CREATE DATABASE IF NOT EXISTS $dbname");
$pdo->query("use $dbname");

Create database with PDO bindParam

This is working:

<?php
$login = 'root';
$password = 'root';
$dsn = "mysql:host=localhost"; 
$dbb = 'account';
$opt = array(
// any occurring errors wil be thrown as PDOException
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
// an SQL command to execute when connecting
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
);

// Making a new PDO conenction.
$conn = new PDO($dsn, $login, $password,$opt);

$db = $conn->prepare( "CREATE SCHEMA IF NOT EXISTS $dbb "); 
$db->execute();          

?>

i guess you can't prepare the database using bindParam

PDO - CREATE DATABASE IF NOT EXISTS

$conn = new PDO("mysql:host=".$host.", $username, $password);
                ^           ^       ^
                1           2       3

Double-quote 1 starts a string.

Double-quote 2 ends a string.

Double-quote 3 starts a string.

That string is not closed until here:

$sql = "CREATE ...
       ^
       4

Double-quote 4 ends the string.

Which means CREATE is being interpreted as a PHP keyword, not as part of a string.

TIP: You can put variables inside a double-quoted string. This makes it much easier to avoid this type of imbalanced-quote mistake.

$conn = new PDO("mysql:host=$host", $username, $password);