[RESOLVED] Delete entire order record

[buffy]

Hi guys,

Well I thought this was working for me but its not, doh

I have a cart, and when people view it they can delete a product out of the cart.... I am trying to do a statement so that if they have removed all the products then the order record gets deleted.

I have two tables:

1) orders - one record for the entire order containing the customerid_fk and an orderid, plus total price etc
2) orderitem - containing productid_fk, orderid_fk and quantity etc

Here is the code I have to remove the orders record if all the records for the sessions orderid are removed from the orderitem table.

Now i know the row=0 needs to somehow only query the orderitem table... but not sure how.

//previous code, which deletes orderitem from orderitem table

// REMOVE SAME ORDERID RECORD FROM ORDER TABLE IF ORDERITEM IS EMPTY
$sqlcartrem = ("SELECT * FROM orderitem, orders 
    WHERE orderitem.OrderID_FK =  " . $_SESSION['OrderID'] . "
    AND orders.OrderID = " . $_SESSION['OrderID']);

//check results, if no temporary cart items show message
$resultcartrem=@mysql_query($sqlcartrem);

//show message if no results, else show items
$num_rows = @mysql_num_rows($resultcartrem);
if ($num_rows==0){

        //delete order record
        $sqldel = ("DELETE * FROM orders WHERE OrderID = " . $orderid);
        //do query
        $resultdel=@mysql_query($sqldel);
        
               //delete orderitem record - not really needed!
        $sqldel2 = ("DELETE * FROM orderitem WHERE OrderID_FK = " . $orderid);
        //do query
        $resultdel2=@mysql_query($sqldel2);


//code to display remaining items or message saying cart is empty 

A bit of a mess I know o_0

[kows]

after reading the code you posted a few times, I was still not sure what you're actually trying to do.. so, I'll go off of your description of what you're wanting to do. I'll walk through my theory of your cart system, I guess.

when adding an item to a customer's cart, that should start a new order if there is no current order. if there is a current order (held in cookies/session/database by user), then that order is resumed. so, creating an order requires two sql queries:

#creating the order
INSERT INTO orders VALUES ...

#adding an item to the order
INSERT INTO orderitem VALUES ...

now, to delete an item from the order, you need to remove a record from the orderitem table only, and then do a check afterward to see if that order has any items left in it. if not, then you delete the entire order. from then on, if the user puts another item in the cart, the process just restarts itself.

applying this all to code? just the deleting part:

<?php
  //user wants to delete an item
  mysql_query("DELETE FROM orderitem WHERE order='$orderid' AND item='$itemid' LIMIT 1");

  //let's check if this order still has any items in it now, and if not, get rid of it
  $order_check = mysql_query("SELECT * FROM orderitem WHERE order='$orderid'");
  if(mysql_num_rows($order_check) == 0){
    //there are no items in this order. delete it
    mysql_query("DELETE FROM orders WHERE id='$orderid' LIMIT 1");

    //if you are holding an order ID in your session here, erase it?
    unset($_SESSION['orderid']);
  }
?>

hope this was what you were looking for.

[buffy]

//duplicated post

[buffy]

Hiya,

Well that was absolutely perfect, did the trick and you formatted it to my needs so I just needed to change the $var's, big thank you.



I'm not sure if I should put what I'm working on now in the same thread....? Let me know if I need to make a new thread.

My new problem () is when a customer logs in to check on their previous orders... I want it to list the orders by their order id, with each order displaying the products ordered.

Well I thought I could do it a crude way by putting a while loop showing the orders, then putting another while loop inside showing the products.

A number of things went wrong....

1) Shows the order multiple times rather then once. Its showing how many times orderitemid is there rather then orderid.... as orderitemid is listed for each product added... it can repeat four times if four products are there...

2) The second while loop inside, lists all the products, and repeats them in the same area four times (again as there are four products). I hoped by basing this loop on the orderid it wouldn't loop wrong.

So I end up with four order results, rather then one order in the db, each with the products listed four times, rather then one order with four products.

Now I know there must be a better way to do this, if anyone could give me a pointer that would be awesome.


Heres the code I started:

//show summary of orders, with option to select an order for more details
$sql = ("SELECT * FROM orders, orderitem
    WHERE orders.CustomerID_FK = " . $_SESSION['customerid'] . "
    AND orders.OrderStatus = 'Shipped'
    AND orderitem.OrderID_FK = orders.OrderID ORDER BY orders.OrderID DESC");

$result=@mysql_query($sql);

//if no rows show 'no orders' message, else continue and show order items

//show order, orderitem stuff
//while loop to display all orders
while ($row = @mysql_fetch_array($result)) {
      $orderid =  $row['OrderID'];
       //etc

//echo stuff here

//show product info for each order depending on orderid
  $sqlitems = ("SELECT * FROM orderitem, product, orders
    WHERE orders.CustomerID_FK = " . $_SESSION['customerid'] . "
    AND orderitem.OrderID_FK = $orderid
    AND orderitem.ProductID_FK = product.ProductID");

$resultitems=@mysql_query($sqlitems);

//while loop to show product stuff
while ($row = @mysql_fetch_array($resultitems)) {
    $quantity  =  $row['OrderItemQuantity'];
    $productid = $row['ProductID'];
        //etc

//echo stuff here


 } //end second while
} //end first while 

[kows]

I'm not sure why you're building your SQL queries the way you are, but I'm thinking you don't fully understand the concept of joining tables ;) in the description you gave, the first WHILE loop should return all of the customer's orders -- and that's all. your query is doing something completely different. a customer's order has NOTHING to do with the items inside of that order (table-wise), they are completely separate. you should be able to use a left join in your second SQL query to combine the product/orderitem tables, but it looks like you're going about it the wrong way.

<?php
  $sql = "SELECT * FROM orders WHERE OrderStatus='Shipped' AND CustomerID_FK='{$_SESSION['customerid']}' ORDER by OrderID DESC";
  $order_query = mysql_query($sql);
  while($orders = mysql_fetch_array($order_query)){
    //orders
    $orderid = $orders['OrderID'];

    $sql = "SELECT * FROM orderitem LEFT JOIN product ON orderitem.ProductID_FK=product.ProductID WHERE orderitem.OrderID_FK='$orderid' ORDER BY product.ProductID";
    $query_item = mysql_query($sql);
    while($items = mysql_fetch_array($query_item)){
      //items
    }
  }
?>

edit: more descriptive post, no code changes

[buffy]

Yes you are completely right

I can't wrap my head around JOINS and am generally just trying to get my new site -based off an old basic one- more robust. It needs things I haven't done before so this is where I'm getting stuck.

I look up examples, formatting ones help but for things like this they don't help too much as they are generally generic. Well that and the fact that my skill level is pretty low

I tried your example, and after some time editing (took me awhile to realise I should be substituting $row for $orders/$item in the while loop as per the example, instead of just changing the two lines to $row - duh) and moving around of the while loop into my nested tables it ... of course ... worked a dream.

Thanks a lot for taking the time to make it suit what i was after, not sure if you just pulled that out of your head but your logic is spot on.

Do you, or someone else know if there is a better way to do this?

I have a *working* "page 1 of 4" for example display going on so if a customer views their orders, it shows 5 per page, with the rest on new pages.

Its working fine, but to get it to work I have to query the db twice... does this put strain on the system? As I use the same code on all my product pages and don't want to be doing it the wrong way. I would hate for it to crash the site the first week it goes live!

My query, may look familiar :P

//limit results per page
// If current page number, use it. if not, set one!
$page = $_GET['page'];
if ($page<1) {
$page = 1;
}
// Figure out the limit for the query based on the current page number.
$limit = 5; // adjust to suit # to display
$from = $page * $limit - $limit;

//get number of results for limit variable
$pagecount =  @mysql_query("SELECT * FROM orders WHERE OrderStatus!='Shipped' AND CustomerID_FK='{$_SESSION['customerid']}' ORDER by OrderID DESC");

//set total number of pages and limit for from/limit query
$total_results = @mysql_num_rows($pagecount);
$total_pages = ceil($total_results / $limit);

$sql = "SELECT * FROM orders WHERE OrderStatus!='Shipped' AND CustomerID_FK='{$_SESSION['customerid']}' ORDER by OrderID DESC LIMIT $from, $limit";

//show query

//show page numbers 
for ($i=1; $i<=$total_pages; $i++) 
{ 
   echo "<a href='". $_SERVER['PHP_SELF']. "?AllOrders=yes&page=". $i."'>".$i."</a>"; 
}; 

[kows]

On if I had "pulled it out of my head": I actually had done something similar on a project in the past and simply took that idea and applied it to your situation.

However, to get to the point -- I don't know of a better way of doing it (with one query instead of two, that is). I've done a few projects that seem somewhat similar to whatever you're working on, and I've looked around, but I could never find a good solution. Of course, using two queries instead of one is going to produce more strain on your server. But, at the very least you have very simple queries which take very little time to execute, whereas complex queries would take much longer and could produce a large server load.

[buffy]

Ok then, sounds good to me, will leave it as is. Going to look at some past threads on here for security stuff to make sure im striping code out in all the places i should be etc... but the end is in sight Need to tackle that file upload again ... that ones for "later".

Thanks again, system won't let me give more rep so am sending you some good vibes instead.