CRUD Operations

Introduction

You can now connect PHP to MySQL and run a single query. Real applications, though, need to do much more than that: they add new records, list existing ones, edit them, and eventually remove them. These four operations together are known as CRUD.

In this lesson, you will build a small, complete CRUD example against a `students` table, wrapping each operation in its own reusable PHP function.

What is CRUD?

CRUD stands for Create, Read, Update, and Delete — the four basic operations any application performs on stored data. Nearly every feature you can imagine in a real application, from posting a comment to editing a profile, boils down to one or more of these four operations.

Setting Up the Table

This lesson uses a simple `students` table, the same shape introduced in the MySQL lesson. Assume it already exists, along with a shared MySQLi connection stored in `$conn`.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    course VARCHAR(100)
);

<?php
$conn = new mysqli("localhost", "root", "", "school");

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Create: Inserting Data

Create maps to the SQL INSERT statement. Here it is wrapped in a small function that takes the new student's details as arguments.

<?php
function createStudent($conn, $name, $age, $course) {
    $sql = "INSERT INTO students (name, age, course) VALUES ('$name', $age, '$course')";
    return $conn->query($sql);
}

if (createStudent($conn, "Amit Verma", 21, "PHP Development")) {
    echo "Student added successfully!";
} else {
    echo "Error: " . $conn->error;
}
?>

Student added successfully!

Read: Fetching Data

Read maps to SELECT. This function returns every student as an array of associative arrays, ready to loop over and display.

<?php
function getStudents($conn) {
    $sql = "SELECT id, name, age, course FROM students";
    $result = $conn->query($sql);
    return $result->fetch_all(MYSQLI_ASSOC);
}

$students = getStudents($conn);

foreach ($students as $student) {
    echo $student["name"] . " (" . $student["age"] . ") - " . $student["course"] . "\n";
}
?>

Amit Verma (21) - PHP Development

Update: Modifying Data

Update maps to the UPDATE statement, matching a specific row by its id so only that row changes.

<?php
function updateStudentCourse($conn, $id, $newCourse) {
    $sql = "UPDATE students SET course = '$newCourse' WHERE id = $id";
    return $conn->query($sql);
}

if (updateStudentCourse($conn, 1, "Advanced PHP")) {
    echo "Student updated successfully!";
}
?>

Student updated successfully!

Delete: Removing Data

Delete maps to the DELETE statement, again matched by id so it removes only the intended row.

<?php
function deleteStudent($conn, $id) {
    $sql = "DELETE FROM students WHERE id = $id";
    return $conn->query($sql);
}

if (deleteStudent($conn, 1)) {
    echo "Student deleted successfully!";
}
?>

Student deleted successfully!

Putting It All Together

With all four functions defined, a script can perform a full cycle of operations against the table using clear, descriptive function calls instead of scattering raw SQL strings everywhere.

<?php
require "db.php";

createStudent($conn, "Sara Khan", 23, "Web Design");

$students = getStudents($conn);
foreach ($students as $student) {
    echo $student["name"] . "\n";
}

updateStudentCourse($conn, 2, "UI/UX Design");
deleteStudent($conn, 3);

$conn->close();
?>

A Warning About User Input

Every example above builds its SQL by directly inserting values into a string with quotes and concatenation. That is fine for values you control, like the fixed examples here — but it becomes dangerous the moment any of those values come from user input, such as a form field.

<?php
// DO NOT do this with real user input
$name = $_POST["name"];
$sql = "INSERT INTO students (name, age, course) VALUES ('$name', 20, 'PHP')";
$conn->query($sql);
?>

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

CRUD operations are the backbone of nearly every data-driven feature you will build. With createStudent(), getStudents(), updateStudentCourse(), and deleteStudent(), you now have a working, reusable pattern for managing records in MySQL from PHP.

In this lesson, you implemented all four CRUD operations against a students table and saw why directly inserting user input into these queries is unsafe. Next, you will fix that exact problem using prepared statements.