PHP & MySQL Teaching Guide

Complete Video Tutorial Script for Your Friend

Prerequisites Setup (Video 1 - 10 minutes)

What You'll Need

Homebrew installed on Mac
MySQL installed via Homebrew
PHP installed (comes with macOS)
A text editor (VS Code recommended)

Initial Setup Commands

# Start MySQL server
brew services start mysql
 
# Verify MySQL is running
brew services list | grep mysql
 
# Access MySQL (first time - no password)
mysql -u root
 
# Inside MySQL, create a test user (recommended for security)
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;
 
# Create a project folder
mkdir ~/php_mysql_tutorial
cd ~/php_mysql_tutorial

Video 1: Understanding the Connection Flow (15 minutes)

Files to Create:

connection_demo.php
test_connection.php

Video Script:

Opening: "Today we're learning how PHP talks to MySQL. Think of it like making a phone call - you need to dial the number, wait for connection, talk, then hang up."

File 1: `connection_demo.php`

<?php
/**
 * BASIC MySQL CONNECTION TEMPLATE
 * This is what you MUST understand for the exam!
 */
 
// Step 1: Create connection (dial the phone)
$mysqli = new mysqli('localhost', 'testuser', 'password', 'database_name');
 
// Step 2: Check if connection worked (did they pick up?)
if($mysqli->connect_errno) {
    echo "Connection failed: " . $mysqli->connect_errno . ": " . $mysqli->connect_error;
    exit(); // Stop if connection fails
}
 
// Step 3: Connection successful!
echo "Connected successfully!<br>";
 
// Step 4: Always close when done (hang up the phone)
$mysqli->close();
?>

Explain each part:

new mysqli(...) - Creates the connection object
- Parameter 1: Server location ('localhost')
- Parameter 2: Username ('testuser')
- Parameter 3: Password ('password')
- Parameter 4: Database name
connect_errno - Connection error number (0 = success)
connect_error - Error message if connection fails
close() - Close the connection (IMPORTANT!)

File 2: `test_connection.php`

<?php
// Create connection
$mysqli = new mysqli('localhost', 'testuser', 'password', '');
 
if($mysqli->connect_errno) {
    die("Connection failed: " . $mysqli->connect_error);
}
 
echo "MySQL connection successful!<br>";
echo "Server info: " . $mysqli->server_info . "<br>";
 
$mysqli->close();
?>

Run in Terminal:

# Navigate to your project folder
cd ~/php_mysql_tutorial
 
# Start PHP development server on port 8000
php -S localhost:8000
 
# Open browser to: http://localhost:8000/test_connection.php

Video 2: Setting Up Your First Database (20 minutes)

Files to Create:

create_database.php
create_table.php
connect.php (reusable connection file)

Video Script:

Opening: "Now that we can connect, let's create a database and table. In your exam, you need to understand how PHP executes SQL commands."

File 1: `connect.php` - The Reusable Connection

<?php
/**
 * REUSABLE CONNECTION FILE
 * This is what require_once() is for!
 * 
 * Create this ONCE, use it in ALL other files.
 */
 
$mysqli = new mysqli('localhost', 'testuser', 'password', 'tutorial_db');
 
if($mysqli->connect_errno) {
    die("Connection failed: " . $mysqli->connect_errno . ": " . $mysqli->connect_error);
}
 
// Don't close here - let the calling file close it!
?>

Explain require_once(): "Instead of writing connection code in every file, we write it ONCE and include it. The _oncemeans PHP won't include it twice even if you accidentally call it multiple times."

File 2: `create_database.php`

<?php
/**
 * CREATING A DATABASE
 * Only run this once!
 */
 
// Connect without specifying database (4th parameter empty)
$mysqli = new mysqli('localhost', 'testuser', 'password', '');
 
if($mysqli->connect_errno) {
    die("Connection failed: " . $mysqli->connect_error);
}
 
// SQL command to create database
$sql = "CREATE DATABASE IF NOT EXISTS tutorial_db";
 
// Execute the command
if($mysqli->query($sql)) {
    echo "Database 'tutorial_db' created successfully!<br>";
} else {
    echo "Error creating database: " . $mysqli->error . "<br>";
}
 
$mysqli->close();
?>

File 3: `create_table.php`

<?php
/**
 * CREATING A TABLE
 * This shows SQL in PHP context (EXAM RELEVANT!)
 */
 
require_once('connect.php'); // Using our reusable connection!
 
// SQL to create product table
$sql = "CREATE TABLE IF NOT EXISTS product (
    p_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    p_name VARCHAR(30) NOT NULL,
    p_price INT NOT NULL,
    p_type VARCHAR(20) DEFAULT 'general'
)";
 
// Execute the SQL
if($mysqli->query($sql)) {
    echo "Table 'product' created successfully!<br>";
} else {
    echo "Error creating table: " . $mysqli->error . "<br>";
}
 
$mysqli->close();
?>

Key Exam Points:

query($sql) - Executes ANY SQL command
Returns true on success, false on failure
Check $mysqli->error for error messages

Run Commands:

# In browser:
http://localhost:8000/create_database.php
# Then:
http://localhost:8000/create_table.php

Video 3: INSERT Operations (25 minutes)

Files to Create:

insert_single.php
insert_form.php
insert_handler.php

Video Script:

Opening: "INSERT is how you add data to MySQL. The exam will test if you understand how POST data becomes SQL queries."

File 1: `insert_single.php` - Basic INSERT

<?php
/**
 * BASIC INSERT EXAMPLE
 * Understanding the SQL syntax in PHP
 */
 
require_once('connect.php');
 
// Method 1: Direct values
$sql = "INSERT INTO product (p_name, p_price, p_type) 
        VALUES ('Pencil', 10, 'stationary')";
 
if($mysqli->query($sql)) {
    echo "Product inserted! ID: " . $mysqli->insert_id . "<br>";
} else {
    echo "Insert failed: " . $mysqli->error . "<br>";
}
 
// Method 2: Using variables
$name = "Eraser";
$price = 5;
$type = "stationary";
 
$sql = "INSERT INTO product (p_name, p_price, p_type) 
        VALUES ('$name', $price, '$type')";
 
if($mysqli->query($sql)) {
    echo "Product inserted! ID: " . $mysqli->insert_id . "<br>";
} else {
    echo "Insert failed: " . $mysqli->error . "<br>";
}
 
$mysqli->close();
?>

Key Exam Concepts:

insert_id - Gets the last inserted AUTO_INCREMENT ID
String values need single quotes in SQL: 'Pencil'
Number values don't need quotes: 10

File 2: `insert_form.php` - HTML Form

<!DOCTYPE html>
<html>
<head>
    <title>Add Product</title>
    <style>
        body { font-family: Arial; padding: 20px; }
        form { max-width: 400px; }
        label { display: block; margin-top: 10px; font-weight: bold; }
        input, select { width: 100%; padding: 8px; margin-top: 5px; }
        button { margin-top: 15px; padding: 10px 20px; background: #4CAF50; color: white; border: none; cursor: pointer; }
    </style>
</head>
<body>
    <h2>Add New Product</h2>
    
    <form action="insert_handler.php" method="POST">
        <label>Product Name:</label>
        <input type="text" name="p_name" required>
        
        <label>Product Price:</label>
        <input type="number" name="p_price" required>
        
        <label>Product Type:</label>
        <select name="p_type">
            <option value="stationary">Stationary</option>
            <option value="accessories">Accessories</option>
            <option value="electronics">Electronics</option>
        </select>
        
        <button type="submit" name="submit">Add Product</button>
    </form>
</body>
</html>

File 3: `insert_handler.php` - Processing Form Data

<?php
/**
 * HANDLING FORM DATA (EXAM CRITICAL!)
 * This shows how $_POST connects to SQL
 */
 
require_once('connect.php');
 
// Check if form was submitted
if(isset($_POST['submit'])) {
    
    // Get data from POST - EXAM TIP: Always check if data exists!
    $name = isset($_POST['p_name']) ? $_POST['p_name'] : '';
    $price = isset($_POST['p_price']) ? $_POST['p_price'] : 0;
    $type = isset($_POST['p_type']) ? $_POST['p_type'] : 'general';
    
    // IMPORTANT: Escape strings to prevent SQL injection!
    $name = $mysqli->real_escape_string($name);
    $type = $mysqli->real_escape_string($type);
    
    // Build SQL query
    $sql = "INSERT INTO product (p_name, p_price, p_type) 
            VALUES ('$name', $price, '$type')";
    
    // Execute query
    if($mysqli->query($sql)) {
        echo "<h3>Success!</h3>";
        echo "Product added with ID: " . $mysqli->insert_id . "<br>";
        echo "<a href='insert_form.php'>Add another</a> | ";
        echo "<a href='view_products.php'>View all products</a>";
    } else {
        echo "<h3>Error!</h3>";
        echo "Failed to insert: " . $mysqli->error . "<br>";
        echo "<a href='insert_form.php'>Go back</a>";
    }
    
} else {
    echo "No data submitted!<br>";
    echo "<a href='insert_form.php'>Go to form</a>";
}
 
$mysqli->close();
?>

EXAM KEY POINTS:

isset($_POST['name']) - Checks if POST data exists
real_escape_string() - Prevents SQL injection (SECURITY!)
Form name attribute must match $_POST['name']
Form method="POST" → use $_POST[]
Form method="GET" → use $_GET[]

Video 4: SELECT Operations (30 minutes)

Files to Create:

view_products.php
view_with_array.php
search_products.php

Video Script:

Opening: "SELECT retrieves data. The exam tests how you loop through results and display them."

File 1: `view_products.php` - Basic SELECT

<?php
/**
 * BASIC SELECT QUERY
 * Shows fetch_array() usage (EXAM FAVORITE!)
 */
 
require_once('connect.php');
 
echo "<h2>All Products</h2>";
 
// SQL SELECT query
$sql = "SELECT * FROM product";
 
// Execute query and get result
$result = $mysqli->query($sql);
 
// Check if query worked
if(!$result) {
    die("Query failed: " . $mysqli->error);
}
 
// Check if we have results
if($result->num_rows > 0) {
    
    echo "<table border='1' cellpadding='10'>";
    echo "<tr><th>ID</th><th>Name</th><th>Price</th><th>Type</th></tr>";
    
    // Loop through each row
    while($row = $result->fetch_array()) {
        echo "<tr>";
        echo "<td>" . $row['p_id'] . "</td>";
        echo "<td>" . $row['p_name'] . "</td>";
        echo "<td>" . $row['p_price'] . "</td>";
        echo "<td>" . $row['p_type'] . "</td>";
        echo "</tr>";
    }
    
    echo "</table>";
    
    echo "<p>Total products: " . $result->num_rows . "</p>";
    
} else {
    echo "No products found!";
}
 
// Free result memory (GOOD PRACTICE!)
$result->free();
$mysqli->close();
?>

EXAM KEY CONCEPTS:

query($sql) - Returns result object or false
num_rows - Number of rows returned
fetch_array() - Gets one row at a time
free() - Clears result from memory
Loop pattern: while($row = $result->fetch_array())

File 2: `view_with_array.php` - Understanding Array Access

<?php
/**
 * UNDERSTANDING fetch_array() RETURN VALUES
 * EXAM TIP: You can access columns by name OR number!
 */
 
require_once('connect.php');
 
$sql = "SELECT p_id, p_name, p_price FROM product LIMIT 1";
$result = $mysqli->query($sql);
 
if($row = $result->fetch_array()) {
    
    echo "<h3>Different ways to access data:</h3>";
    
    // Method 1: By column name (RECOMMENDED)
    echo "Product ID: " . $row['p_id'] . "<br>";
    echo "Product Name: " . $row['p_name'] . "<br>";
    echo "Product Price: " . $row['p_price'] . "<br><br>";
    
    // Method 2: By column index (0, 1, 2, ...)
    echo "Using index [0]: " . $row[0] . "<br>";
    echo "Using index [1]: " . $row[1] . "<br>";
    echo "Using index [2]: " . $row[2] . "<br><br>";
    
    // View the entire array
    echo "<h3>Complete Array Structure:</h3>";
    echo "<pre>";
    print_r($row);
    echo "</pre>";
}
 
$result->free();
$mysqli->close();
?>

File 3: `search_products.php` - WHERE Clause

<?php
/**
 * SELECT WITH WHERE CLAUSE
 * Shows parameterized queries (EXAM COMMON!)
 */
 
require_once('connect.php');
 
// Example 1: Search by exact name
$search_name = "Pencil";
$sql = "SELECT * FROM product WHERE p_name = '$search_name'";
 
echo "<h3>Products named '$search_name':</h3>";
$result = $mysqli->query($sql);
while($row = $result->fetch_array()) {
    echo $row['p_name'] . " - ₹" . $row['p_price'] . "<br>";
}
$result->free();
 
// Example 2: Search by price range
$min_price = 10;
$max_price = 1000;
$sql = "SELECT * FROM product WHERE p_price BETWEEN $min_price AND $max_price";
 
echo "<h3>Products between ₹$min_price and ₹$max_price:</h3>";
$result = $mysqli->query($sql);
while($row = $result->fetch_array()) {
    echo $row['p_name'] . " - ₹" . $row['p_price'] . "<br>";
}
$result->free();
 
// Example 3: Search with LIKE (partial match)
$search_keyword = "P%"; // Starts with P
$sql = "SELECT * FROM product WHERE p_name LIKE '$search_keyword'";
 
echo "<h3>Products starting with 'P':</h3>";
$result = $mysqli->query($sql);
echo "Found: " . $result->num_rows . " products<br>";
while($row = $result->fetch_array()) {
    echo $row['p_name'] . "<br>";
}
$result->free();
 
$mysqli->close();
?>

EXAM SQL OPERATORS:

= - Exact match
>, <, >=, <= - Comparisons
BETWEEN x AND y - Range
LIKE 'P%' - Pattern match (% = wildcard)
IN (1, 2, 3) - Multiple values

Video 5: UPDATE Operations (20 minutes)

Files to Create:

update_price.php
edit_product.php
update_handler.php

Video Script:

Opening: "UPDATE changes existing data. The exam tests if you can build UPDATE queries from form data."

File 1: `update_price.php` - Basic UPDATE

<?php
/**
 * BASIC UPDATE SYNTAX
 * Understanding the UPDATE statement
 */
 
require_once('connect.php');
 
// Example 1: Update single column
$product_id = 1;
$new_price = 25;
 
$sql = "UPDATE product SET p_price = $new_price WHERE p_id = $product_id";
 
if($mysqli->query($sql)) {
    echo "Price updated successfully!<br>";
    echo "Affected rows: " . $mysqli->affected_rows . "<br>";
} else {
    echo "Update failed: " . $mysqli->error . "<br>";
}
 
// Example 2: Update multiple columns
$product_id = 2;
$new_name = "Big Eraser";
$new_price = 15;
$new_type = "stationary";
 
$sql = "UPDATE product 
        SET p_name = '$new_name', 
            p_price = $new_price, 
            p_type = '$new_type' 
        WHERE p_id = $product_id";
 
if($mysqli->query($sql)) {
    echo "Product updated successfully!<br>";
} else {
    echo "Update failed: " . $mysqli->error . "<br>";
}
 
$mysqli->close();
?>

EXAM KEY POINTS:

affected_rows - Number of rows changed
WHERE clause - CRITICAL! Without it, ALL rows update!
Multiple SET - Separate with commas

File 2: `edit_product.php` - Edit Form

<?php
/**
 * EDIT FORM - Loading existing data
 * EXAM PATTERN: Get data, populate form, submit to handler
 */
 
require_once('connect.php');
 
// Get product ID from URL
$p_id = isset($_GET['id']) ? $_GET['id'] : 0;
 
// Fetch product details
$sql = "SELECT * FROM product WHERE p_id = $p_id";
$result = $mysqli->query($sql);
 
if($result->num_rows == 0) {
    die("Product not found! <a href='view_products.php'>Go back</a>");
}
 
$product = $result->fetch_array();
?>
 
<!DOCTYPE html>
<html>
<head>
    <title>Edit Product</title>
    <style>
        body { font-family: Arial; padding: 20px; }
        form { max-width: 400px; }
        label { display: block; margin-top: 10px; font-weight: bold; }
        input, select { width: 100%; padding: 8px; margin-top: 5px; }
        button { margin-top: 15px; padding: 10px 20px; background: #2196F3; color: white; border: none; cursor: pointer; }
    </style>
</head>
<body>
    <h2>Edit Product</h2>
    
    <form action="update_handler.php" method="POST">
        
        <!-- Hidden field for product ID (IMPORTANT!) -->
        <input type="hidden" name="p_id" value="<?php echo $product['p_id']; ?>">
        
        <label>Product ID: <?php echo $product['p_id']; ?></label>
        
        <label>Product Name:</label>
        <input type="text" name="p_name" value="<?php echo $product['p_name']; ?>" required>
        
        <label>Product Price:</label>
        <input type="number" name="p_price" value="<?php echo $product['p_price']; ?>" required>
        
        <label>Product Type:</label>
        <select name="p_type">
            <option value="stationary" <?php if($product['p_type'] == 'stationary') echo 'selected'; ?>>Stationary</option>
            <option value="accessories" <?php if($product['p_type'] == 'accessories') echo 'selected'; ?>>Accessories</option>
            <option value="electronics" <?php if($product['p_type'] == 'electronics') echo 'selected'; ?>>Electronics</option>
        </select>
        
        <button type="submit" name="submit">Update Product</button>
        <a href="view_products.php">Cancel</a>
    </form>
</body>
</html>
 
<?php
$result->free();
$mysqli->close();
?>

EXAM PATTERNS:

Hidden input for ID: <input type="hidden" name="p_id" value="...">
Pre-populate inputs: value="<?php echo $product['p_name']; ?>"
Pre-select dropdown: <?php if($product['p_type'] == 'stationary') echo 'selected'; ?>

File 3: `update_handler.php`

<?php
/**
 * UPDATE HANDLER - Processing edit form
 * EXAM CRITICAL: Understanding POST to UPDATE
 */
 
require_once('connect.php');
 
if(isset($_POST['submit'])) {
    
    // Get data from POST
    $p_id = isset($_POST['p_id']) ? intval($_POST['p_id']) : 0;
    $p_name = isset($_POST['p_name']) ? $_POST['p_name'] : '';
    $p_price = isset($_POST['p_price']) ? intval($_POST['p_price']) : 0;
    $p_type = isset($_POST['p_type']) ? $_POST['p_type'] : 'general';
    
    // Escape strings
    $p_name = $mysqli->real_escape_string($p_name);
    $p_type = $mysqli->real_escape_string($p_type);
    
    // Build UPDATE query
    $sql = "UPDATE product 
            SET p_name = '$p_name', 
                p_price = $p_price, 
                p_type = '$p_type' 
            WHERE p_id = $p_id";
    
    // Execute update
    if($mysqli->query($sql)) {
        echo "<h3>Success!</h3>";
        echo "Product updated successfully!<br>";
        echo "Rows affected: " . $mysqli->affected_rows . "<br>";
        echo "<a href='view_products.php'>View all products</a>";
    } else {
        echo "<h3>Error!</h3>";
        echo "Update failed: " . $mysqli->error . "<br>";
        echo "<a href='edit_product.php?id=$p_id'>Go back</a>";
    }
    
} else {
    echo "No data submitted!<br>";
    echo "<a href='view_products.php'>Go to products</a>";
}
 
$mysqli->close();
?>

Video 6: DELETE Operations (15 minutes)

Files to Create:

delete_product.php
view_with_delete.php

Video Script:

Opening: "DELETE removes data. The exam tests if you understand how to safely delete with WHERE clause."

File 1: `delete_product.php`

<?php
/**
 * DELETE OPERATION
 * EXAM WARNING: Always use WHERE clause!
 */
 
require_once('connect.php');
 
// Get product ID from URL
$p_id = isset($_GET['id']) ? intval($_GET['id']) : 0;
 
if($p_id == 0) {
    die("Invalid product ID! <a href='view_products.php'>Go back</a>");
}
 
// DELETE query
$sql = "DELETE FROM product WHERE p_id = $p_id";
 
if($mysqli->query($sql)) {
    
    if($mysqli->affected_rows > 0) {
        echo "<h3>Success!</h3>";
        echo "Product deleted successfully!<br>";
    } else {
        echo "<h3>No Changes</h3>";
        echo "Product ID $p_id not found!<br>";
    }
    
    echo "<a href='view_products.php'>Back to products</a>";
    
} else {
    echo "<h3>Error!</h3>";
    echo "Delete failed: " . $mysqli->error . "<br>";
    echo "<a href='view_products.php'>Go back</a>";
}
 
$mysqli->close();
?>

File 2: `view_with_delete.php` - Complete CRUD Interface

<?php
/**
 * COMPLETE CRUD VIEW
 * Shows all operations together (EXAM REALISTIC!)
 */
 
require_once('connect.php');
?>
 
<!DOCTYPE html>
<html>
<head>
    <title>Product Management</title>
    <style>
        body { font-family: Arial; padding: 20px; }
        table { border-collapse: collapse; width: 100%; margin-top: 20px; }
        th, td { border: 1px solid #ddd; padding: 12px; text-align: left; }
        th { background-color: #4CAF50; color: white; }
        tr:nth-child(even) { background-color: #f2f2f2; }
        a { text-decoration: none; padding: 5px 10px; margin: 2px; display: inline-block; }
        .add-btn { background: #4CAF50; color: white; }
        .edit-btn { background: #2196F3; color: white; }
        .delete-btn { background: #f44336; color: white; }
        .delete-btn:hover { background: #da190b; }
    </style>
    <script>
        function confirmDelete(name) {
            return confirm("Are you sure you want to delete '" + name + "'?");
        }
    </script>
</head>
<body>
    <h2>Product Management</h2>
    
    <a href="insert_form.php" class="add-btn">➕ Add New Product</a>
    
    <?php
    $sql = "SELECT * FROM product ORDER BY p_id DESC";
    $result = $mysqli->query($sql);
    
    if($result->num_rows > 0) {
        
        echo "<table>";
        echo "<tr>";
        echo "<th>ID</th>";
        echo "<th>Name</th>";
        echo "<th>Price</th>";
        echo "<th>Type</th>";
        echo "<th>Actions</th>";
        echo "</tr>";
        
        while($row = $result->fetch_array()) {
            echo "<tr>";
            echo "<td>" . $row['p_id'] . "</td>";
            echo "<td>" . $row['p_name'] . "</td>";
            echo "<td>₹" . $row['p_price'] . "</td>";
            echo "<td>" . $row['p_type'] . "</td>";
            echo "<td>";
            
            // Edit button
            echo "<a href='edit_product.php?id=" . $row['p_id'] . "' class='edit-btn'>✏️ Edit</a>";
            
            // Delete button with JavaScript confirmation
            echo "<a href='delete_product.php?id=" . $row['p_id'] . "' ";
            echo "class='delete-btn' ";
            echo "onclick='return confirmDelete(\"" . $row['p_name'] . "\");'>";
            echo "🗑️ Delete</a>";
            
            echo "</td>";
            echo "</tr>";
        }
        
        echo "</table>";
        
        echo "<p><strong>Total Products: " . $result->num_rows . "</strong></p>";
        
    } else {
        echo "<p>No products found. <a href='insert_form.php'>Add the first one!</a></p>";
    }
    
    $result->free();
    $mysqli->close();
    ?>
    
</body>
</html>

EXAM CONCEPTS:

Confirmation before delete: JavaScript confirm()
Passing ID via URL: delete_product.php?id=5
Getting URL parameter: $_GET['id']
Security: Use intval() to ensure ID is a number

Video 7: Advanced Concepts (25 minutes)

Files to Create:

join_example.php
dropdown_from_db.php
count_and_aggregate.php

File 1: `join_example.php` - Understanding JOINs

<?php
/**
 * JOIN QUERIES (EXAM LIKELY!)
 * Shows how to combine related tables
 */
 
require_once('connect.php');
 
// First, create product_type table
$sql = "CREATE TABLE IF NOT EXISTS product_type (
    type_id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(30) NOT NULL
)";
$mysqli->query($sql);
 
// Insert some types
$mysqli->query("INSERT IGNORE INTO product_type (type_id, type_name) VALUES (1, 'Stationary')");
$mysqli->query("INSERT IGNORE INTO product_type (type_id, type_name) VALUES (2, 'Accessories')");
$mysqli->query("INSERT IGNORE INTO product_type (type_id, type_name) VALUES (3, 'Electronics')");
 
// Now modify product table to use foreign key (in real scenario)
// For now, let's just do a JOIN query
 
echo "<h3>Products with Type Names (Using JOIN):</h3>";
 
// JOIN query - EXAM IMPORTANT!
$sql = "SELECT p.p_id, p.p_name, p.p_price, pt.type_name 
        FROM product p 
        INNER JOIN product_type pt ON p.p_type = pt.type_name 
        ORDER BY p.p_id";
 
$result = $mysqli->query($sql);
 
if($result) {
    echo "<table border='1' cellpadding='10'>";
    echo "<tr><th>ID</th><th>Product</th><th>Price</th><th>Type</th></tr>";
    
    while($row = $result->fetch_array()) {
        echo "<tr>";
        echo "<td>" . $row['p_id'] . "</td>";
        echo "<td>" . $row['p_name'] . "</td>";
        echo "<td>₹" . $row['p_price'] . "</td>";
        echo "<td>" . $row['type_name'] . "</td>";
        echo "</tr>";
    }
    
    echo "</table>";
    $result->free();
}
 
$mysqli->close();
?>

EXAM JOIN TYPES:

INNER JOIN - Only matching records
LEFT JOIN - All from left table + matches from right
RIGHT JOIN - All from right table + matches from left

File 2: `dropdown_from_db.php` - Dynamic Dropdowns

<?php
/**
 * DYNAMIC DROPDOWN FROM DATABASE
 * EXAM PATTERN: Loading select options from DB
 */
 
require_once('connect.php');
?>
 
<!DOCTYPE html>
<html>
<head>
    <title>Add Product with DB Dropdown</title>
</head>
<body>
    <h2>Add Product (Type from Database)</h2>
    
    <form action="insert_handler.php" method="POST">
        
        <label>Product Name:</label>
        <input type="text" name="p_name" required><br><br>
        
        <label>Product Price:</label>
        <input type="number" name="p_price" required><br><br>
        
        <label>Product Type:</label>
        <select name="p_type" required>
            <option value="">-- Select Type --</option>
            <?php
            // EXAM PATTERN: Populating dropdown from DB
            $sql = "SELECT type_id, type_name FROM product_type";
            $result = $mysqli->query($sql);
            
            if($result) {
                while($row = $result->fetch_array()) {
                    echo "<option value='" . $row['type_name'] . "'>";
                    echo $row['type_name'];
                    echo "</option>";
                }
                $result->free();
            }
            ?>
        </select><br><br>
        
        <button type="submit" name="submit">Add Product</button>
    </form>
    
</body>
</html>
 
<?php
$mysqli->close();
?>

File 3: `count_and_aggregate.php` - SQL Functions

<?php
/**
 * AGGREGATE FUNCTIONS
 * EXAM FUNCTIONS: COUNT, SUM, AVG, MIN, MAX
 */
 
require_once('connect.php');
 
echo "<h2>Product Statistics</h2>";
 
// COUNT
$sql = "SELECT COUNT(*) as total FROM product";
$result = $mysqli->query($sql);
$row = $result->fetch_array();
echo "<p><strong>Total Products:</strong> " . $row['total'] . "</p>";
$result->free();
 
// SUM
$sql = "SELECT SUM(p_price) as total_value FROM product";
$result = $mysqli->query($sql);
$row = $result->fetch_array();
echo "<p><strong>Total Inventory Value:</strong> ₹" . $row['total_value'] . "</p>";
$result->free();
 
// AVG
$sql = "SELECT AVG(p_price) as avg_price FROM product";
$result = $mysqli->query($sql);
$row = $result->fetch_array();
echo "<p><strong>Average Price:</strong> ₹" . number_format($row['avg_price'], 2) . "</p>";
$result->free();
 
// MIN and MAX
$sql = "SELECT MIN(p_price) as min_price, MAX(p_price) as max_price FROM product";
$result = $mysqli->query($sql);
$row = $result->fetch_array();
echo "<p><strong>Price Range:</strong> ₹" . $row['min_price'] . " - ₹" . $row['max_price'] . "</p>";
$result->free();
 
// GROUP BY - Count by type
echo "<h3>Products by Type:</h3>";
$sql = "SELECT p_type, COUNT(*) as count FROM product GROUP BY p_type";
$result = $mysqli->query($sql);
 
echo "<table border='1' cellpadding='10'>";
echo "<tr><th>Type</th><th>Count</th></tr>";
 
while($row = $result->fetch_array()) {
    echo "<tr>";
    echo "<td>" . $row['p_type'] . "</td>";
    echo "<td>" . $row['count'] . "</td>";
    echo "</tr>";
}
 
echo "</table>";
$result->free();
 
$mysqli->close();
?>

Video 8: Common Exam Patterns (30 minutes)

File: `exam_patterns.php`

<?php
/**
 * COMMON EXAM PATTERNS AND PITFALLS
 * Study these patterns!
 */
 
require_once('connect.php');
 
echo "<h2>Exam Pattern Examples</h2>";
 
// ============================================
// PATTERN 1: Checking if record exists
// ============================================
echo "<h3>1. Check if Product Exists</h3>";
 
$product_name = "Pencil";
$sql = "SELECT * FROM product WHERE p_name = '$product_name'";
$result = $mysqli->query($sql);
 
if($result->num_rows > 0) {
    echo "✅ Product '$product_name' exists!<br>";
    $row = $result->fetch_array();
    echo "Price: ₹" . $row['p_price'] . "<br>";
} else {
    echo "❌ Product '$product_name' not found!<br>";
}
$result->free();
 
// ============================================
// PATTERN 2: Conditional INSERT (avoid duplicates)
// ============================================
echo "<h3>2. Insert Only if Not Exists</h3>";
 
$new_product = "Notebook";
$new_price = 50;
 
// Check first
$sql = "SELECT * FROM product WHERE p_name = '$new_product'";
$result = $mysqli->query($sql);
 
if($result->num_rows == 0) {
    // Doesn't exist, insert it
    $sql = "INSERT INTO product (p_name, p_price, p_type) 
            VALUES ('$new_product', $new_price, 'stationary')";
    if($mysqli->query($sql)) {
        echo "✅ Product added!<br>";
    }
} else {
    echo "⚠️ Product already exists!<br>";
}
$result->free();
 
// ============================================
// PATTERN 3: Update or Insert (UPSERT pattern)
// ============================================
echo "<h3>3. Update if Exists, Insert if Not</h3>";
 
$product_name = "Ruler";
$product_price = 15;
 
$sql = "SELECT * FROM product WHERE p_name = '$product_name'";
$result = $mysqli->query($sql);
 
if($result->num_rows > 0) {
    // EXISTS - Update it
    $row = $result->fetch_array();
    $sql = "UPDATE product SET p_price = $product_price WHERE p_id = " . $row['p_id'];
    $mysqli->query($sql);
    echo "✅ Product updated!<br>";
} else {
    // DOESN'T EXIST - Insert it
    $sql = "INSERT INTO product (p_name, p_price, p_type) 
            VALUES ('$product_name', $product_price, 'stationary')";
    $mysqli->query($sql);
    echo "✅ Product inserted!<br>";
}
$result->free();
 
// ============================================
// PATTERN 4: Counting with WHERE
// ============================================
echo "<h3>4. Count Products Above Price</h3>";
 
$min_price = 100;
$sql = "SELECT COUNT(*) as count FROM product WHERE p_price > $min_price";
$result = $mysqli->query($sql);
$row = $result->fetch_array();
echo "Products above ₹$min_price: " . $row['count'] . "<br>";
$result->free();
 
// ============================================
// PATTERN 5: Search with multiple conditions
// ============================================
echo "<h3>5. Complex WHERE Clause</h3>";
 
$sql = "SELECT * FROM product 
        WHERE p_price BETWEEN 10 AND 100 
        AND p_type = 'stationary' 
        ORDER BY p_price DESC";
 
$result = $mysqli->query($sql);
echo "Found " . $result->num_rows . " stationary items between ₹10-100:<br>";
 
while($row = $result->fetch_array()) {
    echo "- " . $row['p_name'] . " (₹" . $row['p_price'] . ")<br>";
}
$result->free();
 
// ============================================
// PATTERN 6: Getting specific column only
// ============================================
echo "<h3>6. Select Specific Columns Only</h3>";
 
$sql = "SELECT p_name, p_price FROM product LIMIT 3";
$result = $mysqli->query($sql);
 
while($row = $result->fetch_array()) {
    // Notice: Only p_name and p_price are available
    echo $row['p_name'] . ": ₹" . $row['p_price'] . "<br>";
}
$result->free();
 
// ============================================
// PATTERN 7: Using ORDER BY and LIMIT
// ============================================
echo "<h3>7. Get Top 3 Most Expensive Products</h3>";
 
$sql = "SELECT * FROM product ORDER BY p_price DESC LIMIT 3";
$result = $mysqli->query($sql);
 
$rank = 1;
while($row = $result->fetch_array()) {
    echo "$rank. " . $row['p_name'] . " - ₹" . $row['p_price'] . "<br>";
    $rank++;
}
$result->free();
 
// ============================================
// PATTERN 8: Searching with LIKE
// ============================================
echo "<h3>8. Search Products Containing 'er'</h3>";
 
$search = "%er%"; // % is wildcard
$sql = "SELECT * FROM product WHERE p_name LIKE '$search'";
$result = $mysqli->query($sql);
 
while($row = $result->fetch_array()) {
    echo "- " . $row['p_name'] . "<br>";
}
$result->free();
 
$mysqli->close();
 
echo "<hr>";
echo "<h3>✨ Key Exam Takeaways:</h3>";
echo "<ul>";
echo "<li>Always check <code>num_rows</code> before using <code>fetch_array()</code></li>";
echo "<li>Use <code>real_escape_string()</code> for user inputs</li>";
echo "<li>Always use WHERE in UPDATE/DELETE to avoid affecting all rows</li>";
echo "<li>Check <code>affected_rows</code> to verify changes</li>";
echo "<li>Free results with <code>free()</code></li>";
echo "<li>Close connection with <code>close()</code></li>";
echo "<li>Use <code>isset()</code> before accessing POST/GET data</li>";
echo "</ul>";
?>

Video 9: Security Best Practices (20 minutes)

File: `security_examples.php`

<?php
/**
 * SECURITY IN PHP & MySQL
 * EXAM TOPIC: Understanding SQL Injection
 */
 
require_once('connect.php');
 
echo "<h2>Security Examples</h2>";
 
// ============================================
// BAD: SQL Injection Vulnerable
// ============================================
echo "<h3>❌ BAD Example (SQL Injection Vulnerable):</h3>";
echo "<p>If user inputs: <code>'; DROP TABLE product; --</code></p>";
 
$user_input = "Pencil"; // Imagine this comes from $_POST
 
// DANGEROUS! Never do this:
// $sql = "SELECT * FROM product WHERE p_name = '$user_input'";
 
echo "<p>If user input wasn't escaped, they could delete your entire table!</p>";
 
// ============================================
// GOOD: Using real_escape_string()
// ============================================
echo "<h3>✅ GOOD Example (Protected):</h3>";
 
$user_input = "Idiot's Guide"; // Contains special character
$user_input = $mysqli->real_escape_string($user_input); // Escape it!
 
$sql = "SELECT * FROM product WHERE p_name = '$user_input'";
$result = $mysqli->query($sql);
 
echo "<p>String <code>Idiot's Guide</code> is safely escaped!</p>";
echo "<p>Result: Found " . $result->num_rows . " products</p>";
$result->free();
 
// ============================================
// Validating User Input
// ============================================
echo "<h3>Input Validation Examples:</h3>";
 
// Example 1: Ensure ID is numeric
if(isset($_GET['id'])) {
    $id = intval($_GET['id']); // Convert to integer
    echo "<p>✅ ID validated as integer: $id</p>";
}
 
// Example 2: Validate email
$email = "test@example.com";
if(filter_var($email, FILTER_VALIDATE_EMAIL)) {
    echo "<p>✅ Valid email: $email</p>";
} else {
    echo "<p>❌ Invalid email!</p>";
}
 
// Example 3: Check if value is not empty
$name = "";
if(empty($name)) {
    echo "<p>⚠️ Name cannot be empty!</p>";
}
 
// ============================================
// Prepared Statements (Advanced - Bonus Knowledge)
// ============================================
echo "<h3>🎓 Bonus: Prepared Statements (Most Secure)</h3>";
 
$search_name = "Pencil";
$search_price = 50;
 
// Prepare statement with placeholders (?)
$stmt = $mysqli->prepare("SELECT * FROM product WHERE p_name = ? OR p_price < ?");
 
// Bind parameters (s = string, i = integer)
$stmt->bind_param("si", $search_name, $search_price);
 
// Execute
$stmt->execute();
 
// Get result
$result = $stmt->get_result();
echo "<p>Found " . $result->num_rows . " products using prepared statement</p>";
 
$result->free();
$stmt->close();
 
$mysqli->close();
 
echo "<hr>";
echo "<h3>🔒 Security Checklist for Exam:</h3>";
echo "<ul>";
echo "<li>✅ Use <code>real_escape_string()</code> for all user inputs</li>";
echo "<li>✅ Validate data types (use <code>intval()</code> for IDs)</li>";
echo "<li>✅ Check if POST/GET data exists with <code>isset()</code></li>";
echo "<li>✅ Never trust user input directly in SQL</li>";
echo "<li>✅ Use <code>empty()</code> to check for empty strings</li>";
echo "<li>✅ Prepared statements are most secure (bonus points!)</li>";
echo "</ul>";
?>

Final Summary: Exam Checklist

File: `exam_checklist.php`

<!DOCTYPE html>
<html>
<head>
    <title>PHP MySQL Exam Checklist</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 900px;
            margin: 50px auto;
            padding: 20px;
            line-height: 1.6;
        }
        h1 { color: #2c3e50; }
        h2 { color: #3498db; margin-top: 30px; }
        .section {
            background: #ecf0f1;
            padding: 15px;
            margin: 15px 0;
            border-left: 4px solid #3498db;
        }
        code {
            background: #2c3e50;
            color: #fff;
            padding: 2px 6px;
            border-radius: 3px;
            font-family: 'Courier New', monospace;
        }
        .important {
            background: #ffe5e5;
            border-left-color: #e74c3c;
        }
        ul { margin: 10px 0; }
        li { margin: 5px 0; }
    </style>
</head>
<body>
    <h1>📋 PHP & MySQL Exam Checklist</h1>
    
    <div class="section important">
        <h2>🔥 Critical Concepts (Must Know!)</h2>
        <ul>
            <li><strong>Connection:</strong> <code>new mysqli('host', 'user', 'pass', 'db')</code></li>
            <li><strong>Check errors:</strong> <code>connect_errno</code>, <code>connect_error</code></li>
            <li><strong>Execute query:</strong> <code>$mysqli->query($sql)</code></li>
            <li><strong>Fetch data:</strong> <code>while($row = $result->fetch_array())</code></li>
            <li><strong>Escape strings:</strong> <code>real_escape_string()</code></li>
            <li><strong>Always use:</strong> <code>isset()</code> before <code>$_POST/$_GET</code></li>
        </ul>
    </div>
    
    <div class="section">
        <h2>💾 CRUD Operations</h2>
        
        <h3>CREATE (INSERT)</h3>
        <code>INSERT INTO table (col1, col2) VALUES ('val1', val2)</code>
        <ul>
            <li>Strings need quotes: <code>'text'</code></li>
            <li>Numbers don't: <code>123</code></li>
            <li>Get last ID: <code>$mysqli->insert_id</code></li>
        </ul>
        
        <h3>READ (SELECT)</h3>
        <code>SELECT * FROM table WHERE condition</code>
        <ul>
            <li>Count results: <code>$result->num_rows</code></li>
            <li>Loop: <code>while($row = $result->fetch_array())</code></li>
            <li>Access: <code>$row['column_name']</code> or <code>$row[0]</code></li>
        </ul>
        
        <h3>UPDATE</h3>
        <code>UPDATE table SET col1='val1' WHERE id=1</code>
        <ul>
            <li>⚠️ Always use WHERE clause!</li>
            <li>Check changes: <code>$mysqli->affected_rows</code></li>
        </ul>
        
        <h3>DELETE</h3>
        <code>DELETE FROM table WHERE id=1</code>
        <ul>
            <li>⚠️ Always use WHERE clause!</li>
            <li>Without WHERE = ALL rows deleted!</li>
        </ul>
    </div>
    
    <div class="section">
        <h2>🔍 SQL Operators (Exam Favorites)</h2>
        <ul>
            <li><code>=</code> - Exact match</li>
            <li><code>>, <, >=, <=</code> - Comparisons</li>
            <li><code>BETWEEN x AND y</code> - Range</li>
            <li><code>LIKE 'P%'</code> - Pattern (% = wildcard)</li>
            <li><code>IN (1,2,3)</code> - Multiple values</li>
            <li><code>AND, OR, NOT</code> - Logical operators</li>
        </ul>
    </div>
    
    <div class="section">
        <h2>📊 Aggregate Functions</h2>
        <ul>
            <li><code>COUNT(*)</code> - Count rows</li>
            <li><code>SUM(column)</code> - Total sum</li>
            <li><code>AVG(column)</code> - Average</li>
            <li><code>MIN(column)</code> - Minimum value</li>
            <li><code>MAX(column)</code> - Maximum value</li>
            <li><code>GROUP BY column</code> - Group results</li>
        </ul>
    </div>
    
    <div class="section">
        <h2>🔗 Understanding Arrays in PHP</h2>
        <ul>
            <li><code>$row = $result->fetch_array()</code> returns associative array</li>
            <li>Access by name: <code>$row['p_name']</code></li>
            <li>Access by index: <code>$row[0]</code></li>
            <li><code>$_POST</code> is an array of form data</li>
            <li><code>$_GET</code> is an array of URL parameters</li>
        </ul>
    </div>
    
    <div class="section">
        <h2>🔐 Security Essentials</h2>
        <ul>
            <li>✅ Use <code>real_escape_string()</code> on ALL user inputs</li>
            <li>✅ Validate data types: <code>intval()</code> for numbers</li>
            <li>✅ Check existence: <code>isset()</code> before using</li>
            <li>✅ Check emptiness: <code>empty()</code> for required fields</li>
            <li>❌ Never trust user input directly</li>
        </ul>
    </div>
    
    <div class="section">
        <h2>📝 Common Exam Patterns</h2>
        
        <h3>Pattern 1: Form to Database</h3>
        <code>
        $name = $mysqli->real_escape_string($_POST['name']);<br>
        $sql = "INSERT INTO table (name) VALUES ('$name')";<br>
        $mysqli->query($sql);
        </code>
        
        <h3>Pattern 2: Display from Database</h3>
        <code>
        $result = $mysqli->query("SELECT * FROM table");<br>
        while($row = $result->fetch_array()) {<br>
        &nbsp;&nbsp;echo $row['column'];<br>
        }
        </code>
        
        <h3>Pattern 3: Edit Form (Pre-populate)</h3>
        <code>
        $id = $_GET['id'];<br>
        $result = $mysqli->query("SELECT * FROM table WHERE id=$id");<br>
        $row = $result->fetch_array();<br>
        // Then use $row['column'] in value attributes
        </code>
        
        <h3>Pattern 4: Dynamic Dropdown</h3>
        <code>
        &lt;select name="type"&gt;<br>
        &lt;?php<br>
        &nbsp;&nbsp;$result = $mysqli->query("SELECT * FROM types");<br>
        &nbsp;&nbsp;while($row = $result->fetch_array()) {<br>
        &nbsp;&nbsp;&nbsp;&nbsp;echo "&lt;option value='".$row['id']."'&gt;".$row['name']."&lt;/option&gt;";<br>
        &nbsp;&nbsp;}<br>
        ?&gt;<br>
        &lt;/select&gt;
        </code>
    </div>
    
    <div class="section important">
        <h2>⚠️ Common Mistakes to Avoid</h2>
        <ul>
            <li>❌ Forgetting WHERE in UPDATE/DELETE</li>
            <li>❌ Not checking <code>isset()</code> before <code>$_POST</code></li>
            <li>❌ Not escaping user input</li>
            <li>❌ Not checking <code>num_rows</code> before <code>fetch_array()</code></li>
            <li>❌ Forgetting to close connection</li>
            <li>❌ Not freeing results</li>
            <li>❌ Using wrong quotes (strings need single quotes in SQL)</li>
        </ul>
    </div>
    
    <div class="section">
        <h2>🎯 Quick Reference</h2>
        
        <h3>File Includes:</h3>
        <ul>
            <li><code>require_once('file.php')</code> - Include file once (stops if missing)</li>
            <li><code>include_once('file.php')</code> - Include file once (warns if missing)</li>
            <li><code>require('file.php')</code> - Include file (can include multiple times)</li>
        </ul>
        
        <h3>Important Properties:</h3>
        <ul>
            <li><code>$mysqli->insert_id</code> - Last inserted AUTO_INCREMENT ID</li>
            <li><code>$mysqli->affected_rows</code> - Rows changed by UPDATE/DELETE</li>
            <li><code>$result->num_rows</code> - Number of rows returned</li>
            <li><code>$result->field_count</code> - Number of columns</li>
        </ul>
        
        <h3>Important Methods:</h3>
        <ul>
            <li><code>$mysqli->query($sql)</code> - Execute SQL</li>
            <li><code>$result->fetch_array()</code> - Get one row</li>
            <li><code>$result->free()</code> - Free memory</li>
            <li><code>$mysqli->close()</code> - Close connection</li>
            <li><code>$mysqli->real_escape_string($str)</code> - Escape string</li>
        </ul>
    </div>
    
    <div class="section">
        <h2>📚 Files You Should Create for Practice</h2>
        <ol>
            <li><code>connect.php</code> - Reusable connection</li>
            <li><code>create_table.php</code> - Table creation</li>
            <li><code>insert_form.php</code> - HTML form</li>
            <li><code>insert_handler.php</code> - Process INSERT</li>
            <li><code>view.php</code> - Display all records</li>
            <li><code>edit.php</code> - Edit form with pre-filled data</li>
            <li><code>update_handler.php</code> - Process UPDATE</li>
            <li><code>delete.php</code> - Delete record</li>
        </ol>
    </div>
    
    <div class="section important">
        <h2>🎓 Final Exam Tips</h2>
        <ul>
            <li>✅ Read questions carefully - they often test specific SQL syntax</li>
            <li>✅ Always write complete code (opening/closing PHP tags)</li>
            <li>✅ Don't forget semicolons in SQL and PHP!</li>
            <li>✅ Use meaningful variable names</li>
            <li>✅ Comment your code if helpful</li>
            <li>✅ Test your SQL syntax mentally</li>
            <li>✅ Remember: Forms use POST/GET, not both!</li>
            <li>✅ Understand the difference between <code>query()</code> and <code>fetch_array()</code></li>
        </ul>
    </div>
    
    <div class="section">
        <h2>🚀 Practice Commands</h2>
        <p><strong>Start MySQL:</strong> <code>brew services start mysql</code></p>
        <p><strong>Stop MySQL:</strong> <code>brew services stop mysql</code></p>
        <p><strong>Start PHP Server:</strong> <code>php -S localhost:8000</code></p>
        <p><strong>Access MySQL CLI:</strong> <code>mysql -u testuser -p</code></p>
        <p><strong>Show databases:</strong> <code>SHOW DATABASES;</code></p>
        <p><strong>Use database:</strong> <code>USE database_name;</code></p>
        <p><strong>Show tables:</strong> <code>SHOW TABLES;</code></p>
    </div>
    
    <hr>
    <p style="text-align: center; color: #7f8c8d;">
        <strong>Good luck on your exam! 🍀</strong><br>
        Remember: Practice makes perfect. Run all these examples yourself!
    </p>
    
</body>
</html>

Teaching Schedule Summary

Video Breakdown:

Video 1 (15 min) - Connection basics
Video 2 (20 min) - Database & table creation, require_once
Video 3 (25 min) - INSERT operations
Video 4 (30 min) - SELECT queries & arrays
Video 5 (20 min) - UPDATE operations
Video 6 (15 min) - DELETE operations
Video 7 (25 min) - JOINs, dropdowns, aggregates
Video 8 (30 min) - Common exam patterns
Video 9 (20 min) - Security practices

Total: ~3 hours of content

Quick Start Commands

# Terminal 1 - Start MySQL
brew services start mysql
 
# Terminal 2 - Navigate and start PHP server
cd ~/php_mysql_tutorial
php -S localhost:8000
 
# Browser
# Open: http://localhost:8000/filename.php
 
# When done
brew services stop mysql
# Press Ctrl+C in Terminal 2 to stop PHP server

Good luck with teaching your friend! 🎓

DB

PHP & MySQL Teaching Guide

Complete Video Tutorial Script for Your Friend

Prerequisites Setup (Video 1 - 10 minutes)

What You'll Need

Initial Setup Commands

Video 1: Understanding the Connection Flow (15 minutes)

Files to Create:

Video Script:

File 1: connection_demo.php

File 2: test_connection.php

Run in Terminal:

Video 2: Setting Up Your First Database (20 minutes)

Files to Create:

Video Script:

File 1: connect.php - The Reusable Connection

File 2: create_database.php

File 3: create_table.php

Run Commands:

Video 3: INSERT Operations (25 minutes)

Files to Create:

Video Script:

File 1: insert_single.php - Basic INSERT

File 2: insert_form.php - HTML Form

File 3: insert_handler.php - Processing Form Data

Video 4: SELECT Operations (30 minutes)

Files to Create:

Video Script:

File 1: view_products.php - Basic SELECT

File 2: view_with_array.php - Understanding Array Access

File 3: search_products.php - WHERE Clause

Video 5: UPDATE Operations (20 minutes)

Files to Create:

Video Script:

File 1: update_price.php - Basic UPDATE

File 2: edit_product.php - Edit Form

File 3: update_handler.php

Video 6: DELETE Operations (15 minutes)

Files to Create:

Video Script:

File 1: delete_product.php

File 2: view_with_delete.php - Complete CRUD Interface

Video 7: Advanced Concepts (25 minutes)

Files to Create:

File 1: join_example.php - Understanding JOINs

File 2: dropdown_from_db.php - Dynamic Dropdowns

File 3: count_and_aggregate.php - SQL Functions

Video 8: Common Exam Patterns (30 minutes)

File: exam_patterns.php

Video 9: Security Best Practices (20 minutes)

File: security_examples.php

Final Summary: Exam Checklist

File: exam_checklist.php

Teaching Schedule Summary

Video Breakdown:

Quick Start Commands

File 1: `connection_demo.php`

File 2: `test_connection.php`

File 1: `connect.php` - The Reusable Connection

File 2: `create_database.php`

File 3: `create_table.php`

File 1: `insert_single.php` - Basic INSERT

File 2: `insert_form.php` - HTML Form

File 3: `insert_handler.php` - Processing Form Data

File 1: `view_products.php` - Basic SELECT

File 2: `view_with_array.php` - Understanding Array Access

File 3: `search_products.php` - WHERE Clause

File 1: `update_price.php` - Basic UPDATE

File 2: `edit_product.php` - Edit Form

File 3: `update_handler.php`

File 1: `delete_product.php`

File 2: `view_with_delete.php` - Complete CRUD Interface

File 1: `join_example.php` - Understanding JOINs

File 2: `dropdown_from_db.php` - Dynamic Dropdowns

File 3: `count_and_aggregate.php` - SQL Functions

File: `exam_patterns.php`

File: `security_examples.php`

File: `exam_checklist.php`