العربية

Module 12: E-commerce Project

Course: Programming with Free AI Tools — Build Real Websites and Apps

Module: 12 of 17

Audience: Ages 15–25, beginners

Focus: Product catalog, product detail page, and MySQL database schema for an online store using PHP and MySQL.

Core principle: Always verify AI-generated code. Run queries, click through pages, and confirm data is correct before trusting generated output.

Table of Contents

Lesson 12.1: Designing the Store Database Schema

Duration: 60 minutes

Learning objectives

Instructor notes

Presentation outline

  1. Entities in a store: products, categories, orders (10 min)
  2. Primary and foreign keys (10 min)
  3. Column types: INT, VARCHAR, DECIMAL, TEXT, DATETIME (10 min)
  4. Writing CREATE TABLE SQL (15 min)
  5. Inserting sample data (15 min)

Demonstration

  1. Open phpMyAdmin and create database store.
  2. Run CREATE TABLE categories with id and name.
  3. Run CREATE TABLE products with category_id foreign key.
  4. Run CREATE TABLE orders referencing products.
  5. Insert 3 sample products and verify with SELECT FROM products;.

AI prompts

  1. "Design a MySQL schema for a small online store with products, categories, and orders tables. Include primary and foreign keys and the correct column types for prices."
  2. "Write INSERT statements to seed my products table with 5 example electronics items, including name, price, and description."

Starter code

Final code

Exercises

  1. Add a stock column (INT) to products and re-run the table creation.
  2. Create a customers table with id, name, email and link it to orders.
  3. Write a SELECT joining products and categories to show product name + category name.

Homework

  1. Design and create an order_items table to support multiple products per order (normalize the schema).
  2. Insert 5 more products across at least 2 categories and verify with a JOIN query.

Quiz

  1. Which column type should you use for a price?
  • a) FLOAT
  • b) DECIMAL(10,2) ✅
  • c) VARCHAR
  • d) TEXT
    1. What does a primary key do?
  • a) Styles the table
  • b) Uniquely identifies each row ✅
  • c) Stores images
  • d) Links to CSS
    1. A foreign key is used to:
  • a) Speed up the internet
  • b) Link a column to a primary key in another table ✅
  • c) Encrypt data
  • d) Delete the table
    1. What does AUTO_INCREMENT do?
  • a) Deletes old rows
  • b) Automatically gives each new row a unique id ✅
  • c) Increments prices
  • d) Renames the table
    1. Which SQL statement shows all rows in products?
  • a) SHOW products;
  • b) SELECT FROM products; ✅
  • c) GET products;
  • d) LIST products;

    • Lesson 12.2: Building the Product Catalog

    Duration: 60 minutes

    Learning objectives

    Instructor notes

    Presentation outline

    1. PHP–MySQL connection basics (10 min)
    2. Writing a SELECT query (10 min)
    3. Looping results into HTML cards (15 min)
    4. Bootstrap grid layout (15 min)
    5. Handling empty/error states (10 min)

    Demonstration

    1. Create config.php with mysqli_connect(...).
    2. Create catalog.php that runs SELECT FROM products.
    3. Output each product as a Bootstrap card with name, price, image.
    4. Show the "No products" message when the table is empty.
    5. Break the query to demonstrate error handling.

    AI prompts

    1. "Write PHP code using mysqli that connects to a MySQL database and displays all products from the products table as Bootstrap cards with name, price, and image."
    2. "Show how to keep my database credentials in a separate config.php and include it safely in other pages."

    Starter code

    Final code

    Exercises

    1. Add the product description to each card (truncated to 50 characters).
    2. Filter the catalog by category_id using a dropdown or query parameter.
    3. Show the category name by JOINing categories in the query.

    Homework

    1. Style the catalog with your own CSS and make it fully responsive on mobile.
    2. Add a search box that filters products by name using a SQL LIKE query (sanitize the input).

    Quiz

    1. Which function connects PHP to MySQL with mysqli?
  • a) mysql_open()
  • b) mysqli_connect() ✅
  • c) db_connect()
  • d) pdo()
    1. How do you include config.php in another page?
  • a) import 'config.php';
  • b) require 'config.php'; ✅
  • c) link config.php
  • d) #include
    1. What does mysqli_fetch_assoc() return?
  • a) A numbered array
  • b) An associative array of the row ✅
  • c) A JSON string
  • d) A boolean
    1. Why use htmlspecialchars() when echoing data?
  • a) To bold the text
  • b) To prevent XSS by escaping HTML ✅
  • c) To speed up PHP
  • d) It is required by MySQL
    1. What should you show if a query returns zero rows?
  • a) A blank page
  • b) A friendly "No products found" message ✅
  • c) A fatal error
  • d) The SQL query

    • Lesson 12.3: Building the Product Detail Page

    Duration: 45 minutes

    Learning objectives

    Instructor notes

    Presentation outline

    1. Reading $_GET['id'] (5 min)
    2. Why prepared statements matter (10 min)
    3. SELECT ... WHERE id = ? with bind (15 min)
    4. Rendering the detail view (10 min)
    5. Handling not-found (5 min)

    Demonstration

    1. Create product.php?id=1.
    2. Cast id: $id = (int) ($_GET['id'] ?? 0);.
    3. Prepare SELECT FROM products WHERE id = ?, bind, execute.
    4. Display full details; if no row, show "Product not found".
    5. Demonstrate a malicious ?id=1 OR 1=1 attempt and show the safe behavior.

    AI prompts

    1. "Write a PHP product detail page using a mysqli prepared statement that gets a product by id from $_GET and shows 404 if not found."
    2. "Explain SQL injection and show why using prepared statements with ? placeholders is safer than concatenating $_GET into a query."

    Starter code

    Final code

    Exercises

    1. Add a "Add to cart" button (links to cart logic from Lesson 12.4).
    2. Show the category name by joining categories.
    3. Display a placeholder image when image is empty.

    Homework

    1. Add a related-products section showing other items in the same category.
    2. Write a prepared statement that updates a product's stock when "purchased".

    Quiz

    1. How do you safely read an id from the URL?
  • a) $_GET['id'] without checks
  • b) Cast it: (int) ($_GET['id'] ?? 0) ✅
  • c) $_POST['id']
  • d) request.id
    1. What prevents SQL injection in this lesson?
  • a) Comments
  • b) Prepared statements with ? placeholders ✅
  • c) Uppercase SQL
  • d) Bootstrap
    1. What does mysqli_stmt_bind_param($stmt, 'i', $id) do?
  • a) Deletes the row
  • b) Binds an integer parameter to the query ✅
  • c) Connects to the DB
  • d) Renders HTML
    1. What should happen if the product id does not exist?
  • a) Show a blank page
  • b) Show a "Product not found" message ✅
  • c) Crash the server
  • d) Redirect to Google
    1. Why is concatenating $_GET into SQL dangerous?
  • a) It is slower
  • b) Attackers can inject their own SQL commands ✅
  • c) MySQL rejects it
  • d) It breaks Bootstrap

    • Lesson 12.4: Cart Basics and Connecting Pages

    Duration: 45 minutes

    Learning objectives

    Instructor notes

    Presentation outline

    1. PHP sessions intro (10 min)
    2. Adding to cart with $_SESSION (10 min)
    3. Displaying cart + total (15 min)
    4. Removing items (5 min)
    5. Wiring pages together (5 min)

    Demonstration

    1. Add session_start(); and a form on product.php posting to cart.php?action=add&id=1.
    2. In cart.php, increment $_SESSION['cart'][$id].
    3. Render cart table joining product data and sum the total.
    4. Add a "Remove" link that unsets the item.
    5. Click through catalog → detail → cart to verify.

    AI prompts

    1. "Write PHP code using sessions to add products to a shopping cart, display the cart with a total price, and allow removing items."
    2. "Explain how PHP sessions work and why session_start() must be called before any output."

    Starter code

    Final code

    Exercises

    1. Add a quantity update control (+ / −) on the cart page.
    2. Persist the cart to the database orders table on a "Checkout" button.
    3. Show the number of cart items in the header of every page.

    Homework

    1. Build a complete mini-store: catalog → detail → cart → simple checkout that inserts into orders.
    2. Write a one-page README explaining your database schema and how the pages connect.

    Quiz

    1. What must be called before any output to use PHP sessions?
  • a) session_open()
  • b) session_start() ✅
  • c) start_session()
  • d) session()
    1. Where are PHP session values stored between page loads?
  • a) In the URL
  • b) On the server, referenced by a session id ✅
  • c) In the browser cache only
  • d) In a cookie file the user can edit freely
    1. How do you add a product to $_SESSION['cart']?
  • a) $_SESSION['cart'][$id] = 1;
  • b) Both increment and set are valid ✅
  • c) cart.add($id)
  • d) session.cart.push
    1. Why compute the total in PHP rather than trusting the browser?
  • a) PHP is faster
  • b) Client values can be tampered with ✅
  • c) JavaScript cannot add
  • d) It is required by law
    1. What does unset($_SESSION['cart'][$id]) do?
  • a) Empties the whole cart
  • b) Removes that one item from the cart ✅
  • c) Logs the user out
  • d) Deletes the product
    • End of Module 12. Verify every query and click through each page before submitting your store.