MySQL Database

PHP and MySQL have become an inseparable pair for web developers. One handles the logic, while the other stores the data. Together, they power everything from simple blogs to large-scale platforms. Understanding PHP MySQL CRUD operations is important for building dynamic and data-driven applications.

In this tutorial, you will learn how to use PHP MYSQL CRUD to access a database, so let's get started.

What is a Database in PHP?

A database is a place where data is stored, managed, and retrieved when needed. PHP helps us to use databases to create dynamic websites that can handle user data, products, or any other information without hardcoding it into the application. MySQL is one of the most popular databases used with PHP because it is fast, reliable, and easy to integrate.

When a website needs to display information, PHP queries the database to retrieve it. For example, a login form checks the user’s credentials against what is stored in the database.

This back-and-forth process between PHP and the database is what makes applications interactive and functional. With a database, developers can manage large amounts of data, So it enables us to add new records, update existing ones, or retrieve data for display.

So, how does it work with PHP?

PHP sends SQL queries to the database to perform tasks like retrieving, inserting, updating, or deleting data. The database processes the query and returns the requested data or a status response. PHP uses the result to update the application, display content, or handle user inputs dynamically. This interaction keeps the application functional and responsive.Anyway, let's see how we can do that using CRUD in the following section.

Basics of Database Operations (CRUD)

CRUD stands for Create, Read, Update, and Delete. These four operations are the core actions for interacting with databases. They are essential for any application that stores or manipulates data, whether it is a simple site or a complex platform.

Here are its operations:

Understanding CRUD gives you the tools to manage and organize data. In the next sections, you will see how to implement these operations step by step.

Connecting PHP to MySQL

The first step is establishing a connection between PHP and your MySQL database. PHP gives you two solid options, which are MySQLi and PDO.

Here is a basic example using MySQLi:

$dbqservername = "localhost";
$dbqusername = "root";
$dbqpassword = "";
$dbqdbname = "example_db";

// Create connection
$qrconne = new mysqli($dbqservername, $dbqusername, $dbqpassword, $dbqdbname);

// Check connection
if ($qrconne->connect_error) {
    die("Unable to connect to that database : " . $qrconne->connect_error);
}
echo "Connected successfully";

This script connects to a local MySQL database called example_db. If there is an issue, it prints an error message.

Create Data

If you need to insert or add new records you have to use the following code:

$qrsql = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";

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

This example inserts a new user into the users table. The script checks for errors and displays a message.

Read Data

Reading data is just as important. Use an SQL SELECT statement to get what you need.

$qrsql = "SELECT id, username, email FROM users";
$qrresult = $qrconne->query($qrsql );

if ($qrresult->num_rows > 0) {
    while ($drow = $qrresult->fetch_assoc()) {
        echo "ID: " . $drow["id"] . " - Name: " . $drow["username"] . " - Email: " . $drow["email"] . "<br>";
    }
} else {
    echo "0 results";
}

This script retrieves all users and prints their details. Always handle cases where no data is returned.

Update Data

Updating records lets you change what is stored without deleting anything.

$qrqsql = "UPDATE users SET email='new_email@example.com' WHERE username='john_doe'";

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

This example changes the email for a specific user. Updates like these should always have a condition (WHERE) to avoid overwriting everything.

Delete Data

When data is no longer needed, you can delete it with an SQL DELETE statement.

$qrsql = "DELETE FROM users WHERE username='john_doe'";

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

Deleting records should also include a condition. Otherwise, you risk clearing your entire table.

Closing Connections

Always close the connection when you are done with the database. It is a simple step, but it helps free up resources.

$conn->close();

This is especially important for applications with high traffic. Unclosed connections can slow everything down over time.

Wrapping Up

PHP and MySQL handle dynamic data through CRUD operations. They allow you to add, retrieve, update, and delete data. Connecting PHP to MySQL and using secure practices like prepared statements ensures reliable and safe database interactions.