Run multiple CREATE TABLe queries with MySQL and PHP

Question

I need to create a large number of tables at once and add data to the first one using PHP and MySQL. My code looks like this:

$sql = "CREATE TABLE table1 (
code VARCHAR(5) PRIMARY KEY,
name VARCHAR(50) NOT NULL
);";

//adding businesses to table
$sql .= "INSERT INTO table1 (code, name)
VALUES ('CODE', 'name');";
//^this basic idea is run a few more times

$sql .= "CREATE TABLE table2 (
code VARCHAR(5) PRIMARY KEY,
name VARCHAR(50) NOT NULL
);";
//^same basic thing run a bunch more times w/ variations to columns

if ($conn->multi_query($sql) === TRUE) {
echo "<p>Tables created.</p>
<a href=\"adduser_form.php\">Continue</a>";
} else {
    echo "<p>Error creating tables: " . $conn->error . "</p>";
}

This only creates the first table and adds the data to it, it won't create any other tables and I get no error messages (the success message is shown). I've been googling a solution for an hour and I can't come up with anything. I'm thinking I need a way to hold off creating the next table until the previous one has been created?

I'm very new to MySQL so newbie-friendly would be very much appreciated:)

This just tries to create the first table twice (then returns the "table already exists" error). — Maddy, Nov 08 '15 at 21:09
I tried putting in `IF NOT EXISTS` when creating the tables which then makes the query exceed maximum execution time. — Maddy, Nov 08 '15 at 21:11
Nevermind! I was putting it in the wrong place. That worked! Thanks! (If you add it as an answer you can have the points for it, if you don't I will) — Maddy, Nov 09 '15 at 18:49

score 0 · Answer 1 · answered Nov 08 '15 at 07:52

Try using backticks in your SQL statements, such as:

$sql .= "CREATE TABLE `table2` (
    `code` VARCHAR(5) PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL
);";

You should also use backticks in your INSERT statement (and your first CREATE query).

If I'm not mistaken, name is reserved word.

Run multiple CREATE TABLe queries with MySQL and PHP

1 Answers1