Cannot combine MySQL queries into one?

[Mistrel]

When I try to tell MySQL to create two tables in a single query from php nothing shows up in the DB:


$query = 'CREATE TABLE table1(
cid INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(cid));

CREATE TABLE table2(
cid INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(cid))';
mysql_query($query);

If I split it into two queries it works fine:


$query = 'CREATE TABLE table1(
cid INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(cid))';
mysql_query($query);

$query = 'CREATE TABLE table2(
cid INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(cid))';
mysql_query($query);

Is there something wrong with my syntax? It works fine when I submit the query in phpmyadmin.

[djr33]

Did you try just removing the extra line break? (Between the two queries.)

Since PHP is actually sending that exact code, it must be perfect... phpmyadmin may fix little things like extra line breaks, 'cause it's more user friendly (though PHP offers more control).

[Mistrel]

Yes, I did. MySQL will yell at me if it's not present when I submit the query with phpmyadmin.

[djr33]

Ah. Alright.
Not really sure.

I would think that PHP is able to do more than one at a time, though I'm not sure. Maybe the syntax is different... like ;, or something... but that doesn't really make sense.

sorry.

Someone should know soon.

[thetestingsite]

try outting a semicolon ( ; ) at the end of the secind query. see if that fixes it.

[Mistrel]

A MySQL query cannot end in a semi-colon.

[Twey]

It can.

The PHP mysql_query() is designed to return a recordset that is the result of that query. It doesn't make sense for it to be able to execute more than one query at once.

[djr33]

Ah, that makes sense.

Is there a better function to execute multiple queries (not to retrieve anything, but to execute commands, like creating a table, etc.)?

[Twey]

I don't believe so, no.

[djr33]

Would @mysql_query('ten commands here') work?
It wouldn't return an error and would hopefully send the command through the sql processing engine...

[codeexploiter]

Checkout this article (http://blog.core10.co.uk/2004/10/multiple-sql-queries-using-mysql-and.aspx) that explains about this topic;

[Twey]

Since PHP is actually sending that exact code, it must be perfect...I don't think it does, actually. I may be wrong here (I haven't investigated thoroughly) but I believe the query string is translated into a binary format before being sent to the server.