Transfer Cart from Session to Database

Question

I am writing a program with php/mysql in which logged in users can order items through a shopping cart which I have stored in the session. What I am trying to achieve is to get this cart information from the session and place it into my database when the user submits the order. No payment information is involved in my system. My php knowledge is basic to low intermediate.

My cart page, which displays the items in the users cart, does so via the following.

if($_SESSION['cart']) { //if the cart isn't empty
    //show the cart
    echo "<table width=\"100%\" border=\"0\" padding=\"3\" align=\"center\">";  //format the cart using a HTML table
        echo "<tr>";
            echo "<th align=\"center\"></th>";
            echo "<th align=\"center\">Quantity</th>";
            echo "<th align=\"center\">Item</th>";
            echo "<th align=\"center\">Price</th>";
        echo "</tr>";

        //iterate through the cart, the $product_id is the key and $quantity is the value
        foreach($_SESSION['cart'] as $item_id => $quantity) {   

            //get the name, description and price from the database - this will depend on your database implementation.
            //use sprintf to make sure that $product_id is inserted into the query as a number - to prevent SQL injection
            $sql = sprintf("SELECT title, price FROM food WHERE food_id = %d;", $item_id); 

            $result = mysql_query($sql);

            //Only display the row if there is a product (though there should always be as we have already checked)
            if(mysql_num_rows($result) > 0) {

                list($title, $price) = mysql_fetch_row($result);

                $line_cost = number_format($price * $quantity, 2);      //work out the line cost
                $total = number_format($total + $line_cost, 2);         //add to the total cost

                echo "<tr>";
                    //show this information in table cells
                    echo "<td align=\"center\"><a href=\"$_SERVER[PHP_SELF]?action=remove&id=$item_id\" data-role=\"button\" data-icon=\"minus\" data-iconpos=\"notext\" data-transition=\"none\">Remove Item</a></td>";
                    echo "<td align=\"center\">$quantity</td>";
                    echo "<td>$title</td>";
                    //along with a 'remove' link next to the quantity - which links to this page, but with an action of remove, and the id of the current product
                    echo "<td align=\"center\">$$line_cost</td>";
                echo "</tr>";

            }

        }

        //show the total
        echo "<tr>";
            echo "<th colspan=\"3\" align=\"right\" >Total</th>";
            echo "<td align=\"center\">$$total</td>";
        echo "</tr>";

        //show the empty cart link - which links to this page, but with an action of empty. A simple bit of javascript in the onlick event of the link asks the user for confirmation
        echo "<tr>";
            echo "<td colspan=\"4\" align=\"right\"><a href=\"/delete_cart.php\" data-role=\"button\" data-icon=\"delete\" data-iconpos=\"left\" data-rel=\"dialog\">Empty Cart</a></td>";
        echo "</tr>";

        echo "<tr>";
            echo "<td colspan=\"4\" align=\"center\"><a href=\"/order.php\" data-role=\"button\">Order</a></td>";
        echo "</tr>";
    echo "</table>";

}else{
    //otherwise tell the user they have no items in their cart
    echo "<p align=\"center\">You have no items in your shopping cart.</p>";

}

Then on the order page, I retrive the cart information again and get a few more details from the user. Below is the code that submits the order information to my orders table.

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "submit_order")) {
$insertSQL = sprintf("INSERT INTO orders (user_id, `date`, total, info) VALUES (%s, %s, %s, %s)",
                   GetSQLValueString($_POST['user_id'], "int"),
                   GetSQLValueString($_POST['date'], "text"),
                   GetSQLValueString($_POST['total'], "text"),
                   GetSQLValueString($_POST['info'], "text"));

mysql_select_db($database_ngs_canteen, $ngs_canteen);
$Result1 = mysql_query($insertSQL, $ngs_canteen) or die(mysql_error());

$insertGoTo = "/ngs_canteen/submit.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}

I am having trouble with entering the items in the shopping cart session to the database. My other form on this page which inserts the user's id, total price, order date works fine, but I can't for the life of me figure out how to submit the cart items.

I have tried things like this but have so far been unsuccessful.

$result = @mysql_query($query);
if (@mysql_affected_rows($dbc) == 1) {

// Get the Order ID.
$oid = @mysql_insert_id($dbc);

// Insert the specific order contents into the database.
$query = "INSERT INTO order_items (order_id, food_id, quantity, price) VALUES (";foreach ($_SESSION['cart'] as $item_id => $value) {
$query .= "$oid, $food_id, {$value['quantity']}, {$value['price']})";
}

Any help would be much appreciated, even a pointer in the right direction. If you need any extra information, please ask.

Thanks, Jake.

Answer 1

For anyone who runs into the same problem as myself in the future, after a lot of tinkering, here is the code that got things working for me.

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "submit_order")) {
$insertSQL = sprintf("INSERT INTO orders (user_id, `date`, total) VALUES (%s, %s, %s)",
                   GetSQLValueString($_POST['user_id'], "int"),
                   GetSQLValueString($_POST['date'], "text"),
                   GetSQLValueString($_POST['total'], "text"));

mysql_select_db($database_ngs_canteen, $ngs_canteen);
$Result1 = mysql_query($insertSQL, $ngs_canteen) or die(mysql_error());

$order_id = mysql_insert_id();

if ($order_id) {
foreach ($_SESSION['cart'] as $item_id => $quantity) {
    $sql = sprintf("SELECT food_id, price FROM food WHERE food_id = %d;", $item_id); 
            $result = mysql_query($sql);
            if(mysql_num_rows($result) > 0) {
                list($food_id, $price) = mysql_fetch_row($result);
                $line_cost = number_format($price * $quantity, 2);                  
            }   
$query = sprintf("INSERT INTO order_items (order_id, food_id, quantity, cost) VALUES (%s, %s, %s, %s)",
    GetSQLValueString("$order_id", "int"),
    GetSQLValueString("$food_id", "int"),
    GetSQLValueString("$quantity", "int"),
    GetSQLValueString("$line_cost", "text"));

mysql_select_db($database_ngs_canteen, $ngs_canteen);
$Result1 = mysql_query($query, $ngs_canteen) or die(mysql_error());
}}

$insertGoTo = "/ngs_canteen/submit.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}

After calling this code, the users shopping cart which was once stored in the session, will now be transfered perfectly into your database.