[SOLVED] PHP/MySQL Insert/Delete multiple question

[SocomNegotiator]

Ok well I have a page that brings up items that a user has put in their "cart". There is no limit to how much they can add to it, but these items when added are kept in a pre_order table of the db. Now on this page I want a submit button that will submit all the items they have chosen and put them into the order table of the db and then deleting the stuff from the pre_order table. Here is how that pre_order table works. A user adds a single item...that item gets an id, user_id, item_id, and timestamp...So each item added has its own pre_order id. So I need a way that when the user clicks on the submit order button it will take every pre_order id that has to do with their user_id and transfer it to the order table of the db. Is there a way to do this...? Each item is it's own row...

[discomatt]

Perhaps subqueries might help. Assuming table structure is the same

 

INSERT INTO `orders` (`user_id`, `item_id`, `timestamp`)

( SELECT (`user_id`, `item_id`, `timestamp`) FROM `cart` WHERE `user_id` = $userId )

 

DELETE FROM `cart` WHERE `user_id` = $userId

[craygo]

you can just select the rows and insert them in the loop

 

<?php
$userid = $_POST['userid'];
$sql = "SELECT id, user_id, item_id FROM pre_order WHERE user_id = '$userid'";
$res = mysql_query($sql) or die(mysql_error());
$error = "0";
while($r = mysql_fetch_assoc($res)){
$insert = "INSERT INTO order_db (`user_id`, `item_id`) VALUES ('".$r['user_id']."', '".$r['item_id']."')";
$ires = mysql_query($ires);
  if(!$ires){
  $error .= "Could not move row ".$r['id']." Error: ".mysql_error()."<br />";
  }
// track the id's of the new inserts
$insert_ids[] = mysql_insert_id();
}
if($error){
echo $error;
// remove any rows that HAVE been inserted from the pre_order table
$ids = implode("', '", $insert_ids);
$remove = "DELETE FROM order_db WHERE id IN ('$ids')";
$dres = mysql_query($remove);
  if($dres){
  echo "pre_order items have been removed from order_db";
  } else {
  echo "Could not remove pre_order items from order_db Error: ".mysql_error();
  }
} else {
$pre_del = "DELETE FROM pre_order WHERE user_id = '$userid'";
$pres = mysql_query($pre_del);
  if($pres){
  echo "Pre_order table has been cleared for user $userid";
  } else {
  echo "Could not remove pre_order items for user $userid Error: ".mysql_error();
  }
}
?>

 

Hope I didn't forget anything. Just put this together quick

 

Ray

[discomatt]

My code will save you a mysql_query call.... GO TEAM!

[SocomNegotiator]

you can just select the rows and insert them in the loop

 

<?php
$userid = $_POST['userid'];
$sql = "SELECT id, user_id, item_id FROM pre_order WHERE user_id = '$userid'";
$res = mysql_query($sql) or die(mysql_error());
$error = "0";
while($r = mysql_fetch_assoc($res)){
$insert = "INSERT INTO order_db (`user_id`, `item_id`) VALUES ('".$r['user_id']."', '".$r['item_id']."')";
$ires = mysql_query($ires);
  if(!$ires){
  $error .= "Could not move row ".$r['id']." Error: ".mysql_error()."<br />";
  }
// track the id's of the new inserts
$insert_ids[] = mysql_insert_id();
}
if($error){
echo $error;
// remove any rows that HAVE been inserted from the pre_order table
$ids = implode("', '", $insert_ids);
$remove = "DELETE FROM order_db WHERE id IN ('$ids')";
$dres = mysql_query($remove);
  if($dres){
  echo "pre_order items have been removed from order_db";
  } else {
  echo "Could not remove pre_order items from order_db Error: ".mysql_error();
  }
} else {
$pre_del = "DELETE FROM pre_order WHERE user_id = '$userid'";
$pres = mysql_query($pre_del);
  if($pres){
  echo "Pre_order table has been cleared for user $userid";
  } else {
  echo "Could not remove pre_order items for user $userid Error: ".mysql_error();
  }
}
?>

 

Hope I didn't forget anything. Just put this together quick

 

Ray

 

 

It looks really nice thanks man...however this is the error i get

 

0Could not move row 5 Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (id, user_id, item_id, amount) VALUES (5, 8, 1, 4)' at line 1
Could not move row 6 Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (id, user_id, item_id, amount) VALUES (6, 8, 4, 5)' at line 1
Could not move row 9 Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (id, user_id, item_id, amount) VALUES (9, 8, 7, 12)' at line 1
Could not remove pre_order items from order_db Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order WHERE id IN ('0', '0', '0')' at line 1

[discomatt]

Make sure you don't copy over the cart's auto increment column.

[SocomNegotiator]

Make sure you don't copy over the cart's auto increment column.

 

Why is that...?

[trq]

Make sure you don't copy over the cart's auto increment column.

 

Why is that...?

 

Because you can't.

[craygo]

the problem is you named your table order. Order is a reserved word in mysql so you have to surround the table name with ticks.

 

$sql = "INSERT INTO `order` (`user_id`, `item_id`) VALUES ('".$r['user_id']."', '".$r['item_id']."')";

 

Same thing goes for other error.

 

Ray