Problem with shopcart code

resident1155 · November 21, 2009

Hello, I am having a bit of trouble being able to add a product to my shopcart. My mysql database is setup correctly with the tables (User, ShopCart, ShopCartLine, Product, Order, OrderLine, etc.).

Right now I am using mode to pass the item number (productID) to the shopcart. In my switch statement i am essentially getting that item number through the variable $action. The problem is my query doesn't work. Another problem is, I am not really sure of the correct way to setup session variables to be equal to the shopcartID in the shopcart table. More specifically, I'm not exactly sure how I can use the session_id() function to implement this. I currently hard coded a userid (from the user table) instead of passing the value through session. That is another issue which I am not concerned with at the moment. I just want to get this shopcart working in a reasonable fashion.

Can somebody give me an idea of what I am doing wrong and or what I need to do to make this work properly? I am really starting to get lost. By the way, I do plan on adding cases to the switch statement for update and delete. Thanks in advance everyone! Here is the code:

<?php include 'db-inc.php'; ?>
<?php include 'header.php'; ?>

<!-- <html>
<head>
  <title>Shopping Cart Contents:</title>
  <style type="text/css">
   th { background-color: #999;}
   .odd_row { background-color: #EEE;  }
   .even_row { background-color: #FFF; }
  </style>
<head>
<body>
<h1> Ecommerce Multimedia Store </h1>
-->
<?php
$action = $_GET['mode'];
$ProductID = $_GET['itemno'];
$session = session_id();
$UserID = 1;

//connect to mysql
$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or 
    die ('Unable to connect. Check your connection parameters.');

//select database	
mysql_select_db(MYSQL_DB) or die(mysql_error($db));

switch ($action) {
case 'add' :
     if (!empty($ProductID)) {
         $query = 'INSERT INTO ShopCart( UserID )VALUES( $UserID )'; 
             mysql_query($query, $db) or die(mysql_error($db));

 $query = ' SELECT ShopCartID FROM ShopCart WHERE UserID = $UserID ORDER BY ShopCartID DESC LIMIT 1'
           
         $query = 'INSERT INTO ShopCartLine(ShopCartID, ProductID, Quantity)VALUES(" . $ShopcartID . '", "' . $ProductID. '", "', "' . $Quantity . '")';
             mysql_query($query, $db) or die(mysql_error($db));
          
}
break;
}


$query = 'SELECT Product.ProductID, Quantity, Title, ListPrice
          FROM ShopCartLine JOIN Product ON ShopCartLine.ProductID = Product.ProductID
	  JOIN ShopCart ON ShopCartLine.ShopCartID=ShopCart.ShopCartID
          WHERE ShopCart.ShopCartID = "' . $session . '"
          ORDER BY Product.ProductID ASC';
$result = mysql_query($query, $db) or die (mysql_error($db));

$rows = mysql_num_rows($result);
if ($rows == 1) {
     echo '<p>You currently have 1 product in your cart.</p>';
} else {
        echo '<p>You currently have ' . $rows . ' products in your cart.</p>';
}

if ($rows > 0) {
?>

<table style="width: 75%;">
  <tr>
   <th style="width: 100px;"> </th><th>Item Name</th><th><Quantity</th>
   <th>Price Each</th><th>Extended Price</th>
  </tr>
<?php
   $total = 0;
   $odd = true;
   while ($row = mysql_fetch_array($result)) {
      echo ($odd == true) ? '<tr class="odd_row">' : '<tr class="even_row">';
           $odd = !$odd;
           extract($row);
?>
      <td><a href="shopcart.php?ProductID=<?php echo $ProductID;?>"><?php
        echo $Title; ?></a></td>
      <td>
       <form method="post" action="shopcartupdate.php">
        <div>
         <input type="text" name="Quantity" maxlength="2" size="2"
          value="<?php echo $Quantity; ?>"/>
         <input type="hidden" name="ProductID"
          value="<?php echo $ProductID; ?>"/>
         <input type="hidden" name="redirect" value="shopcart.php"/>
         <input type="submit" name=submit" value="Change Qty"/>
        </div>
       </form>
      </td>
      <td style="text-align: right;"> $<?php echo $ListPrice; ?></td>
      <td style="text-align: right;"> $<?php echo number_format
    ($ListPrice * $Quantity, 2); ?>
      </td>
     </tr>
    <?php
             $total = $total + $ListPrice * $Quantity;
      }
    ?>
</table>
<p> Your total before shipping is:
<strong>$<?php echo number_format($total, 2); ?></strong></p>
<form method="post" action="checkout.php">
<div>
  <input type="submit" name="submit" value="Proceed to Checkout"
   style="font-weight: bold;"/>
</div>
</form>
<form method="post" action="shopcartupdate.php">
<div>
  <input type="hidden" name="redirect" value="index.php"/>
  <input type="submit" name="submit" value="Empty Cart" />
</div>
</form>
<?php
}
?>

<hr/>
<p><a href="index.php">< Back to main page</a></p>
</body>
</html>


<?php include 'footer.php'; ?>

Andy-H · November 21, 2009

Does db-inc.php or header.php make a call to session_start ?

resident1155 · November 21, 2009

Yes, db-inc.php has this code.

Andy-H · November 21, 2009

<?php include 'db-inc.php'; ?>
<?php include 'header.php'; ?>

<!-- <html>
<head>
  <title>Shopping Cart Contents:</title>
  <style type="text/css">
   th { background-color: #999;}
   .odd_row { background-color: #EEE;  }
   .even_row { background-color: #FFF; }
  </style>
<head>
<body>
<h1> Ecommerce Multimedia Store </h1>
-->
<?php
$action = $_GET['mode'];
$ProductID = $_GET['itemno'];
$session = session_id();
$UserID = 1;

//connect to mysql
$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or 
    die ('Unable to connect. Check your connection parameters.');

//select database   
mysql_select_db(MYSQL_DB) or die(mysql_error($db));

switch ($action) {
case 'add' :
     if (!empty($ProductID)) {
         $query = 'INSERT INTO ShopCart( UserID )VALUES( $UserID )'; 
             mysql_query($query, $db) or die(mysql_error($db));

    $query = ' SELECT ShopCartID FROM ShopCart WHERE UserID = $UserID ORDER BY ShopCartID DESC LIMIT 1'; // semi-colon
           
         $query = 'INSERT INTO ShopCartLine(ShopCartID, ProductID, Quantity)VALUES("' . $ShopcartID . '", "' . $ProductID. '", "' . $Quantity . '")';

//was missing an apostrophie above
// above also overwrites other query string before it is used...

             mysql_query($query, $db) or die(mysql_error($db));
          
   }
break;
}


$query = 'SELECT Product.ProductID, Quantity, Title, ListPrice
          FROM ShopCartLine JOIN Product ON ShopCartLine.ProductID = Product.ProductID
        JOIN ShopCart ON ShopCartLine.ShopCartID=ShopCart.ShopCartID
          WHERE ShopCart.ShopCartID = "' . $session . '"
          ORDER BY Product.ProductID ASC';
$result = mysql_query($query, $db) or die (mysql_error($db));

$rows = mysql_num_rows($result);
if ($rows == 1) {
     echo '<p>You currently have 1 product in your cart.</p>';
} else {
        echo '<p>You currently have ' . $rows . ' products in your cart.</p>';
}

if ($rows > 0) {
?>

<table style="width: 75%;">
  <tr>
   <th style="width: 100px;"> </th><th>Item Name</th><th><Quantity</th>
   <th>Price Each</th><th>Extended Price</th>
  </tr>
<?php
   $total = 0;
   $odd = true;
   while ($row = mysql_fetch_array($result)) {
      echo ($odd == true) ? '<tr class="odd_row">' : '<tr class="even_row">';
           $odd = !$odd;
           extract($row);
?>
      <td><a href="shopcart.php?ProductID=<?php echo $ProductID;?>"><?php
        echo $Title; ?></a></td>
      <td>
       <form method="post" action="shopcartupdate.php">
        <div>
         <input type="text" name="Quantity" maxlength="2" size="2"
          value="<?php echo $Quantity; ?>"/>
         <input type="hidden" name="ProductID"
          value="<?php echo $ProductID; ?>"/>
         <input type="hidden" name="redirect" value="shopcart.php"/>
         <input type="submit" name=submit" value="Change Qty"/>
        </div>
       </form>
      </td>
      <td style="text-align: right;"> $<?php echo $ListPrice; ?></td>
      <td style="text-align: right;"> $<?php echo number_format
    ($ListPrice * $Quantity, 2); ?>
      </td>
     </tr>
    <?php
             $total = $total + $ListPrice * $Quantity;
      }
    ?>
</table>
<p> Your total before shipping is:
<strong>$<?php echo number_format($total, 2); ?></strong></p>
<form method="post" action="checkout.php">
<div>
  <input type="submit" name="submit" value="Proceed to Checkout"
   style="font-weight: bold;"/>
</div>
</form>
<form method="post" action="shopcartupdate.php">
<div>
  <input type="hidden" name="redirect" value="index.php"/>
  <input type="submit" name="submit" value="Empty Cart" />
</div>
</form>
<?php
}
?>

<hr/>
<p><a href="index.php">< Back to main page</a></p>
</body>
</html>


<?php include 'footer.php'; ?>

resident1155 · November 21, 2009

Okay, I made your edits. I also changed $query to $shopcartID as after the row is inserted into the shopcart table, I want to pull the most recent shopcart ID and insert it into the shopcartline table. For some reason, I am still getting a blank page as a result on the shopcart table. I can't even view it either, which is the result of the switch case not working correctly.

I should note that the attributes in the ShopCart table are ShopCartID (autoincrememnt), UserID, and ExpirationDate. I currently have ExpirationDate set to allow nulls. Any additional help is appreciated. Here is the modified code:

<?php include 'db-inc.php'; ?>
<?php include 'header.php'; ?>

<html>
<head>
  <title>Shopping Cart Contents:</title>
  <style type="text/css">
   th { background-color: #999;}
   .odd_row { background-color: #EEE;  }
   .even_row { background-color: #FFF; }
  </style>
<head>
<body>
<h1> Ecommerce Multimedia Store </h1>

<?php
$action = $_GET['mode'];
$ProductID = $_GET['itemno'];
$session = session_id();
$UserID = 1;
$Quantity = 1;

//connect to mysql
$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or 
    die ('Unable to connect. Check your connection parameters.');

//select database	
mysql_select_db(MYSQL_DB) or die(mysql_error($db));

switch ($action) {
case 'add' :
     if (!empty($ProductID)) {
         $query = 'INSERT INTO ShopCart( UserID )VALUES( $UserID )'; 
             mysql_query($query, $db) or die(mysql_error($db));

 $ShopCartID = ' SELECT ShopCartID FROM ShopCart WHERE UserID = $UserID ORDER BY ShopCartID DESC LIMIT 1';
           
         $query = 'INSERT INTO ShopCartLine(ShopCartID, ProductID, Quantity)VALUES("' . $ShopCartID . '", "' . $ProductID. '", "' . $Quantity . '")';
             mysql_query($query, $db) or die(mysql_error($db));
          
}
break;
}


$query = 'SELECT Product.ProductID, Quantity, Title, ListPrice
          FROM ShopCartLine JOIN Product ON ShopCartLine.ProductID = Product.ProductID
	  JOIN ShopCart ON ShopCartLine.ShopCartID=ShopCart.ShopCartID
          WHERE ShopCart.ShopCartID = "' . $session . '"
          ORDER BY Product.ProductID ASC';
$result = mysql_query($query, $db) or die (mysql_error($db));

$rows = mysql_num_rows($result);
if ($rows == 1) {
     echo '<p>You currently have 1 product in your cart.</p>';
} else {
        echo '<p>You currently have ' . $rows . ' products in your cart.</p>';
}

if ($rows > 0) {
?>

<table style="width: 75%;">
  <tr>
   <th style="width: 100px;"> </th><th>Item Name</th><th><Quantity</th>
   <th>Price Each</th><th>Extended Price</th>
  </tr>
<?php
   $total = 0;
   $odd = true;
   while ($row = mysql_fetch_array($result)) {
      echo ($odd == true) ? '<tr class="odd_row">' : '<tr class="even_row">';
           $odd = !$odd;
           extract($row);
?>
      <td><a href="shopcart.php?ProductID=<?php echo $ProductID;?>"><?php
        echo $Title; ?></a></td>
      <td>
       <form method="post" action="shopcartupdate.php">
        <div>
         <input type="text" name="Quantity" maxlength="2" size="2"
          value="<?php echo $Quantity; ?>"/>
         <input type="hidden" name="ProductID"
          value="<?php echo $ProductID; ?>"/>
         <input type="hidden" name="redirect" value="shopcart.php"/>
         <input type="submit" name=submit" value="Change Qty"/>
        </div>
       </form>
      </td>
      <td style="text-align: right;"> $<?php echo $ListPrice; ?></td>
      <td style="text-align: right;"> $<?php echo number_format
    ($ListPrice * $Quantity, 2); ?>
      </td>
     </tr>
    <?php
             $total = $total + $ListPrice * $Quantity;
      }
    ?>
</table>
<p> Your total before shipping is:
<strong>$<?php echo number_format($total, 2); ?></strong></p>
<form method="post" action="checkout.php">
<div>
  <input type="submit" name="submit" value="Proceed to Checkout"
   style="font-weight: bold;"/>
</div>
</form>
<form method="post" action="shopcartupdate.php">
<div>
  <input type="hidden" name="redirect" value="index.php"/>
  <input type="submit" name="submit" value="Empty Cart" />
</div>
</form>
<?php
}
?>

<hr/>
<p><a href="index.php">< Back to main page</a></p>
</body>
</html>


<?php include 'footer.php'; ?>

resident1155 · November 21, 2009

By the way, now I am getting the following mysql error: Unknown column '$UserID' in 'field list'

Andy-H · November 21, 2009

<?php include 'db-inc.php'; ?>
<?php include 'header.php'; ?>

<html>
<head>
  <title>Shopping Cart Contents:</title>
  <style type="text/css">
   th { background-color: #999;}
   .odd_row { background-color: #EEE;  }
   .even_row { background-color: #FFF; }
  </style>
<head>
<body>
<h1> Ecommerce Multimedia Store </h1>

<?php
$action = $_GET['mode'];
$ProductID = (int)$_GET['itemno'];
$session = session_id();
$UserID = 1;
$Quantity = 1;

//connect to mysql
$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or 
    die ('Unable to connect. Check your connection parameters.');

//select database   
mysql_select_db(MYSQL_DB) or die(mysql_error($db));

switch ($action) {
case 'add' :
     if (!empty($ProductID)) {
         $query = 'INSERT INTO ShopCart( UserID )VALUES( $UserID )'; 
             mysql_query($query, $db) or die(mysql_error($db));

    $Shop = "SELECT ShopCartID FROM ShopCart WHERE UserID = " . $UserID . " ORDER BY ShopCartID DESC LIMIT 1";
    $Cart = mysql_query($Shop);
    $ShopCart = mysql_fetch_row($Cart);

   $ShopCartID = $ShopCart[0];
           
         $query = "INSERT INTO ShopCartLine(ShopCartID, ProductID, Quantity)VALUES(" . $ShopCartID . ", " . $ProductID. ", " . $Quantity . ")";
             mysql_query($query, $db) or die(mysql_error($db));
          
   }
break;
}


$query = 'SELECT Product.ProductID, Quantity, Title, ListPrice
          FROM ShopCartLine JOIN Product ON ShopCartLine.ProductID = Product.ProductID
        JOIN ShopCart ON ShopCartLine.ShopCartID=ShopCart.ShopCartID
          WHERE ShopCart.ShopCartID = "' . $session . '"
          ORDER BY Product.ProductID ASC';
$result = mysql_query($query, $db) or die (mysql_error($db));

$rows = mysql_num_rows($result);
if ($rows == 1) {
     echo '<p>You currently have 1 product in your cart.</p>';
} else {
        echo '<p>You currently have ' . $rows . ' products in your cart.</p>';
}

if ($rows > 0) {
?>

<table style="width: 75%;">
  <tr>
   <th style="width: 100px;"> </th><th>Item Name</th><th><Quantity</th>
   <th>Price Each</th><th>Extended Price</th>
  </tr>
<?php
   $total = 0;
   $odd = true;
   while ($row = mysql_fetch_array($result)) {
      echo ($odd == true) ? '<tr class="odd_row">' : '<tr class="even_row">';
           $odd = !$odd;
           extract($row);
?>
      <td><a href="shopcart.php?ProductID=<?php echo $ProductID;?>"><?php
        echo $Title; ?></a></td>
      <td>
       <form method="post" action="shopcartupdate.php">
        <div>
         <input type="text" name="Quantity" maxlength="2" size="2"
          value="<?php echo $Quantity; ?>"/>
         <input type="hidden" name="ProductID"
          value="<?php echo $ProductID; ?>"/>
         <input type="hidden" name="redirect" value="shopcart.php"/>
         <input type="submit" name=submit" value="Change Qty"/>
        </div>
       </form>
      </td>
      <td style="text-align: right;"> $<?php echo $ListPrice; ?></td>
      <td style="text-align: right;"> $<?php echo number_format
    ($ListPrice * $Quantity, 2); ?>
      </td>
     </tr>
    <?php
             $total = $total + $ListPrice * $Quantity;
      }
    ?>
</table>
<p> Your total before shipping is:
<strong>$<?php echo number_format($total, 2); ?></strong></p>
<form method="post" action="checkout.php">
<div>
  <input type="submit" name="submit" value="Proceed to Checkout"
   style="font-weight: bold;"/>
</div>
</form>
<form method="post" action="shopcartupdate.php">
<div>
  <input type="hidden" name="redirect" value="index.php"/>
  <input type="submit" name="submit" value="Empty Cart" />
</div>
</form>
<?php
}
?>

<hr/>
<p><a href="index.php">< Back to main page</a></p>
</body>
</html>


<?php include 'footer.php'; ?>

resident1155 · November 21, 2009

Fantastic! I have just one more problem. Something isn't quite right with my $query that displays the contents of ShopCartLine. Can you point me in the right direction on that one? It keeps saying the contents of the cart is 0. Currently I have the ShopCartID pointing to $session, but for right now I changed it to $Shop. I think that is the correct thing to do, but it is still not working...so now I have this:

$query = ' SELECT Product.ProductID, Quantity, Title, ListPrice
          FROM ShopCartLine JOIN Product ON ShopCartLine.ProductID = Product.ProductID
	  JOIN ShopCart ON ShopCartLine.ShopCartID=ShopCart.ShopCartID
          WHERE ShopCart.ShopCartID = "' . $Shop . '"
          ORDER BY Product.ProductID ASC';

Any help is appreciated. Thanks!

resident1155 · November 21, 2009

Okay, I modified my code and I am now able to add to the shopcart...however....

If, when I am viewing the shopcart contents, I do a hard refresh of the page, it clears the variable data and I am left with 0 items in the shopcart again. I am trying to get a better answer from the board regarding the use of session variables to be assigned to my ShopCartID. I know I need a session variable because it will keep my data stored as opposed to losing it during a page refresh with a regular php variable.

I'm still unsure how to properly execute this, although I know I am close. I hope somebody can help me because I would really appreciate it. Here is the code again:

<?php include 'db-inc.php'; ?>
<?php include 'header.php'; ?>

<html>
<head>
  <title>Shopping Cart Contents:</title>
  <style type="text/css">
   th { background-color: #999;}
   .odd_row { background-color: #EEE;  }
   .even_row { background-color: #FFF; }
  </style>
<head>
<body>
<h1> Ecommerce Multimedia Store </h1>

<?php
$action = $_GET['mode'];
$ProductID = $_GET['itemno'];
$UserID = 3;
$Quantity = 1;

//connect to mysql
$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or 
    die ('Unable to connect. Check your connection parameters.');

//select database	
mysql_select_db(MYSQL_DB) or die(mysql_error($db));

switch ($action) {
case 'add' :
     if (!empty($ProductID)) {
         $query = 'INSERT INTO ShopCart( UserID )VALUES("' . $UserID . '")'; 
             mysql_query($query, $db) or die(mysql_error($db));

 $Shop = 'SELECT ShopCartID FROM ShopCart WHERE UserID = "' . $UserID . '" ORDER BY ShopCartID ASC LIMIT 1';
         $Cart = mysql_query($Shop);
         $ShopCart = mysql_fetch_row($Cart);

         $ShopCartID = $ShopCart[0];

         $query = 'INSERT INTO ShopCartLine(ShopCartID, ProductID, Quantity)VALUES("' . $ShopCartID . '", "' . $ProductID . '", "' . $Quantity . '")';
             mysql_query($query, $db) or die(mysql_error($db));
         
}
break;
}

$query = 'SELECT Product.ProductID, Quantity, Title, ListPrice
          FROM ShopCartLine JOIN Product ON ShopCartLine.ProductID = Product.ProductID
	  JOIN ShopCart ON ShopCartLine.ShopCartID=ShopCart.ShopCartID
          WHERE ShopCart.ShopCartID  = "' . $ShopCartID . '"
          ORDER BY Product.ProductID ASC';
$result = mysql_query($query, $db) or die (mysql_error($db));

$rows = mysql_num_rows($result);
if ($rows == 1) {
     echo '<p>You currently have 1 product in your cart.</p>';
} else {
        echo '<p>You currently have ' . $rows . ' products in your cart.</p>';
}

if ($rows > 0) {
?>

<table style="width: 75%;">
  <tr>
   <th style="width: 100px;"> </th><th>Item Name</th><th><Quantity</th>
   <th>Price Each</th><th>Extended Price</th>
  </tr>
<?php
   $total = 0;
   $odd = true;
   while ($row = mysql_fetch_array($result)) {
      echo ($odd == true) ? '<tr class="odd_row">' : '<tr class="even_row">';
           $odd = !$odd;
           extract($row);
?>
      <td><a href="shopcart.php?ProductID=<?php echo $ProductID;?>"><?php
        echo $Title; ?></a></td>
      <td>
       <form method="post" action="shopcartupdate.php">
        <div>
         <input type="text" name="Quantity" maxlength="2" size="2"
          value="<?php echo $Quantity; ?>"/>
         <input type="hidden" name="ProductID"
          value="<?php echo $ProductID; ?>"/>
         <input type="hidden" name="redirect" value="shopcart.php"/>
         <input type="submit" name=submit" value="Change Qty"/>
        </div>
       </form>
      </td>
      <td style="text-align: right;"> $<?php echo $ListPrice; ?></td>
      <td style="text-align: right;"> $<?php echo number_format
    ($ListPrice * $Quantity, 2); ?>
      </td>
     </tr>
    <?php
             $total = $total + $ListPrice * $Quantity;
      }
    ?>
</table>
<p> Your total before shipping is:
<strong>$<?php echo number_format($total, 2); ?></strong></p>
<form method="post" action="checkout.php">
<div>
  <input type="submit" name="submit" value="Proceed to Checkout"
   style="font-weight: bold;"/>
</div>
</form>

<?php
}
?>

<hr/>
<p><a href="index.php">< Back to main page</a></p>
</body>
</html>


<?php include 'footer.php'; ?>

Andy-H · November 22, 2009

Do you have a login script you can show us?

resident1155 · November 23, 2009

Well, eventually I will be passing the UserID with a session variable (from the login script), but right now I am just trying to get the shopcart to function properly so I hard coded the UserID on the ShopCart page.

I have been reading a lot about how most people usually store the shopping cart contents in a session using a session array. Something like $_SESSION['cart contents']. I don't fully understand how to do that, so I was looking to see if anyone here can guess as to how I would do it based on the code I currently have for ShopCart. There has to be somebody out there with a similar database design as mine where there is a ShoppingCart and a ShoppingCartLine.

Any help here is appreciated. Thanks.

Sign In

Problem with shopcart code

Recommended Posts

resident1155

Link to comment

Share on other sites

Andy-H

Link to comment

Share on other sites

resident1155

Link to comment

Share on other sites

Andy-H

Link to comment

Share on other sites

resident1155

Link to comment

Share on other sites

resident1155

Link to comment

Share on other sites

Andy-H

Link to comment

Share on other sites

resident1155

Link to comment

Share on other sites

resident1155

Link to comment

Share on other sites

Andy-H

Link to comment

Share on other sites

resident1155

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information