[RESOLVED] Update cart quantity for same items

[buffy]

Hi,

Currently I have a situation where:

If someone adds and item to the cart with say a quantity of 2, then adds the same product again with a quantity of 2 (or whatever quantity), the cart shows the same item listed twice.

So I need to:

1) Check the table for whether that product exists
2) If it does, update the quantity with the old + new
3) Update the total for the orderitem

I'm assuming this will be using COUNT on the product page to determine if its there already?

I have some code on my product pages, when someone clicks "add to cart" it reloads the page and passes the productid. The following code checks if an order exists and creates one, or if one exists it adds the item to the order.

It was done a long time ago, so if its not efficient let me know as I'd be happy to change it :)

Example code added.

Code to add item:

// check to make sure there is a DB record
$sql="SELECT count(*) thecount FROM table
WHERE ID = " . $_SESSION['ID'];
$result=@mysql_query($sql);
$row=@mysql_fetch_row($result);
if ($row[0]==0) {
// if not create it
$sqlcreate="INSERT INTO ..."
$resultcreate=@mysql_query($sqlcreate);
// get new OrderID and save in $_SESSION['ID']
$_SESSION['ID'] = mysql_insert_id();
}

} else {
$sqlcreate2="INSERT INTO ..."
$resultcreate2=@mysql_query($sqlcreate2);
// get new ID and save in $_SESSION['ID']
$_SESSION['ID']=mysql_insert_id();
}

//get price and quantity variables via POST
//calculate totalprice of item
//insert orderid, quantity, price, product id and totalprice into orderitem table


Looking at it after all this time and seeing the else is the same as the if, its weird.

I also have some other questions:

- when do most people change the stock level, when an order is sent? then if not paid, admin cancels the order and it updates the stock level?
- do you put javascript or something in to check the quantity they add isn't greater then the amount in the db?
and
- I have some javascript to open a new popup window showing the enlarged image, if they click back on the main page the popup goes to the back. If they then enlarge another image, it loads in the old popup window but its still behind the main window. Anyway to force it to the front?


Thanks for you help!

[kows]

for the javascript stuff, you should be able to force it to focus using the focus() function on the window you're creating. you should be naming the window, then when clicking on the link you should be able to add something like:
<a href="#" onclick="new_window('window_name','image.jpg'); window_name.focus(); return false;">click here</a>
edit: found a function so that you can do it more elegantly here (http://javascript-array.com/scripts/window_open/).

I would check quantities with PHP, I don't see why you couldn't.

and lastly, as far as stock/inventory stuff goes, I'm not sure what most people do. I think if someone orders an item and there is enough inventory to fill that order, that quantity of items should be reserved for that person (first come, first serve). when the order is canceled, it can simply place that quantity back into the inventory.

now, to the PHP.
you have mostly the right idea, I'd probably do it like this:
<?php
//see if we need to add to cart
if($_REQUEST['productid'] > 0){
//check if the order exists already
//don't use count() because we actually want the quantity
$sql = "SELECT quantity FROM orders WHERE OrderID='{$_SESSION['OrderID']}' LIMIT 1";
$query = mysql_query($sql);
if(mysql_num_rows($query) == 0){
//insert
$sql = "INSERT INTO orders (fields) VALUES ......";
echo 'added new order';
}else{
//add up our new quantity
extract(mysql_fetch_assoc($query));
$newquantity = $_SESSION['quantity'] + $quantity;
//update
$sql = "UPDATE orders SET quantity='$newquantity' WHERE OrderID='{$_SESSION['OrderID']}' LIMIT 1";
echo 'updated quantity';
}
mysql_query($sql);
$oid = mysql_insert_id(); //will return 0 if we updated
//if we inserted a record, update our session variable
$_SESSION['OrderID'] = ($oid > 0) ? $oid : $_SESSION['OrderID'];
}
?>

edit: added mysql_insert_id() call and rearranged some things.

[buffy]

Hi kows :)

The sample javascript examples were great... ended up putting it into an include since I use it on every product page.

Am having a go at the cart/quantity part at the moment. Just getting an error for the "if(mysql_num_rows($query) == 0){" below:



// check to make sure there is a DB record
$sqlcount="SELECT ... "
$resultcount=@mysql_query($sqlcount);
$row=@mysql_fetch_row($resultcount);
if ($row[0]==0) {
// if not create it - is insert into Orders (OrderID, etc) values ();
$sqlcreate="INSERT INTO ... "
$resultcreate=@mysql_query($sqlcreate);
// get new OrderID and save in $_SESSION['OrderID']
$_SESSION['OrderID'] = mysql_insert_id();
}

} else {
$sqlcreate2="INSERT INTO ... "
$resultcreate2=@mysql_query($sqlcreate2);
// get new OrderID and save in $_SESSION['OrderID']
$_SESSION['OrderID']=mysql_insert_id();
}

//get variables

//see if we need to add to cart
$sql = "SELECT ... "
$query = mysql_query($sql);
if(mysql_num_rows($query) == 0){
//insert item details into order
$sqlinsert = "INSERT INTO table VALUES id, price, etc... "
echo 'added new order';
}else{
//add up our new quantity
extract(mysql_fetch_assoc($query));
$newquantity = $_SESSION['quantity'] + $quantity;
//update exisiting orderitem record
$sqlinsert = "UPDATE table VALUES qantity... "
echo 'updated quantity';
}
mysql_query($sqlinsert);
$oid = mysql_insert_id(); //will return 0 if we updated
//if we inserted a record, update our session variable
$_SESSION['OrderID'] = ($oid > 0) ? $oid : $_SESSION['OrderID'];


I googled this and it said there could be a connection error, which there shouldn't be, or the syntax is wrong which it shouldn't be either. I copied and pasted the table and feild name - so the query should be okay... i'll try do some more debugging.

Another problem is... I am still using the original example I posted first, as it:
1) checks if an order is in the order table
2) if there isn't, it creates one

Then I got the post variables for price, quantity etc and put in your code (to check if same orderitem exists).

One of the problems I'm having with my code is that I realised if I clear the auth sessions and add to a cart that previously had items in it, my current code doesn't work as the authenticated session is gone. So it creates a new orderid and order which isn't ideal.

Is this okay? I checked out a clothing order site and after emptying my auth session the cart was still populated so im guessing its not the norm ;)

Thanks again

[kows]

first, after looking through your code, I see you're using mysql_escape_string(). you should stop using this function as it's now deprecated. if you're not using the mysqli extension (or any other), then use mysql_real_escape_string() instead.

second, what error are you getting? is it an actual error being thrown by PHP, or is it simply not finding the order? if it's the latter, I would suggest printing out $sql so that you can see if your $_SESSION['OrderID'] is being put into the query or not. if it is, copy and paste it and run it in the MySQL console (or through phpMyAdmin) and make sure that it's actually getting a result?

third, regarding your authentication session -- why exactly are you clearing it? if you do clear your session, then all of your session variables will be destroyed (that's how sessions work). if you wish to preserve some of the session (like your cart), only unset the parts you no longer want. I can only assume that you're talking about being logged into a website, creating a cart, then logging out, and having the cart you created in that session still be accessible? correct me if I'm wrong!

and finally, fourth, the code. (forgive this random-thinking-ramble) the sample in my last post was basically a rewrite of what you had in the first post (of my thinking of what you wanted to do). from what I can tell, the original code you posted only ever creates an order (and doesn't update it?), and both of your insert queries are exactly the same. my code should do the same thing (I think) -- if the order exists in the database, then you update it (using an UPDATE query); otherwise, you create a new order (using an INSERT query). if what I posted won't work for you, I'll probably need a better understanding of your table design (I'm just trying to decipher how you're doing this)? since it seems like you're using what I posted for something a little bit different. what I don't understand is what OrderID_FK and ProductID_FK are (since in your WHERE clauses you refer to OrderID).

I won't bother going on any more until you give me a bit more information @_@.

[buffy]

Hi kows,

Am still working on this, just wanted to give you an update. Warning, its a bit of a mess. In answer to your reply:

1) Have changed it back to mysql_real_escape_string, it gave me an error once upon a time bit is all good now.

2) Error was:
mysql_num_rows(): supplied argument is not a valid MySQL result resource in ...
So I changed the code to:
$resultquantity = mysql_query($sqlquantity);
$row=@mysql_fetch_row($resultquantity);
if ($row[0]==0) {


3) Session error. Don't worry about this, sorry I cleared my browsers authenticated sessions to try and fake a senario where a customer was returning after logging out, I wanted to make sure it wasn't creating a new orderid if an order already exisited. But I'll focus on what we are doing now and park that for later.

4) The code. Yes I repeated the insert query.... can't remember now if that was what i had to do to get it to work or whether i was covering all bases. Problem I have is when I remove it, I can't get the orderid from a previous unsaved order to use if there is one. This will hopefully make more sense as you read on.

My tables:

orders:
Contains and overview of the order, e.g. orderid, customerid_fk, order date, total price, order shipping details, etc

orderitem:
Contains the details of the order, e.g. orderitemid, orderid_fk (to link it to the orders table), productid_fk, quantity, price, totalprice etc

I will try and list what I am trying to get the code to do:

a) Product page lists items, customer clicks add to basket, page reloads and passes the productid

b) if a productid is passed, then (this is where my logic isn't good)
- check to see if an orderid variable is stored
-- if yes, don't do anything (but need to get a orderid to add new items to if customer is returning and a order exists)
-- if no, create a new orders record, add the customers id, or a '0' id for guest. Assign orderid session variable

c) get the posted quantity and price. Calculate for the total

d) run query to check if the passed productid already exists in the orderitem table for the same order
- if yes, update the quantity of that orderitem
- if no, insert a new record into the orderitem table, with the orderid session variable as the orderid_fk, and productid, quantity, price and total


I'm pretty confused at the moment, as you probably are, reading the first if/then I have to see whether or not to create an order.
My original code (plus some extra bits), did work to create and order, insert orderitem details, and the customer details (or 0 for guest, until they logged in then it updated the order with the logged in customerid, this works).

But I know its prob not the best, it needs to be solid so that if the customer does something unusual it doesn't create a error (like creating a new order when there already is a unsaved one, or not adding items to the correct order). Plus i need to do this quantity check thing....

I am still doing some testing at the moment, just wanted to answer your post.

[kows]

edit: deleted my post, since after looking at your queries more I understood what you were doing better. I had originally thought you were doing this crazily. it makes more sense now. so, let me get down to business.

alright, so, I'm pretty sure I understand what you're trying to do here now. I'm starting from scratch (using my own variables/etc to make it easier):
<?php
if(isset($_REQUEST['productid'], $_REQUEST['quantity']) && is_numeric($_REQUEST['productid']) && is_numeric($_REQUEST['quantity'])){
//product id is set, check if there is currently an order for this person
if(isset($_SESSION['orderid']) && $_SESSION['orderid'] > 0){
//already has an order, so we either add or update the order

//first check if this product exists on this person's order
extract(mysql_fetch_assoc(mysql_query("SELECT COUNT(*) as num FROM orderitem WHERE order_id='{$_SESSION['orderid']}' AND product_id='{$_REQUEST['productid']}' LIMIT 1")));
if($num > 0){
//this item DOES exist on this person's order, so we increase its quantity.
$order_sql = "UPDATE orderitem SET quantity=quantity+{$_REQUEST['quantity']} WHERE order_id='{$_SESSION['orderid']}' AND product_id='{$_REQUEST['productid']}' LIMIT 1";
echo 'updated quantity of your existing order';
}else{
//this item does NOT exist on this person's order, so we need to get product information and insert a new orderitem
//getting product information (you will most likely need to change this, no idea how you store it)
extract(mysql_fetch_assoc(mysql_query("SELECT unitprice FROM products WHERE product_id='{$_REQUEST['productid']}' LIMIT 1")));
if($unitprice > 0){
//insert a new order
$order_sql = "INSERT INTO orderitem (order_id, product_id, quantity, unitprice) VALUES('{$_SESSION['orderid']}', '{$_REQUEST['productid']}', '{$_REQUEST['quantity']}', '$unitprice')";
echo 'added a new item to an existing order';
}else{
echo "that product doesn't exist!";
}
}
}else{
//new user, new order
//first, we create a new order
//again, no idea how you save this, but I thought I read you saved their session id?
$customer_session = session_id();
mysql_query("INSERT INTO orders (customer_id, customer_session) VALUES('{$_SESSION['customerid']}', '{$customer_session}')");
//get that order id
$_SESSION['orderid'] = mysql_insert_id();
//get product information
extract(mysql_fetch_assoc(mysql_query("SELECT unitprice FROM products WHERE product_id='{$_REQUEST['productid']}' LIMIT 1")));
if($unitprice > 0){
//now we add this item to the order
$order_sql = "INSERT INTO orderitem (order_id, product_id, quantity, unitprice) VALUES('{$_SESSION['orderid']}', '{$_REQUEST['productid']}', '{$_REQUEST{'quantity']}', '$unitprice')";
echo 'added a new order';
}else{
echo "that product doesn't exist!";
}
}
//we're done.
mysql_query($order_sql);
}
?>
now, the only thing I'd change about this after finishing it, is that I reiterate two queries to the database to see if the product exists (though only if the item doesn't already exist on an order). I would argue that it might be better to check if the product exists first, then to do all of the order checking. it'd be a simple rearrangement, and would maybe just save on maintaining the code. it would provide the same results for any case, though if the order existed and the item existed on that order, it would require one more query that was not beneficial. hope you're following what I have to say about that @_@ it's really not a huge deal, though..

anyway! let me know if that's what you were actually looking for.

[buffy]

Hi kows,

I have edited your code, which helped me a lot.

Theres a couple of issues, but these might be happening my side so I won't go into them yet. At the moment my showcart page isn't showing the items, but I know they are there as they are in the db.

Can you please take a look at the below. My old method of adding orders shows up in the cart, but maybe the new method requires the showcart code to be altered... At the moment it is giving me the cart is empty message just after if ($num_rows==0){.

Note: OrderStatus is 'unsaved' by default for a new order.


//show items based on customer id if logged in
if ($_SESSION['loggedin'] == true)
{
//sql bsed on customer id
$sqlcart ...

} else {
//else show items based on order id - For guests
$sqlcart...

} //end loggedin if

//check results, if no temporary cart items show message

//show message if no results, else show items

if ($num_rows==0){
//show cart empty message
} else {
//while loop to show items
}

[kows]

if $num_rows is coming up as 0, it has to do with your query (whether it needs to be modified, or it's just invalid, I'm not sure of). echo out $sqlcart so that you can see exactly what PHP is interpreting your query as, and make sure your variables are in the right places (and are actually set). if you don't notice any immediate problems, then copy and paste the query and run it yourself through phpMyAdmin or the MySQL console.

also, you should stop suppressing errors with "@"! with the functions you're using, you should -never- have an error (unless your query has an error, which it shouldn't either), so these errors will help tell you what's wrong while you're in development. even while not in development, I advise you to properly check inputs before queries/etc to make sure that you won't be getting any errors, than outright suppressing them.

as far as problems with your code go, everything in the first little bit looks fine. I don't have access to your table, so I can't run those queries to make sure they're doing what they should be.

not sure if you wanted me to look over the second part, though I did and with a quick skim through I didn't see anything actually wrong (you said you weren't going to get into your problems with that, anyway). I did see that in the "update quantity" query, you queried the table for the current quantity, and then defined $newquantity to use it but didn't really have any need to do so. you can simply let MySQL do the math for you, rather than PHP. you always want to try to query the database as little as possible. I've updated your query below (and as a general rule, I always try to put brackets around math in queries, or single quotes around every value. I think it makes it easier to decipher):
UPDATE orderitem SET OrderItemQuantity=(OrderItemQuantity + {$_REQUEST['quantity']}), OrderItemTotal=((OrderItemQuantity+{$_REQUEST['quantity']})*$pricep) WHERE OrderID_FK= ...

feel free to post any other problems with the code I gave you.


edit: oh, I just realized that since you're updating OrderItemQuantity, the update might take place before the update to OrderItemTotal, and that update might take the new quantity into account. this will screw the whole OrderItemTotal value up, so if you're noticing that this is happening, placing the update to OrderItemTotal before the OrderItemQuantity update might fix the problem. it all depends on how MySQL handles this, and if it keeps a cache of the unchanged values while doing an update. hope that made sense.

[buffy]

Hi kows,

Removed the @, got a $result error, tried it in PHP Admin using a number instead of the order session variable and it was fine.

Echo'ed out the order session variable and there was nothing stored.... checked the other page and it was a case problem, orderid vs OrderID .... doh.

Just to confirm, I can use $OrderID and Session['OrderID'] seperately as they are two different var's yes?

Also regarding querying the database as little as possible, do I need to have:

extract(mysql_fetch_assoc(mysql_query("SELECT ProductPrice FROM product WHERE ProductID='{$_REQUEST['productid']}' LIMIT 1")));
if($ProductPrice > 0){

If instead I can use the $price that was posted along with the quantity and productid? Could I use this variable instead of $ProductPrice and get rid of the extract query?



So my cart and quantity are now looking pretty good. Which means I can now go back to my other cart issue from earlier o_0

Currently:
A guest comes to the site - add's items to the cart - then logs in.
The order is updated with the customer's id.
If the customer logs out, and comes back later and logs in and goes to the show cart the previously added items are there (which is fine. The query is to display all 'unsaved' orders for that customers id).
But if they add another item the order id is not set anymore since they logged out. So a new order is created for any items added when they log back in.

So I figure I can either:
1) Add a statement on the show cart page, to detect if there is 1 or more orders present for the same customer with a status of unsaved. If so, somehow combine them and delete the customers other 'unsaved' order records. Make sure combined order is the session orderid.
OR
2) Add another check on the product pages, to add it to their previous unsaved order if there is one, and use that order id as the session order id.

I think number 2 is more manageable. Only problem is, if they log out and return as a guest, then I can't do a check for existing orders as I won't have their customer id.

Which leads me to think I would have to do it on the show cart page... does this sound correct?

[kows]

as long as you don't have register_globals on (which is now off by default in PHP, and should never be on anyway -- and might have already been/should be getting removed in future versions), then yes, $OrderID and $_SESSION['OrderID'] are completely different variables.

to your second question, you can definitely use the price that was posted. all you need to be sure of for that situation is that you're not allowing websites with unwelcome referers to post to your website. otherwise, they could maliciously post to your script and get something for free. I usually put something like this in my MySQL connection include:
if($_SERVER['REQUEST_METHOD'] == "POST"){
$referer = parse_url($_SERVER['HTTP_REFERER']);
//redirect people posting to this website via another
if($referer['host'] != "subdomain.mydomain.com" && $referer['host'] != "mydomain.com"){
header("Location: /");
}
}

as for your new problem with returning customers -- I guess it all depends on how you handle unsaved orders. if a guest customer creates an order, then creates an account, the order is now assigned their customer ID. if they then leave and let the session expires, when they come back and log back in, then you either need to assign their $_session['OrderID'] to the very last orderid that they created, or trash unsaved orders periodically with a cron job and assume that if a customer doesn't save an order, that they don't want it? you can get the last order created by a customer by doing a select query and limiting it to 1 result, and sorting it by the orderid (desc).
SELECT OrderID FROM orders WHERE CustomerID='$customerid' ORDER BY OrderID DESC LIMIT 1
as for what you said near the end there, if they log out and return as a guest and begin adding items to their cart but don't login, there's really nothing you can do. the customer will just end up logging in at one point, and then their order should be assigned an ID. when they go to their "order list" or whatever, they'll see a bunch of random old orders.

now, I'm not sure how you are getting the customer to "save" their orders, but if they aren't being saved then I would just scrap them. this might not be ideal for you though, depending on your situation. if you're even thinking about scrapping unsaved orders, then I might suggest not even saving unsaved orders to the database. I would just save all of your current order in the session, and PHP will dispose of all of that information for you when the session expires. otherwise, you will have to keep track of the last time the user did something on your website (so as not to accidentally delete a user's unsaved order who is still creating it) (also, this basically means updating the database on every page load to update that user's lastactivity time in the user table). to scrap them using a cron job and database, you could try doing (hope I got the join correct):
$expiredtime = time() - (60 * 30); //orders without activity in the last 30 minutes
$sql = "DELETE o.*, oi.* FROM users u LEFT JOIN orders o ON u.id=o.customerid LEFT JOIN orderitem oi ON o.id=oi.orderid WHERE o.status='unsaved' AND u.lastactivity<$expiredtime";
I'm really uncertain of the sql, so I'm about to make a bunch of test tables and try it out.

edit: SQL was fine except that apparently you cannot delete using the actual table names, and need to use the aliases. in an article I had read while writing that query, it said you couldn't use aliases for delete. oh well. modified the sql to delete from the alias tables.

[buffy]

Hi

Great, have put the redirect script in my connection file. Removed the subdomain as we don't have one yet, put in a redirect to the index. Tried to test it but it didn't redirect so will double check that later.

For the site, its preferable that the items added to the cart are still there when you log back in, just because thats what most people expect when they log back in. Will have to look into this as far as futurability is concerned, i.e removing the product if it is out of stock. I'll save your temp order code for the future, I'm sure I'll have a use for it. The join code was great too ;)

For guest orders I have a link in the admin section that deletes all temporary guest orders.

I have tried to create some code for the order to do the following scenario:

1) Customer returns to site as a guest after previously adding items to the cart and logging out
2) They add a couple more (generating a new order id) then log in
3) In the log in page the following code:
-- firstly see's if they have a previous 'unsaved' order
--- if they don't, it assigns their customer id to the previous guest order
--- if they do have a previous unsaved order, it retrieves the order id from the order table
---- updates the id of the items in the order table with the retrieved id
---- deletes the session order from the order table
---- assigns the retrieved order id as the new order id

But my code isn't retrieving the order id, instead its set as nothing.

Here is the code:

//if there is a previous unsaved order, combine them
//first check to see if they already have an unsaved order
extract(mysql_fetch_assoc(mysql_query("SELECT COUNT(*) as num FROM $table WHERE field='$example' AND field2='$example' LIMIT 1")));
if($num == 0){
//if no rows, insert customerid into new order
$sqlid = ("UPDATE $table SET field ... ");
$resultid=mysql_query($sqlid);
} else {
$sqlgetid = ("SELECT $table FROM field WHERE ... ORDER BY field DESC LIMIT 1");
//store exisiting id
//update items
$sqlupdate = ("UPDATE ... ");
$resultupdate=mysql_query($sqlupdate);
//delete old record
//set existings id
} //end no rows if

[kows]

well, as far as I can tell, in your code $OrderID is never actually defined. you define $sqlgetid as a query, but never query it. I assume you meant to query it, and then define $oldorderid as $OrderID.. but, when you're echoing it, $OrderID and $oldorderid would come up as being the exact same; so I hope you know that's going to happen.

either way, querying it should solve your problem for now, I think.

[buffy]

When you say query, do you mean like:

Example variables only.


//retrieve the existing order orderid
$sqlgetid = ("SELECT OrderID FROM $table WHERE OrderStatus='$status' AND CustID=$cid ORDER BY OrderID DESC LIMIT 1");
$resultid = mysql_query($sqlgetid);

[kows]

well, since you were referring to $OrderID, I assumed you were meaning to use an extract() on it.
extract(mysql_fetch_assoc(mysql_query($sqlgetid)));
echo $OrderID;

[buffy]

Changed it to that and did a couple of other things.

Had to extract the db twice, once DESC to get the new order id and once ASC to get the old order id, then combined & deleted the orders. Put it on the cart display page in the end.

So now theres three extracts... the third is at the top to determine whether the combine needs to take place.

Hopefully all these extracts and query's on these pages and the bunch in the quantity code don't make it come to a halt when more then say 10 people use it at a time.

I can see how it would be waaaaaaaaaay easier to force them to log in before they can add stuff to the cart... then I wouldn't need all the extra stuff all over the show just incase they do stuff in a different sequence. But I think that ways not as friendly.

Thanks again kows ... now onto the next thing :)