E5 - Save Order in Database

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.

Open phpMyAdmin and enter these commands:

1. (optional) Delete the database from previous examples and make a new one.

DROP DATABASE NeatTreats;
CREATE DATABASE NeatTreats;

2. Use the NeatTreats database for further manipulation.

USE NeatTreats;

3. Create a table called Cart with 3 INT fields: CartID, ProductID and Quantity. There will be no Product table at this stage.

CREATE TABLE Cart (
    CartID INT auto_increment,
    ProductID INT,
    Quantity INT,

4. 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.

    PRIMARY KEY (CartID, ProductID)
);

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

5. Add a link to checkout the temporary cart. Set the href attribute to "on_checkout.php".

<a href="on_checkout.php">Checkout</a>

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

6. Add PHP tags to the file.

<?php ?>

7. Create a database link object for querying the database with.

$databaseLink = new mysqli("localhost", "root", "", "NeatTreats");

8. 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.

$databaseLink->query("INSERT INTO Cart (ProductID, Quantity) VALUES (0, 0);");
$nextCartID = $databaseLink->insert_id;
$databaseLink->query("DELETE FROM Cart WHERE CartID=$nextCartID;");

9. 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.

$saveCartQuery = "INSERT INTO Cart (CartID, ProductID, Quantity) VALUES ";

10. Assign the cart cookie to a variable.

$cart = json_decode($_COOKIE["cart"] ?? "{}", true);

11. 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), ... ;

$isFirst = true;
foreach ($cart as $productID => $quantity) {
    if ($isFirst) $isFirst = false;
    else $saveCartQuery .= ", ";
    $saveCartQuery .= "($nextCartID, $productID, $quantity)";
}

12. Execute this query and close the database link.

$databaseLink->query($saveCartQuery);
$databaseLink->close();

13. Expire the cart cookie after you checkout because this is a typical online shop feature.

setcookie("cart", "", time() - 3600, "/");

14. Redirect the user to an order_confirm.php page.

header("Location: order_confirm.php");

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

16. The final code:

<?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. Add some basic HTML with a headline and paragraph thanking the customer for their order.

<html><body> <h2>Order Confirmation</h2> <p>Thanks for your order!</p> </body></html>

18. Add a header redirection to the above file on_checkout.php on the line above where we set the cookie to clear the cart.

header("Location: order_confirm.php");

Parent topic: Example 5