CRUD Operations in PHP

This guide walks you through how to perform CRUD operations in PHP using MySQL as the database. If you want to know how to do CRUD in PHP, this post is for you.

Prerequisites

• A working installation of PHP and MySQL.
• Basic understanding of PHP and SQL.

Setting Up the Database

For the sake of simplicity, we'll work with a users table. Use the following SQL command to create it:

CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

Database Connection

Before performing operations, we need to connect to the database. Let's create a config.php file.

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_dbname";

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

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

Remember to replace your_username, your_password, and your_dbname with your database details.

CRUD Operations

Create

To insert a new user into our users table:

<?php
include 'config.php';

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

// Set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john.doe@example.com";
$stmt->execute();

$stmt->close();
$conn->close();
?>

Read

To fetch and display users:

<?php
include 'config.php';

$sql = "SELECT id, firstname, lastname, 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["firstname"]. " " . $row["lastname"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

Update

To update the email of a user:

<?php
include 'config.php';

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

// Set parameters and execute
$email = "new.email@example.com";
$id = 1; // Change to the ID of the user you want to update
$stmt->execute();

$stmt->close();
$conn->close();
?>

Delete

To delete a user:

<?php
include 'config.php';

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

// Set parameters and execute
$id = 1; // Change to the ID of the user you want to delete
$stmt->execute();

$stmt->close();
$conn->close();
?>

Closing Thoughts

CRUD operations form the backbone of many applications. This guide provided a basic overview of how to perform these operations in PHP with MySQL. Always remember to sanitize user inputs and consider implementing more advanced features like pagination, searching, or sorting for larger applications.