PHP can connect to a MySQL database to store, retrieve, update, and delete information. This is useful for student management systems, library applications, login systems, and websites.
PHP is a server-side programming language, while MySQL is a relational database management system.
Example workflow:
PHP does not automatically connect to MySQL. You must configure a database server and create a connection.
XAMPP provides a local development environment containing Apache, PHP, and MariaDB. MariaDB is commonly used as a MySQL-compatible database server in XAMPP.
school.Open phpMyAdmin at:
http://localhost/phpmyadmin/Depending on your XAMPP version, the database server may be MariaDB rather than Oracle MySQL. The basic examples in this lesson work with typical MySQL-compatible setups.
A database contains tables that store related information. You can create a database using SQL.
CREATE DATABASE school;Select the database before creating a table:
USE school;CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL,
course VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Column explanation:
id uniquely identifies each student.AUTO_INCREMENT generates the next numeric ID automatically.name stores the student's name.email stores the student's email address.course stores the selected course.created_at records the creation timestamp.
The SQL INSERT INTO statement adds new
records to a table.
INSERT INTO students (name, email, course)
VALUES (
'Rahul Kumar',
'rahul@example.com',
'PHP Programming'
);You can run this SQL statement in phpMyAdmin's SQL tab.
INSERT INTO students (name, email, course)
VALUES (
'Priya Singh',
'priya@example.com',
'Web Development'
);
The database generates the id and
created_at values automatically when
the relevant defaults apply.
In PHP applications, use prepared statements rather than concatenating user input directly into SQL queries.
The SQL SELECT statement retrieves records
from a table.
SELECT * FROM students;Retrieve only selected columns:
SELECT name, email, course
FROM students;Retrieve a specific student's record:
SELECT *
FROM students
WHERE id = 1;
You can use ORDER BY to sort the results.
SELECT *
FROM students
ORDER BY name ASC;These SQL queries can be executed in phpMyAdmin. In the next lesson, you will learn how to connect PHP to the database using PDO.
UPDATE students
SET course = 'Full Stack Development'
WHERE id = 1;
The WHERE clause limits which records are
updated. Without it, every record in the table may be updated.
DELETE FROM students
WHERE id = 2;
The WHERE clause limits which records are
deleted. Without it, all records in the table may be deleted.
Be careful when executing update and delete statements. Test them with a SELECT query first and maintain backups for important data.
PDO (PHP Data Objects) provides a consistent interface for
accessing supported databases. This example creates a
connection to the school database.
<?php
$host = "localhost";
$dbname = "school";
$username = "root";
$password = "";
try {
$pdo = new PDO(
"mysql:host=$host;dbname=$dbname;charset=utf8mb4",
$username,
$password,
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]
);
echo "Database connected successfully.";
} catch (PDOException $e) {
error_log($e->getMessage());
echo "Database connection failed.";
}
?>Expected output: Database connected successfully.
This output appears only when the database server is available and the connection details are correct.
The example assumes PDO MySQL support is enabled and that your local database uses the specified credentials. In production, use environment-based configuration and a database account with limited permissions.
After connecting to MySQL, PHP can execute a SELECT query and display the retrieved records.
<?php
$pdo = new PDO(
"mysql:host=localhost;dbname=school;charset=utf8mb4",
"root",
"",
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]
);
$stmt = $pdo->query(
"SELECT id, name, email, course FROM students ORDER BY id"
);
$students = $stmt->fetchAll();
?>
<table border="1" cellpadding="8">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Course</th>
</tr>
<?php foreach ($students as $student): ?>
<tr>
<td>
<?= (int) $student['id'] ?>
</td>
<td>
<?= htmlspecialchars(
$student['name'],
ENT_QUOTES | ENT_SUBSTITUTE,
'UTF-8'
) ?>
</td>
<td>
<?= htmlspecialchars(
$student['email'],
ENT_QUOTES | ENT_SUBSTITUTE,
'UTF-8'
) ?>
</td>
<td>
<?= htmlspecialchars(
$student['course'],
ENT_QUOTES | ENT_SUBSTITUTE,
'UTF-8'
) ?>
</td>
</tr>
<?php endforeach; ?>
</table>
The fetchAll() method returns all matching
rows as an array. For large tables, retrieve records in
pages instead of loading everything at once.
Escape database values when displaying them in HTML, because database content is not automatically safe for output.
This example combines an HTML form, basic validation,
a PDO connection, and a prepared INSERT statement.
Save it as add_student.php.
<?php
$message = "";
if ($_SERVER["REQUEST_METHOD"] === "POST") {
$name = trim($_POST["name"] ?? "");
$email = trim($_POST["email"] ?? "");
$course = trim($_POST["course"] ?? "");
if ($name === "" || $course === "") {
$message = "Name and course are required.";
} elseif (
filter_var($email, FILTER_VALIDATE_EMAIL) === false
) {
$message = "Please enter a valid email address.";
} else {
try {
$pdo = new PDO(
"mysql:host=localhost;dbname=school;charset=utf8mb4",
"root",
"",
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]
);
$sql = "INSERT INTO students (name, email, course)
VALUES (:name, :email, :course)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
"name" => $name,
"email" => $email,
"course" => $course
]);
$message = "Student added successfully.";
} catch (PDOException $e) {
error_log($e->getMessage());
$message = "Unable to add student. Please try again.";
}
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Add Student</title>
</head>
<body>
<h2>Student Registration</h2>
<form method="post">
<label for="name">Student Name:</label>
<input type="text" id="name" name="name" required>
<br><br>
<label for="email">Email:</label>
<input type="email" id="email" name="email" required>
<br><br>
<label for="course">Course:</label>
<input type="text" id="course" name="course" required>
<br><br>
<button type="submit">Add Student</button>
</form>
<p>
<?= htmlspecialchars(
$message,
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?>
</p>
</body>
</html>
Before running this example, create the
school database and the
students table from the earlier sections.
Also make sure the database credentials match your setup.
The prepared statement protects the SQL query from injection through these parameter values. For a production registration system, also consider duplicate email handling, CSRF protection, length limits, and appropriate authorization.
1. Which database system is commonly used with PHP?
2. Which PDO method prepares an SQL statement?
3. Which SQL statement retrieves records from a table?