Workshop 12 : PHP & MySQL Online Store Pt.3

In this workshop, we will further enhance the developed Online Store by adding a shopping cart functionality. To do this, we first need some new tables created in the lohky database to be able to implement the shopping cart functionality.

Mysql > use lohky 

mysql> create table store_shoppertrack(
     id int not null primary key auto_increment,
     session_id varchar (32),
     sel_item_id int,
     sel_item_qty smallint,
     sel_item_size varchar (25),
     sel_item_color varchar (25),
     date_added datetime
     );
Query OK, 0 rows affected (0.00 sec)

mysql> create table store_orders (
    -> id int not null primary key auto_increment,
    -> order_date datetime,
    -> order_name varchar (100),
    -> order_address varchar (255),
    -> order_city varchar (50),
    -> order_state char(50),
    -> order_zip varchar (10),
    -> order_tel varchar (25),
    -> order_email varchar (100),
    -> item_total float(6,2),
    -> shipping_total float (6,2),
    -> authorization varchar (50),
    -> status enum ('processed', 'pending')
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table store_orders_items(
    -> id int not null primary key auto_increment,
    -> order_id int,
    -> sel_item_id int,
    -> sel_item_qty smallint,
    -> sel_item_size varchar (25),
    -> sel_item_color varchar (25),
    -> sel_item_price float (6,2)
    -> )
    -> ;
Query OK, 0 rows affected (0.00 sec)

Then we move on to the PHP part of the shopping cart implementation, in which functions to manage (add/remove/view) the shopping cart is constructed:

addToCart.php:

<?php
session_start();

//connect to database
$conn = mysql_connect("localhost", "lohky", "welcome") or die(mysql_error());
mysql_select_db("lohky",$conn)  or die(mysql_error());

if ($_POST[sel_item_id] != "") {
   //validate item and get title and price
    $get_iteminfo = "select item_title from store_items where id = $_POST[sel_item_id]";
    $get_iteminfo_res = mysql_query($get_iteminfo) or die(mysql_error());

    if (mysql_num_rows($get_iteminfo_res) < 1) {
           //invalid id, send away
           header("Location: seestore.php");
           exit;
    } else {
           //get info
           $item_title =  mysql_result($get_iteminfo_res,0,'item_title');

           //add info to cart table
           $addtocart = "insert into store_shoppertrack values ('', '$PHPSESSID', '$_POST[sel_item_id]', '$_POST[sel_item_qty]', '$_POST[sel_item_size]', '$_POST[sel_item_color]', now())";
          mysql_query($addtocart);

           //redirect to showcart page
           header("Location: showcart.php");
          exit;
    }
} else {
    //send them somewhere else
    header("Location: seestore.php");
    exit;
}
?>

removeFromCart.php

<?php
session_start();
//connect to database
$conn = mysql_connect("localhost", "lohky", "welcome")  or die(mysql_error());
mysql_select_db("lohky",$conn)  or die(mysql_error());

if ($_GET[id] != "") {
    $delete_item = "delete from store_shoppertrack where id = $_GET[id] and session_id = '$PHPSESSID'";
    mysql_query($delete_item) or die(mysql_error());

    //redirect to showcart page
    header("Location: showcart.php");
    exit;

} else {
    //send them somewhere else
    header("Location: seestore.php");
    exit;
}
?>

showCart.php

<?php
session_start();

//connect to database
$conn = mysql_connect("localhost", "lohky", "welcome") or die(mysql_error());
mysql_select_db("lohky",$conn)  or die(mysql_error());

$display_block = "<h1>Your Shopping Cart</h1>";

//check for cart items based on user session id
$get_cart = "select st.id, si.item_title, si.item_price, st.sel_item_qty, st.sel_item_size, st.sel_item_color from store_shoppertrack as st  left join store_items as si on si.id = st.sel_item_id where  session_id = '$PHPSESSID'";
$get_cart_res = mysql_query($get_cart) or die(mysql_error());

if (mysql_num_rows($get_cart_res) < 1) {
    //print message
    $display_block .= "<P>You have no items in your cart.
    Please <a href=\"seestore.php\">continue to shop</a>!</p>";

} else {
    //get info and build cart display
    $display_block .= "
    <table celpadding=3 cellspacing=2 border=1 width=98%>
    <tr>
    <th>Title</th>
    <th>Size</th>
    <th>Color</th>
    <th>Price</th>
    <th>Qty</th>
    <th>Total Price</th>
    <th>Action</th>
    </tr>";

    while ($cart = mysql_fetch_array($get_cart_res)) {
           $id = $cart['id'];
           $item_title = stripslashes($cart['item_title']);
           $item_price = $cart['item_price'];
           $item_qty = $cart['item_qty'];
           $item_color = $cart['sel_item_color'];
           $item_size = $cart['sel_item_size'];
        $total_price = sprintf("%.02f", $item_price * $item_qty);

           $display_block .= "<tr>
           <td align=center>$item_title <br></td>
           <td align=center>$item_size <br></td>
           <td align=center>$item_color <br></td>
           <td align=center>\$ $item_price <br></td>
           <td align=center>$item_qty <br></td>
           <td align=center>\$ $total_price</td>
           <td align=center><a href=\"removefromcart.php?id=$id\">remove</a></td>
           </tr>";
    }

    $display_block .= "</table>";
}
?>
<HTML>
<HEAD>
<TITLE>My Store</TITLE>
</HEAD>
<BODY>
<? print $display_block; ?>
</BODY>
</HTML>