Insert Multiple Rows


#1

I’m trying to insert multiple rows into a MySQL db. The code I’ve been using is this:

<?
mysql_connect ('localhost', 'USERNAME', 'PASSWORD') ;
mysql_select_db ('DATABASE');

$sql = "INSERT INTO `php_blog` ( `id` , `timestamp` , `title` , `entry` ) VALUES ( '', '123456789', 'today', 'test' );INSERT INTO `php_blog` ( `id` , `timestamp` , `title` , `entry` ) VALUES ( '', '123456789', 'today', 'test' );INSERT INTO `php_blog` ( `id` , `timestamp` , `title` , `entry` ) VALUES ( '', '123456789', 'today', 'test' );";

$result = mysql_query($sql) or
print ("Can't insert rows into table 'php_blog'.<br />" . $sql . "<br />" . mysql_error());

mysql_close();
?>

And this is the error I get:

Can't insert rows into table 'php_blog'.
INSERT INTO `php_blog` ( `id` , `timestamp` , `title` , `entry` ) VALUES ( '', '123456789', 'today', 'test' );INSERT INTO `php_blog` ( `id` , `timestamp` , `title` , `entry` ) VALUES ( '', '123456789', 'today', 'test' );INSERT INTO `php_blog` ( `id` , `timestamp` , `title` , `entry` ) VALUES ( '', '123456789', 'today', 'test' );
You have an error in your SQL syntax near ';INSERT INTO `php_blog` ( `id` , `timestamp` , `title` , `entry` ) VALUES ( '', ' at line 1

Help?


#2

Beleive it or not that is supposted to do that. It is part of the security features to help prevent SQL injection in your code. They talked about it on the php-general mailing list so you might want to check out the archives.

Insead of trying to do all the insertions at once try a loop or multiple queries.


#3

MySQL has an alternate syntax on insert for that kind of situation.

INSERT INTO table_name (`a`, `b` ) VALUES( 1, 2 ), ( 3, 4 ), ( 5, 6 )