Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts

    Mysql INSERT multiple rows at once

    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)...

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

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,858
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Code:
    $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?
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    Juniper747 (01-19-2012)

  • #3
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,332
    Thanks
    60
    Thanked 526 Times in 513 Posts
    Blog Entries
    4
    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.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #4
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts
    You were right abduraooft, your solution worked... thanks!

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •