[RESOLVED] Mysql INSERT multiple rows at once

[juniper747]

I am running the following script to INSERT rows into one table and DELETE rows from another table. However, it is only inserting 1 row, while deleting ALL the rows...

How can I change this so that it inserts ALL the rows and deletes ALL the rows... I am basically moving rows from one table (DELETE) and adding them to another table (INSERT)...

$sql_tag_check = mysql_query("SELECT * FROM potentials WHERE candidate='$mem_email' ") or die (mysql_error());
$num_sql_tags = mysql_num_rows($sql_tag_check);
if($num_sql_tags > 0){
	while($row = mysql_fetch_array($sql_tag_check)){
		$d_id = $row["d_id"];
		$mem_id = $row["mem_id"];
	}
	$sql_d_id_swap = mysql_query("INSERT INTO confirmed (mem_id, d_id, contacts) VALUES('$mem_id', '$d_id', '$id')")  or die (mysql_error());
	$sql_del_temp = mysql_query("DELETE FROM potentials WHERE candidate='$mem_email' ") or die (mysql_error());
}

[Krik]

$ins_qry ="
	INSERT INTO confirmed (mem_id, d_id, contacts)
	VALUES
		('$mem_id', '$d_id', '$id'),
		('$mem_id', '$d_id', '$id'),
		('$mem_id', '$d_id', '$id')
	";

$sql_d_id_swap = mysql_query($ins_qry)  or die (mysql_error());

I hope that is self explanatory.

[bradgrafelman]

First, an explanation of why your code doesn't work. This:

	while($row = mysql_fetch_array($sql_tag_check)){
		$d_id = $row["d_id"];
		$mem_id = $row["mem_id"];
	}

makes no sense, because you're simply overwriting the values of those variables on each iteration of the loop without ever doing anything with the previous values. Thus, the end result is that you only get the values from the last row in the result set. In other words, you might as well get rid of the while() loop altogether and just SELECT that one row instead.

Next, where does $id come from?

Finally, note that Krik's code above is simply going to insert the same data three times in the 'confirmed' table. Is this the desired effect?

[juniper747]

Bradger thanks. You're right, my code worked, I was querying from one table (potentials) and inserting into another (confirmed)... but it was only getting the values from the last row.

I was able to resolve the problem by simply moving the INSERT query into the while loop, so that now, each value gets moved from one table to the next. Then when all that's done, my DELETE function goes and deletes all the values from the potentials table.

[Weedpacket]

The loop isn't necessary

INSERT INTO confirmed (mem_id, d_id, contacts)
	SELECT (mem_id, d_id, $id) FROM potentials
		WHERE candidate='$mem_email'

Assuming $id and $mem_email are given.