Bhaal Posted February 16, 2007 Share Posted February 16, 2007 I have a simple "add to cart" function that simply adds a record to a "cart" table. But, I'd really like it to check and see if the item being added is already in the table. If it's not in the table, do an insert query; if it's already in the table, do an update query - and update the "quantity" field, by incrementing it by one. Original insert query: if($_POST["submit_x"]) { $MyTotal = $_POST[itemPrice]; $q1 = "insert into cart set OrderID = '$PHPSESSID', ItemID = '$_POST[itemID]', ItemName = '$_POST[itemName]', ItemPrice = '$_POST[itemPrice]', ItemQty = '1', ItemType = '$_POST[itemType]', ItemTotal = '$MyTotal' "; mysql_query($q1) or die(mysql_error()); } Please don't laugh at the following, but here's what I've been trying: if($_POST["submit_x"]) { //get current table contents $sql = "select * from cart where OrderID = '$PHPSESSID' "; $rql = mysql_query($sql) or die(mysql_error()); while($aql = mysql_fetch_array($rql)) { $myItem = $aql[itemID]; $qty = $aql[itemQty]; $newQty = $qty++; $ItemID = '$_POST[itemID]'; if($myItem == $ItemID){ $q1 = "update cart set ItemQty = $newQty where OrderID = '$PHPSESSID' and ItemID = '$ItemID' "; mysql_query($q1) or die(mysql_error()); } else { $MyTotal = $_POST[itemPrice]; $q1 = "insert into cart set OrderID = '$PHPSESSID', ItemID = '$_POST[itemID]', ItemName = '$_POST[itemName]', ItemPrice = '$_POST[itemPrice]', ItemQty = '1', ItemType = '$_POST[itemType]', ItemTotal = '$MyTotal' "; mysql_query($q1) or die(mysql_error()); } } } This obviously doesn't work at all. Any help is very much appreciated... Quote Link to comment Share on other sites More sharing options...
sayedsohail Posted February 16, 2007 Share Posted February 16, 2007 I am not sure this is the problem in your while loop you use $aql do you think it should be $sql. while($aql = mysql_fetch_array($rql)) Any errors, can you please describe your problem, what is happening any error code etc. regards Quote Link to comment Share on other sites More sharing options...
arifsor Posted February 16, 2007 Share Posted February 16, 2007 why you put single quote '$_POST[itemID]' try to remove it $_POST[itemID] Quote Link to comment Share on other sites More sharing options...
Bhaal Posted February 16, 2007 Author Share Posted February 16, 2007 I removed the single quotes around $_POST[itemID] in the line: $ItemID = $_POST[itemID]; - but the result is the sameas before: nothing is saved to the table. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted February 16, 2007 Share Posted February 16, 2007 Try it like this: <?php // Update SQL $sql = "UPDATE ..."; $uq = mysql_query($sql); if(!$uq){ // Query failed, existing record must not exist // Insert SQL $sql = "INSERT INTO ..."; $iq = mysql_query($sql); if(!$iq){ echo "Error: Could not add your item to the cart."; } } ?> Or if you have the proper version of MySQL you can do an INSERT ... UPDATE query. Quote Link to comment Share on other sites More sharing options...
Bhaal Posted February 17, 2007 Author Share Posted February 17, 2007 This is kind of working (thanks, roopurt18!): if($_POST["submit_x"]) { $sql = "select * from cart where OrderID = '$PHPSESSID' "; $rql = mysql_query($sql) or die(mysql_error()); $rows = mysql_num_rows($rql); if($rows == '0') { $MyTotal = $_POST[itemPrice]; $q1 = "insert into cart set OrderID = '$PHPSESSID', ItemID = '$_POST[itemID]', ItemName = '$_POST[itemName]', ItemPrice = '$_POST[itemPrice]', ItemQty = '1', ItemType = '$_POST[itemType]', ItemTotal = '$MyTotal' "; mysql_query($q1) or die(mysql_error()); } else { while($aql = mysql_fetch_array($rql)) { $myItem = $aql[itemID]; $qty = $aql[itemQty]; $newQty = $qty++; $ItemID = $_POST[itemID]; if($myItem == $ItemID) { $q1 = "update cart set ItemQty = $newQty where OrderID = '$PHPSESSID' and ItemID = '$ItemID' "; $uq = mysql_query($q1); } } if(!$uq) { $MyTotal = $_POST[itemPrice]; $q1 = "insert into cart set OrderID = '$PHPSESSID', ItemID = '$_POST[itemID]', ItemName = '$_POST[itemName]', ItemPrice = '$_POST[itemPrice]', ItemQty = '1', ItemType = '$_POST[itemType]', ItemTotal = '$MyTotal' "; mysql_query($q1) or die(mysql_error()); } } } It IS preventing duplicates (YEAH!!!). However - it's not updating the Quantity ($qty). The first part sees if there are records in the cart - if not, do a simple insert. If there are records, it loops through and tries to find dupes. If it finds dupes, it's supposed to do an update. If it doesn't find dupes it's supposed to do the insert. If the item exists, it's supposed to increment quantity by 1, hence the line: $qty = $aql[itemQty]; $newQty = $qty++; My guess is that it's not firing the update portion of the query at all. I can't see the flaw in the logic. Any clues? Thanks again - this is already a huge leap forward. Quote Link to comment Share on other sites More sharing options...
sspoke Posted February 17, 2007 Share Posted February 17, 2007 that ++ thing isn't that good its ok ya.. but its a chance of luck lets say if someone does 2 php interpreter's at some close times like milliseconds 1 php php interpreter does qty++; line and starts going down.. near update query while the other php interpreter is doing the same thing so you got 2 php interpreters near the update query with same qty I would make a function or something in MySQL for that not PHP Quote Link to comment Share on other sites More sharing options...
Bhaal Posted February 17, 2007 Author Share Posted February 17, 2007 Alright sspoke - good advice, but much easier said than done, for a newbie like me at least. "...make a function or something in MySQL for that not PHP..." I don't think I've ever attempted anything like that - is that like a stored procedure in SQLServer? Does MySQL support functions on the database level?? Quote Link to comment Share on other sites More sharing options...
sspoke Posted February 17, 2007 Share Posted February 17, 2007 ya exactly stored procedures than in PHP to SQL you would just call EXEC sp_IncreaseQty '$PHPSESSID' the coding for stored producures is very different to what im used to do so IdK really how it works just know how to call them with EXECUTE CREATE PROCEDURE sp_IncreaseQty ya ??? lol Quote Link to comment Share on other sites More sharing options...
Bhaal Posted February 17, 2007 Author Share Posted February 17, 2007 Well, I still don't understand why the update query portion of this code isn't firing. Any ideas about that? Quote Link to comment Share on other sites More sharing options...
redarrow Posted February 17, 2007 Share Posted February 17, 2007 post your current code please cheers Quote Link to comment Share on other sites More sharing options...
Bhaal Posted February 17, 2007 Author Share Posted February 17, 2007 Code hasn't changed since I last posted it: if($_POST["submit_x"]) { $sql = "select * from cart where OrderID = '$PHPSESSID' "; $rql = mysql_query($sql) or die(mysql_error()); $rows = mysql_num_rows($rql); if($rows == '0') { $MyTotal = $_POST[itemPrice]; $q1 = "insert into cart set OrderID = '$PHPSESSID', ItemID = '$_POST[itemID]', ItemName = '$_POST[itemName]', ItemPrice = '$_POST[itemPrice]', ItemQty = '1', ItemType = '$_POST[itemType]', ItemTotal = '$MyTotal' "; mysql_query($q1) or die(mysql_error()); } else { while($aql = mysql_fetch_array($rql)) { $myItem = $aql[itemID]; $qty = $aql[itemQty]; $newQty = $qty++; $ItemID = $_POST[itemID]; if($myItem == $ItemID) { $q1 = "update cart set ItemQty = $newQty where OrderID = '$PHPSESSID' and ItemID = '$ItemID' "; $uq = mysql_query($q1); } } if(!$uq) { $MyTotal = $_POST[itemPrice]; $q1 = "insert into cart set OrderID = '$PHPSESSID', ItemID = '$_POST[itemID]', ItemName = '$_POST[itemName]', ItemPrice = '$_POST[itemPrice]', ItemQty = '1', ItemType = '$_POST[itemType]', ItemTotal = '$MyTotal' "; mysql_query($q1) or die(mysql_error()); } } } Quote Link to comment Share on other sites More sharing options...
redarrow Posted February 17, 2007 Share Posted February 17, 2007 there u go <?php if($_POST["submit_x"]) { $sql = "select * from cart where OrderID = '$PHPSESSID' "; $rql = mysql_query($sql) or die(mysql_error()); $rows = mysql_num_rows($rql); if($rows == '0') { $MyTotal = $_POST[itemPrice]; $q1 = "insert into cart set OrderID = '$PHPSESSID', ItemID = '$_POST[itemID]', ItemName = '$_POST[itemName]', ItemPrice = '$_POST[itemPrice]', ItemQty = '1', ItemType = '$_POST[itemType]', ItemTotal = '$MyTotal' "; mysql_query($q1) or die(mysql_error()); } else { while($aql = mysql_fetch_array($rql)) { $myItem = $aql[itemID]; $qty = $aql[itemQty]; $newQty = $qty++; $ItemID = $_POST[itemID]; if($myItem == $ItemID) { $q2 = "update cart set ItemQty = $newQty where OrderID = '$PHPSESSID' and ItemID = '$ItemID' "; $uq = mysql_query($q2); } } if(!$uq) { $MyTotal = $_POST[itemPrice]; $q3 = "insert into cart set OrderID = '$PHPSESSID', ItemID = '$_POST[itemID]', ItemName = '$_POST[itemName]', ItemPrice = '$_POST[itemPrice]', ItemQty = '1', ItemType = '$_POST[itemType]', ItemTotal = '$MyTotal' "; mysql_query($q3) or die(mysql_error()); } } } ?> Quote Link to comment Share on other sites More sharing options...
Bhaal Posted February 17, 2007 Author Share Posted February 17, 2007 Thanks redarrow. I made one other change that seems to work - instead of: $newQty = $qty++; I'm using: $newQty = $qty + 1; ...which works in a testing environment... THANKS ALL! I truly appreciate it! (Mark this one: resolved) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.