← Back to Module

PHP Forms & Database Integration

CMU529: Advanced Web Development - Session 6

Birmingham Newman University

Lecturer: James Williams

Connecting forms to databases with MySQL

3-hour session • 25 slides • 2 interactive tasks

Session Timeline:

  • 10 min: Registration & waiting
  • 20 min: Opening slides
  • 45 min: Task 1
  • 15 min: Break/Catch up
  • 20 min: Secondary slides
  • 45 min: Task 2
  • Remaining: Self-study

Learning Objectives

  • Connect PHP forms to MySQL databases
  • Implement secure database operations
  • Handle form data with database storage
  • Create user registration and login systems
  • Build data-driven web applications

Database Connection

Basic Database Connection:

<?php
// Database configuration
$host = "localhost";
$username = "root";
$password = "";
$database = "ecommerce";

// Create connection
try {
  $pdo = new PDO("mysql:host=$host;dbname=$database", $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Connected successfully";
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>
  • PDO provides database abstraction
  • Exception handling for errors
  • Secure connection management

Prepared Statements

Secure Database Queries:

// User registration with prepared statement
$stmt = $pdo->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");

$username = $_POST['username'];
$email = $_POST['email'];
$password = password_hash($_POST['password'], PASSWORD_DEFAULT);

try {
  $stmt->execute([$username, $email, $password]);
  echo "User registered successfully";
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
  • Prevents SQL injection attacks
  • Separates data from SQL logic
  • Improves performance with repeated queries

User Authentication

Login Verification:

function authenticateUser($email, $password) {
  global $pdo;

  $stmt = $pdo->prepare("SELECT id, username, password FROM users WHERE email = ?");
  $stmt->execute([$email]);
  $user = $stmt->fetch();

  if ($user && password_verify($password, $user['password'])) {
    $_SESSION['user_id'] = $user['id'];
    $_SESSION['username'] = $user['username'];
    return true;
  }
  return false;
}
  • Password hashing with password_hash()
  • Session management for user state
  • Secure credential verification

Form Processing

Complete Registration Process:

if ($_SERVER["REQUEST_METHOD"] == "POST") {
  $errors = validateRegistration($_POST);

  if (empty($errors)) {
    try {
      $stmt = $pdo->prepare("INSERT INTO users (username, email, password, created_at) VALUES (?, ?, ?, NOW())");
      $hashedPassword = password_hash($_POST['password'], PASSWORD_DEFAULT);
      $stmt->execute([$_POST['username'], $_POST['email'], $hashedPassword]);
      echo "Registration successful!";
    } catch(PDOException $e) {
      $errors[] = "Registration failed: " . $e->getMessage();
    }
  }
}
  • Validate input before database operations
  • Handle database errors gracefully
  • Provide user-friendly feedback

CRUD Operations

Create, Read, Update, Delete:

// CREATE - Insert new product
$stmt = $pdo->prepare("INSERT INTO products (name, price, description) VALUES (?, ?, ?)");
$stmt->execute([$name, $price, $description]);

// READ - Get all products
$stmt = $pdo->query("SELECT * FROM products ORDER BY name");
$products = $stmt->fetchAll();

// UPDATE - Modify product
$stmt = $pdo->prepare("UPDATE products SET price = ? WHERE id = ?");
$stmt->execute([$newPrice, $productId]);

// DELETE - Remove product
$stmt = $pdo->prepare("DELETE FROM products WHERE id = ?");
$stmt->execute([$productId]);
  • Always use prepared statements
  • Validate data before operations
  • Handle errors with try-catch

Data Validation

Database-Level Validation:

function checkEmailExists($email) {
  global $pdo;
  $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE email = ?");
  $stmt->execute([$email]);
  return $stmt->fetchColumn() > 0;
}

function validateUniqueUsername($username) {
  global $pdo;
  $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE username = ?");
  $stmt->execute([$username]);
  return $stmt->fetchColumn() == 0;
}
  • Check for duplicate data
  • Validate data integrity
  • Prevent database conflicts

Session Management

User Session Handling:

session_start();

// Check if user is logged in
function isLoggedIn() {
  return isset($_SESSION['user_id']);
}

// Logout function
function logout() {
  session_destroy();
  header("Location: login.php");
  exit();
}

// Protect pages
if (!isLoggedIn()) {
  header("Location: login.php");
  exit();
}
  • Maintain user state across requests
  • Secure session management
  • Protect restricted pages

Error Handling

Database Error Management:

try {
  $stmt = $pdo->prepare("INSERT INTO orders (user_id, total) VALUES (?, ?)");
  $stmt->execute([$userId, $total]);
  $orderId = $pdo->lastInsertId();

  // Insert order items
  foreach ($items as $item) {
    $stmt = $pdo->prepare("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)");
    $stmt->execute([$orderId, $item['id'], $item['quantity']]);
  }

  $pdo->commit();
  echo "Order placed successfully";
} catch(PDOException $e) {
  $pdo->rollback();
  error_log("Database error: " . $e->getMessage());
  echo "Order failed. Please try again.";
}
  • Use try-catch for database operations
  • Log errors for debugging
  • Provide user-friendly messages

Task 1: User Registration System

Instructions:

  1. Create a complete user registration system with:
    • User registration form with validation
    • Database connection using PDO
    • Prepared statements for security
    • Password hashing implementation
    • Email and username uniqueness check
    • Error handling and display
  2. Implement login functionality
  3. Add session management
  4. Create password reset functionality
  5. Test all security measures

Time: 45 minutes

This task will help you master database integration techniques

Break Time

15 Minutes

Take a break, ask questions, or catch up on the previous task.

Next: Secondary slides and Task 2

Database Schema

Users Table:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Products Table:

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock INT DEFAULT 0,
  image VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Product Management

Product Listing with Search:

<?php
// Pagination
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$limit = 12;
$offset = ($page - 1) * $limit;

// Search functionality
$search = isset($_GET['search']) ? trim($_GET['search']) : '';

// Build query
$where = "WHERE 1=1";
$params = [];

if (!empty($search)) {
  $where .= " AND (name LIKE ? OR description LIKE ?)";
  $params[] = "%$search%";
  $params[] = "%$search%";
}

// Get products
$stmt = $pdo->prepare("SELECT * FROM products $where ORDER BY created_at DESC LIMIT ? OFFSET ?");
$params[] = $limit;
$params[] = $offset;
$stmt->execute($params);
$products = $stmt->fetchAll();
?>

File Upload

Image Upload Handling:

function handleImageUpload($file) {
  $uploadDir = "uploads/";
  $errors = [];

  // Check if file was uploaded
  if ($file["error"] !== UPLOAD_ERR_OK) {
    $errors[] = "File upload failed";
    return $errors;
  }

  // Validate file type
  $allowedTypes = ["image/jpeg", "image/png", "image/gif"];
  if (!in_array($file["type"], $allowedTypes)) {
    $errors[] = "Invalid file type";
  }

  // Validate file size (2MB max)
  if ($file["size"] > 2 * 1024 * 1024) {
    $errors[] = "File too large (max 2MB)";
  }

  // Generate unique filename
  $extension = pathinfo($file["name"], PATHINFO_EXTENSION);
  $filename = uniqid() . "." . $extension;
  $filepath = $uploadDir . $filename;

  // Move uploaded file
  if (!move_uploaded_file($file["tmp_name"], $filepath)) {
    $errors[] = "Failed to save file";
  }

  return $errors;
}

Shopping Cart

Cart Database Tables:

CREATE TABLE cart (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  product_id INT,
  quantity INT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  total DECIMAL(10,2),
  status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Performance Optimization

Database Indexes:

// Add indexes for better performance
CREATE INDEX idx_products_name ON products(name);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_users_email ON users(email);

// Optimized query with joins
$stmt = $pdo->prepare("SELECT p.*, c.name as category_name FROM products p LEFT JOIN categories c ON p.category_id = c.id WHERE p.name LIKE ? ORDER BY p.created_at DESC LIMIT ? OFFSET ?");
  • Use indexes on frequently searched columns
  • Implement pagination for large datasets
  • Optimize image sizes and formats
  • Use lazy loading for images

Security Best Practices

Security Implementation:

// Secure password hashing
$hashedPassword = password_hash($password, PASSWORD_DEFAULT);

// Input sanitization
$username = htmlspecialchars(trim($_POST['username']));
$email = filter_var(trim($_POST['email']), FILTER_SANITIZE_EMAIL);

// CSRF protection
if (!isset($_POST['csrf_token']) || $_POST['csrf_token'] !== $_SESSION['csrf_token']) {
  die('CSRF token validation failed');
}
  • Always use prepared statements
  • Hash passwords with password_hash()
  • Validate and sanitize all input
  • Use HTTPS in production

Error Logging

Custom Error Handler:

function customErrorHandler($errno, $errstr, $errfile, $errline) {
  $errorMessage = date('Y-m-d H:i:s') . " Error: [$errno] $errstr in $errfile on line $errline\n";
  error_log($errorMessage, 3, "logs/error.log");
  return true;
}

set_error_handler("customErrorHandler");

// Database error handling
try {
  $stmt = $pdo->prepare("INSERT INTO products (name, price) VALUES (?, ?)");
  $stmt->execute([$name, $price]);
} catch(PDOException $e) {
  error_log("Database error: " . $e->getMessage());
  echo "An error occurred. Please try again.";
}

Testing & Debugging

Debug Functions:

// Debug function
function debug($data) {
  echo "<pre>";
  print_r($data);
  echo "</pre>";
}

// Test database connection
function testConnection() {
  global $pdo;
  try {
    $stmt = $pdo->query("SELECT 1");
    echo "Database connection successful";
  } catch(PDOException $e) {
    echo "Database connection failed: " . $e->getMessage();
  }
}

// Validate form data
function validateProductData($data) {
  $errors = [];
  if (empty($data['name'])) $errors[] = "Product name is required";
  if (!is_numeric($data['price']) || $data['price'] <= 0) $errors[] = "Valid price is required";
  return $errors;
}

Task 2: Complete E-commerce System

Instructions:

  1. Build a complete e-commerce system with:
    • User registration and login forms
    • Product management (CRUD operations)
    • Shopping cart functionality
    • Order processing system
    • File upload for product images
    • Search and filter functionality
  2. Implement comprehensive validation
  3. Add security measures (CSRF, input sanitization)
  4. Include error handling and logging
  5. Add pagination for product listings
  6. Test all database operations

Time: 45 minutes

This task will help you build a complete e-commerce database system

Session Summary

  • Database integration is essential for dynamic web applications
  • Prepared statements prevent SQL injection attacks
  • Password hashing protects user credentials
  • Session management maintains user state
  • Error handling improves application reliability
  • Performance optimization enhances user experience
  • Security best practices protect against vulnerabilities
  • Testing and debugging ensure code quality

Next Session:

Advanced PHP Features - Object-oriented programming and advanced techniques