PHP Project — Build an E-Commerce System from Scratch

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  parent_id INT DEFAULT NULL,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(280) NOT NULL UNIQUE,
  description TEXT,
  price DECIMAL(10, 2) NOT NULL,
  compare_at_price DECIMAL(10, 2) DEFAULT NULL,
  stock_quantity INT NOT NULL DEFAULT 0,
  sku VARCHAR(50) NOT NULL UNIQUE,
  is_visible TINYINT(1) DEFAULT 1,
  image_url VARCHAR(500) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE carts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_id VARCHAR(255) NOT NULL,
  user_id INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE cart_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cart_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT DEFAULT NULL,
  session_id VARCHAR(255) NOT NULL,
  status ENUM('pending', 'paid', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
  subtotal DECIMAL(10, 2) NOT NULL,
  tax DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  total DECIMAL(10, 2) NOT NULL,
  shipping_address TEXT,
  billing_address TEXT,
  payment_method VARCHAR(50),
  stripe_payment_intent VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  total_price DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

<?php
require_once 'config.php';

$category = $_GET['category'] ?? null;
$search = $_GET['search'] ?? null;
$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 12;
$offset = ($page - 1) * $perPage;

$pdo = getDb();
$where = ['p.is_visible = 1'];
$params = [];

if ($category) {
  $where[] = 'c.slug = :category';
  $params['category'] = $category;
}

if ($search) {
  $where[] = '(p.name LIKE :search OR p.description LIKE :search2)';
  $params['search'] = "%$search%";
  $params['search2'] = "%$search%";
}

$whereClause = implode(' AND ', $where);

$countStmt = $pdo->prepare("SELECT COUNT(*) FROM products p JOIN categories c ON p.category_id = c.id WHERE $whereClause");
$countStmt->execute($params);
$total = $countStmt->fetchColumn();
$totalPages = max(1, ceil($total / $perPage));

$stmt = $pdo->prepare("
  SELECT p.id, p.name, p.slug, p.price, p.compare_at_price, p.image_url, p.stock_quantity,
         c.name as category_name
  FROM products p
  JOIN categories c ON p.category_id = c.id
  WHERE $whereClause
  ORDER BY p.created_at DESC
  LIMIT :limit OFFSET :offset
");
$stmt->bindValue('limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue('offset', $offset, PDO::PARAM_INT);
foreach ($params as $key => $val) {
  $stmt->bindValue(":$key", $val);
}
$stmt->execute();
$products = $stmt->fetchAll();
?>
<div class="product-grid">
  <?php foreach ($products as $product): ?>
    <div class="product-card">
      <a href="/product.php?slug=<?= urlencode($product['slug']) ?>">
        <img src="<?= htmlspecialchars($product['image_url'] ?? '/images/placeholder.png') ?>"
             alt="<?= htmlspecialchars($product['name']) ?>" loading="lazy">
        <h3><?= htmlspecialchars($product['name']) ?></h3>
        <div class="price">
          $<?= number_format($product['price'], 2) ?>
          <?php if ($product['compare_at_price']): ?>
            <span class="compare-price">$<?= number_format($product['compare_at_price'], 2) ?></span>
          <?php endif; ?>
        </div>
        <div class="stock <?= $product['stock_quantity'] > 0 ? 'in-stock' : 'out-of-stock' ?>">
          <?= $product['stock_quantity'] > 0 ? 'In Stock' : 'Out of Stock' ?>
        </div>
      </a>
      <button onclick="addToCart(<?= $product['id'] ?>)" <?= $product['stock_quantity'] < 1 ? 'disabled' : '' ?>>
        Add to Cart
      </button>
    </div>
  <?php endforeach; ?>
</div>

<?php if ($totalPages > 1): ?>
<div class="pagination">
  <?php for ($i = 1; $i <= $totalPages; $i++): ?>
    <a href="/catalog.php?page=<?= $i ?><?= $category ? '&category=' . urlencode($category) : '' ?>"><?= $i ?></a>
  <?php endfor; ?>
</div>
<?php endif; ?>

<?php
require_once 'config.php';

session_start();
$sessionId = session_id();

$pdo = getDb();

// Get or create cart
$stmt = $pdo->prepare('SELECT id FROM carts WHERE session_id = :sid');
$stmt->execute(['sid' => $sessionId]);
$cart = $stmt->fetch();

if (!$cart) {
  $stmt = $pdo->prepare('INSERT INTO carts (session_id) VALUES (:sid)');
  $stmt->execute(['sid' => $sessionId]);
  $cartId = $pdo->lastInsertId();
} else {
  $cartId = $cart['id'];
}

// Add item to cart
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['product_id'])) {
  $productId = (int)$_POST['product_id'];
  $quantity = max(1, (int)($_POST['quantity'] ?? 1));

  $stmt = $pdo->prepare('SELECT stock_quantity FROM products WHERE id = :id AND is_visible = 1');
  $stmt->execute(['id' => $productId]);
  $product = $stmt->fetch();

  if ($product && $product['stock_quantity'] >= $quantity) {
    $stmt = $pdo->prepare('SELECT id, quantity FROM cart_items WHERE cart_id = :cart_id AND product_id = :product_id');
    $stmt->execute(['cart_id' => $cartId, 'product_id' => $productId]);
    $existing = $stmt->fetch();

    if ($existing) {
      $newQty = min($existing['quantity'] + $quantity, $product['stock_quantity']);
      $stmt = $pdo->prepare('UPDATE cart_items SET quantity = :qty WHERE id = :id');
      $stmt->execute(['qty' => $newQty, 'id' => $existing['id']]);
    } else {
      $stmt = $pdo->prepare('INSERT INTO cart_items (cart_id, product_id, quantity) VALUES (:cart_id, :product_id, :qty)');
      $stmt->execute(['cart_id' => $cartId, 'product_id' => $productId, 'qty' => $quantity]);
    }
  }
}

// Get cart items
$stmt = $pdo->prepare('
  SELECT ci.id, ci.quantity, p.id as product_id, p.name, p.price, p.image_url, p.stock_quantity
  FROM cart_items ci
  JOIN products p ON ci.product_id = p.id
  WHERE ci.cart_id = :cart_id
');
$stmt->execute(['cart_id' => $cartId]);
$items = $stmt->fetchAll();

$subtotal = 0;
foreach ($items as $item) {
  $subtotal += $item['price'] * $item['quantity'];
}
?>

<h1>Shopping Cart</h1>

<?php if (empty($items)): ?>
  <p>Your cart is empty. <a href="/catalog.php">Continue shopping</a></p>
<?php else: ?>
  <table>
    <tr><th>Product</th><th>Price</th><th>Quantity</th><th>Total</th><th></th></tr>
    <?php foreach ($items as $item): ?>
    <tr>
      <td><?= htmlspecialchars($item['name']) ?></td>
      <td>$<?= number_format($item['price'], 2) ?></td>
      <td>
        <form method="POST" action="/cart/update.php" style="display:inline">
          <input type="hidden" name="item_id" value="<?= $item['id'] ?>">
          <input type="number" name="quantity" value="<?= $item['quantity'] ?>" min="1" max="<?= $item['stock_quantity'] ?>">
          <button type="submit">Update</button>
        </form>
      </td>
      <td>$<?= number_format($item['price'] * $item['quantity'], 2) ?></td>
      <td><a href="/cart/remove.php?id=<?= $item['id'] ?>">Remove</a></td>
    </tr>
    <?php endforeach; ?>
  </table>
  <h3>Subtotal: $<?= number_format($subtotal, 2) ?></h3>
  <a href="/checkout.php" class="btn">Proceed to Checkout</a>
<?php endif; ?>

<?php
require_once 'config.php';
require_once 'vendor/autoload.php';

session_start();
$sessionId = session_id();

\Stripe\Stripe::setApiKey(STRIPE_SECRET_KEY);

$pdo = getDb();

$stmt = $pdo->prepare('SELECT id FROM carts WHERE session_id = :sid');
$stmt->execute(['sid' => $sessionId]);
$cart = $stmt->fetch();

if (!$cart) {
  header('Location: /catalog.php');
  exit;
}

$stmt = $pdo->prepare('
  SELECT ci.quantity, p.id, p.name, p.price
  FROM cart_items ci JOIN products p ON ci.product_id = p.id
  WHERE ci.cart_id = :cart_id
');
$stmt->execute(['cart_id' => $cart['id']]);
$items = $stmt->fetchAll();

if (empty($items)) {
  header('Location: /catalog.php');
  exit;
}

$subtotal = 0;
$lineItems = [];
foreach ($items as $item) {
  $lineTotal = $item['price'] * $item['quantity'];
  $subtotal += $lineTotal;
  $lineItems[] = [
    'price_data' => [
      'currency' => 'usd',
      'product_data' => ['name' => $item['name']],
      'unit_amount' => (int)round($item['price'] * 100),
    ],
    'quantity' => $item['quantity'],
  ];
}

$tax = round($subtotal * 0.08, 2);  // 8% tax
$total = $subtotal + $tax;

// Create Stripe checkout session
$checkoutSession = \Stripe\Checkout\Session::create([
  'payment_method_types' => ['card'],
  'line_items' => $lineItems,
  'mode' => 'payment',
  'success_url' => SITE_URL . '/order-confirmed.php?session_id={CHECKOUT_SESSION_ID}',
  'cancel_url' => SITE_URL . '/cart.php',
  'metadata' => [
    'cart_id' => $cart['id'],
    'session_id' => $sessionId,
  ],
]);

echo '<!DOCTYPE html><html><head>';
echo '<script src="https://js.stripe.com/v3/"></script>';
echo '</head><body>';
echo '<h1>Checkout</h1>';
echo '<p>Subtotal: $' . number_format($subtotal, 2) . '</p>';
echo '<p>Tax (8%): $' . number_format($tax, 2) . '</p>';
echo '<p><strong>Total: $' . number_format($total, 2) . '</strong></p>';
echo '<button id="checkout-button">Pay with Card</button>';
echo '<script>';
echo 'var stripe = Stripe("' . STRIPE_PUBLISHABLE_KEY . '");';
echo 'document.getElementById("checkout-button").addEventListener("click", function() {';
echo '  stripe.redirectToCheckout({ sessionId: "' . $checkoutSession->id . '" });';
echo '});';
echo '</script>';
echo '</body></html>';