Having a few problems, please help if you can!

[mongoose00318]

Hello,

 

Alright I am having a few issues here. Let me explain what I am doing...this is a script that allows a customer to split an order..he can split certain items out of one order and it will create another order with the items he split added to this order, and then subtracting the items from the old order. The products are stored in one field called products, they are stored in a delimited list like this qty, product name, price each, product id | qty, product name, price each, product id |etc.

 

Now, before you say why are you storing your products like that?? The reason I have to store my products like so is because the customer does not want the products stored in the order table to be dependent off the products table like they were before. In that case if he modified a product it would modify all of the orders with that product stored in them. Make sense? I am aware a delimited list probably wasn't the best way to do this but it was the best way I could think of.

 

Ok here is my list of issues.

  1) Everytime you split an order, it is not creating a new order to store the items that were split into. It created an order the first time I did it, and now all it does is it keeps updating the products field in that order, rather than creating a new order.

  2) Next I do not know how I can update the old products (stored in the $prod array). It needs to update the products qtys after the order was split, and then if all of the qty for that product were moved to the new order it just needs to remove that product from the $prod array rather than updating the quantity.

 

The code is below. Please let me know if I can provide any more information to help! Thanks everyone!

 

// Get Old Order
$get_order = @mysql_query("SELECT * FROM orders WHERE order_id = {$_POST['order_id']}");
$order = @mysql_fetch_assoc($get_order);

// Get Old Order Items
$products = $order['products'];
//breaking products text down for display
$prod = array();

$_products = explode('|', $products);
foreach ($_products AS $p) 
$prod[] = explode(',', $p);

/*
$get_items = @mysql_query("SELECT product_id, qty FROM order_items WHERE order_id = {$order['order_id']}");
$items = array();
while(($row = @mysql_fetch_assoc($get_items)) !== false) {
  $items[] = $row;
}
*/

if(empty($prod)) {
  header("Location: tracking.php");
  die();
}


// Create New Order
mysql_query("INSERT INTO orders SET customer_id = {$order['customer_id']}, order_status = {$order['order_status']}, order_date = '{$order['order_date']}', order_date_paid = '{$order['order_date_paid']}', order_shipping = '{$order['order_shipping']}', order_shipping_fee = '{$order['order_shipping_fee']}', order_insurance = '{$order['order_insurance']}', order_insurance_fee = '{$order['order_insurance_fee']}', order_insurance_total = '{$order['order_insurance_total']}', order_grand_total = '{$order['order_grand_total']}', order_date = '{$order['order_date']}', order_filled = '{$order['order_filled']}', order_ship_date = '{$order['ship_date']}'");
$get_new_order = @mysql_query("SELECT MAX(order_id) AS order_id FROM orders") or die(mysql_error());
$new_order_id = @mysql_result($get_new_order, 'order_id', 0);


// Add Items to New Order & Remove Items from Old Order
$new_items = array();
$_new_items = '';
foreach($prod as $p2) {
  for($i = 0; $i < $p2[0]; $i++) { 
    if(!empty($_POST[trim($p2[3]).'_'.$i])) {
      $new_items[trim($p2[3])]++;
    }	
  }
}

//construct new static products list
foreach($new_items as $id=>$qty) {
$get_product = mysql_query("SELECT name, price FROM products WHERE product_id = '{$id}'");
$got_product = mysql_fetch_assoc($get_product);
$_new_items .= $qty.','.$got_product['name'].','.$got_product['price'].','.$id.'|';
//echo $id.' - '.$qty.'<br>';
}
//remove last character in products text before going into DB
$_new_items = substr_replace($_new_items ,"",-1);

//update products field in new order
mysql_query("UPDATE orders SET products = '{$_new_items}' WHERE order_id = '{$new_order_id}'");

[mongoose00318]

I figured out how to fix the query. I had some fields in there that were wrong. Does anyone have any suggestions on how to do the 2nd problem? :/ I cant get anywhere with this.