Page 1 of 1

How do I perform multiple inserts ?

Posted: Wed Jun 21, 2006 4:51 am
by freefall
The functionality:
perform 1 insert
retrieve the id created for that row
perform a second set of inserts including that value from the 1st insert.

The problem:
@mysql_query can only be used once on a request....

Any suggestions as to how to do this?

Posted: Wed Jun 21, 2006 5:35 am
by Weirdan
mysql_query can only be used once on a request....
eh... why do you think so?

Posted: Wed Jun 21, 2006 5:44 am
by freefall
Because the page blows up if I try and use it for a second time and:

"If you need to execute sevaral SQL commands in a row (usually called batcg SQL) using PHP you canot use mysql_query() since it can execute single command only."

Dont get me wrong, I'd love to know if it is possible and how :)

Posted: Wed Jun 21, 2006 6:58 am
by Todd_Z
Pseudo Code:

Code: Select all

$sql = "SELECT MAX(id) AS last_id FROM table";
$last_id = $mySQL->get_field_value( $sql, 'id' );

for ( $i=0; $i<ITERATIONS; $i++ ) {

  $sql = "INSERT INTO table SET `field` = 'value', `last_id` = '{$last_id}'";
  $mySQL->execute_query( $sql );
  $last_id = $mySQL->last_inserted_id();

}
Something like that should get the ball rolling.

Posted: Wed Jun 21, 2006 6:58 am
by Todd_Z
By the way - if you need a mySQL class to help you organize this stuff, send me a pm

Posted: Wed Jun 21, 2006 7:14 am
by Weirdan
freefall wrote:Because the page blows up if I try and use it for a second time and:

"If you need to execute sevaral SQL commands in a row (usually called batcg SQL) using PHP you canot use mysql_query() since it can execute single command only."

Dont get me wrong, I'd love to know if it is possible and how :)
mysql_query() can't perform several queries at once, like this:

Code: Select all

mysql_query("select * from table1; select * from table2;");
Yet nothing prevents you from calling mysql_query() several times, like this:

Code: Select all

$res = mysql_query("select id from table1");
while(list($id) = mysql_fetch_row($res)) {
   mysql_query("insert into table2 set id=" . $id);
}

Posted: Wed Jun 21, 2006 11:03 am
by freefall
reet, thx for all the replies.

I'm good for a class thanks and got it working.

Basically, when I had multiple @mysql_query() (only needed 3) I got no error messages returning for anything, jsut a failure. It transpired I'd made a booboo that was unrelated to the insert statements. Once I resolved those it worked again.

Again, thx for all your time.