CRUD Operations with PHP and MySQL

Hey there! Today, we're diving into the world of PHP and MySQL to build a simple CRUD application. CRUD stands for Create, Read, Update, and Delete—these are the four basic operations you can do on data. We'll walk through each step, showing you the code and what the results look like. Let's get started!

Setting Up Your Environment

Before we jump into the code, you need to set up your environment. Make sure you have PHP, MySQL, and a web server like Apache installed. You can use XAMPP or MAMP to get everything up and running quickly. Once you've got that, create a database for our project.

CREATE DATABASE crud_example;
USE crud_example;

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

Code Example: Connecting to the Database

To interact with MySQL, we need to connect to the database from our PHP script. Here's a simple way to do that:

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

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

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

If everything is set up correctly, you should see "Connected successfully" when you run this script.

Creating Data (Create)

Let's start by adding some data to our database. This is the 'Create' part of CRUD.

Code Example: Inserting Data

Here's a PHP script to insert a new user into the database:

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

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

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

Executed Code Result

When you run this script, you should see:

New record created successfully

Check your database, and you should see the new user added to the 'users' table.

Reading Data (Read)

Now, let's read the data we've just inserted. This is the 'Read' part of CRUD.

Code Example: Retrieving Data

Here's how you can retrieve and display the data from the database:

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

Executed Code Result

Running this script will give you something like:

id: 1 - Name: John Doe - Email: john@example.com

This shows all the users currently in the database.

Updating Data (Update)

Next up is updating existing data. This is the 'Update' part of CRUD.

Code Example: Updating Data

Let's say we want to update John's email address. Here's how you can do it:

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

$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;
}
?>

Executed Code Result

After running this script, you'll see:

Record updated successfully

Check the database, and you'll see that John's email has been updated.

Deleting Data (Delete)

Finally, let's delete some data. This is the 'Delete' part of CRUD.

Code Example: Deleting Data

Here's a script to delete a user from the database:

<?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;
}
?>

Executed Code Result

When you run this script, you'll see:

Record deleted successfully

Check the database, and you'll see that the user has been removed.

Wrapping Up

And there you have it! We've covered how to create, read, update, and delete data using PHP and MySQL. These are the fundamental operations you'll use in almost any web application that deals with a database. Play around with the code and try extending it to handle more complex operations. Happy coding!