SQL Create multiple tables at once

Question

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?

Bojangles · Accepted Answer · 2012-01-07 03:53:08Z

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.

This doesn't seem to be working... do I need to swap out MYSQL_QUERY for MYSQL_MULTI_QUERY ?

jcmeloni · Accepted Answer · 2012-01-07 02:36:53Z

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.

Welcome to SO, and thank you for helping fellow coders. Have your first upvote :-)

Abhay · Accepted Answer · 2012-01-07 02:36:02Z

1

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

answered Jan 7, 2012 at 2:36

Abhay

6,6852 gold badges26 silver badges17 bronze badges

1 Comment

Thomas Over a year ago

Where should I change them out? Just at the bottom where it says 'return mysql_query($sql)'?

Collectives™ on Stack Overflow

SQL Create multiple tables at once

3 Answers 3

1 Comment

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Linked

Related