PDO creating database and tables

Question

I'm currently struggling with an assignment. I am to create a PHP script that will create a database and all the tables for that database. I have been able to cobble together the script to create the database itself from reading here and W3Schools, however I am stumped as to how to have the same script create tables on that new database. Here's what I have to create a new database:

<?php
$servername = "localhost";
$username = "root";
$password = "mysql";

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);
echo "DB created successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

I tried to follow on that to then create tables with this:

<?php
$servername = "localhost";
$username = "root";
$password = "mysql";

try {
$conn = new PDO("mysql:host=$servername", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE DATABASE IF NOT EXISTS musicDB";
$sql = "use musicDB";
$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();
}

$conn = null;
?>

However that is not working and I get the following error: CREATE TABLE IF NOT EXISTS ARTISTS ( ID int(11) AUTO_INCREMENT PRIMARY KEY, artistname varchar(30) NOT NULL) SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected

Basically, how do I tell the script to use the newly created table and then create tables for it? And I know the username and password are showing but this is running on my laptop and will never be anywhere so I'm not worried.

But I can't use dbname=musicdb since I haven't created the db yet. I did try that though but it kicks out and error. Unless I'm not understanding what you're saying, which I wouldn't be surprised. — jadda313, Feb 05 '16 at 20:33
This post should help you http://stackoverflow.com/questions/2583707/can-i-create-a-database-using-pdo-in-php — Jay Blanchard, Feb 05 '16 at 20:35
It's worth to check already available libraries that can help you in such a task eg: http://docs.phinx.org/en/latest/ — manRo, Feb 05 '16 at 21:11

score 9 · Accepted Answer · answered Feb 05 '16 at 20:40

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();
}

I love you, that did it. I didn't realize I had to specifically tell it to execute after each command like that. I just tried it and it created the db and the table with the structure listed. Thank you! Sucks to be a noob lol — jadda313, Feb 05 '16 at 22:46

score 0 · Answer 2 · answered Feb 05 '16 at 20:33

Instead, try wrapping all these statements in a procedure and call that from your code behind like

create procedure usp_createDB
as
begin
CREATE DATABASE IF NOT EXISTS musicDB;
use musicDB;
CREATE TABLE IF NOT EXISTS ARTISTS (
ID int(11) AUTO_INCREMENT PRIMARY KEY,
artistname varchar(30) NOT NULL);
end

PDO creating database and tables

2 Answers2

Linked