DEV Community

David Kanekanian
David Kanekanian

Posted on

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;
Enter fullscreen mode Exit fullscreen mode

2. Use the NeatTreats database for further manipulation.

USE NeatTreats;
Enter fullscreen mode Exit fullscreen mode

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,
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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

6. Add PHP tags to the file.

<?php ?>
Enter fullscreen mode Exit fullscreen mode

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

$databaseLink = new mysqli("localhost", "root", "", "NeatTreats");
Enter fullscreen mode Exit fullscreen mode

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;");
Enter fullscreen mode Exit fullscreen mode

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 ";
Enter fullscreen mode Exit fullscreen mode

10. Assign the cart cookie to a variable.

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

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)";
}
Enter fullscreen mode Exit fullscreen mode

12. Execute this query and close the database link.

$databaseLink->query($saveCartQuery);
$databaseLink->close();
Enter fullscreen mode Exit fullscreen mode

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

setcookie("cart", "", time() - 3600, "/");
Enter fullscreen mode Exit fullscreen mode

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

header("Location: order_confirm.php");
Enter fullscreen mode Exit fullscreen mode

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, "/");
?>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

Parent topic: Example 5

Top comments (0)