Beginner's Guide to CRUD Operations in PHP

Introduction

PHP is a flexible and widely-used server-side scripting language that powers many of the dynamic and interactive web pages we see today. As a beginner myself, I find the journey of learning PHP both challenging and rewarding. In this post, we will explore more advanced topics in PHP, building upon the basics covered in my previous post.

If you haven't read my first post, PHP Primer: A Beginner's Guide, I highly recommend checking it out. It covers the fundamentals of PHP, including setting up your development environment, understanding basic syntax, and working with variables and data types.

As we dive deeper into PHP, I welcome any feedback, suggestions, or corrections. Your comments not only help me improve but also create a collaborative learning environment for all readers. Let's continue our PHP journey together!

Setting Up a MySQL Database

Before we start coding, we need to set up a MySQL database. If you have XAMPP installed, you're already halfway there!

Configuring MySQL in XAMPP

1. Open XAMPP Control Panel: Launch the XAMPP control panel and start the "Apache" and "MySQL" services.

2. Open XAMPP Control Panel: Launch the XAMPP control panel and start the "Apache" and "MySQL" services.

3. Create a Database:

• Click on the "New" button on the left sidebar.

• Enter a name for your database and click "Create."

There is another alternative option of creating database by writing CREATE DATABASE database_name; command in SQL script and then click Go command.

These steps are shown below with images.

First option of creating a database:

Creating a database using MySQL command on SQL script:

Creating Tables Using phpMyAdmin

1. Select Your Database: Click on the database you just created.

2. Create a Table:

• Enter a name for your table (e.g., users).

• Specify the number of columns and click "Go."

• Define the columns (e.g., id, name, email, age).

Or by using MySQL commands in SQL script

CREATE TABLE users (
    id INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL, 
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE,
    age INT(3) NOT NULL
)

and then click Go.

Connecting PHP to MySQL

Using 'mysqli' to Connect to MySQL

Updated code below

<!-- Opening PHP tag to write PHP code -->
<?php

// Specifies the hostname of the MySQL server.
$servername = "localhost";

// The MySQL username. "root" is the default administrative username for MySQL.
$username = "root";

// The MySQL password for the specified user. It is empty ("") by default for the root user in many local development environments.
$password = "";

// The name of the database you want to connect to.
$dbname = "php_project";

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

// Check connection
if (!$conn) {
    // Log the error and display a generic message to the user
    error_log("Connection failed: " . mysqli_connect_error());
    die("Connection failed. Please try again later.");
}

// If the connection is successful, display or log a success message
echo "Connected successfully";

// Close the connection (optional, as it will close when the script ends)
mysqli_close($conn);

?>

Performing CRUD Operations

Performing CRUD operations in the context of web development refers to the basic operations that can be performed on data stored in a database: Create, Read, Update, and Delete. These operations are fundamental to building dynamic and interactive web applications where users can interact with data. CRUD operations are the backbone of database interactions in web applications. PHP allows you to perform these operations easily by defining variables that contain SQL code and executing them using PHP's database interaction libraries like MySQLi

Create: Inserting Data

Updated code ↓

<?php
// Set a value for each variable. Variables type of values should be same as set in database
$name = "person1";
$email = "[e-mail]";
$age = 25;

// Prepare the SQL statement
$stmt = mysqli_prepare($conn, "INSERT INTO users (name, email, age) VALUES ($name, $email, $age)");

// Bind parameters to the prepared statement
mysqli_stmt_bind_param($stmt, "ssi", $name, $email, $age);

// Execute the prepared statement
if (mysqli_stmt_execute($stmt)) {
    echo "New record created successfully </br>";
} else {
    // Log the error for debugging purposes
    error_log("Error: " . mysqli_stmt_error($stmt));

    // Display a generic error message to the user
    echo "An error occurred while creating the record. Please try again later.";
}

// Close the prepared statement
mysqli_stmt_close($stmt);

Read: Fetching Data

The Read operation is used to fetch data from a database. This is typically done using the SELECT statement in SQL. Here's a step-by-step code and explanation of how to perform a read operation in PHP:

// Create an SQL query
$sql = "SELECT id, name, email, age FROM users";
$result = mysqli_query($conn, $sql);

// Check if there are any results
if (mysqli_num_rows($result) > 0) {
    // Fetch and output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. " - Age: " . $row["age"]. "<br>";
    }
} else {
    echo "0 results";
}

Update: Modifying Data

Have you ever needed to modify existing data in a database? How did you approach it?
The update operation in PHP is used to modify existing records in a MySQL database. This is essential for maintaining accurate and current data within your application. For instance, if a user's information changes, such as their email address or age, you would use the update operation to reflect these changes in your database.

Updated code

<?php
// Assuming you already have a connection established in $conn

$newAge = 32;
$email = '[e-mail]';

// Prepare an SQL statement
$stmt = mysqli_prepare($conn, "UPDATE users SET age=$newAge WHERE email=$email");

if ($stmt) {
    // Bind parameters to the prepared statement
    mysqli_stmt_bind_param($stmt, "is", $newAge, $email);

    // Execute the prepared statement
    if (mysqli_stmt_execute($stmt)) {
        echo "Record updated successfully";
    } else {
        // Log the error internally, do not display it to the user
        error_log("Error executing statement: " . mysqli_stmt_error($stmt));
        echo "An error occurred while updating the record. Please try again later.";
    }

    // Close the statement
    mysqli_stmt_close($stmt);
} else {
    // Log the error internally, do not display it to the user
    error_log("Error preparing statement: " . mysqli_error($conn));
    echo "An error occurred. Please try again later.";
}

// Close the connection
mysqli_close($conn);
?>

Based on the code written above, if the process of Update goes right we'll get the message "Record updated successfully", in this case the age value of the user with the specified email will change to 32 and we can see the results in our database.

Delete: Removing Data

The delete operation in PHP is used to remove records from a database table. This operation is performed using the SQL DELETE statement, which specifies the conditions under which records should be deleted. The syntax of the DELETE statement allows you to specify one or more conditions to ensure that only the intended records are removed from the database.

Updated code

<?php

$email = '[e-mail]';

// Prepare an SQL statement
$stmt = mysqli_prepare($conn, "DELETE FROM users WHERE email=$email");

if ($stmt) {
    // Bind parameter to the prepared statement
    mysqli_stmt_bind_param($stmt, "s", $email);

    // Execute the prepared statement
    if (mysqli_stmt_execute($stmt)) {
        // Verify if any records were deleted using mysqli_stmt_affected_rows
        if (mysqli_stmt_affected_rows($stmt) > 0) {
            echo "Record deleted successfully";
        } else {
            echo "No record found with the specified email.";
        }
    } else {
        // Log the error internally, do not display it to the user
        error_log("Error executing statement: " . mysqli_stmt_error($stmt));
        echo "An error occurred while deleting the record. Please try again later.";
    }

    // Close the statement
    mysqli_stmt_close($stmt);
} else {
    // Log the error internally, do not display it to the user
    error_log("Error preparing statement: " . mysqli_error($conn));
    echo "An error occurred. Please try again later.";
}

// Close the connection
mysqli_close($conn);
?>

Further Reading:

• Official PHP Documentation
• W3Schools PHP Tutorial

Conclusion

CRUD operations are the backbone of database interactions in web applications. By mastering these operations, you can build dynamic and interactive applications. I'd love to hear about your experiences with CRUD operations! Share your thoughts in the comments below and let's keep the discussion going.

I want to express my sincere gratitude to each and every one of you who took the time to read this post and share your insights. Your engagement and feedback are incredibly valuable as we continue to learn and grow together.

Don't forget to check out my previous post for more foundational concepts, and feel free to leave your feedback or comments below. Thank you for joining me on this exploration of CRUD operations in PHP.

Comments

[benanamen]

I cannot believe in 2024 people are still writing insecure "tutorials".

1. You never ever use variables in your query's. You need to use Prepared Statements. The posted code is vulnerable to an SQL Injection Attack. Your entire DB can easily be deleted.

2. Never output internal system errors to the user with mysqli_error($conn). You can leak sensitive information that is only good to hackers and useless to the user.

This is far from "Mastering" anything.

DO NOT USE THIS INSECURE CODE!!!

[RKadriu]

Thank you for your feedback. I completely agree that security is a critical aspect of web development, and it looks like I have room to improve in this area.

Could you please explain in more detail how I can better implement prepared statements and proper error handling in my examples? I want to ensure that my tutorials are secure and up-to-date with best practices. Your insights would be incredibly valuable in helping me achieve this.

Thank you again for pointing this out, and I look forward to your detailed suggestions.

[benanamen]

Happy to help. Here is one of your code blocks revised. I will leave it to you to revise the rest of the tutorial code.

<?php
// Set a value for each variable. Variables type of values should be same as set in database
$name = "person1";
$email = "[e-mail]";
$age = 25;

// Prepare the SQL statement
$stmt = mysqli_prepare($conn, "INSERT INTO users (name, email, age) VALUES (?, ?, ?)");

// Bind parameters to the prepared statement
mysqli_stmt_bind_param($stmt, "ssi", $name, $email, $age);

// Execute the prepared statement
if (mysqli_stmt_execute($stmt)) {
    echo "New record created successfully </br>";
} else {
    // Log the error for debugging purposes
    error_log("Error: " . mysqli_stmt_error($stmt));

    // Display a generic error message to the user
    echo "An error occurred while creating the record. Please try again later.";
}

// Close the prepared statement
mysqli_stmt_close($stmt);

[Weakspy]

I think he proved a good point. Thanks for the good article.

[Paul Phillips]

I had to check the date on this post too.
Looks like something is expect to see from 2010!
This is what have PHP a bad name 😟

[Jake T. (Vampeyer )]

Thank you very much sir - its professionals like you who keep me here on this platform.

[Stefan]

I'm sure you have the best intentions with this article - and as you wrote, you have yourself just startet on your php journey.

I'm afraid you have yourself fallen victim to some very outdated tutorials that ignore all the current best practices including those related to security.

I recommend you to work your way through the 11h video tutorial by Laracasts, which is available for free in YouTube. It's called PHP For Beginners - Complete Laracasts Course.

Apart from that, there's a YT channel called Programming with Gio with a load of excellent and up to date tutorials.

These are the two I would recommend for anyone starting to learn PHP in 2024.

All the best and have fun on your journey 🥳

[RKadriu]

Thank you so much for your feedback and for sharing these valuable resources! I truly appreciate your advice and the time you took to help me improve. It’s important to stay updated with best practices that I have more to learn in this area.

I'll definitely check out the Laracasts tutorial and the Programming with Gio channel. They sound like excellent resources that can help me enhance my understanding and skills in PHP.

Thank you again for your encouragement and support. I'm excited to continue my learning journey with the insights you've provided. All the best to you as well!

[Gentt]

Te pergzoj per postin Rinesa :)) Pretty cool info rreth CRUD/PHP 👏

[RKadriu]

Thank you a lot for the feedback, I really appreciate it 🙂