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. #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    15,055
    Thanks
    165
    Thanked 2,249 Times in 2,236 Posts
    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)

  3. Users who have thanked abduraooft for this post:

    Juniper747 (01-19-2012)

  4. #3
    Master Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    5,051
    Thanks
    106
    Thanked 591 Times in 578 Posts
    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.
    Quote Originally Posted by deathshadow View Post
    So seriously, loosen up that tie, let out the belt, and try relating to normal people on the street instead of the gentleman's club crowd.

  5. #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!

  6. #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    17,026
    Thanks
    4
    Thanked 2,668 Times in 2,637 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
  •