Cart Data in Sessions or Database?

[sojomy]

Ok, I am trying to write a custom shopping cart system for a small site that
sells candles. What I can't decide is whether to use Sessions for the cart
data, or a database. Does anyone have any recommendations? What are the
advantages and disadvantages of both?

I started to do the site with all of the cart data in Sessions, but as I
started to put the data into the Session variable, I noticed that I was
needing nesting Arrays (and more nesting arrays). Here is a sample of
the Session data that I had when three items were added. As you can see,
the [5] and [3] arrays are the part numbers. The [5] array has two candles
inside of it. They are the same part number with the same Name and Price,
but they are different scents and each scent has it's own quantity. The
candles in the database are one row per part, but each part can have an
attribute (the scent). So my arrays started having arrays which also
had arrays. Here is the output of the Session array...

Array (
	[Cart] => Array (
			[5] => Array (
					[Apple Cinnamon] => Array (
							[Name] => Victorian Jar (10oz)
							[Price] => 7
							[Quantity] => 1
					)
					[Bay Breeze] => Array (
							[Name] => Victorian Jar (10oz)
							[Price] => 7
							[Quantity] => 2
					)
			)
			[3] => Array (
					[Grape] => Array (
							[Name] => Glass Flower Pot (4oz)
							[Price] => 3
							[Quantity] => 1
					)
			)
	)
)

Pretty confusing huh?

Ok, so if Sessions are ok for storing shopping cart data, am I storing my
data in a effecient way? Each part that is added is an array under the
[Cart] array (Which is under the $_SESSION array). Each part has an array
for each scent that is added, and each scent has an array that holds the
Name of the Part Number as well as the Price and Quantity. Should I put
the Part Name and the Price under the Item Number instead of putting them
under the Scent? The Part Name and Price will be constant for each Part
Number, so putting it under each Scent would be duplicating data, but the
way that I have it now, all I have under each Part Number is a list of
arrays, not Two Strings with an unknown amount of arrays. Is that confusing?

Ok, so my other alternative is to go with storing the shopping cart data in
the database. If I do that, I am not sure what I should store. Do I have
just one big table that looks like this

Session ID	 Part ID		 Part Name	 Scent		 Quantity
XXX		 1			 Candle 1	   Blueberry	 1
YYY		 3			 Candle 3	   Strawberry	 3
XXX		 6			 Candle 6	   Orange		 6

Or would I use seperate tables for anything? Also, when the cart data is
in the database, would I just move it from the cart table to the "orders"
table once the order has been processed? If so, what would I do with data
that was put in the cart by someone, but the order was not processed?
Wouldn't the table just fill up with data? How would I go about trash
collection? Or would I just leave it there?


Please, if anyone has any suggestions or answers to the questions above,
please let me know. I am an intermediate newbie trying to do something
I have never done before. I would prefer to start out by doing it the
right way, rather than doing it the "not-so-right" way and find out
later, only to have to do it over.

Thanks in advance (and more afterwards)

[arborint]

Use sessions if all you every want is a simple cart.

Use a database if you want to do things like save carts, have wish lists, do analysis of user's shopping patterns, etc.

[sojomy]

Ok, I will probably use the database to store cart data then. Does anyone have any suggestions. Like would I use only one table for the cart data, and would I use the session ID as the primary unique key? Does anyone have any general tips to throw my direction. Maybe a list of everyone's suggestions about what they think is the best method of storing cart data?

[Big Fat Bob]

Yo

You would use sessions regardless as you need persistence from page to page as the visitor navigates your web site.

I would also store their basket to the database short term in the event the session is lost, thus you pick up the basket again based on a cookie.

Once the visitor does make an purchase, you move their basket to the tables you have for storing their name, address, zip code, etc and remove the basket you stored in the table I stated above

[sojomy]

Originally Posted by Big Fat Bob

Yo

Yo

Originally Posted by Big Fat Bob

You would use sessions regardless as you need persistence from page to page as the visitor navigates your web site.

Right, I'm just trying to decide whether I should store the cart contents in the session variable instead of a database. But I decided to store them in the database, and just link the database to their session....somehow

Originally Posted by Big Fat Bob

I would also store their basket to the database short term in the event the session is lost, thus you pick up the basket again based on a cookie.

Ok, so how would I link the user to their cart in the database? You said a cookie. Do I write the session ID to a cookie on their computer and then use the session id in the database? If I do, what happens when they come back? Do I lookup the session id from the cookie and set the new session id to that so it links up with the database? And what do I do if they have cookies disabled? I have never used cookies before, only sessions, so I'm a little naive about cookies. Is it just an identifier so I recognize them when they come back? Is there any way to do it without a cookie?


Thanks for the quick reply too

[Big Fat Bob]

Yo

You said a cookie. Do I write the session ID to a cookie on their computer and then use the session id in the database?

Yes. You query the temp database data based on the session id you stored to the cookie, which is the PK within the database table row.

If someone has their cookies disabled, then nothing much you can do about it, just alert the user that they need to enable cookies is one option ?

Cookies are not that difficult to work with or understand, look at the on line manual to begin with, then look at Kevin Yank's articles.

One has something to do with using cookies which will help you out a lot

[Atealtha]

Look up HEAP table types. Session variables are stored on HDD, but data on HEAP tables are stored in memory.

[pfitz]

I'd go for sessions and create a cart class so you simply instantiate the cart class and have your add_item() display_item() and calculate_total() functions within that.

try www.phpclasses.org for a few decent carts. I modified one of those under GPL and its great for all basic carts.

Database storage has its benefits including storing temp carts for a long time so users have their contents when they come back - but that is also dependent upon cookies.

One problem I had recently with a sessions cart was shared SSL with trans_session_id turned off - the cookie domain changed whjen it went shared SSL and the cart was lost.

A database cart solves that issue at least.

[sinapra]

Database could be better for future references but you can head with sessions for basic displays that can store the basic values like items and the user info, others could be stored and fetched thru DB which eases the job of data being lost if there are other secured reasons.
I have always worked on data storage for carts.

Regards

[stuartwar]

I will shortly be tackling this same issue. Our site has close to 20,000 lines for sale and we will likely have 2 options for customers.
1. create an account and log in - this would enable wish lists etc.
2. no login - basket held in database, but indexed by session.

I have not fully thought this out yet, but I see no need to have a separate table for basket and order items. I would have a table 'tblBasketHeader' linked to 'tblBasketItems' by a key field.

'tblBasketHeader' would have fields for 'sessionID', 'customerID', 'status' etc. and a field 'orderID' which would link to a table 'tblOrderHeader' once an order was placed. This would contain order specific info. such as delivery address etc.

A script could be run periodically to delete entries from tblBasketItems and tblBasketHeader which had no customerID OR orderID AND which fell within a date range (say, older than 1 month).

[sinapra]

well we dont have to have a seperate table for each item or say each entity, we can have a common table on orderId which would have entries of the order items and customer details, well it depends upon how much load your server can take as well with other constraints playing role!!!

[SteveW]

I have just implemented one for a customer and it was done by filling a tmp_order db with each item. (bit like a transaction db)

On completion of the order, a main order record was created with an order number (with custid, date, total order value, freight etc.).

On order confirmation the tmp_orders were dumped to a more permanent db (with the order_id from the primary order record as a secondary key)

This allowed the order (and products) to be tracked, processed as well as statistics gathered for the client.

We did a 30+ user test on 70 odd products from 5 categories and it performed flawlessly.

By doing it this way you can simply call the summarised order details from your https connection direct from the db and not worry about carrying session arrays around...

I used the session_id to differentiate simultaneous customers - this allowed me to key into the db from either side of SSL. Apart from that, there should be no need to carry the whole order to the payment area, just the custID and order total...

Hope this helps a little... It worked for me - beautifuly..

[sinapra]

that was my point, I am sure Steve's way of doing is same as I did for my carts and it surely increases the performance and eases the job in future.

[SteveW]

Sorry Sinapra - soundz like I woz stealing your thunder

[sinapra]

not at all, your explaination was clearer than me , I hope it can be effective to the users!!!!

[Big Fat Bob]

Yo

Found some old script I had from a while back, hope it's some use to someone

class basket
 {
 
 function basket()
 {
  if(isset($HTTP_COOKIE_VARS["OrderID"]))
  {
   return $HTTP_COOKIE_VARS["OrderID"];
  }
  else
  {
   session_start();
   setcookie("OrderID", session_id(), time() + ((3600 * 24) * 30));
   
   return session_id();
  }
 }
 
 function basket_total($OrderID)
 {
  $db = new db_connect;
  $db -> select_user_db();
  
  $basket = 0;
  $sql = $db -> query_user_db("SELECT * FROM product_cart WHERE cart_session = '$OrderID' ORDER BY id");
  
  while($row = mysql_fetch_array($sql))
  {
   $quantity = $row["cart_quantity"];
   $basket += $quantity;
  }
  
  $db -> close_user_db();
  
  return $basket;
 }
 
 function show_basket_total($OrderID)
 {
  $db = new db_connect;
  $db -> select_user_db();
  
  $basket = 0;
  $sql = $db -> query_user_db("SELECT * FROM product_cart WHERE cart_session = '$OrderID' ORDER BY id");
  
  while($row = mysql_fetch_array($sql))
  {
   $quantity = $row["cart_quantity"];
   $basket += $quantity;
  }
  
  $db -> close_user_db();
  
  if($basket == 0)
  {
   echo("Your shopping basket is empty.");
  }
  else
  {
   $str = "You have <b>".$basket."</b> item";
   
   if($basket > 1) { $str .= "s"; }
   
   $str .= " in your shopping basket.";
   
   echo($str);
  }
 }
 
 function add_item($id, $qty, $type, $OrderID)
 { 
  $type = $type[0];
  $type = ($type == "Yes")? 1:0;
  
  $db = new db_connect;
  $db -> select_user_db();
  
  $db -> query_user_db("INSERT INTO product_cart SET cart_id = '$id', cart_type = '$type', cart_session = '$OrderID', cart_quantity = '$qty'");
  $db -> close_user_db();
 }
 
 function remove_item($id, $OrderID)
 { 
  $db = new db_connect;
  $db -> select_user_db();
  
  $db -> query_user_db("DELETE FROM product_cart WHERE id = '$id' AND cart_session = '$OrderID'");
  $db -> close_user_db(); /* something wrong with this ? */
 }
 
 function update_item($qty, $cartid, $id, $OrderID)
 {
  $db = new db_connect;
  $db -> select_user_db();
  $db -> query_user_db("UPDATE product_cart SET cart_quantity = '$qty' WHERE id = '$id' AND cart_id = '$cartid' AND cart_session = '$OrderID'");
  $db -> close_user_db();
 }
 
 } 

#index.php

include("client/protected/classes.php");
 
 # Get or Create a new Order ID and shopping basket
 
 $cart = new basket;
 
 # Create or Import a new template file(s)
 
 $p = new template;
 $p -> get_template("client/templates/template-head.tpl");
 $p -> get_template("client/templates/template-pane.tpl");
 ?>
 <table align="left" border="0" width="544" valign="top" cellspacing="0" cellpadding="0">
 <tbody>
 <tr>
 <td align="right" width="100%" valign="top">
  <br>
  <div style="text-align:left;width:544px;border-bottom:solid 1px skyblue;font:10px helvetica;letter-spacing:3px;">&nbsp;LATEST PRODUCTS</div>
  <br>
  <? $p -> import_template("client/templates/template-prodlist.tpl"); /* INCLUDE to keep HTML clean for development */ ?>
  <br>
  <div style="text-align:center;width:544px;">Browse our shop for more exciting offers available this month.</div>
 </td></tr>
 <tr>
 <td align="right" width="100%" valign="top">
  <br>
  <div style="text-align:left;width:544px;border-bottom:solid 1px skyblue;font:10px helvetica;letter-spacing:3px;">&nbsp;LATEST FORUMS</div>
  <br>
  <table border="0" width="100%" cellspacing="0" cellpadding="0"><tbody>
  <?
  $db = new db_connect;
  $db -> select_user_db();
  $sql  = "SELECT discussion_topics.topic_id AS topicID, topic_date AS topicDATE, ";
  $sql .= "topic_views, topic_topic, discussion_lookup.topic_id, discussion_lookup.mess_id, ";
  $sql .= "discussion_lookup.user_id, discussion_lookup.cat_id, user_name ";
  $sql .= "FROM discussion_topics, discussion_lookup, discussion_users ";
  $sql .= "WHERE discussion_topics.topic_id = discussion_lookup.topic_id ";
  $sql .= "AND discussion_lookup.user_id = discussion_users.user_id";
  
  $sql = $db -> query_user_db($sql);
  
  $num_rows = $db -> total_rows($sql);
  if($num_rows == 0)
  {
   ?>
   <tr>
   <td width="100%" align="left" valign="center" height="16">There are no new forum discussions posted today.</td></tr>
   <?
  }
  else
  {
   ?>
   <tr>
   <td width="45%" align="left" valign="center" height="16">Topic</td>
   <td width="15%" align="center" valign="center">Posts</td>
   <td width="15%" align="center" valign="center">Views</td>
   <td width="25%" align="left" valign="center">Started By</td></tr>
   <?
   $count = 0;
   while($row = mysql_fetch_array($sql))
   {
    $Auther = $row["user_name"];
    $Topic = $row["topic_topic"];
    $Views = $row["topic_views"];
    
    $color = ($count++ & 1) ? "yellow":"white";
    ?>
    <tr>
    <td width="45%" align="left" valign="center" height="16"><a href="forums.php?id=<? echo($row["topicID"]); ?>" target="_self"><font color="<? echo($color); ?>"><? echo($Topic); ?></font></a></td>
    <td width="15%" align="center" valign="center"><font color="<? echo($color); ?>">0</font></td>
    <td width="15%" align="center" valign="center"><font color="<? echo($color); ?>"><? echo($Views); ?></font></td>
    <td width="25%" align="left" valign="center"><font color="<? echo($color); ?>"><? echo($Auther); ?></font></td></tr>
    <?
   }
  }
  ?>
  </tbody></table>
 </td></tr>
 </tbody></table>
 <? 
 $p -> get_template("client/templates/template-foot.tpl");

#basket.php

include("admin/library/library.php");
 include("client/protected/classes.php");
 
 # Get or Create a new Order ID and shopping basket
 
 $cart = new basket;
 
 if(isset($action) && $action == "add")
 { 
  $cart -> add_item($HTTP_GET_VARS["id"], $HTTP_POST_VARS["Quantity"], $HTTP_POST_VARS["Rad"], $OrderID);
 }
 else if(isset($action) && $action == "update")
 {
  # Check first to see if an item is to be removed
  
  /* an array of CHECKBOX FORM elements */
  
  $delete_item = $HTTP_POST_VARS["delete"];
  
  if(!empty($delete_item))
  {
   /* only if *this* CHECKBOX has been selected (delete item) */
   
   while(list($key, $id) = each($delete_item))
   { 
    $cart -> remove_item($id, $OrderID);
   }
  }
  
  # Check next to update item quantities in database table
  
  /* an array of SELECT FORM elements (quantity) */
  
  $qty_item = $HTTP_POST_VARS["select"];
  
  /* an array of HIDDEN FORM elements (product ref.) */
  
  $item_productid = $HTTP_POST_VARS["cartid"];
  
  /* an array of HIDDEN FORM elements (unique db table id) */
  
  $item_id = $HTTP_POST_VARS["id"];
  
  while(list($key, $cartid) = each($item_productid)) 
  { 
      $cart -> update_item($qty_item[$key] /* quantity */, $cartid, $item_id[$key] /* unique id */, $OrderID);
  }
 }
  
 # Create or Import a new template file(s)
 
 $p = new template;
 $p -> get_template("client/templates/template-head.tpl");
 $p -> get_template("client/templates/template-pane.tpl");
 ?>
 <table align="left" border="0" width="544" valign="top" cellspacing="0" cellpadding="0">
 <tbody>
 <tr>
 <td align="right" width="100%" valign="top">
  <br>
  <div style="text-align:left;width:544px;border-bottom:solid 1px skyblue;font:10px helvetica;letter-spacing:3px;">&nbsp;YOUR SHOPPING BASKET</div>
 </td></tr>
 <?
 $var = ($cart -> basket_total($OrderID) >= 1)? "true":"false";
 ?>
 <tr>
 <td align="right" width="100%" valign="top">
  <br>
  <table border="0" cellspacing="0" align="left" cellpadding="0"><tbody>
  <tr>
  <td align="left" width="100%"><? $cart -> show_basket_total($OrderID); ?></td></tr>
  </tbody></table>
 </td></tr>
 <?
 if($cart -> basket_total($OrderID) >= 1)
 {
  FormStartTag("BasketControl", "post", "basket.php?action=update");
  ?>
  <tr>
  <td align="right" width="100%" valign="top">
   <br>
   <table border="0" cellspacing="0" align="left" cellpadding="0"><tbody>
   <tr>
   <td width="9%" align="right" height="16" valign="top">Remove&nbsp;</td>
   <td width="12%" align="left" valign="top">&nbsp;Ref.</td>
   <td width="10%" align="center" valign="top">Price</td>
   <td width="12%" align="center" valign="top">Discount</td>
   <td width="8%" align="center" valign="top">Type</td>
   <td width="10%" align="center" valign="top">Quantity</td>
   <td width="23%" align="left" valign="top">Product Desc.</td>
   <td width="16%" align="left" valign="top">&nbsp;Sub-Total</td></tr>
   <?
   $db = new db_connect;
   $db -> select_user_db();
   
   $sql = $db -> query_user_db("SELECT * FROM product_cart WHERE cart_session = '$OrderID' ORDER BY id");
   
   $count = 0;
   $subtotal = 0;
   while($row = mysql_fetch_array($sql))
   {
    $Id = $row["id"];
    $cartId = $row["cart_id"];
    $cartType = $row["cart_type"];
    $cartQuantity = $row["cart_quantity"];
    $sql_1 = $db -> query_user_db("SELECT * FROM product_inventory WHERE inv_id = '$cartId'");
    
    $row_1 = mysql_fetch_array($sql_1);
    
    $itemPrice = $row_1["inv_price"];
    $itemDiscount = $row_1["inv_discount"];
    $itemProduct = $row_1["inv_product"];
    
    if($cartType != 1) 
    { 
     $itemPrice = round($itemPrice /= 2, 2); 
    }
    ?>
    <tr>
    <td align="right" width="9%" height="16" valign="center">
     <? FormElement("hidden", 0, 0, "id[]", $Id); ?>
     <? FormElement("hidden", 0, 0, "cartid[]", $cartId); ?>
     <input class="void" type="checkbox" name="delete[]" value="<? echo($Id); ?>">&nbsp;
    </td>
    <td align="left" width="12%" valign="center">&nbsp;<font color="yellow"><? echo($cartId); ?></font></td>
    <td align="center" width="10%" valign="center">
     <? FormElement("hidden", 0, 0, "price", $itemPrice); 
     echo("£".$itemPrice); ?>
    </td>
    <td align="center" width="12%" valign="center">
     <? 
     FormElement("hidden", 0, 0, "discount", $itemDiscount);
     if($itemDiscount == 0) { echo("None"); } else { echo($itemDiscount."%"); } ?>
    </td>
    <td align="center" width="8%" valign="center">
    <? 
     FormElement("hidden", 0, 0, "type", $cartType); 
     echo($cartType != 1? "Chart":"Kit"); ?>
    </td>
    <td align="center" width="10%" valign="center">
    <? 
    NumericSelectBox(9 /* select list limit */, "select[]", $cartQuantity /* option to pre-select */); 
    ?>
    </td>
    <td align="left" width="23%" valign="center"><? echo($itemProduct); ?></td>
    <td align="left" width="16%" valign="center">&nbsp;<div id="<? echo($count); ?>"></div></td></tr>
    <?
    $count++;
   }
   ?>
   <tr>
   <td colspan="7" height="16" valign="bottom"><br>Total (Excluding Postage & Packaging)</td>
   <td align="left" valign="bottom">&nbsp;<div id="subTotal"></div></td></tr>
   </tbody></table>
  </td></tr>
  <tr>
  <td valign="top" align="center" width="100%"><br><input type="button" value="&nbsp;<< Continue&nbsp;" onClick='window.location="index.php";'>&nbsp;<input type="submit" value="Update Basket">&nbsp;<input type="button" value="&nbsp;Checkout >>&nbsp;" onClick='window.location="checkout.php";'></td></tr>
  <?
  FormEndTag();
 }
 ?>
 </tbody></table>
 <? 
 $p -> get_template("client/templates/template-foot.tpl");

#catelog.php

include("client/protected/classes.php");
 
 # Create or Import a new template file(s)
 
 $p = new template;
 $p -> get_template("client/templates/template-head.tpl");
 $p -> get_template("client/templates/template-pane.tpl");
 ?>
 <table height="240" align="left" border="0" width="544" valign="top" cellspacing="0" cellpadding="0">
 <tbody>
 <tr>
 <td align="right" width="100%" valign="top" height="16">
  <br>
  <?
  if(!isset($HTTP_GET_VARS["id"]) || $HTTP_GET_VARS["id"] == "") 
  {
   $id = 2;
  }
  
  $db = new db_connect;
  $db -> select_user_db();
  $sql = $db -> query_user_db("SELECT product_catalog.cat_id, product_catalog.cat_name FROM product_catalog WHERE product_catalog.cat_id = '$id'");
  ?>
  <div style="text-align:left;width:544px;border-bottom:solid 1px skyblue;font:10px helvetica;letter-spacing:3px;">&nbsp;SEARCH PRODUCTS BY CATAGORY</div>
 </td></tr>
 <tr>
 <td align="right" width="100%" valign="top">
  <br>
  <table border="0" width="544" align="right" cellspacing="0" cellpadding="0"><tbody>
  <tr>
  <td colspan="3" align="left" width="100%">You searched products by <b><? echo(mysql_result($sql, 0, 1)); ?></b>.<br><br></td></tr>
  <?
  $query  = "SELECT product_inventory.inv_id AS ID, product_inventory.inv_product AS PRODUCT, product_inventory.inv_price AS PRICE, product_inventory.inv_discount AS DISCOUNT, ";
  $query .= "product_catalog.cat_id, ";
  $query .= "product_lookup.cat_id, product_lookup.inv_id, product_lookup.auth_id ";
  $query .= "FROM product_inventory, product_catalog, product_lookup ";
  $query .= "WHERE product_catalog.cat_id = '$id' AND product_lookup.cat_id = product_catalog.cat_id AND product_lookup.inv_id = product_inventory.inv_id";
  
  $sql = $db -> query_user_db($query);
  
  if($db -> total_rows($sql) >= 1)
  {
   ?>
   <tr>
   <td colspan="2">&nbsp;</td>
   <td height="16" valign="center" width="15%" align="left">Price</td>
   <td height="16" valign="center" width="20%" align="left">Discount</td></tr>
   <?
   $count = 0;
  
   while($row = mysql_fetch_array($sql))
   {
    $color = ($count++ & 1) ? "yellow":"white";
    ?>
    <tr>
    <td align="left" height="16" width="15%" valign="center"><font color="<? echo($color); ?>"><? echo($row["ID"]); ?></font></td>
    <td align="left" height="16" width="35%" valign="center"><a class="body" href="product.php?id=<? echo($row["ID"]); ?>" target="_self"><font color="<? echo($color); ?>"><? echo($row["PRODUCT"]); ?></font></a></td>
    <td align="left" height="16" width="15%" valign="center"><font color="<? echo($color); ?>">£<? echo($row["PRICE"]); ?></font></td>
    <td align="left" height="16" width="20%" valign="center"><font color="<? echo($color); ?>"><? if($row["DISCOUNT"] == "0") { echo("None"); } else { echo($row["DISCOUNT"]."%"); } ?></font></td></tr>
    <?
   }
  }
  else
  {
   ?>
   <tr>
   <td width="100%" align="left" height="16" valign="center">No products were found for this catagory.</td></tr>
   <?
  }
  ?>
  </tbody></table>
 </td></tr>
 <?
 $sql = $db -> query_user_db("SELECT * FROM product_catalog WHERE product_catalog.cat_parent = '$id' ORDER BY product_catalog.cat_id");
 
 if($db -> total_rows($sql) >= 1)
 {
  ?>
  <tr>
  <td align="right" width="100%" valign="top" height="100%">
   <br>
   <table border="0" width="544" align="right" cellspacing="0" cellpadding="0"><tbody>
   <tr>
   <td align="left" width="100%">The following sub-catagories below are also available.</td></tr>
   <tr>
   <td align="left" width="100%"><br>
   <ul>
   <?
   while($row = mysql_fetch_array($sql))
   {
    ?>
    <li><a class="body" href="catalog.php?id=<? echo($row['cat_id']); ?>" target="_self" title="<? echo($row['cat_description']); ?>"><? echo($row["cat_name"]); ?></a>
    <?
   }
   ?>
   </ul>
   </td></tr>
   </tbody></table>
  </td></tr>
  <?
 }
 ?>
 </tbody></table>
 <? 
 $p -> get_template("client/templates/template-foot.tpl");

#product.php

include("admin/library/library.php");
 include("client/protected/classes.php");
 
 # Get or Create a new Order ID and shopping basket
 
 $cart = new basket;
 
 # Create or Import a new template file(s)
 
 $p = new template;
 $p -> get_template("client/templates/template-head.tpl");
 $p -> get_template("client/templates/template-pane.tpl");
 ?>
 <table align="left" border="0" width="544" valign="top" cellspacing="0" cellpadding="0">
 <tbody>
 <?
 
 function getPathDir($ID, $NAME = "")
  { 
   $db = new db_connect;
   $db -> select_user_db();
   
   $query  = "SELECT product_catalog.cat_id, product_catalog.cat_parent, ";
   $query .= "product_catalog.cat_name ";
   $query .= "FROM product_catalog ";
   $query .= "WHERE product_catalog.cat_id = '$ID'";
   
   $sql = $db -> query_user_db($query);
   
   $Id = mysql_result($sql, 0, 0);
   $Parent = mysql_result($sql, 0, 1);
   $Name = mysql_result($sql, 0, 2);
   
   $NAME .= ",".$Name;
   
   if($Parent != 1)
   {
    $sql = $db -> query_user_db("SELECT product_catalog.cat_id FROM product_catalog WHERE product_catalog.cat_id = '$Parent'");
    
    return getPathDir(mysql_result($sql, 0, 0), $NAME);
   }
   else
   {
    return $NAME;
   }
  }
  
 $db = new db_connect;
 $db -> select_user_db();
  
 $query = "SELECT
   product_inventory.inv_id AS inventory,
   product_inventory.inv_price,
   product_inventory.inv_discount,
   product_inventory.inv_description,
   product_inventory.inv_product,
   product_inventory.inv_image,
   product_lookup.cat_id,
   product_lookup.inv_id,
   product_lookup.auth_id FROM
   product_inventory, product_lookup WHERE
   product_inventory.inv_id = product_lookup.inv_id AND
   product_lookup.inv_id = '$id'";
   
 $sql = $db -> query_user_db($query);
 $row = mysql_fetch_array($sql);
  
 $InventoryId = $row["inventory"];
 $InventoryPrice = $row["inv_price"];
 $InventoryDiscount = $row["inv_discount"];
 $InventoryDescription = $row["inv_description"];
 $InventoryProduct = $row["inv_product"];
 $InventoryImage = $row["inv_image"];
  
 $AuthorId = $row["auth_id"];
 $CatalogId = $row["cat_id"];
 $InventoryDescription = explode("\n", $InventoryDescription);
  
 $sql_ = $db -> query_user_db("SELECT auth_forename, auth_surname FROM product_author WHERE product_author.auth_id = '$AuthorId'");
 $row_ = mysql_fetch_array($sql_);
  
 $AuthorName = $row_["auth_forename"]." ".$row_["auth_surname"];
 
 $Directory = getPathDir($CatalogId); /* custom written function */
   
 $Direct = explode(",", $Directory); 
 $Direct = array_reverse($Direct);
 
 $ImagePath = ""; /* re-set variable to avoid previous pathname */
 
 foreach($Direct as $Val) 
 { 
  $ImagePath .= "/". $Val; 
 } 
    
 $InventoryImage = "client/images/product_catalog/".strtolower($ImagePath)."/".$InventoryImage.".jpg";
  
 $db -> close_user_db();
 ?>
 <tr>
 <td align="right" width="100%" valign="top">
  <br>
  <div style="text-align:left;width:544px;border-bottom:solid 1px skyblue;font:10px helvetica;letter-spacing:3px;">&nbsp;<? echo($InventoryProduct); ?></div>
 </td></tr>
 <tr>
 <td align="right" width="100%" valign="top">
  <br>
  <table border="0" width="544" align="right" cellspacing="0" cellpadding="0"><tbody>
  <tr>
  <td width="181" align="center" rowspan="6"><img src="<? echo($InventoryImage); ?>"></td>
  <td colspan="2" width="362" align="left" height="16" valign="center"><? echo($InventoryDescription[0]); ?></td></tr>
  <tr>
  <td colspan="2" width="362" align="left" height="16" valign="center"><? echo($InventoryDescription[1]); ?></td></tr>
  <tr>
  <td colspan="2" width="362" align="left" height="16" valign="center"><? echo($InventoryDescription[2]); ?></td></tr>
  <tr>
  <td colspan="2" width="362" align="left" height="16" valign="center"><? echo($InventoryDescription[3]); ?></td></tr>
  <tr>
  <td colspan="2" width="362" align="left" height="16" valign="center"><? echo($InventoryDescription[4]); ?></td></tr>
  <tr>
  <td colspan="2" width="362" align="left" height="16" valign="center"><? echo($InventoryDescription[5]); ?></td></tr></tr>
  <tr>
  <td width="181" align="center" height="16">Designer</td>
  <td width="181" align="center">Price (Per Kit)</td>
  <td width="181" align="center">Discount (Kit Only)</td></tr>
  <tr>
  <td width="181" align="center" height="16"><a class="body" href="profile.php?auth=<? echo($AuthorId); ?>" target="_self"><? echo($AuthorName); ?></a></td>
  <td width="181" align="center">£<? echo($InventoryPrice); ?></td>
  <td width="181" align="center"><? if($InventoryDiscount == "0") { echo("None"); } else { echo($InventoryDiscount."%"); } ?></td></tr>
  </tbody></table>
 </td></tr>
 <?
 FormStartTag("AddToBasket", "post", "basket.php?action=add&id=".$id);
 FormElement("hidden", 0, 0, "Price", $InventoryPrice);
 FormElement("hidden", 0, 0, "Discount", $InventoryDiscount);
 ?>
 <tr>
 <td colspan="3" align="right" width="100%" valign="top">
  <br>
  <table border="0" width="544" cellspacing="0" cellpadding="0" align="right"><tbody>
  <tr>
  <td colspan="4" width="100%" align="left" height="16" valign="center">Please choose whether you want the complete kit, or only the chart to this product.<br><br></td></tr> 
  <tr>
  <td width="40%" align="left" height="16" valign="center">Chart Only</td>
  <td width="10%" align="center" valign="center"><input class="void" type="radio" name="Rad[]" value="No" onClick="ReducePrice();"></td>
  <td width="40%" align="left" valign="center">Complete Kit</td>
  <td width="10%" align="center" valign="center"><input class="void" type="radio" name="Rad[]" value="Yes" checked onClick="RestorePrice();"></td></tr>
  </tbody></table>
 </td></tr>
 <tr>
 <td align="right" width="100%" valign="top">
  <br>
  <table border="0" width="544" cellspacing="0" cellpadding="0" align="right"><tbody>
  <tr>
  <td width="181" align="center" height="16">Product Code</td>
  <td width="181" align="center" height="16">Quantity</td>
  <td width="181" align="center" height="16">Sub-Total</td></tr>
  <tr>
  <td width="181" align="center" height="16"><font color="yellow"><? echo($id); ?></font></td>
  <td width="181" align="center" height="16">
   <select name="Quantity" size="1" onChange="ModifyBasket();">
   <?
   for($a = 1;$a <= 9;$a++)
   {
    if($a == 1)
    {
     echo('<option value="'.$a.'" selected>'.$a.'</option>');
    }
    else
    {
     echo('<option value="'.$a.'">'.$a.'</option>');
    }
   }
   ?>
   </select> 
  </td>
  <td width="181" align="center" height="16"><div id="SubTotal"></div></td></tr>
  <tr>
  <td colspan="3" width="544" align="center" height="16"><br><input type="button" value="<< Cancel" onClick="window.location='index.php';">&nbsp;<input type="submit" value="Basket >>"><br><br></td></tr>
  </tbody></table>
 </td></tr>
 <?
 FormEndTag();
 ?> 
 
 
 </tbody></table>
 <?
 $p -> get_template("client/templates/template-foot.tpl");

#checkout.php

include("admin/library/library.php");
 include("client/protected/classes.php");
 
 # Create or Import a new template file(s)
 
 $p = new template;
 $p -> get_template("client/templates/template-head.tpl");
 $p -> get_template("client/templates/template-pane.tpl");
 ?>
 <table height="240" align="left" border="0" width="544" valign="top" cellspacing="0" cellpadding="0">
 <tbody>
 <tr>
 <td align="right" width="100%" valign="top" height="16">
  <br>
  <div style="text-align:left;width:544px;border-bottom:solid 1px skyblue;font:10px helvetica;letter-spacing:3px;">&nbsp;CHECKING OUT YOUR SHOPPING BASKET</div>
 </td></tr>
 <?
 FormStartTag("PostageRegion", "post", "orders.php");
 ?>
 <tr>
 <td align="right" width="100%" valign="top" height="100%"><br>
  <table border="0" width="544" cellspacing="0" cellpadding="0"><tbody>
  <tr>
  <td colspan="2" align="left" height="16" width="100%">Please choose your postage region.</td></tr>
  <tr>
  <td colspan="2" align="left" height="16" width="100%"><br>
   <select name="Region" size="1" onChange="ModifyRates();">
   <option value="0" selected>--- Region ---</option>
   <option value="1">United Kingdom</option>
   <option value="2">Western Europe</option>
   <option value="3">Rest Of World</option>
   </select>&nbsp;Postage rates below based on selected region.
  </td></tr>
  <tr>
  <td colspan="2" align="left" width="100%"><br><div id="Rates">&nbsp;</div><br></td></tr>
  <tr>
  <td colspan="2" width="100%" align="left"><input class="void" type="checkbox" name="Express">&nbsp;Next day delivery UK mainland only (an additional charge is necessary).</td></tr>
  <tr>
  <td colspan="2" align="left" height="16" width="100%"><br>Please complete all form fields before sending your order.</td></tr>
  <tr>
  <td align="left" width="25%"><br>Title</td>
  <td align="left"><br><input type="text" size="8" name="Title"></td></tr>
  <tr>
  <td align="left" width="25%">Forename</td>
  <td align="left"><input type="text" size="16" name="Forename"></td></tr>
  <tr>
  <td align="left" width="25%">Surname</td>
  <td align="left"><input type="text" size="16" name="Surname"></td></tr>
  <tr>
  <td align="left" width="25%">Address 1</td>
  <td align="left"><input type="text" size="24" name="Address1"></td></tr>
  <tr>
  <td align="left" width="25%">Address 2</td>
  <td align="left"><input type="text" size="24" name="Address2"></td></tr>
  <tr>
  <td align="left" width="25%">Address 3</td>
  <td align="left"><input type="text" size="24" name="Address3"></td></tr>
  <tr>
  <td align="left" width="25%">Country</td>
  <td align="left"><input type="text" size="16" name="Country"></td></tr>
  <tr>
  <td align="left" width="25%">Postcode</td>
  <td align="left"><input type="text" size="12" name="Postcode"></td></tr>
  <tr>
  <td align="left" width="25%">Email Address</td>
  <td align="left"><input type="text" size="24" name="EmailAddress"></td></tr>
  <tr>
  <td colspan="2" width="100%" align="center"><br><input type="button" value="<< Basket" onClick="window.location='basket.php';">&nbsp;<input type="submit" value="&nbsp;Order >>"><br><br></td></tr>
  </tbody></table>
 </td></tr>
 <?
 FormEndTag();
 ?>
 </tbody></table>
 <? 
 $p -> get_template("client/templates/template-foot.tpl");

#orders.php

global $state;
 
 session_start();
 
 if(!$PHPSESSID || !$OrderID)
 {
  /* no session has been found, so begin a new one */
  
  session_register("OrderID");
  session_register("BasketTotal");
  
  session_register("CartPrice");
  session_register("CartQuantity");
  session_register("CartProduct");
  session_register("CartSubTotal");
  session_register("CartProductType");
  
  /* get a unique ID for this user */
  
  $OrderID = session_id();
  
  $BasketTotal = 0;
 }
 
?>
<html>
<head>
 <title>Order Form</title>
 <style type="text/css" media="screen">
 
 BODY    {
      color    : black;
      background-color : white;
      
      font    : 12px arial, helvetica;
      }
 
 TABLE    {
      font    : 12px arial, helvetica;
      }
           
 </style>
 <style type="text/css" media="print">
 
 BODY    {
      color    : black;
      background-color : white;
      
      font    : 12px arial, helvetica;
      }
 
 TABLE    {
      font    : 12px arial, helvetica;
      }
      
 DIV.OrderButtons {
      display    : none;
      }
      
 </style>
</head>
<body leftmargin="0" topmargin="4" marginwidth="0" marginheight="4">
 <table border="0" width="80%" align="center" cellspacing="0" cellpadding="0"><tbody>
 <tr>
 <td colspan="4" width="100%" align="center"><div style="font:bold 22px;">domain-name-here Limited</div></td></tr>
 <tr>
 <td colspan="4" width="100%" align="center">Please print this order form and post it to the following UK address<br><br><i>**** removed ****</i></td></tr>
 <tr>
 <td colspan="4" align="center" width="100%"><br>Please quote your customer reference at all times<br><br><font size="+1"><b><? echo($OrderID); ?></b></font></td></tr>
 <tr>
 <td colspan="4" align="left" width="100%"><br><br><div style="border-bottom:double 3px black;font:18px;">DELIVERY ADDRESS</div></td></tr>
 <tr>
 <td colspan="4" align="left" width="100%"><br><? echo($HTTP_POST_VARS["Title"]." ".$HTTP_POST_VARS["Forename"]." ".$HTTP_POST_VARS["Surname"]); ?></td></tr>
 <tr>
 <td colspan="4" align="left" width="100%"><? echo($HTTP_POST_VARS["Address1"]); ?></td></tr>
 <tr>
 <td colspan="4" align="left" width="100%"><? echo($HTTP_POST_VARS["Address2"]); ?></td></tr>
 <tr>
 <td colspan="4" align="left" width="100%"><? echo($HTTP_POST_VARS["Address3"]); ?></td></tr>
 <tr>
 <td colspan="4" align="left" width="100%"><? echo($HTTP_POST_VARS["Country"]); ?></td></tr>
 <tr>
 <td colspan="4" align="left" width="100%"><? echo($HTTP_POST_VARS["Postcode"]); ?></td></tr>
 <tr>
 <td colspan="4" align="left" width="100%"><br><br><div style="border-bottom:double 3px black;font:18px;">ORDER DETAILS</div><br></td></tr>
 <?
 if($BasketTotal != 0)
 {
  ?>
  <tr>
  <td width="25%" align="right" style="border-right:solid 2px black;border-bottom:solid 1px black;"><b>PRODUCT CODE&nbsp;</b></td>
  <td width="25%" align="left" style="border-right:solid 2px black;border-bottom:solid 1px black;"><b>&nbsp;TYPE</b></td>
  <td width="25%" align="center" style="border-right:solid 2px black;border-bottom:solid 1px black;"><b>QUANTITY</b></td>
  <td align="left" style="border-bottom:solid 1px black;"><b>&nbsp;SUB-TOTAL</b></td></tr>
  <?
  $total = 0;
  for($num = 0;$num < sizeOf($CartPrice);$num++)
  {
   if($CartProduct[$num] != 0)
   {
    ?>
    <tr>
    <td width="25%" align="right" height="16" valign="bottom" style="border-right:solid 2px black;"><b><? echo($CartProduct[$num]); ?>&nbsp;</b></td>
    <td width="25%" align="left" height="16" valign="bottom" style="border-right:solid 2px black;"><b>&nbsp;<? echo($CartProductType[$num]); ?></b></td>
    <td width="25%" align="center" valign="bottom" style="border-right:solid 2px black;"><b><? echo($CartQuantity[$num]); ?></b></td>
    <td align="left" valign="bottom"><b>&nbsp;£<? echo($CartSubTotal[$num]); ?></b></td></tr>
    <?
    
    $total += $CartSubTotal[$num];
   }
  }
  $postage = array(0, 3.95, 4.93, 6.16);
  
  $region = $HTTP_POST_VARS["Region"];
  $postagecost = $postage[$region];
  
  if($total <= 50 && $total >= 25) 
  { 
   $percentage = $postagecost / 100;
   $percentage = $percentage * 20;
   
   $posttotal = round($postagecost - $percentage, 2);
  }
  else if($total >= 50)
  {
   $percentage = $postagecost / 100;
   $percentage = $percentage * 40;
   
   $posttotal = round($postagecost - $percentage, 2);
  }
  else
  {
   $posttotal = $postagecost;
  }
  
  if($HTTP_POST_VARS["Express"] == "on" && $region == 1)
  {
   $posttotal += $postagecost;
  }
  
  $grandtotal = $posttotal + $total;
  ?>
  <tr>
  <td colspan="3" width="66%" height="16" valign="bottom" style="border-top:solid 1px black;"><b>Total (Excluding Postage & Packaging)</b></td>
  <td align="left" valign="bottom" style="border-top:solid 1px black;"><b>&nbsp;£<? echo($total); ?></b></td></tr>
  <tr>
  <td colspan="3" width="66%" height="16" valign="bottom" style="border-top:solid 1px black;"><b>Postage And Package <? if($HTTP_POST_VARS["Express"] == "on" && $region == 1) { echo("(Next Day Delivery)"); } ?></b></td>
  <td align="left" valign="bottom" style="border-top:solid 1px black;"><b>&nbsp;£<? echo($posttotal); ?></b></td></tr>
  <tr>
  <td colspan="3" width="66%" height="16" valign="bottom" style="border-top:solid 1px black;border-bottom:solid 2px black;"><b>Grand Total (Including Postage & Packaging)</b></td>
  <td align="left" valign="bottom" style="border-top:solid 1px black;border-bottom:solid 2px black;"><b>&nbsp;£<? echo($grandtotal); ?></b></td></tr>
  <?
 }
 else
 {
  ?>
  <tr>
  <td colspan="4" width="100%" align="left">Your basket is empty.</td></tr>
  <?
 }
 ?>
 <tr>
 <td colspan="4" width="100%" align="center"><div class="OrderButtons"><br><br><input style="font:bold;" type="button" value="<< CHECKOUT" onClick="window.location='checkout.php';">&nbsp;<input style="font:bold;" type="button" value="PRINT ORDER" name="Print" onClick="window.print();"></div></td></tr>
 <tr>
 <td colspan="4" width="100%" align="center"><br><br><font size="-2"><b><u>You are adviced to print a copy of this order form for your own reference.</u><br><br>Please read the terms of use for this website fully before you send an order, as doing so bounds you to those terms set out.<br>A copy of terms of use can also be requested from the UK address above.</b></font></td></tr>
 <tr>
 <td colspan="4" align="left" width="100%"><br><br><div style="border-bottom:double 3px black;font:18px;">COMMENTS TO PASS ON ?</div></td></tr>
 </tbody></table>
</body></html>

Sorry about the long post

[Gator99]

In your cart database it may be good to also store the actual price paid. That way if the product price changes in the future it won't effect the integrity of your historical data.

[sojomy]

Originally Posted by Gator99

In your cart database it may be good to also store the actual price paid. That way if the product price changes
in the future it won't effect the integrity of your historical data.

That brings up a question I had. In the orders database (where we keep orders that were actually submitted),
should there be any reference to the parts table? Like should I put the PartID of the product that was ordered
which would link it to the product details (specifically the name and price), or should I put the product name and price
in the orders table as well. Because what happens if in the future I rename or even remove a product from the
database, will the orders table be broken or linked to missing data?

And in the future, instead of removing products from the products table, should I just have a field that decides
whether or not to display them on the web? Is removing products that have been ordered a bad idea?

[nos]

I would be very careful using the sessionid as your unique/primary key/whatever. Remember, the sessionid is tied to that session. If the user's browser crashes/closes/whatever, all the information stored is essentially lost. In similar situations I've instead used something like md5(time() . $client_ip) or something like that. Then store this key in the session. If the user's browser closes for whatever reason, when they relogin you can retrieve this key and things are exactly where they left.

[sojomy]

Originally Posted by nos

I would be very careful using the sessionid as your unique/primary key/whatever. Remember, the sessionid is tied
to that session. If the user's browser crashes/closes/whatever, all the information stored is essentially lost. In similar
situations I've instead used something like md5(time() . $client_ip) or something like that. Then store this key in the session. If
the user's browser closes for whatever reason, when they relogin you can retrieve this key and things are exactly where they left.

nos,
how is storing the sessionid in the database (and a cookie to retrieve it later) any different than storing
md5(time() . $client_ip) in the database (and a cookie to retrieve it later)? Wouldn't it be the same thing
if you just stored the sessionid in a session variable and used it in the cookie and the database? And if the
browser closes/crashes or whatever, you would recall the variable from the cookie, and it wouldn't really
remember what the value was, just that it matches what was saved in the database, right?

I am still learning, so I am asking if it's the same thing, I'm not telling, k?
Thanks for the idea to think about too.

[Gator99]

Originally Posted by sojom

And in the future, instead of removing products from the products table, should I just have a field that decides whether or not to display them on the web? Is removing products that have been ordered a bad idea?

A flag field, such as a two parameter enum, that sets it to active or inactive in your catalog is a prefereable way to do this. Since the product table is referenced in your cart table, you wouldn't want to delete any entries.

[Gator99]

Originally Posted by nos

I would be very careful using the sessionid as your unique/primary key/whatever. Remember, the sessionid is tied to that session. If the user's browser crashes/closes/whatever, all the information stored is essentially lost. In similar situations I've instead used something like md5(time() . $client_ip) or something like that. Then store this key in the session.

If you're going to set a key as a Cookie, why do you need the Session? Simply use the cookie value to track the "session".

[sojomy]

Originally Posted by Gator99

If you're going to set a key as a Cookie, why do you need the Session? Simply use the cookie value to track the "session".

How do sites like sitepoint keep you logged in? Do they pull your cookie from your computer on every page load? Or
do they check for a session variable, and if there isn't one, then they load it from the cookie and store it in a session
variable? Is there any advantage for one or the other, either pulling the cookie on every page load, or using the session
variable on every page load and if there isn't one, then load it from the cookie?

[Gator99]

The sessionID itself is stored as a cookie (or url query if not available). So a site that keeps you logged in is usually reading a cookie on every page load whether or not they employ built-in php sessions. I guess the question would be is it better perfomance wise to read the user-info from the database on every page load or to store it in the session at log in and read it from there on every page load. Since the session data is written to a file on disk, I don't know if there would be any advantage to this as opposed to reading the data from mysql.

[sojomy]

Originally Posted by Gator99

I guess the question would be is it better perfomance wise to read the user-info from the database on every page load
or to store it in the session at log in and read it from there on every page load. Since the session data is written to a
file on disk, I don't know if there would be any advantage to this as opposed to reading the data from mysql.

But with the database method, you have to make a connection and close a connection to the database on every
pageload (plus any other database stuff you do on that page), right? Accessing a session variable is quicker
than making a database connection, making an sql query and closing the database connection from what I've
read. But I COULD be wrong...I'm used to it.