PHP Database Connectivity

CMU529: Advanced Web Development - Session 7

Birmingham Newman University

Lecturer: James Williams

Connecting PHP to MySQL, CRUD operations, prepared statements

3-hour session • 22 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 to MySQL databases
Perform CRUD operations with PHP
Use prepared statements for security
Handle database errors properly
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

Basic Queries

                    Simple SELECT Query:

                    // Get all products

                    $stmt = $pdo->query("SELECT * FROM products");

                    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

                    // Display products

                    foreach ($products as $product) {

                      echo "Name: " . $product['name'] . "<br>";

                      echo "Price: $" . $product['price'] . "<br>";

                    }

                    // Get single product

                    $stmt = $pdo->query("SELECT * FROM products WHERE id = 1");

                    $product = $stmt->fetch(PDO::FETCH_ASSOC);

Use query() for simple queries
fetchAll() gets all results
fetch() gets single result

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

INSERT Operations

                    Adding New Records:

                    // Insert new product

                    $stmt = $pdo->prepare("INSERT INTO products (name, price, description) VALUES (?, ?, ?)");

                    $name = "Laptop";

                    $price = 999.99;

                    $description = "High-performance laptop";

                    try {

                      $stmt->execute([$name, $price, $description]);

                      $productId = $pdo->lastInsertId();

                      echo "Product added with ID: " . $productId;

                    } catch(PDOException $e) {

                      echo "Insert failed: " . $e->getMessage();

                    }

Use prepared statements for security
lastInsertId() gets the new record ID
Always handle exceptions

SELECT with Conditions

                    Filtered Queries:

                    // Get products by category

                    $category = "electronics";

                    $stmt = $pdo->prepare("SELECT * FROM products WHERE category = ?");

                    $stmt->execute([$category]);

                    $products = $stmt->fetchAll();

                    // Get expensive products

                    $minPrice = 500;

                    $stmt = $pdo->prepare("SELECT * FROM products WHERE price > ? ORDER BY price DESC");

                    $stmt->execute([$minPrice]);

                    $expensiveProducts = $stmt->fetchAll();

                    // Search products

                    $searchTerm = "%laptop%";

                    $stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE ?");

                    $stmt->execute([$searchTerm]);

Use WHERE clauses for filtering
ORDER BY for sorting results
LIKE for pattern matching

UPDATE Operations

                    Modifying Records:

                    // Update product price

                    $productId = 1;

                    $newPrice = 899.99;

                    $stmt = $pdo->prepare("UPDATE products SET price = ? WHERE id = ?");

                    $stmt->execute([$newPrice, $productId]);

                    // Update multiple fields

                    $stmt = $pdo->prepare("UPDATE products SET name = ?, price = ?, description = ? WHERE id = ?");

                    $stmt->execute(["Gaming Laptop", 1299.99, "High-end gaming laptop", $productId]);

                    // Check if update was successful

                    if ($stmt->rowCount() > 0) {

                      echo "Product updated successfully";

                    } else {

                      echo "No product found with that ID";

                    }

rowCount() shows affected rows
Always use WHERE clause
Update multiple fields in one query

DELETE Operations

                    Removing Records:

                    // Delete product by ID

                    $productId = 5;

                    $stmt = $pdo->prepare("DELETE FROM products WHERE id = ?");

                    $stmt->execute([$productId]);

                    // Delete multiple records

                    $category = "old_products";

                    $stmt = $pdo->prepare("DELETE FROM products WHERE category = ?");

                    $stmt->execute([$category]);

                    // Check deletion result

                    if ($stmt->rowCount() > 0) {

                      echo "Deleted " . $stmt->rowCount() . " products";

                    } else {

                      echo "No products found to delete";

                    }

Always use WHERE clause
Be careful with DELETE operations
Check rowCount() for confirmation

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: Basic CRUD Operations

Instructions:

Create a simple product management system with:

Database connection using PDO
Add new products (INSERT)
Display all products (SELECT)
Update product details (UPDATE)
Delete products (DELETE)

Use prepared statements for all queries
Add proper error handling
Create a simple HTML interface
Test all CRUD operations

Time: 45 minutes

This task will help you master basic database operations

Break Time

15 Minutes

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

Next: Secondary slides and Task 2

Database Joins

                    Joining Tables:

                    // Get products with category names

                    $stmt = $pdo->prepare("SELECT p.*, c.name as category_name FROM products p LEFT JOIN categories c ON p.category_id = c.id");

                    $stmt->execute();

                    $products = $stmt->fetchAll();

                    // Get orders with user details

                    $stmt = $pdo->prepare("SELECT o.*, u.username FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE o.user_id = ?");

                    $stmt->execute([$userId]);

                    $orders = $stmt->fetchAll();

                    // Display joined data

                    foreach ($products as $product) {

                      echo "Product: " . $product['name'] . " - Category: " . $product['category_name'] . "<br>";

                    }

LEFT JOIN includes all left table records
INNER JOIN only includes matching records
Use table aliases for clarity

Aggregation Functions

                    Data Aggregation:

                    // Count total products

                    $stmt = $pdo->query("SELECT COUNT(*) as total FROM products");

                    $result = $stmt->fetch();

                    echo "Total products: " . $result['total'] . "<br>";

                    // Average product price

                    $stmt = $pdo->query("SELECT AVG(price) as avg_price FROM products");

                    $result = $stmt->fetch();

                    echo "Average price: $" . number_format($result['avg_price'], 2) . "<br>";

                    // Products by category count

                    $stmt = $pdo->query("SELECT category, COUNT(*) as count FROM products GROUP BY category");

                    $categories = $stmt->fetchAll();

                    foreach ($categories as $cat) {

                      echo $cat['category'] . ": " . $cat['count'] . " products<br>";

                    }

COUNT() counts records
AVG() calculates average
GROUP BY groups results

Pagination

                    Page Navigation:

                    // Pagination variables

                    $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;

                    $limit = 10;

                    $offset = ($page - 1) * $limit;

                    // Get total count

                    $stmt = $pdo->query("SELECT COUNT(*) as total FROM products");

                    $total = $stmt->fetch()['total'];

                    $totalPages = ceil($total / $limit);

                    // Get products for current page

                    $stmt = $pdo->prepare("SELECT * FROM products ORDER BY name LIMIT ? OFFSET ?");

                    $stmt->execute([$limit, $offset]);

                    $products = $stmt->fetchAll();

                    // Display pagination links

                    for ($i = 1; $i <= $totalPages; $i++) {

                      echo "<a href='?page=$i'>Page $i</a> ";

                    }

LIMIT controls number of results
OFFSET skips previous pages
Calculate total pages for navigation

Transactions

                    Transaction Management:

                    try {

                      $pdo->beginTransaction();

                      // Create order

                      $stmt = $pdo->prepare("INSERT INTO orders (user_id, total) VALUES (?, ?)");

                      $stmt->execute([$userId, $total]);

                      $orderId = $pdo->lastInsertId();

                      // Add order items

                      foreach ($cartItems as $item) {

                        $stmt = $pdo->prepare("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)");

                        $stmt->execute([$orderId, $item['id'], $item['quantity']]);

                        // Update stock

                        $stmt = $pdo->prepare("UPDATE products SET stock = stock - ? WHERE id = ?");

                        $stmt->execute([$item['quantity'], $item['id']]);

                      }

                      $pdo->commit();

                      echo "Order completed successfully";

                    } catch(PDOException $e) {

                      $pdo->rollback();

                      echo "Order failed: " . $e->getMessage();

                    }

beginTransaction() starts transaction
commit() saves all changes
rollback() undoes all changes

Search Functionality

                    Product Search:

                    // Search products

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

                    if (!empty($search)) {

                      $searchTerm = "%$search%";

                      $stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE ? OR description LIKE ? ORDER BY name");

                      $stmt->execute([$searchTerm, $searchTerm]);

                    } else {

                      $stmt = $pdo->query("SELECT * FROM products ORDER BY name");

                    }

                    $products = $stmt->fetchAll();

                    // Display search form

                    echo "<form method='GET'>";

                    echo "<input type='text' name='search' placeholder='Search products...'>";

                    echo "<button type='submit'>Search</button>";

                    echo "</form>";

Use LIKE for pattern matching
Search multiple fields
Handle empty search terms

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 before inserting

                    if (checkEmailExists($email)) {

                      echo "Email already exists";

                    } else {

                      // Proceed with registration

                    }

Check for duplicate data
Validate data integrity
Prevent database conflicts

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

Task 2: Complete E-commerce Database System

Instructions:

Build a complete e-commerce database system with:

User registration and login forms
Product management (CRUD operations)
Shopping cart functionality
Order processing system
Search and filter functionality

Implement comprehensive validation
Add security measures (CSRF, input sanitization)
Include error handling and logging
Add pagination for product listings
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
CRUD operations enable data management
Error handling improves application reliability
Transactions ensure data consistency
Performance optimization enhances user experience
Security best practices protect against vulnerabilities
Proper validation ensures data integrity

Next Session:

Session Management & User Authentication - User registration, login systems, and security