Page 1 of 1

Multiple MySQL Statements

Posted: Sat Aug 23, 2003 5:11 pm
by php_wiz_kid
I was wondering if there was any way to run multiple mysql statements through mysql_query(). Here's some code:

Code: Select all

<?php
$mysqlQuery = "DROP TABLE IF EXISTS `" . $dir . "_auth_access`;
CREATE TABLE `" . $dir . "_auth_access` (
  `group_id` mediumint(8) NOT NULL default '0',
  `forum_id` smallint(5) unsigned NOT NULL default '0',
  `auth_view` tinyint(1) NOT NULL default '0',
  `auth_read` tinyint(1) NOT NULL default '0',
  `auth_post` tinyint(1) NOT NULL default '0',
  `auth_reply` tinyint(1) NOT NULL default '0',
  `auth_edit` tinyint(1) NOT NULL default '0',
  `auth_delete` tinyint(1) NOT NULL default '0',
  `auth_sticky` tinyint(1) NOT NULL default '0',
  `auth_announce` tinyint(1) NOT NULL default '0',
  `auth_vote` tinyint(1) NOT NULL default '0',
  `auth_pollcreate` tinyint(1) NOT NULL default '0',
  `auth_attachments` tinyint(1) NOT NULL default '0',
  `auth_mod` tinyint(1) NOT NULL default '0',
  KEY `group_id` (`group_id`),
  KEY `forum_id` (`forum_id`)
) TYPE=MyISAM;";
$mysqlQueryResult = mysql_query($mysqlQuery);
?>
I just get the error
ERROR NUMBER: 1064
ERROR MESSAGE: You have an error in your SQL syntax near ';
CREATE TABLE `rp_auth_access` ( `group_id` mediumint(8) NOT NULL defau' at line 2

I know that the reason is because it deals with multiple statements because I can put 1 statement from above in and it works fine, but if I put 2 then I get the error.

Thanks to anyone that can help.

Posted: Sat Aug 23, 2003 8:43 pm
by BDKR
Well, as you can do this through the mysql client, it won't work the way you want it too. However, I think the newer mysqli client library will allow this. I'm not 100% sure.

Cheers,
BDKR

Posted: Sat Aug 23, 2003 10:48 pm
by php_wiz_kid
So just upgrade to 4.1 and it MIGHT allow multiple quries through PHP?

Posted: Sun Aug 24, 2003 1:00 am
by BDKR
First off, this is an issue with the client library that PHP compiles into itself to speak to the database. Not the database itself. However, the newer client libraries may not be backwards compatible with older databases.

On the other hand, if you're really not familiar with how this works, I'd suggest you just issue two queries and get it over with. Later on, you could come back and figure this out at your leirsure.

Whatever the case may be, you need to do some digging! I'd check on the mysql and PHP-DEV mailing lists and google.

Cheers,
BDKR

Posted: Sun Aug 24, 2003 1:27 am
by php_wiz_kid
Those were just 2 queries out of like 70, literally. That's why I don't want to issue a query for each statement.

Posted: Sun Aug 24, 2003 2:16 am
by BDKR
php_wiz_kid wrote:Those were just 2 queries out of like 70, literally. That's why I don't want to issue a query for each statement.
Well, I have to be honest with you and say that I think you would be better off issuing each of them independently as opposed to a batch.

Why?

What if query 34 of those ~ 70 fails?

Now at this point in time, I have no idea how the mysqli libs will handle that. Will it say that query number 34 chocked, or kick out something (as an error message) useless, if anything at all?

I suggest you put those queries in an array then iterate over that array running a query at each node in the array. To keep your code clean, that array could even be a seperate file with all of the queries.

Anyways, something like this might be good.

Code: Select all

# This is your array of queries
$query_array=file("location of query file');

while(list($k, $v)=each($query_array))
   {
    if($queryObject->doQuery($v)==false)
       { /* do error stuff then break out of loop */ }
   }
Now in that "/* do error stuff */" section, you could write the error out to a file or whatever and have complete control over the situation.

Also, if you're worried about speed, don't, unless you have some old 233 Pentium Pro with 128 megs that is also your web server, dns server, domain controller, and is running on Windows. If you have MySQL tuned reasonably well, it'll kick out some serious numbers. 70 queries is a cake walk. The two databases I built, tuned, and maintained in my last job could do 2100 inserts a second! Now that was after I did some tweaking, but even before that it was kicking out 1200 inserts a second.

I wouldn't worry about it.

Cheers,
BDKR

Posted: Sun Aug 24, 2003 2:22 am
by BDKR
One more option is to create a text file with each of your queries then issue a system command such as...

Code: Select all

mysql your_db < your_queries_file
Just make sure you use a system function that will catch the output in the event that there is an error.
Check the mysql manual online for more information about this.

Cheers,
BDKR

Posted: Sun Aug 24, 2003 12:13 pm
by php_wiz_kid
Well, if you error handle the PHP correctly the error will tell you where the mysql error took place. It'll give you a line number, and a where it went wrong in that line.

I'll give your ideas a try though. Thanks for the input.

Posted: Sun Aug 24, 2003 12:54 pm
by BDKR
php_wiz_kid wrote: It'll give you a line number where it went wrong in that line.
At least by putting the queries in an array, you could more easily get a grip on WHICH query gave up the ghost by making note of the point in the array. Just return that point in your error handling.

A question: How bad of an effect will this have if one of the queries fails part way through? Are the later queries dependent upon the early ones? If so, how do you recover from an error part way through the process?

I'd consider using transactions. MySQL supports it.

Cheers,
BDKR