How to store cart content in MYSQL table (foreach?)

Question

I have a working cart (see code below) but now i would like to store the content in 'orders' MYSQL table, in the 'dbexample' database. Added to that a 'order_number' and 'order_status' field. That would allow me to make a order tracking/status system.

My cart looks like this: (With the "buy now" i want to open the place-order.php. That's where the code should go.)

<?php
session_start();
include_once("config.php");
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>View shopping cart</title>
<link href="style/style.css" rel="stylesheet" type="text/css"></head>
<body>
<h1 align="center">View Cart</h1>
<div class="cart-view-table-back">
<form method="post" action="cart_update.php">
<table width="100%"  cellpadding="6" cellspacing="0"><thead><tr><th>Quantity</th><th>Name</th><th>Price</th><th>Total</th><th>Remove</th></tr></thead>
  <tbody>
    <?php
    if(isset($_SESSION["cart_products"])) //check session var
    {
        $total = 0; //set initial total value
        $b = 0; //var for zebra stripe table 
        foreach ($_SESSION["cart_products"] as $cart_itm)
        {
            //set variables to use in content below
            $product_name = $cart_itm["product_name"];
            $product_qty = $cart_itm["product_qty"];
            $product_price = $cart_itm["product_price"];
            $product_code = $cart_itm["product_code"];
            $product_color = $cart_itm["product_color"];
            $subtotal = ($product_price * $product_qty); //calculate Price x Qty

            $bg_color = ($b++%2==1) ? 'odd' : 'even'; //class for zebra stripe 
            echo '<tr class="'.$bg_color.'">';
            echo '<td><input type="text" size="2" maxlength="2" name="product_qty['.$product_code.']" value="'.$product_qty.'" /></td>';
            echo '<td>'.$product_name.'</td>';
            echo '<td>'.$currency.$product_price.'</td>';
            echo '<td>'.$currency.$subtotal.'</td>';
            echo '<td><input type="checkbox" name="remove_code[]" value="'.$product_code.'" /></td>';
            echo '</tr>';
            $total = ($total + $subtotal); //add subtotal to total var
        }

        $grand_total = $total + $shipping_cost; //grand total including shipping cost
        foreach($taxes as $key => $value){ //list and calculate all taxes in array
                $tax_amount     = round($total * ($value / 100));
                $tax_item[$key] = $tax_amount;
                $grand_total    = $grand_total + $tax_amount;  //add tax val to grand total
        }

        $list_tax       = '';
        foreach($tax_item as $key => $value){ //List all taxes
            $list_tax .= $key. ' : '. $currency. sprintf("%01.2f", $value).'<br />';
        }
        $shipping_cost = ($shipping_cost)?'Shipping Cost : '.$currency. sprintf("%01.2f", $shipping_cost).'<br />':'';
    }
    ?>
    <tr><td colspan="5"><span style="float:right;text-align: right;"><?php echo $shipping_cost. $list_tax; ?>Amount Payable : <?php echo sprintf("%01.2f", $grand_total);?></span></td></tr>
    <tr><td colspan="5"><a href="index.php" class="button">Add More Items</a><button type="submit">Update</button></td></tr>
    <a href="place-order.php" ><img src="images/buynow.jpg" width="179"
  </tbody>
</table>
<input type="hidden" name="return_url" value="<?php 
$current_url = urlencode($url="http://".$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI']);
echo $current_url; ?>" />
</form>
</div>

</body>
</html>

As a test i copied this page, called it place-order.php and only added:

$sql = "INSERT INTO orders (product_code, product_name, product_price, product_qty)
VALUES ('$product_code', '$product_name', '$product_price', '$product_qty' )";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

This works too, but only ONE product is added to the 'orders' table. I suspect it should be done with "for each" or something ? Tried A LOT but couldn't get it to work. Anyone who can point me in the right direction ?

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Sep 15 '17 at 14:29

Mateusz Marchel · Answer 1 · 2017-09-15T15:16:11.293

Using your syntax you can actually insert multiple rows like this.

INSERT INTO tableName
    (column1, column2, column3, column4)
VALUES
    ('value1', 'value2', 'value3', 'value4'),
    ('value1', 'value2', 'value3', 'value4'),
    ('value1', 'value2', 'value3', 'value4');

You can execute this in your existing foreach loop where you counting subtotals and total.

Rembember that using variables without sanitization in SQL queries leads to serious security issues. You can protect yourself against that using for example prepared queries for mysqli or PDO. You can read more about this here:

How can I prevent SQL injection in PHP?

http://php.net/manual/en/book.pdo.php

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

How to store cart content in MYSQL table (foreach?)

1 Answers1