...

View Full Version : Mysql INSERT multiple rows at once



Juniper747
01-19-2012, 07:14 AM
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
01-19-2012, 08:47 AM
$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
01-19-2012, 01:28 PM
The way its written yes, it should.

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



$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
01-19-2012, 06:56 PM
You were right abduraooft, your solution worked... thanks!

Fou-Lu
01-19-2012, 07:02 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum