Objective:
The objective of this lesson is to teach learners how to integrate PHP with MySQL. By the end of this lesson, participants should be able to:
- Establish a connection between PHP and MySQL.
- Execute SQL queries using PHP to interact with a MySQL database.
Description:
2.1 Connecting to a MySQL Database Using PHP
2.1.1 Introduction
Explore the importance of connecting PHP applications to MySQL databases for dynamic and data-driven web applications
2.1.2 PHP MySQL Connection
Learn how to establish a connection to a MySQL database using PHP. Cover essential parameters such as hostname, username, password, and database name.
<?php
$hostname = "localhost";
$username = "root";
$password = "your_password";
$database = "your_database";
// Create a connection
$connection = mysqli_connect($hostname, $username, $password, $database);
// Check the connection
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
} else {
echo "Connected successfully!";
}
?>
2.1.3 Closing the Connection
After performing database operations, it’s crucial to close the connection to free up resources and ensure good resource management.
<?php
// Database operations
// Close the connection
mysqli_close($connection);
?>
2.2 Executing SQL Queries with PHP
2.2.1 Introduction to SQL Queries in PHP
Understand the basics of executing SQL queries within PHP scripts.
2.2.2 Query Execution Functions
Explore PHP functions such as mysqli_query() for executing queries. Cover SELECT, INSERT, UPDATE, and DELETE queries.
<?php
// Example SELECT query
$sqlSelect = "SELECT * FROM users";
$resultSelect = mysqli_query($connection, $sqlSelect);
// Example INSERT query
$sqlInsert = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";
$resultInsert = mysqli_query($connection, $sqlInsert);
// Example UPDATE query
$sqlUpdate = "UPDATE users SET email = 'updated_email@example.com' WHERE username = 'john_doe'";
$resultUpdate = mysqli_query($connection, $sqlUpdate);
// Example DELETE query
$sqlDelete = "DELETE FROM users WHERE username = 'john_doe'";
$resultDelete = mysqli_query($connection, $sqlDelete);
?>
2.2.3 Fetching Results
Demonstrate how to retrieve and process the results of SELECT queries using functions like mysqli_fetch_assoc().
<?php
// Example SELECT query with result fetching
$sqlSelect = "SELECT * FROM users";
$resultSelect = mysqli_query($connection, $sqlSelect);
while ($row = mysqli_fetch_assoc($resultSelect)) {
echo "Username: " . $row['username'] . ", Email: " . $row['email'] . "<br>";
}
?>
2.2.4 Error Handling
Discuss the importance of error handling to identify and troubleshoot issues with SQL queries.
<?php
// Example of error handling
$result = mysqli_query($connection, $sql);
if (!$result) {
die("Query failed: " . mysqli_error($connection));
}
?>
2.3 Best Practices and Security
2.3.1 Prepared Statements
Introduce the concept of prepared statements for secure and efficient SQL queries.
<?php
// Example of a prepared statement
$stmt = mysqli_prepare($connection, "INSERT INTO users (username, email) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, "ss", $username, $email);
// Set parameters and execute
$username = "john_doe";
$email = "john@example.com";
mysqli_stmt_execute($stmt);
?>
2.3.2 Sanitizing User Input
Emphasize the importance of sanitizing user input to prevent SQL injection attacks.
<?php
// Example of sanitizing user input
$username = mysqli_real_escape_string($connection, $_POST['username']);
$password = mysqli_real_escape_string($connection, $_POST['password']);
$sqlLogin = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$resultLogin = mysqli_query($connection, $sqlLogin);
?>
2.3.3 Transactions
Introduce the concept of transactions to ensure the integrity of database operations.
<?php
// Example of a transaction
mysqli_autocommit($connection, false);
$sql1 = "INSERT INTO orders (customer_id, total_amount) VALUES (1, 50)";
$sql2 = "UPDATE customers SET balance = balance - 50 WHERE customer_id = 1";
mysqli_query($connection, $sql1);
mysqli_query($connection, $sql2);
mysqli_commit($connection);
mysqli_autocommit($connection, true);
?>
2.4 Advanced Queries
2.4.1 JOIN Operations
Explain how to use JOIN operations to combine rows from two or more tables based on a related column.
<?php
// Example of a JOIN operation
$sqlJoin = "SELECT users.username, orders.order_id FROM users JOIN orders ON users.user_id = orders.user_id";
$resultJoin = mysqli_query($connection, $sqlJoin);
?>
2.5 Practice Exercise
Provide a hands-on exercise where learners connect PHP to a MySQL database, execute various SQL queries, and implement security measures.
Task:
- Database Setup:
- Create a MySQL database named “ecommerce” with tables for users and products.
- Use the following SQL statements:
-- Create the ecommerce database
CREATE DATABASE ecommerce;
-- Use the ecommerce database
USE ecommerce;
-- Create the users table
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL
);
-- Create the products table
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
PHP Connection:
- Connect PHP to the “ecommerce” database.
<?php
$hostname = "localhost";
$username = "root";
$password = "your_password";
$database = "ecommerce";
$connection = mysqli_connect($hostname, $username, $password, $database);
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
} else {
echo "Connected successfully!";
}
?>
Secure User Registration Form:
- Implement a secure user registration form with input validation.
<?php
// Example of secure user registration form
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$username = mysqli_real_escape_string($connection, $_POST['username']);
$email = mysqli_real_escape_string($connection, $_POST['email']);
$sqlInsertUser = "INSERT INTO users (username, email) VALUES ('$username', '$email')";
$resultInsertUser = mysqli_query($connection, $sqlInsertUser);
if ($resultInsertUser) {
echo "User registered successfully!";
} else {
echo "Error: " . mysqli_error($connection);
}
}
?>
Insert Sample User Data:
- Insert sample user data into the “users” table.
<?php
// Insert sample user data
$sqlInsertSampleUser = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";
$resultInsertSampleUser = mysqli_query($connection, $sqlInsertSampleUser);
?>
Fetch and Display Users:
- Fetch and display the list of users using a SELECT query.
<?php
// Fetch and display users
$sqlFetchUsers = "SELECT * FROM users";
$resultFetchUsers = mysqli_query($connection, $sqlFetchUsers);
while ($user = mysqli_fetch_assoc($resultFetchUsers)) {
echo "Username: " . $user['username'] . ", Email: " . $user['email'] . "<br>";
}
?>
Explore JOIN Operations:
- Explore JOIN operations to retrieve user orders.
<?php
// Example of a JOIN operation
$sqlJoinOrders = "SELECT users.username, orders.order_id FROM users JOIN orders ON users.user_id = orders.user_id";
$resultJoinOrders = mysqli_query($connection, $sqlJoinOrders);
?>
This practice exercise provides a comprehensive hands-on experience in integrating PHP with MySQL, executing various queries, implementing security measures, and exploring advanced concepts. Learners will gain practical skills in handling real-world scenarios in web development.