TalkPHP - Multiple mysql queries

TalkPHP (http://www.talkphp.com/forums.php)

- Absolute Beginners (http://www.talkphp.com/absolute-beginners/)

- - Multiple mysql queries (http://www.talkphp.com/absolute-beginners/2129-multiple-mysql-queries.html)

Multiple mysql queries

Its been bugging me on how to do multiple mysql queries without a new mysql_query statement being called each time. This essentially calls a new one on every command, but its much cleaner

PHP Code:


		
			
$query = "INSERT INTO `table` VALUES (bla,bla);
INSERT INTO `another_Table` VALUES (bla,bla);";

$arr= explode( ';', $query );
foreach( $arr as $command )
{
    mysql_query( $command );
}

As long as every command is separated with a semi-colon (which is required in regular mysql), it will execute them all.

I'd run a "rtrim" on every line for cleanliness, but this is essentially what I've done in my database class.

The only way that I know of is if you can use the mysqli library. Check out mysqli_multi_query.

The best thing you can do is simply (for this example) is to rip a MySQL SQL Dump from phpMyAdmin. It has all the characters you need to escape.

Every sentence that starts with '--' is an comment line, and every sentence that starts with a regular command like INSERT, UPDATE, TRUNCATE and ends with a ;, is a query.

To clarify, rtime removes all the whitespace behind each line. So if you (for human readability) want to use enters, that's fine. No problem there. Even TABs are fine.

For as far as I know, there is no way to perform multiple inserts without using a explode command to get rid of all the ... bullshit so to speak. The lines have to be cleaned first.

I tried my own using the foreach loop ($values as $value) or something like that and it worked. Aside of the last rule where it suddenly failed. But that's a bug in my script.

I'm guessing you want to use this for an installation?

Mark

edit,

Bill beat me to it lol, the mysqli way (as I just found out) is possible as well. You can then even import your file using file_get_contents();
Awesome way as well, I didn't know it was possible, after some research.

Quote:

Originally Posted by ReSpawN (Post 9876)

I'm guessing you want to use this for an installation?
.

That is what I built it for, there are over 35 commands taking just over 2 pages. I didn't want to use mysqli, more work then its worth IMO for something like this

It's only a good thing IF you have mysqli, and as we all know servers aren't maintained equally. Mine has no mysqli and no PDO, doubtless other's lack these too--lacking either one, both, or neither. It is a design flaw not to prepare for these possibilities, in my opinion.

I too have used the method, as I said before, without error.

Just a thought, but to split the string by ';' yields preceding whitespace, so the rtrim mention should be an ltrim.