PHP and MySQL: CRUD Operations

CRUD is an acronym for the four fundamental operations required to manage persistent data in any application: Create, Read, Update, and Delete. Mastering these four operations is essential for building any dynamic, database-driven website.

We will continue using the MySQLi Object-Oriented approach established in the previous chapter.

Setting Up the Connection (Reminder)

For all the examples below, assume you have successfully connected to your database ($conn) and the connection is open.

PHP

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "tutorial_users";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

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

1. Create (INSERT)

The CREATE operation adds new records (rows) into a database table. The SQL command for this is INSERT INTO.

Example: Adding a New Student

PHP

<?php
    $first = "Maria";
    $email = "maria@example.com";
    
    // The query includes the column names and the values to be inserted
    $sql = "INSERT INTO Students (firstname, email) 
            VALUES ('$first', '$email')";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully. Last ID: " . $conn->insert_id;
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
?>

Note: We are using variables directly in the query for simplicity here. In production, always use Prepared Statements to prevent SQL injection (covered later).

2. Read (SELECT)

The READ operation retrieves data from one or more tables. The SQL command for this is SELECT. This is often the most complex operation, as it requires querying the database and then fetching the results in PHP.

Example: Retrieving All Student Names

PHP

<?php
    $sql = "SELECT id, firstname, email FROM Students";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // Output data of each row
        while ($row = $result->fetch_assoc()) {
            echo "ID: " . $row["id"] . " - Name: " . $row["firstname"] . " - Email: " . $row["email"] . "<br>";
        }
    } else {
        echo "0 results found.";
    }
?>

• $result->num_rows: Checks if any rows were returned.
• $result->fetch_assoc(): Fetches one row of data as an associative array for each iteration of the while loop.

3. Update (UPDATE)

The UPDATE operation modifies existing records in a table. The SQL command is UPDATE.

Example: Changing a Student’s Email

PHP

<?php
    $sql = "UPDATE Students SET email='new.maria@example.com' 
            WHERE firstname='Maria'";

    if ($conn->query($sql) === TRUE) {
        echo "Record updated successfully.";
    } else {
        echo "Error updating record: " . $conn->error;
    }
?>

4. Delete (DELETE)

The DELETE operation removes existing records from a table. The SQL command is DELETE FROM.

Example: Deleting a Student Record

PHP

<?php
    $sql = "DELETE FROM Students WHERE id=10"; 

    if ($conn->query($sql) === TRUE) {
        echo "Record deleted successfully.";
    } else {
        echo "Error deleting record: " . $conn->error;
    }
    
    // Always close the connection when finished
    $conn->close();
?>

Next Steps (Security)

You now know how to perform all four essential database actions. However, directly embedding user variables into SQL queries is a massive security risk. The next essential chapter must cover Prepared Statements to protect your application from SQL Injection.