PHP and MySQL Integration

Connecting and Interacting with MySQL Databases in PHP Applications

Introduction to PHP-MySQL Integration

PHP and MySQL form a powerful partnership for building dynamic web applications. PHP provides the scripting capability to process data and generate dynamic content, while MySQL offers robust data storage and retrieval capabilities.

Analogy: The Restaurant Model

Think of the PHP-MySQL relationship like a restaurant operation:

  • MySQL is the kitchen and pantry where all ingredients (data) are stored, organized, and prepared
  • PHP is the waitstaff who takes customer requests, brings them to the kitchen, and delivers the prepared food (data) back to the customer
  • SQL queries are the "orders" that PHP places with the kitchen
  • Database connections are like the service doors between the dining area and the kitchen
  • Connection parameters are like the staff credentials that allow access to restricted areas

Just as a restaurant needs efficient communication between waitstaff and kitchen to function properly, your web application requires proper integration between PHP and MySQL to deliver a smooth user experience.

PHP Database Integration Methods

PHP offers several methods for connecting to MySQL databases:

graph TD PHP[PHP Database Integration] --- MySQLi[MySQLi Extension] PHP --- PDO[PDO - PHP Data Objects] PHP --- Legacy[mysql_* Functions] MySQLi --- MySQLiP[Procedural API] MySQLi --- MySQLiO[Object-Oriented API] PDO --- PDOS[Supports multiple databases] PDO --- PDOP[Prepared statements] PDO --- PDOE[Error handling with exceptions] Legacy --- Deprecated[Deprecated since PHP 5.5] Legacy --- Removed[Removed in PHP 7.0] style PHP fill:#f9f,stroke:#333,stroke-width:2px style Legacy fill:#ffcccc,stroke:#333 style MySQLi fill:#ccffcc,stroke:#333 style PDO fill:#ccccff,stroke:#333

Comparison of PHP-MySQL APIs:

Feature MySQLi PDO mysql_* (Legacy)
PHP Version PHP 5.0+ PHP 5.1+ Removed in PHP 7.0
Database Support MySQL only Multiple databases MySQL only
API Style Procedural & OO OO only Procedural only
Prepared Statements Yes Yes No
Error Handling Errors or Exceptions Exceptions Errors only
Recommended Use MySQL-specific applications Cross-database applications Not recommended

Important Note:

The legacy mysql_* functions are obsolete and shouldn't be used in new code. They were removed entirely in PHP 7.0. Always use MySQLi or PDO for MySQL database interactions in modern PHP applications.

Setting Up PHP for MySQL

Before connecting to MySQL, ensure that PHP is properly configured with MySQL support.

Checking MySQL Support in PHP

Verify MySQL Extensions:


<?php
// Create a simple PHP info file
phpinfo();
?>

Look for the "mysqli" and "pdo_mysql" sections in the output.

Check Extensions in PHP Script:


<?php
// Check for MySQL extensions
if (extension_loaded('mysqli')) {
    echo "MySQLi extension is loaded<br>";
} else {
    echo "MySQLi extension is NOT loaded<br>";
}

if (extension_loaded('pdo_mysql')) {
    echo "PDO MySQL extension is loaded<br>";
} else {
    echo "PDO MySQL extension is NOT loaded<br>";
}
?>

Installing MySQL Support for PHP

Windows (with XAMPP, WAMP, etc.)

Most PHP development stacks for Windows include MySQL support by default. If extensions are disabled, you can enable them in the php.ini file:

  1. Locate your php.ini file (usually in the PHP installation directory)
  2. Uncomment these lines by removing the semicolon (;) at the beginning: 
    
extension=mysqli
extension=pdo_mysql
  3. Restart your web server

Linux (Ubuntu/Debian)


# Install PHP MySQL extensions
sudo apt update
sudo apt install php-mysql

# Restart web server (Apache example)
sudo systemctl restart apache2

macOS

With Homebrew:


# Install PHP with MySQL support
brew install php

# If PHP is already installed, link the MySQL extension
brew install php-mysql

# Restart web server (if using built-in server, just restart it)

Connecting to MySQL with MySQLi

The MySQLi extension (MySQL Improved) provides a dedicated interface for working with MySQL databases. It offers both procedural and object-oriented programming interfaces.

MySQLi Procedural Approach

Basic Connection and Query:


<?php
// Connection parameters
$servername = "localhost";
$username = "webuser";
$password = "password123";
$dbname = "myapp_db";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully<br>";

// Perform SQL query
$sql = "SELECT id, username, email FROM users LIMIT 5";
$result = mysqli_query($conn, $sql);

// Check if query was successful
if ($result) {
    // Process the results
    if (mysqli_num_rows($result) > 0) {
        echo "<table border='1'>";
        echo "<tr><th>ID</th><th>Username</th><th>Email</th></tr>";
        while ($row = mysqli_fetch_assoc($result)) {
            echo "<tr>";
            echo "<td>" . $row["id"] . "</td>";
            echo "<td>" . $row["username"] . "</td>";
            echo "<td>" . $row["email"] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "0 results";
    }
    // Free result set
    mysqli_free_result($result);
} else {
    echo "Error executing query: " . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

MySQLi Object-Oriented Approach

Object-Oriented Connection and Query:


<?php
// Connection parameters
$servername = "localhost";
$username = "webuser";
$password = "password123";
$dbname = "myapp_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully<br>";

// Perform SQL query
$sql = "SELECT id, username, email FROM users LIMIT 5";
$result = $conn->query($sql);

// Check if query was successful
if ($result) {
    // Process the results
    if ($result->num_rows > 0) {
        echo "<table border='1'>";
        echo "<tr><th>ID</th><th>Username</th><th>Email</th></tr>";
        while ($row = $result->fetch_assoc()) {
            echo "<tr>";
            echo "<td>" . $row["id"] . "</td>";
            echo "<td>" . $row["username"] . "</td>";
            echo "<td>" . $row["email"] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "0 results";
    }
    // Free result set
    $result->free();
} else {
    echo "Error executing query: " . $conn->error;
}

// Close connection
$conn->close();
?>

Connecting to MySQL with PDO

PDO (PHP Data Objects) provides a database-agnostic interface for database operations, supporting multiple database types with a consistent API.

Basic PDO Connection and Query:


<?php
// Connection parameters
$servername = "localhost";
$username = "webuser";
$password = "password123";
$dbname = "myapp_db";

try {
    // Create PDO connection
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    
    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "Connected successfully<br>";
    
    // Perform SQL query
    $stmt = $conn->query("SELECT id, username, email FROM users LIMIT 5");
    
    // Process the results
    if ($stmt->rowCount() > 0) {
        echo "<table border='1'>";
        echo "<tr><th>ID</th><th>Username</th><th>Email</th></tr>";
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            echo "<tr>";
            echo "<td>" . $row["id"] . "</td>";
            echo "<td>" . $row["username"] . "</td>";
            echo "<td>" . $row["email"] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "0 results";
    }
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

// Connection closes automatically when script ends
// or you can close it explicitly:
$conn = null;
?>

PDO Advantages:

  • Database-agnostic (works with 12+ different databases)
  • Consistent, object-oriented API
  • Named parameters in prepared statements
  • Better exception-based error handling
  • Supports more fetch modes and styles
  • Easier to switch database systems

PDO Considerations:

  • Doesn't support all MySQL-specific features
  • Slightly more verbose for simple operations
  • May require additional configuration for some MySQL features
  • Only available in object-oriented style

Connection Management Best Practices

Centralizing Database Connections

For maintainability and security, it's good practice to centralize your database connection code.

Creating a Database Connection Class:


<?php
// File: Database.php
class Database {
    private $host = "localhost";
    private $username = "webuser";
    private $password = "password123";
    private $database = "myapp_db";
    private $conn;
    
    // Get database connection
    public function getConnection() {
        $this->conn = null;
        
        try {
            $this->conn = new PDO(
                "mysql:host=" . $this->host . ";dbname=" . $this->database,
                $this->username,
                $this->password
            );
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->conn->exec("SET NAMES 'utf8'");
        } catch(PDOException $e) {
            echo "Connection Error: " . $e->getMessage();
        }
        
        return $this->conn;
    }
}
?>

Using the Database Class:


<?php
// Include database class
require_once 'Database.php';

// Create database instance
$database = new Database();
$conn = $database->getConnection();

// Now use $conn for database operations
$stmt = $conn->query("SELECT username FROM users LIMIT 5");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['username'] . "<br>";
}
?>

Securing Connection Information

Never hardcode sensitive database credentials directly in your PHP files. Instead, use configuration files or environment variables.

Using a Configuration File:


// File: config.php (Place outside web root if possible)
<?php
return [
    'database' => [
        'host' => 'localhost',
        'name' => 'myapp_db',
        'user' => 'webuser',
        'password' => 'password123',
        'charset' => 'utf8mb4'
    ]
];
?>

// File: Database.php
<?php
class Database {
    private $host;
    private $username;
    private $password;
    private $database;
    private $conn;
    
    public function __construct() {
        // Load configuration
        $config = require 'config.php';
        $db_config = $config['database'];
        
        $this->host = $db_config['host'];
        $this->username = $db_config['user'];
        $this->password = $db_config['password'];
        $this->database = $db_config['name'];
    }
    
    // Rest of the class...
}
?>

Using Environment Variables (with .env file):


// .env file (install dotenv package to use this approach)
DB_HOST=localhost
DB_NAME=myapp_db
DB_USER=webuser
DB_PASS=password123

// Database.php
<?php
// Requires the phpdotenv package
require 'vendor/autoload.php';
$dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
$dotenv->load();

class Database {
    private $host;
    private $username;
    private $password;
    private $database;
    private $conn;
    
    public function __construct() {
        $this->host = $_ENV['DB_HOST'];
        $this->username = $_ENV['DB_USER'];
        $this->password = $_ENV['DB_PASS'];
        $this->database = $_ENV['DB_NAME'];
    }
    
    // Rest of the class...
}
?>

Executing SQL Queries in PHP

Basic Query Execution

The simplest way to execute SQL queries is using direct query methods:

Basic Query Execution with MySQLi:


<?php
// Procedural style
$sql = "SELECT * FROM users WHERE active = 1";
$result = mysqli_query($conn, $sql);

// Object-oriented style
$sql = "SELECT * FROM users WHERE active = 1";
$result = $conn->query($sql);
?>

Basic Query Execution with PDO:


<?php
$sql = "SELECT * FROM users WHERE active = 1";
$stmt = $conn->query($sql);
?>

Processing Query Results

After executing a SELECT query, you need to process the results:

Fetching Results with MySQLi:


<?php
// Procedural style
$result = mysqli_query($conn, "SELECT * FROM users");
if (mysqli_num_rows($result) > 0) {
    // Fetch as associative array
    while ($row = mysqli_fetch_assoc($result)) {
        echo $row['username'] . "<br>";
    }
    
    // OR fetch as numeric array
    // mysqli_data_seek($result, 0); // Reset result pointer
    // while ($row = mysqli_fetch_row($result)) {
    //     echo $row[1] . "<br>"; // username is in index 1
    // }
    
    // OR fetch as object
    // mysqli_data_seek($result, 0); // Reset result pointer
    // while ($row = mysqli_fetch_object($result)) {
    //     echo $row->username . "<br>";
    // }
}

// Object-oriented style
$result = $conn->query("SELECT * FROM users");
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo $row['username'] . "<br>";
    }
}
?>

Fetching Results with PDO:


<?php
$stmt = $conn->query("SELECT * FROM users");

// Fetch as associative array
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['username'] . "<br>";
}

// OR fetch as numeric array
// $stmt = $conn->query("SELECT * FROM users");
// while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
//     echo $row[1] . "<br>"; // username is in index 1
// }

// OR fetch as object
// $stmt = $conn->query("SELECT * FROM users");
// while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
//     echo $row->username . "<br>";
// }

// OR fetch all results at once
// $stmt = $conn->query("SELECT * FROM users");
// $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
// foreach ($users as $user) {
//     echo $user['username'] . "<br>";
// }
?>

Insert, Update, and Delete Operations

Basic Data Manipulation with MySQLi:


<?php
// INSERT operation
$sql = "INSERT INTO users (username, email, password_hash) 
        VALUES ('johndoe', 'john@example.com', '$2y$10$...')";
if (mysqli_query($conn, $sql)) {
    echo "New record created successfully. ID: " . mysqli_insert_id($conn);
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

// UPDATE operation
$sql = "UPDATE users SET email = 'john.doe@example.com' WHERE username = 'johndoe'";
if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully. Affected rows: " . mysqli_affected_rows($conn);
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

// DELETE operation
$sql = "DELETE FROM users WHERE id = 5";
if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully. Affected rows: " . mysqli_affected_rows($conn);
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}
?>

Basic Data Manipulation with PDO:


<?php
try {
    // INSERT operation
    $sql = "INSERT INTO users (username, email, password_hash) 
            VALUES ('johndoe', 'john@example.com', '$2y$10$...')";
    $count = $conn->exec($sql);
    echo "New record created successfully. ID: " . $conn->lastInsertId();
    
    // UPDATE operation
    $sql = "UPDATE users SET email = 'john.doe@example.com' WHERE username = 'johndoe'";
    $count = $conn->exec($sql);
    echo "Record updated successfully. Affected rows: " . $count;
    
    // DELETE operation
    $sql = "DELETE FROM users WHERE id = 5";
    $count = $conn->exec($sql);
    echo "Record deleted successfully. Affected rows: " . $count;
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Prepared Statements for Security

Prepared statements are a crucial security feature that helps prevent SQL injection attacks by separating SQL code from user-supplied data.

SQL Injection Vulnerability

Consider this vulnerable code: 


$username = $_POST['username']; // User input
$sql = "SELECT * FROM users WHERE username = '$username'";
$result = mysqli_query($conn, $sql);

If a malicious user enters ' OR '1'='1 as the username, the query becomes: 


SELECT * FROM users WHERE username = '' OR '1'='1'

This would return all users because the condition '1'='1' is always true.

MySQLi Prepared Statements

MySQLi Prepared Statement (Procedural):


<?php
// Prepare statement
$stmt = mysqli_prepare($conn, "SELECT * FROM users WHERE username = ?");

// Bind parameters (s = string, i = integer, d = double, b = blob)
$username = "johndoe";
mysqli_stmt_bind_param($stmt, "s", $username);

// Execute statement
mysqli_stmt_execute($stmt);

// Get results
$result = mysqli_stmt_get_result($stmt);

// Process results
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['username'] . ": " . $row['email'] . "<br>";
}

// Close statement
mysqli_stmt_close($stmt);
?>

MySQLi Prepared Statement (Object-Oriented):


<?php
// Prepare statement
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ?");

// Bind parameters
$username = "johndoe";
$stmt->bind_param("s", $username);

// Execute statement
$stmt->execute();

// Get results
$result = $stmt->get_result();

// Process results
while ($row = $result->fetch_assoc()) {
    echo $row['username'] . ": " . $row['email'] . "<br>";
}

// Close statement
$stmt->close();
?>

PDO Prepared Statements

PDO Prepared Statement with Positional Parameters:


<?php
try {
    // Prepare statement
    $stmt = $conn->prepare("SELECT * FROM users WHERE username = ?");
    
    // Execute with parameters
    $username = "johndoe";
    $stmt->execute([$username]);
    
    // Process results
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['username'] . ": " . $row['email'] . "<br>";
    }
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

PDO Prepared Statement with Named Parameters:


<?php
try {
    // Prepare statement
    $stmt = $conn->prepare("SELECT * FROM users WHERE username = :username AND active = :status");
    
    // Bind parameters
    $params = [
        ':username' => 'johndoe',
        ':status' => 1
    ];
    
    // Execute statement
    $stmt->execute($params);
    
    // Process results
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['username'] . ": " . $row['email'] . "<br>";
    }
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

CRUD Operations with Prepared Statements

Complete CRUD Example with PDO:


<?php
try {
    // CREATE (INSERT)
    $insert = $conn->prepare("
        INSERT INTO users (username, email, password_hash, created_at) 
        VALUES (:username, :email, :password, NOW())
    ");
    
    $insert->execute([
        ':username' => 'newuser',
        ':email' => 'new@example.com',
        ':password' => password_hash('password123', PASSWORD_DEFAULT)
    ]);
    
    echo "User created with ID: " . $conn->lastInsertId() . "<br>";
    
    // READ (SELECT)
    $select = $conn->prepare("SELECT * FROM users WHERE username = :username");
    $select->execute([':username' => 'newuser']);
    $user = $select->fetch(PDO::FETCH_ASSOC);
    
    echo "User details: " . $user['username'] . " (" . $user['email'] . ")<br>";
    
    // UPDATE
    $update = $conn->prepare("
        UPDATE users 
        SET email = :email 
        WHERE username = :username
    ");
    
    $update->execute([
        ':email' => 'updated@example.com',
        ':username' => 'newuser'
    ]);
    
    echo "Updated rows: " . $update->rowCount() . "<br>";
    
    // DELETE
    $delete = $conn->prepare("DELETE FROM users WHERE username = :username");
    $delete->execute([':username' => 'newuser']);
    
    echo "Deleted rows: " . $delete->rowCount() . "<br>";
    
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Handling MySQL Errors in PHP

Proper error handling makes your applications more robust and helps with debugging.

Error Handling with MySQLi

Basic MySQLi Error Handling:


<?php
// Procedural style
if (!$result = mysqli_query($conn, $sql)) {
    die("Query failed: " . mysqli_error($conn));
}

// Object-oriented style
if (!$result = $conn->query($sql)) {
    die("Query failed: " . $conn->error);
}
?>

More Robust MySQLi Error Handling:


<?php
// Set error reporting mode (development)
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $conn = new mysqli($servername, $username, $password, $dbname);
    $result = $conn->query($sql);
    
    // Process results...
    
} catch (mysqli_sql_exception $e) {
    // Log the error to a file
    error_log("MySQL Error: " . $e->getMessage() . " in " . $e->getFile() . " on line " . $e->getLine());
    
    // Display user-friendly message
    echo "Sorry, a database error occurred. Please try again later.";
    
    // For debugging in development environment
    if (defined('ENVIRONMENT') && ENVIRONMENT === 'development') {
        echo "<pre>Error: " . $e->getMessage() . "</pre>";
    }
}
?>

Error Handling with PDO

PDO Error Handling with Exceptions:


<?php
try {
    // Set PDO to throw exceptions on error
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Execute query
    $stmt = $conn->query($sql);
    
    // Process results...
    
} catch (PDOException $e) {
    // Log the error
    error_log("Database Error: " . $e->getMessage() . " in " . $e->getFile() . " on line " . $e->getLine());
    
    // Display user-friendly message
    echo "Sorry, a database error occurred. Please try again later.";
    
    // Detailed error for development
    if (defined('ENVIRONMENT') && ENVIRONMENT === 'development') {
        echo "<pre>";
        echo "Error: " . $e->getMessage() . "<br>";
        echo "Code: " . $e->getCode() . "<br>";
        echo "File: " . $e->getFile() . "<br>";
        echo "Line: " . $e->getLine() . "<br>";
        echo "</pre>";
    }
}
?>

Error Handling Best Practices

  • Never display raw SQL errors to end users in production
  • Log detailed error information for debugging
  • Provide user-friendly error messages
  • Use try-catch blocks for structured error handling
  • Consider a different error handling strategy for development vs. production
  • Add context to error logs (e.g., user ID, timestamp)
  • For critical errors, consider alerting administrators (e.g., via email)

Practical Applications

Simple User Authentication System

User Registration:


<?php
// register.php

// Process registration form submission
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    require_once 'database.php';
    
    // Get form data
    $username = trim($_POST['username']);
    $email = trim($_POST['email']);
    $password = $_POST['password'];
    $confirm_password = $_POST['confirm_password'];
    
    // Validate inputs
    $errors = [];
    
    if (empty($username)) {
        $errors[] = "Username is required";
    } elseif (!preg_match('/^[a-zA-Z0-9_]+$/', $username)) {
        $errors[] = "Username can only contain letters, numbers, and underscores";
    }
    
    if (empty($email)) {
        $errors[] = "Email is required";
    } elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $errors[] = "Invalid email format";
    }
    
    if (empty($password)) {
        $errors[] = "Password is required";
    } elseif (strlen($password) < 8) {
        $errors[] = "Password must be at least 8 characters";
    }
    
    if ($password !== $confirm_password) {
        $errors[] = "Passwords do not match";
    }
    
    // If validation passes, register the user
    if (empty($errors)) {
        try {
            $database = new Database();
            $conn = $database->getConnection();
            
            // Check if username already exists
            $stmt = $conn->prepare("SELECT id FROM users WHERE username = :username");
            $stmt->execute([':username' => $username]);
            
            if ($stmt->rowCount() > 0) {
                $errors[] = "Username already taken";
            } else {
                // Check if email already exists
                $stmt = $conn->prepare("SELECT id FROM users WHERE email = :email");
                $stmt->execute([':email' => $email]);
                
                if ($stmt->rowCount() > 0) {
                    $errors[] = "Email already registered";
                } else {
                    // Insert new user
                    $hash = password_hash($password, PASSWORD_DEFAULT);
                    
                    $stmt = $conn->prepare("
                        INSERT INTO users (username, email, password_hash, created_at) 
                        VALUES (:username, :email, :password, NOW())
                    ");
                    
                    $stmt->execute([
                        ':username' => $username,
                        ':email' => $email,
                        ':password' => $hash
                    ]);
                    
                    // Registration successful
                    $success = "Registration successful! You can now log in.";
                    
                    // Redirect to login page
                    header("Location: login.php?registered=1");
                    exit;
                }
            }
        } catch (PDOException $e) {
            $errors[] = "Registration failed: " . $e->getMessage();
        }
    }
}
?>

<!-- Registration Form HTML -->
<!DOCTYPE html>
<html>
<head>
    <title>Register</title>
</head>
<body>
    <h1>Register</h1>
    
    <?php if (!empty($errors)): ?>
        <div class="errors">
            <ul>
                <?php foreach ($errors as $error): ?>
                    <li><?php echo $error; ?></li>
                <?php endforeach; ?>
            </ul>
        </div>
    <?php endif; ?>
    
    <form method="post" action="">
        <div>
            <label for="username">Username:</label>
            <input type="text" name="username" value="">
        </div>
        
        <div>
            <label for="email">Email:</label>
            <input type="email" name="email" value="">
        </div>
        
        <div>
            <label for="password">Password:</label>
            <input type="password" name="password">
        </div>
        
        <div>
            <label for="confirm_password">Confirm Password:</label>
            <input type="password" name="confirm_password">
        </div>
        
        <div>
            <input type="submit" value="Register">
        </div>
    </form>
    
    <p>Already have an account? <a href="login.php">Log in</a></p>
</body>
</html>

User Login:


<?php
// login.php

// Initialize session
session_start();

// Check if user is already logged in
if (isset($_SESSION["loggedin"]) && $_SESSION["loggedin"] === true) {
    header("location: dashboard.php");
    exit;
}

// Process login form submission
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    require_once 'database.php';
    
    // Get form data
    $username = trim($_POST['username']);
    $password = $_POST['password'];
    
    // Validate inputs
    $errors = [];
    
    if (empty($username)) {
        $errors[] = "Please enter username";
    }
    
    if (empty($password)) {
        $errors[] = "Please enter password";
    }
    
    // If validation passes, attempt login
    if (empty($errors)) {
        try {
            $database = new Database();
            $conn = $database->getConnection();
            
            // Prepare statement
            $stmt = $conn->prepare("SELECT id, username, password_hash FROM users WHERE username = :username");
            $stmt->execute([':username' => $username]);
            
            // Check if username exists
            if ($stmt->rowCount() == 1) {
                $user = $stmt->fetch(PDO::FETCH_ASSOC);
                
                // Verify password
                if (password_verify($password, $user['password_hash'])) {
                    // Password is correct, start a new session
                    session_start();
                    
                    // Store data in session variables
                    $_SESSION["loggedin"] = true;
                    $_SESSION["id"] = $user["id"];
                    $_SESSION["username"] = $user["username"];
                    
                    // Update last login timestamp
                    $update = $conn->prepare("UPDATE users SET last_login = NOW() WHERE id = :id");
                    $update->execute([':id' => $user["id"]]);
                    
                    // Redirect to dashboard
                    header("location: dashboard.php");
                    exit;
                } else {
                    $errors[] = "Invalid password";
                }
            } else {
                $errors[] = "Username not found";
            }
        } catch (PDOException $e) {
            $errors[] = "Login failed: " . $e->getMessage();
        }
    }
}
?>

<!-- Login Form HTML -->
<!DOCTYPE html>
<html>
<head>
    <title>Login</title>
</head>
<body>
    <h1>Login</h1>
    
    <?php if (isset($_GET['registered']) && $_GET['registered'] == 1): ?>
        <div class="success">
            Registration successful! Please log in.
        </div>
    <?php endif; ?>
    
    <?php if (!empty($errors)): ?>
        <div class="errors">
            <ul>
                <?php foreach ($errors as $error): ?>
                    <li><?php echo $error; ?></li>
                <?php endforeach; ?>
            </ul>
        </div>
    <?php endif; ?>
    
    <form method="post" action="">
        <div>
            <label for="username">Username:</label>
            <input type="text" name="username" value="">
        </div>
        
        <div>
            <label for="password">Password:</label>
            <input type="password" name="password">
        </div>
        
        <div>
            <input type="submit" value="Login">
        </div>
    </form>
    
    <p>Don't have an account? <a href="register.php">Register</a></p>
</body>
</html>

Practical Activities

Activity 1: Database Connection Wrapper

Create a reusable database connection class with the following features:

  1. Support for both MySQLi and PDO
  2. Configuration loaded from a separate file
  3. Error handling with logging
  4. Method to check connection status
  5. Method to execute prepared statements

Activity 2: Simple CRUD Application

Create a simple product management system with:

  1. A products table with id, name, description, price, and stock_quantity
  2. A web form to add new products
  3. A page to list all products with edit and delete options
  4. A search function to find products by name
  5. Form validation and error handling

Activity 3: User Authentication System

Extend the authentication example by adding:

  1. User profile page with editable information
  2. "Forgot password" functionality
  3. Email verification upon registration
  4. Login/logout logs stored in the database
  5. Remember me functionality using cookies

Key Takeaways

Next Steps

In our next lecture, we'll cover prepared statements and security in depth, exploring how to build secure database interactions in PHP applications.