CRUD Operations (Create, Read, Update, Delete) in PHP with MySQLi

Table of Contents

• What Are CRUD Operations?
• Setting Up the Database for CRUD Operations
• Creating Data in MySQL (Create)
  • Inserting Data Using MySQLi
  • Prepared Statements for Inserting Data
• Reading Data from MySQL (Read)
  • Selecting Data Using MySQLi
  • Fetching Data: Associative, Numeric, and Both Types
  • Handling No Data Returned
• Updating Data in MySQL (Update)
  • Updating Records with MySQLi
  • Prepared Statements for Updates
• Deleting Data from MySQL (Delete)
  • Deleting Records with MySQLi
  • Prepared Statements for Deleting Data
• Error Handling in CRUD Operations
• Summary

What Are CRUD Operations?

CRUD stands for Create, Read, Update, and Delete—the four basic operations for managing data in a database. These operations are fundamental to database management systems and are crucial for developing dynamic web applications.

1. Create: Insert new data into a database.
2. Read: Retrieve data from a database.
3. Update: Modify existing data in the database.
4. Delete: Remove data from the database.

In this module, we’ll explore how to implement each of these operations in PHP using the MySQLi extension, ensuring efficient and secure interaction with your MySQL database.

Setting Up the Database for CRUD Operations

Before we start with CRUD operations, ensure you have a MySQL database with a table ready for these operations. For this module, we’ll use a table named users with the following structure:

CREATE DATABASE sample_db;
USE sample_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

This table will be used for all CRUD operations. If you haven’t already set up the database, please do so before proceeding.

Creating Data in MySQL (Create)

The Create operation allows us to insert new data into the database. We can use the INSERT INTO SQL statement to achieve this.

Inserting Data Using MySQLi

To insert data into the users table, use the following example:

<?php
// Database credentials
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sample_db";

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

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

// Insert data
$sql = "INSERT INTO users (name, email) VALUES ('Jane Doe', '[email protected]')";

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

// Close connection
$conn->close();
?>

In this code:

• The query() method executes the INSERT INTO SQL query.
• If the query is successful, a success message is displayed.

Prepared Statements for Inserting Data

To enhance security and avoid SQL injection attacks, it’s recommended to use prepared statements for inserting data.

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

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

// Prepare and bind
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);

// Set parameters and execute
$name = "Jane Doe";
$email = "[email protected]";
$stmt->execute();

echo "New record created successfully";

// Close connection
$stmt->close();
$conn->close();
?>

Using prepared statements ensures that user input is safely handled, making it resistant to SQL injection.

Reading Data from MySQL (Read)

The Read operation retrieves data from the database. We use the SELECT SQL query to fetch data from the table.

Selecting Data Using MySQLi

To read data from the users table, use the following query:

<?php
$sql = "SELECT id, name, email FROM users";
$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["name"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

In this code:

• The query() method executes the SELECT query.
• The fetch_assoc() method returns each row as an associative array.

Fetching Data: Associative, Numeric, and Both Types

You can fetch data in different formats:

• Associative: Fetches data as an associative array (column names as keys).
• Numeric: Fetches data as a numeric array (column indexes as keys).
• Both: Fetches data as both an associative and numeric array.

// Fetch associative and numeric
$row = $result->fetch_array(MYSQLI_BOTH);
echo "id: " . $row[0] . " - Name: " . $row["name"] . "<br>";

Handling No Data Returned

When no data is found, it’s important to handle that case. In the example above, we used if ($result->num_rows > 0) to check if any rows were returned. If no rows are found, we print “0 results”.

Updating Data in MySQL (Update)

The Update operation modifies existing data in the database. To update data, we use the UPDATE SQL query.

Updating Records with MySQLi

Here’s an example of how to update a record in the users table:

<?php
$sql = "UPDATE users SET email='[email protected]' WHERE id=1";

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

$conn->close();
?>

This code updates the email address of the user with id=1.

Prepared Statements for Updates

To prevent SQL injection, use prepared statements when updating data:

<?php
// Prepare and bind
$stmt = $conn->prepare("UPDATE users SET email=? WHERE id=?");
$stmt->bind_param("si", $email, $id);

// Set parameters and execute
$email = "[email protected]";
$id = 1;
$stmt->execute();

echo "Record updated successfully";

// Close connection
$stmt->close();
$conn->close();
?>

Prepared statements ensure that the data is handled securely and efficiently.

Deleting Data from MySQL (Delete)

The Delete operation removes data from the database. To delete a record, we use the DELETE SQL query.

Deleting Records with MySQLi

Here’s an example of how to delete a record:

<?php
$sql = "DELETE FROM users WHERE id=1";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

This code deletes the user with id=1 from the users table.

Prepared Statements for Deleting Data

Again, to ensure security, use prepared statements for deletion:

<?php
// Prepare and bind
$stmt = $conn->prepare("DELETE FROM users WHERE id=?");
$stmt->bind_param("i", $id);

// Set parameter and execute
$id = 1;
$stmt->execute();

echo "Record deleted successfully";

// Close connection
$stmt->close();
$conn->close();
?>

Using prepared statements with deletion operations reduces the risk of SQL injection.

Error Handling in CRUD Operations

Error handling is crucial for identifying and troubleshooting issues in your application. In PHP, we use the error property of MySQLi to display errors when executing queries.

For example:

if ($conn->query($sql) === FALSE) {
    echo "Error: " . $conn->error;
}

To enable more detailed error reporting, you can configure MySQLi to throw exceptions on errors:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

This ensures that errors are raised as exceptions, making it easier to catch and handle them.

Summary

In this module, we covered the four basic CRUD operations—Create, Read, Update, and Delete—using MySQLi in PHP. We discussed both procedural and object-oriented approaches to perform these operations securely, with an emphasis on using prepared statements to prevent SQL injection.