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

<?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 = "person1@example.com";
$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 ";
} 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"]. "
";
}
} 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 = 'person1@example.com';

// 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 = 'person3@example.com';

// 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](https://www.php.net/docs.php)
- [W3Schools PHP Tutorial] (https://www.w3schools.com/php/)


## 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.