CODEWITHSUNDEEP
phpmysqlsql
Thomas
Thomas

Reputation: 5089

SQL Create multiple tables at once

I need to create multiple tables at once. Im having a hard time figuring out the correct method for accomplishing this. Currently my script looks like this:

  private function buildDB() {
    $sql = <<<MySQL_QUERY
CREATE TABLE IF NOT EXISTS headings (
type        VARCHAR(150),
heading     VARCHAR(100),
uniqueid    VARCHAR(100)
)

CREATE TABLE IF NOT EXISTS titles (
type        VARCHAR(150),
heading     VARCHAR(100),
uniqueid    VARCHAR(100)
)
MySQL_QUERY;

    return mysql_query($sql);
  }

Obviously, this doesn't work and no tables are created. Is there a simple way for creating multiple tables at once?

Upvotes: 3

Views: 16496

Answers (3)

Bojangles
Bojangles

Reputation: 101483

MySQL is getting confused because you're not delimiting your queries. Add a semicolon after the first CREATE statement:

private function buildDB() {
    $sql = <<<MySQL_QUERY
        CREATE TABLE IF NOT EXISTS headings (
        type        VARCHAR(150),
        heading     VARCHAR(100),
        uniqueid    VARCHAR(100)
        );

        CREATE TABLE IF NOT EXISTS titles (
        type        VARCHAR(150),
        heading     VARCHAR(100),
        uniqueid    VARCHAR(100)
        )
MySQL_QUERY;

    return mysql_query($sql);
}

Also, make sure MySQL_QUERY is at the beginning of the line with no other characters, except maybe a semicolon, as per the Heredoc documentation.

Seeing as the above doesn't appear to work, give this code a try:

private function buildDB() {
    $sql1 = "CREATE TABLE IF NOT EXISTS headings (
        type        VARCHAR(150),
        heading     VARCHAR(100),
        uniqueid    VARCHAR(100))";

    $sql2 = "CREATE TABLE IF NOT EXISTS titles (
        type        VARCHAR(150),
        heading     VARCHAR(100),
        uniqueid    VARCHAR(100))";
MySQL_QUERY;

    return mysql_query($sql1) && mysql_query($sql2);
}

You could use mysqli_multi_query() (the MySQL version doesn't exist), but you'd have to use MySQLi then. The above code returns the logical AND of the two queries, so you still get a 0 returned if one fails.

Upvotes: 7

jcmeloni
jcmeloni

Reputation: 1234

You can issue multiple queries as long as they are properly delimited. When issuing multiple queries, change this:

CREATE TABLE IF NOT EXISTS headings (
type        VARCHAR(150),
heading     VARCHAR(100),
uniqueid    VARCHAR(100)
)

CREATE TABLE IF NOT EXISTS titles (
type        VARCHAR(150),
heading     VARCHAR(100),
uniqueid    VARCHAR(100)
)

to 

CREATE TABLE IF NOT EXISTS headings (
type        VARCHAR(150),
heading     VARCHAR(100),
uniqueid    VARCHAR(100)
); // added a friendly semicolon

CREATE TABLE IF NOT EXISTS titles (
type        VARCHAR(150),
heading     VARCHAR(100),
uniqueid    VARCHAR(100)
); // added a friendly semicolon

and you should be set.

If not, throw some debugging in there to see exactly where your SQL or surrounding code is unhappy.

Upvotes: 2

Abhay
Abhay

Reputation: 6645

It is because mysql_query() can execute only a single query at a time. Please try using mysqli::multi_query() instead (http://php.net/manual/en/mysqli.multi-query.php) but do end your queries with a semi-colon.

The simplest approach however is to directly run the CREATE TABLE statements in your MySQL client

Upvotes: 1

Related Questions