resident1155 Posted November 21, 2009 Share Posted 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'; ?> Quote Link to comment https://forums.phpfreaks.com/topic/182349-problem-with-shopcart-code/ Share on other sites More sharing options...
Andy-H Posted November 21, 2009 Share Posted November 21, 2009 Does db-inc.php or header.php make a call to session_start ? Quote Link to comment https://forums.phpfreaks.com/topic/182349-problem-with-shopcart-code/#findComment-962274 Share on other sites More sharing options...
resident1155 Posted November 21, 2009 Author Share Posted November 21, 2009 Yes, db-inc.php has this code. Quote Link to comment https://forums.phpfreaks.com/topic/182349-problem-with-shopcart-code/#findComment-962282 Share on other sites More sharing options...
Andy-H Posted November 21, 2009 Share Posted 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'; ?> Quote Link to comment https://forums.phpfreaks.com/topic/182349-problem-with-shopcart-code/#findComment-962288 Share on other sites More sharing options...
resident1155 Posted November 21, 2009 Author Share Posted 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'; ?> Quote Link to comment https://forums.phpfreaks.com/topic/182349-problem-with-shopcart-code/#findComment-962304 Share on other sites More sharing options...
resident1155 Posted November 21, 2009 Author Share Posted November 21, 2009 By the way, now I am getting the following mysql error: Unknown column '$UserID' in 'field list' Quote Link to comment https://forums.phpfreaks.com/topic/182349-problem-with-shopcart-code/#findComment-962310 Share on other sites More sharing options...
Andy-H Posted November 21, 2009 Share Posted 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'; ?> Quote Link to comment https://forums.phpfreaks.com/topic/182349-problem-with-shopcart-code/#findComment-962313 Share on other sites More sharing options...
resident1155 Posted November 21, 2009 Author Share Posted 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! Quote Link to comment https://forums.phpfreaks.com/topic/182349-problem-with-shopcart-code/#findComment-962339 Share on other sites More sharing options...
resident1155 Posted November 21, 2009 Author Share Posted 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'; ?> Quote Link to comment https://forums.phpfreaks.com/topic/182349-problem-with-shopcart-code/#findComment-962425 Share on other sites More sharing options...
Andy-H Posted November 22, 2009 Share Posted November 22, 2009 Do you have a login script you can show us? Quote Link to comment https://forums.phpfreaks.com/topic/182349-problem-with-shopcart-code/#findComment-962872 Share on other sites More sharing options...
resident1155 Posted November 23, 2009 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/182349-problem-with-shopcart-code/#findComment-963563 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.