[SOLVED] I'm doing this the hard way... hoping for some help

[vozzek]

Hi all,

 

I'm populating a mySQL shopping cart database (tbl_cart), and the following code gets called whenever someone clicks the 'Add To Cart' button.  Ideally I want to check the cart for duplicate items, in which case I'd UPDATE the quantity field by one instead of doing an INSERT.

 

The problem:  I have up to five non-required 'options', which could be color, size, etc...  and I need to check to see if these are the same as well.  If they are not, I need to do the INSERT because it would be a whole different item (large instead of small for example).  But if all the options are the same (including the custom text field), I need to do the update.

 

Right now the update works... but only if I add the same thing to the cart (with the same options) consecutively.  If I add a different product number in the middle, my logic doesn't work and an insert happens where I wanted an update.  Here's my code:

 

<?php
// current session id
   $sid = session_id();
   
// initialize and set options (passed from detail page)
   $opt_a = $opt_b = $opt_c = $opt_d = $opt_e = $custom = "";
   if ($option_a) $opt_a = $option_a;
   if ($option_b) $opt_b = $option_b;
   if ($option_c) $opt_c = $option_c;
   if ($option_d) $opt_d = $option_d;
   if ($option_e) $opt_e = $option_e;
   if ($custom_text) $custom = $custom_text;
   
// check if the product is already
// in cart table for this session
// (RecordID was passed as a variable from master to detail)
   $sql = "SELECT *
       FROM tbl_cart
	   WHERE pd_id = $RecordID AND ct_session_id = '$sid'";

   $result = mysql_query($sql) or die(mysql_error());
   if (mysql_num_rows($result) == 0) {
            // put the product in cart table
	    $sql = "INSERT INTO tbl_cart (pd_id, ct_qty, ct_session_id, ct_date, ct_opt_a, ct_opt_b, ct_opt_c, ct_opt_d, ct_opt_e, ct_custom)
			    VALUES ($RecordID, 1, '$sid', NOW(), '$opt_a', '$opt_b', '$opt_c', '$opt_d', '$opt_e', '$custom')";
	    $result = mysql_query($sql) or die(mysql_error());
        } else {
	    // update product quantity in cart table (if not a duplicate)

		$dupflg = 1;
		if ($option_a) {
		    if ($option_a != $row_rs_add_to_cart['ct_opt_a']) $dupflg = 0;
			           }
		if ($option_b) {
		    if ($option_b != $row_rs_add_to_cart['ct_opt_b']) $dupflg = 0;
			           }   
		if ($option_c) {
		    if ($option_c != $row_rs_add_to_cart['ct_opt_c']) $dupflg = 0;
			           }
		if ($option_d) {
		    if ($option_d != $row_rs_add_to_cart['ct_opt_d']) $dupflg = 0;
			           }   
		if ($option_e) {
		    if ($option_e != $row_rs_add_to_cart['ct_opt_e']) $dupflg = 0;
			           }
        if ($custom) {
		    if ($custom != $row_rs_add_to_cart['ct_custom']) $dupflg = 0;
			         }
		if ($dupflg == 1) {		 // duplicate item, so update quantity
	     $sql = "UPDATE tbl_cart 
	            SET ct_qty = ct_qty + 1
			    WHERE ct_session_id = '$sid' AND pd_id = $RecordID";		
     	     $result = mysql_query($sql) or die(mysql_error());	
		                  }
		if ($dupflg == 0) {
		          $sql = "INSERT INTO tbl_cart (pd_id, ct_qty, ct_session_id, ct_date, ct_opt_a, ct_opt_b, ct_opt_c, ct_opt_d, ct_opt_e, ct_custom)
			              VALUES ($RecordID, 1, '$sid', NOW(), '$opt_a', '$opt_b', '$opt_c', '$opt_d', '$opt_e', '$custom')";
	              $result = mysql_query($sql) or die(mysql_error());
		                  }				
               }
?>

 

Hmmm... after re-looking at this, I'm thinking I need a WHILE loop to check the entire database instead of just the first row.  That's the problem, eh?  I'm unsure (php-wise) where I'd need it.  Any help would be greatly appreciated, including advice on how to shorten my code.  I'm sure I butchered this... but I'm just learning.

 

Thanks in advance.

[Barand]

When you do the initial selection, select using the options

 

<?php

$sql = "SELECT *
       FROM tbl_cart
	   WHERE pd_id = $RecordID AND ct_session_id = '$sid'
                       AND ct_opt_a = '$opta'
                       AND ct_opt_b = '$optb'
                       AND ct_opt_c = '$optc'
                       AND ct_opt_d = '$optd'
                       AND ct_opt_e = '$opte'
                       ";
?>

 

If it finds a match, update otherwise insert   

 

[vozzek]

Barand, thanks!  That saves a lot of time and coding.  I wasn't sure I could make those comparisons within the SQL query.

 

It still however, is not updating quantity.  Am I doing the initialization (to null values) correctly?  Because it's never getting to the 'else' portion of the if statement, even when all the parameters are the same.

 

EDIT: Nevermind!  I missed some underscores (opta should be opt_a, etc...)  Thanks again Barand, for showing me the easy way.

 

Here's the updated code:

 

<?php
// current session id
   $sid = session_id();
   
// initialize and set options
   $opt_a = $opt_b = $opt_c = $opt_d = $opt_e = $custom = "";
   if ($option_a) $opt_a = $option_a;
   if ($option_b) $opt_b = $option_b;
   if ($option_c) $opt_c = $option_c;
   if ($option_d) $opt_d = $option_d;
   if ($option_e) $opt_e = $option_e;
   if ($custom_text) $custom = $custom_text;
   
// check if the product is already
// in cart table for this session
// (RecordID was passed as a variable from master to detail)
   $sql = "SELECT *
       FROM tbl_cart
	   WHERE pd_id = $RecordID AND ct_session_id = '$sid'
                       AND ct_opt_a = '$opta'
                       AND ct_opt_b = '$optb'
                       AND ct_opt_c = '$optc'
                       AND ct_opt_d = '$optd'
                       AND ct_opt_e = '$opte'
	       AND ct_custom = '$custom'
                       ";

   $result = mysql_query($sql) or die(mysql_error());
   if (mysql_num_rows($result) == 0) {
            // put the product in cart table
	    $sql = "INSERT INTO tbl_cart (pd_id, ct_qty, ct_session_id, ct_date, ct_opt_a, ct_opt_b, ct_opt_c, ct_opt_d, ct_opt_e, ct_custom)
			    VALUES ($RecordID, 1, '$sid', NOW(), '$opt_a', '$opt_b', '$opt_c', '$opt_d', '$opt_e', '$custom')";
	    $result = mysql_query($sql) or die(mysql_error());
		} else {
	    // update product quantity in cart table
		 $sql = "UPDATE tbl_cart 
	            SET ct_qty = ct_qty + 1
			    WHERE ct_session_id = '$sid' AND pd_id = $RecordID";		
     	     $result = mysql_query($sql) or die(mysql_error());	
		       }
?>

[Barand]

One observation - if the user orders a red one, a blue one and a green one and you find a match on the red one, that update would update all three. You need to update the one you selected