CRUD stands for Create, Read, Update and Delete. These four operations form the backbone of almost every dynamic web application, from simple contact managers to full scale business systems. In this tutorial you will build a working PHP and MySQL CRUD application using MySQLi with prepared statements to keep your app secure against SQL injection.
By the end of this guide you will have a functional student records manager that lets you add, view, edit and delete records from a MySQL database.
What You Will Need
- A local server environment such as XAMPP, WAMP or MAMP (PHP 7.4 or higher recommended)
- MySQL or MariaDB
- A code editor such as VS Code
- Basic understanding of HTML and PHP syntax
Step 1: Create the Database and Table
Open phpMyAdmin or your MySQL client and run the following SQL to create a database and a students table.
CREATE DATABASE IF NOT EXISTS crud_tutorial;
USE crud_tutorial;
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
course VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);This creates a simple table to store student records with a name, email and course.
Step 2: Set Up the Database Connection
Create a file called db.php. This file will handle the connection and be included in every other page.
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "crud_tutorial";
$conn = mysqli_connect($host, $username, $password, $database);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
?>Using a single connection file keeps your code organized and easy to maintain across the whole project.
Step 3: Create (Insert Records)
Create a file called create.php. This page will display a form and insert the submitted data into the database using a prepared statement.
<?php
include 'db.php';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$full_name = trim($_POST['full_name']);
$email = trim($_POST['email']);
$course = trim($_POST['course']);
$stmt = mysqli_prepare($conn, "INSERT INTO students (full_name, email, course) VALUES (?, ?, ?)");
mysqli_stmt_bind_param($stmt, "sss", $full_name, $email, $course);
if (mysqli_stmt_execute($stmt)) {
header("Location: read.php");
exit;
} else {
$error = "Something went wrong. Please try again.";
}
mysqli_stmt_close($stmt);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Add Student</title>
</head>
<body>
<h2>Add New Student</h2>
<?php if (!empty($error)) echo "<p style='color:red;'>$error</p>"; ?>
<form method="POST" action="">
<label>Full Name</label><br>
<input type="text" name="full_name" required><br><br>
<label>Email</label><br>
<input type="email" name="email" required><br><br>
<label>Course</label><br>
<input type="text" name="course" required><br><br>
<button type="submit">Add Student</button>
</form>
</body>
</html>Prepared statements bind user input safely so the values can never be interpreted as SQL commands, which protects the application from injection attacks.
Step 4: Read (Display Records)
Create a file called read.php to fetch and display every record in the students table.
<?php
include 'db.php';
$result = mysqli_query($conn, "SELECT * FROM students ORDER BY id DESC");
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Student Records</title>
</head>
<body>
<h2>Student Records</h2>
<a href="create.php">Add New Student</a>
<table border="1" cellpadding="8" cellspacing="0">
<tr>
<th>ID</th>
<th>Full Name</th>
<th>Email</th>
<th>Course</th>
<th>Actions</th>
</tr>
<?php while ($row = mysqli_fetch_assoc($result)): ?>
<tr>
<td><?= htmlspecialchars($row['id']) ?></td>
<td><?= htmlspecialchars($row['full_name']) ?></td>
<td><?= htmlspecialchars($row['email']) ?></td>
<td><?= htmlspecialchars($row['course']) ?></td>
<td>
<a href="update.php?id=<?= $row['id'] ?>">Edit</a> |
<a href="delete.php?id=<?= $row['id'] ?>" onclick="return confirm('Delete this record?')">Delete</a>
</td>
</tr>
<?php endwhile; ?>
</table>
</body>
</html>The htmlspecialchars() function escapes output so any stored text cannot break the page layout or run as script code, which guards against cross site scripting.
Step 5: Update (Edit Records)
Create a file called update.php. This page loads the existing record into a form, then saves any changes back to the database.
<?php
include 'db.php';
$id = intval($_GET['id'] ?? $_POST['id'] ?? 0);
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$full_name = trim($_POST['full_name']);
$email = trim($_POST['email']);
$course = trim($_POST['course']);
$stmt = mysqli_prepare($conn, "UPDATE students SET full_name = ?, email = ?, course = ? WHERE id = ?");
mysqli_stmt_bind_param($stmt, "sssi", $full_name, $email, $course, $id);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
header("Location: read.php");
exit;
}
$stmt = mysqli_prepare($conn, "SELECT * FROM students WHERE id = ?");
mysqli_stmt_bind_param($stmt, "i", $id);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$student = mysqli_fetch_assoc($result);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Edit Student</title>
</head>
<body>
<h2>Edit Student</h2>
<?php if ($student): ?>
<form method="POST" action="">
<input type="hidden" name="id" value="<?= $student['id'] ?>">
<label>Full Name</label><br>
<input type="text" name="full_name" value="<?= htmlspecialchars($student['full_name']) ?>" required><br><br>
<label>Email</label><br>
<input type="email" name="email" value="<?= htmlspecialchars($student['email']) ?>" required><br><br>
<label>Course</label><br>
<input type="text" name="course" value="<?= htmlspecialchars($student['course']) ?>" required><br><br>
<button type="submit">Update Student</button>
</form>
<?php else: ?>
<p>Record not found.</p>
<?php endif; ?>
</body>
</html>This page uses mysqli_stmt_get_result() to pull the current values into the form fields so the user can see and edit what is already saved.
Step 6: Delete Records
Create a file called delete.php to remove a record safely using its ID.
<?php
include 'db.php';
$id = intval($_GET['id'] ?? 0);
if ($id > 0) {
$stmt = mysqli_prepare($conn, "DELETE FROM students WHERE id = ?");
mysqli_stmt_bind_param($stmt, "i", $id);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
}
header("Location: read.php");
exit;
?>Always confirm deletions on the front end, as shown in the confirm dialog in read.php, since this action cannot be undone.
Step 7: Test Your Application
- Save all files inside a single folder in your server's root directory (for example
htdocs/crud_tutorial). - Start Apache and MySQL from your local server control panel.
- Visit
http://localhost/crud_tutorial/read.phpin your browser. - Add a student, edit the record, then delete it to confirm every operation works as expected.
Best Practices to Keep in Mind
- Always use prepared statements instead of directly inserting variables into SQL queries.
- Escape output with
htmlspecialchars()before displaying user submitted data. - Validate and sanitize form input on both the client and server side.
- Use
intval()when casting IDs from the URL to prevent unexpected input types. - Keep database credentials outside of publicly accessible files when deploying to production.
Conclusion
You have now built a complete PHP and MySQL CRUD application covering Create, Read, Update and Delete operations using secure prepared statements. This same pattern, a connection file, a create form, a read table, an update form and a delete handler, can be extended to manage products, blog posts, users, invoices or any other data your application needs to store.
Once comfortable with this structure, consider adding features such as pagination, search filters, form validation messages and user authentication to build out a more complete system.
Hit me with a comment!