E5 - Save Order in Database
Tags: php, sql, tutorial
Published: 2020-04-15 19:16:52

Once the cart is confirmed, it must be saved in the database. First we must create a table to house the confirmed carts.

To get the most out of this tutorial, you should have already completed the previous stage where we created [a temporary shopping cart](https://dev.to/herobank110/e5-temporary-shopping-cart-4bc1).

Open phpMyAdmin and enter these commands:

1.&nbsp;(optional) Delete the database from previous examples and make a new one.
```sql
DROP DATABASE NeatTreats;
CREATE DATABASE NeatTreats;
```

2.&nbsp;Use the NeatTreats database for further manipulation.
```sql
USE NeatTreats;
```

3.&nbsp;Create a table called Cart with 3 INT fields: CartID, ProductID and Quantity. There will be no Product table at this stage.
```sql
CREATE TABLE Cart (
    CartID INT auto_increment,
    ProductID INT,
    Quantity INT,
```

4.&nbsp;Make a compound primary key with the CartID and ProductID. This means several rows can have the same CartID but they must have different ProductIDs.
```sql
    PRIMARY KEY (CartID, ProductID)
);
```

Edit `cart.php` from the previous stage in the following steps:

5.&nbsp;Add a link to checkout the temporary cart. Set the href attribute to "on_checkout.php".
```php
<a href="on_checkout.php">Checkout</a>
```

Create a file called `on_checkout.php` for editing in the following steps:

6.&nbsp;Add PHP tags to the file.
```php
<?php ?>
```

7.&nbsp;Create a database link object for querying the database with.
```php
$databaseLink = new mysqli("localhost", "root", "", "NeatTreats");
```

8.&nbsp;Each time we checkout, we want all the products to share a CartID. The CartID field in the Cart table is set to auto increment, so it automatically picks the next valid CartID when you try to insert something. But when you insert multiple rows, they each get a different ID. Therefore, add a temporary row to obtain the next valid CartID, then delete the temporary row.
```php
$databaseLink->query("INSERT INTO Cart (ProductID, Quantity) VALUES (0, 0);");
$nextCartID = $databaseLink->insert_id;
$databaseLink->query("DELETE FROM Cart WHERE CartID=$nextCartID;");
```

9.&nbsp;Create a variable to store the actual query of saving the cart in the database. The values will be added from a loop on the actual cart items.
```php
$saveCartQuery = "INSERT INTO Cart (CartID, ProductID, Quantity) VALUES ";
```

10.&nbsp;Assign the cart cookie to a variable.
```php
$cart = json_decode($_COOKIE["cart"] ?? "{}", true);
```

11.&nbsp;Use a foreach loop to add each row’s values from the cart cookie. Rows need to be separated with a comma, but without a comma before the first row or after the last row. For visualisation purposes, the final query should hopefully look something like this: `INSERT INTO Cart (CartID, ProductID, Quantity) VALUES (123, 1, 5), (123, 2, 4), ... ;`
```php
$isFirst = true;
foreach ($cart as $productID => $quantity) {
    if ($isFirst) $isFirst = false;
    else $saveCartQuery .= ", ";
    $saveCartQuery .= "($nextCartID, $productID, $quantity)";
}
```

12.&nbsp;Execute this query and close the database link.
```php
$databaseLink->query($saveCartQuery);
$databaseLink->close();
```

13.&nbsp;Expire the cart cookie after you checkout because this is a typical online shop feature.
```php
setcookie("cart", "", time() - 3600, "/");
```

14.&nbsp;Redirect the user to an order_confirm.php page.
```php
header("Location: order_confirm.php");
```

15.&nbsp;Place an order in the database and then open phpMyAdmin to check on the database.

16.&nbsp;The final code:
```php
<?php
$databaseLink = new mysqli("localhost", "root", "", "NeatTreats");
$databaseLink->query("INSERT INTO Cart (ProductID, Quantity) VALUES (0, 0);");
$nextCartID = $databaseLink->insert_id;
$databaseLink->query("DELETE FROM Cart WHERE CartID=$nextCartID;");


$saveCartQuery = "INSERT INTO Cart (CartID, ProductID, Quantity) VALUES ";
$cart = json_decode($_COOKIE["cart"] ?? "{}", true);
$isFirst = true;
foreach ($cart as $productID => $quantity) {
    if ($isFirst) $isFirst = false;
    else $saveCartQuery .= ", ";
    $saveCartQuery .= "($nextCartID, $productID, $quantity)";
}
$saveCartQuery .= ";";
$databaseLink->query($saveCartQuery);
$databaseLink->close();

setcookie("cart", "", time() - 3600, "/");
?>
```

## Bonus Stage

Create a file called `order_confirm.php` and open it for editing in the following steps:

17.&nbsp;Add some basic HTML with a headline and paragraph thanking the customer for their order.
```php
<html><body> <h2>Order Confirmation</h2> <p>Thanks for your order!</p> </body></html>
```

18.&nbsp;Add a header redirection to the above file `on_checkout.php` on the line above where we set the cookie to clear the cart.
```php
header("Location: order_confirm.php");
```

Parent topic: [Example 5](https://dev.to/herobank110/exercise-5-shopping-facility-443n)