Mastering CRUD Operations with PHP and MySQL

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

<!-- 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) { // Statement, which: Stops the execution of the script and outputs the message "Connection failed: " followed by the actual connection error. die("Connection failed: " . mysqli_connect_error());
}
//If the connection is successful, "Connected successfully" is displayed.
echo "Connected successfully";
// ?>

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

<?php
// Set a value for each variable. Variables type of values should be same as set in database
$name = "person1";
$email = "[email protected]";
$age = 25; $sql = "INSERT INTO users (name, email, age) VALUES ('$name', '$email', $age)"; if (mysqli_query($conn, $sql)) { echo "New record created successfully </br>";
} else { echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
?>

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.

$newAge = 32;
$sql = "UPDATE users SET age=$newAge WHERE email='[email protected]'"; // Execute the query
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($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.

//SQL query to delete data from database using a specific field (in this case email.)
$sql = "DELETE FROM users WHERE email='[email protected]'"; // Execute the query
if (mysqli_query($conn, $sql)) {
//Verify if any records were deleted using mysqli_affected_rows if (mysqli_affected_rows($conn) > 0) { echo "Record deleted successfully"; } else { echo "No record found with the specified email."; }
} else { echo "Error deleting record: " . mysqli_error($conn);
} mysqli_close($conn);

Further Reading:

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.