The Project | About Us | Contribute | Donations | License

HOME

CRUD stands for Create, Read, Update, and Delete. These are the four basic operations for managing data in a database. In this section, we will learn how to perform these operations using PHP and MySQL.

Introduction to CRUD Operations

CRUD operations are essential for any application that interacts with a database. Here’s a brief overview of each operation:

Create: Insert new records into the database.
Read: Retrieve data from the database.
Update: Modify existing records in the database.
Delete: Remove records from the database.

Setting Up the Database

Before we start with CRUD operations, let's set up a sample database and table.

Creating a Database and Table

CREATE DATABASE sample_db;

USE sample_db;

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

Connecting to the Database

First, we need to establish a connection to the MySQL database using PHP.

<?php
$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);
}
echo "Connected successfully";
?>

Create Operation

To insert a new record into the users table, we use the INSERT INTO SQL statement.

Example: Inserting a New User

<?php
$name = "John Doe";
$email = "[email protected]";
$age = 30;

$sql = "INSERT INTO users (name, email, age) VALUES ('$name', '$email', $age)";

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

Read Operation

To retrieve data from the users table, we use the SELECT SQL statement.

Example: Retrieving All Users

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

Update Operation

To modify existing records in the users table, we use the UPDATE SQL statement.

Example: Updating a User's Information

<?php
$id = 1;
$newEmail = "[email protected]";

$sql = "UPDATE users SET email='$newEmail' WHERE id=$id";

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

Delete Operation

To remove records from the users table, we use the DELETE SQL statement.

Example: Deleting a User

<?php
$id = 1;

$sql = "DELETE FROM users WHERE id=$id";

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

Practical Exercise

Task

Create a new user with the name "Jane Doe", email "[email protected]", and age 25.
Retrieve and display all users.
Update Jane Doe's email to "[email protected]".
Delete the user with the name "Jane Doe".

Solution

<?php
// 1. Create a new user
$name = "Jane Doe";
$email = "[email protected]";
$age = 25;

$sql = "INSERT INTO users (name, email, age) VALUES ('$name', '$email', $age)";
$conn->query($sql);

// 2. Retrieve and display all users
$sql = "SELECT id, name, email, age FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. " - Age: " . $row["age"]. "<br>";
    }
} else {
    echo "0 results";
}

// 3. Update Jane Doe's email
$newEmail = "[email protected]";
$sql = "UPDATE users SET email='$newEmail' WHERE name='Jane Doe'";
$conn->query($sql);

// 4. Delete the user with the name "Jane Doe"
$sql = "DELETE FROM users WHERE name='Jane Doe'";
$conn->query($sql);
?>

Conclusion

In this section, we covered the basics of performing CRUD operations using PHP and MySQL. We learned how to:

Insert new records into a database.
Retrieve data from a database.
Update existing records.
Delete records from a database.

These operations form the foundation of any database-driven application. In the next module, we will explore more advanced database interactions using PDO (PHP Data Objects).

Performing CRUD Operations

Introduction to CRUD Operations

Setting Up the Database

Creating a Database and Table

Connecting to the Database

Create Operation

Example: Inserting a New User

Read Operation

Example: Retrieving All Users

Update Operation

Example: Updating a User's Information

Delete Operation

Example: Deleting a User

Practical Exercise

Task

Solution

Conclusion

PHP Programming Course

Module 1: Introduction to PHP

Module 2: Control Structures

Module 3: Functions

Module 4: Arrays

Module 5: Working with Forms

Module 6: Working with Files

Module 7: Object-Oriented Programming (OOP)

Module 8: Working with Databases

Module 9: Advanced PHP Techniques

Module 10: PHP Frameworks and Best Practices

Module 11: Project: Building a Web Application