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());
}

[abduraooft]

$sql_d_id_swap = mysql_query("INSERT INTO confirmed (mem_id, d_id, contacts) VALUES('$mem_id', '$d_id', '$id')")  or die (mysql_error());

Shouldn't that line be inside the "while loop block" to get repeated?

[tangoforce]

The way its written yes, it should.

However mysql for insertions also allows you to make use of one query for multiple records:

PHP Code:

$sql_d_id_swap = mysql_query("INSERT INTO confirmed (mem_id, d_id, contacts) VALUES('$mem_id', '$d_id', '$id'), ('$mem_id2', '$d_id2', '$id2')")  or die (mysql_error()); 


If the op wanted, they could turn the 'values' into a string which gets concat'd inside the loop and then put into the query.

[Juniper747]

You were right abduraooft, your solution worked... thanks!

[Fou-Lu]

Don't forget as well if you use an updated MySQLi or PDO adapter for the mysql connection you can use prepared statements and bind and commit during the loop. This will improve the performance over each iteration and execute, but will increase the query count unlike the multiple insert.