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
– jadda313, Commented Feb 5, 2016 at 20:33
This post should help you stackoverflow.com/questions/2583707/… — Jay Blanchard
– Jay Blanchard, Commented Feb 5, 2016 at 20:35
It's worth to check already available libraries that can help you in such a task eg: docs.phinx.org/en/latest — manRo
– manRo, Commented Feb 5, 2016 at 21:11

Barmar · Accepted Answer · 2016-02-05 20:40:55Z

9

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

answered Feb 5, 2016 at 20:40

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jadda313 Over a year ago

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

Rahul · Accepted Answer · 2016-02-05 20:33:29Z

0

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

answered Feb 5, 2016 at 20:33

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

PDO creating database and tables

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related