Results 1 to 8 of 8
  1. #1

    Multiple Insert Query's Help

    Ok here is the problem, I basically am moving the table data from orders to placed_orders ( it's a shopping cart like system ) I'm doing so by using a for loop and taking the data and reinsterting it into the other table then deleting it from the previous.

    Now the problem, here is my code:
    PHP Code:
    for ($i 0$i $num_results$i++) {
          
    $results $db->fetcharray();
          
    $populate_sql .= "INSERT INTO `placed_orders` (`orderId`,`itemId`,`qty` ,`username`) VALUES ('$order_id','$results[itemId]','$results[qty]','$results[username]');";
        } 
    The problem is the out put of $populate_sql if echo'd works just fine if you copy and paste then run the query in phpMyAdmin

    But if you try and mysql_query($populate_sql) it gives errors all over.

    Can anyone point me to a tutorial that allows you to do multiple insert queries like this or explain what I need to do?

    Thanks
    MSN Use the board link under profile!
    Email me through this board!
    Why? So my email isn't harvested!

  2. #2
    Join Date
    Mar 2004
    Posts
    1,301
    could you try this: ( making sure that your mysql's version is 3.22.5 or later)

    PHP Code:
    $populate_sql ="INSERT INTO `placed_orders` (`orderId`,`itemId`,`qty` ,`username`) VALUES "// leave a space after " VALUES"

    for ($i 0$i $num_results$i++) {
          
    $results $db->fetcharray();
          if(
    $i $num_results -1) {
          
    $populate_sql .="('$order_id','$results[itemId]','$results[qty]','$results[username]'),"// note that there is a comma after the closing parenthesis")"
          
    }
          else { 
    // last insert
          
    $populate_sql .="('$order_id','$results[itemId]','$results[qty]','$results[username]')";// note that there is No comma after the closing parenthesis")"
         
    }
     }

    mysql_query$populate_sql ); 
    the format should be:
    PHP Code:
    $sql "INSERT INTO placed_orders (orderId, itemId, qty, username) 
      VALUES
      ('','1','3','tom' ),
      ('','3','10','john'),
      ('','34','11','kayla')"

    Last edited by orbitz; 04-19-2005 at 03:24 AM.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,715
    Since they're different tables, you could use
    INSERT INTO table (column_list) SELECT...

    that will avoid the whole loop mess entirely. I also see you using $db for the select, but you run mysql_query directly for the insert. That could have some dangerously interesting inconsistencies.
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

  4. #4
    Join Date
    Dec 2003
    Location
    UK
    Posts
    652
    Originally posted by hiryuu
    Since they're different tables, you could use
    INSERT INTO table (column_list) SELECT...

    that will avoid the whole loop mess entirely. I also see you using $db for the select, but you run mysql_query directly for the insert. That could have some dangerously interesting inconsistencies.
    I'd agree with this, there's little point of pulling the data from the database to PHP to then put it back into the database, just move it between tables using mysql, this will be far more efficient and will scale much better.

    Alternatively, why not have a single orders table and then simply have a status flag (ordered, pending, processed, dispatched, rejected, cancelled etc...). Your front end processing will then only have to issue an update to the flag, much less IO.

  5. #5
    the only point is to get a standard orderId number on all of the entries ( so they are all identified as the same orderId ) which is impossible right now since they add items at different times to the 'cart' I know I'm doing it wierd
    MSN Use the board link under profile!
    Email me through this board!
    Why? So my email isn't harvested!

  6. #6
    Join Date
    Dec 2003
    Location
    UK
    Posts
    652
    you could add items (I guess you have a customer ID) with a customer ID and then return all pending items, update them to confirmed order status and update the order ID.

    You need to think about scaleability when you design your system otherwise you'll have a big job later when throughput increases.

  7. #7
    I had encountered this problem too.. This is not allowed. But there is a way to do this by creating array or something.. It is better you use FOREACH statements and execute the query one by one. the disadvantage is, it can cost a lost of queries and take some extra fractions of seconds.. but it will really matter in large sites.

  8. #8
    well I used the method orbitz posted and it worked great

    The thing is I used the database for a cart system rather than crappy sessions which I HATE!!!!

    It all worked out fine and really it goes from the orders cart -> to placed orders

    orders is the temporary for the user and placed_orders for the admin to view all placed orders.
    MSN Use the board link under profile!
    Email me through this board!
    Why? So my email isn't harvested!

Posting Permissions

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