How do I perform multiple inserts ?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
freefall
Forum Commoner
Posts: 48
Joined: Sun Jun 18, 2006 3:55 am

How do I perform multiple inserts ?

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

mysql_query can only be used once on a request....
eh... why do you think so?
freefall
Forum Commoner
Posts: 48
Joined: Sun Jun 18, 2006 3:55 am

Post 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 :)
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

By the way - if you need a mySQL class to help you organize this stuff, send me a pm
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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);
}
freefall
Forum Commoner
Posts: 48
Joined: Sun Jun 18, 2006 3:55 am

Post 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.
Post Reply