[SOLVED] Insert multiple SQL queries

[gerkintrigg]

Hi. I'm trying to use the following code: to insert into a MySQL database, and while the resultant code works fine in PhpMyAdmin, it fails when run in PHP...

here it is:

<?php // start the session 
session_start(); 
$root='../../';
$location='';
header("Cache-control: private"); //IE 6 Fix 
include $root.'includes/db.php';
$no_of_rooms=$_POST['number_of_rooms'];
$id=$_REQUEST['id'];
$i=1;
while($i<=$no_of_rooms){
$q.="
INSERT INTO `rooms` ( `name`,`property_id` )
VALUES (
'$i', '$id'
);
";
$i++;
}
echo $q;
if($sql=mysql_query($q)){
echo 'No worries';
}
else { echo mysql_error();
}
?>

any ideas?

 

[adam84]

I am pretty sure, you can only insert one row at a time.

 

Try somthing like this:

$i=1;
$successfull = 0;
$flag = false;

while($i<=$no_of_rooms){
$q.="INSERT INTO `rooms` ( `name`,`property_id` ) VALUES ('$i', '$id');";
  	if( mysql_query($q) ){
   		$successfull++;
}
else { 
	echo mysql_error();
	$flag = true;
	break;
}
   	$i++;
}

if( $successfull > 0 && !$flag ){
echo 'No worries';
}else{ 
echo 'Error, please try again';
}

[php_dave]

you can only insert 1 row at a time but you can execute more that 1 sql statement at a time - and it looks like you are concatinating a number of insert qureries. 

 

What is the error message?

[akitchin]

you can only run one SQL statement for each call to mysql_query().  therefore you'll have to write each one and run it independently, or you could go through the loop, write the statement and add it to an array, then process the array to run through each query.  it's up to you.

 

depending on your engine, you may be able to use a queue and commit each, then try to run them all in the same process (and rollback if it fails).

 

i've also heard mysqli_multi_query() (or something like that) can be used to run multiple statements in one call.

[php_dave]

you can only run one SQL statement for each call to mysql_query().

 

Sorry to hijack the thread - but for my understanding - if this is the case how do sql injections work?

 

for example  SELECT id from user_Table where id = '1'; drop table users;

 

I haven tested but was always under the impression that mysql_query() would handle mulitple queries.

[akitchin]

http://us.php.net/manual/en/function.mysql-query.php

 

as far as i know, SQL injection occurs often by subqueries, wherein one forces the server to perform a separate query as a subquery in the larger, unique query.  keep in mind i'm not an expert on it by far, so hopefully someone else can clarify at some point (provided we answer the OP's question as well).

[gerkintrigg]

I figured as much - bit of a shame but nevermind. It just seems rather messy.

 

Nevermind. Thanks for that.