MySQL CRUD Tutorials in PHP: A Step-by-Step Guide

MySQL is a widely used open-source relational database management system (RDBMS) known for its performance and versatility. In this tutorial, we’ll explore the fundamental steps to perform CRUD (Create, Read, Update, Delete) operations using MySQL in a PHP application. We’ll cover database connection, data manipulation, and error handling with practical examples and detailed explanations.

Prerequisites

Before we begin, make sure you have the following prerequisites:

1. MySQL: MySQL should be installed and running on your server or local development environment. You can download MySQL from the official MySQL website.

2. PHP: Ensure you have PHP installed. You can download PHP from the official PHP website.

Step 1: Connecting to MySQL

To connect to a MySQL database from a PHP application, you’ll use the MySQLi extension. Create a connection to the MySQL server:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database_name";

// Create a connection
$connection = new mysqli($servername, $username, $password, $database);

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

Replace "your_username", "your_password", and "your_database_name" with your MySQL credentials.

Step 2: Create (Insert) Data

Let’s start with creating (inserting) data into a table. Assume you have a “users” table with columns id, username, and email. Here’s how you can insert a new user:

<?php
// SQL query to insert data into the "users" table
$sql = "INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]')";

if ($connection->query($sql) === TRUE) {
    echo "Data inserted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $connection->error;
}
?>

Step 3: Read (Select) Data

You can retrieve data from the “users” table using SQL queries. Here’s an example of selecting data from the table and displaying it:

<?php
// SQL query to retrieve data from the "users" table
$sql = "SELECT id, username, email FROM users";
$result = $connection->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . ", Username: " . $row["username"] . ", Email: " . $row["email"] . "<br>";
    }
} else {
    echo "No results found";
}
?>

Step 4: Update Data

Updating data in MySQL is straightforward. Here’s an example of updating a user’s email address:

<?php
// SQL query to update a user's email address
$sql = "UPDATE users SET email='[email protected]' WHERE username='john_doe'";

if ($connection->query($sql) === TRUE) {
    echo "Data updated successfully";
} else {
    echo "Error: " . $sql . "<br>" . $connection->error;
}
?>

Step 5: Delete Data

You can delete data from the “users” table using SQL queries. Here’s an example of deleting a user:

<?php
// SQL query to delete a user
$sql = "DELETE FROM users WHERE username='john_doe'";

if ($connection->query($sql) === TRUE) {
    echo "User deleted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $connection->error;
}
?>

Step 6: Error Handling

Handle errors gracefully using try-catch blocks:

<?php
try {
    // Your MySQL operations here
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
?>

Conclusion

In this tutorial, we’ve covered the basic CRUD operations using MySQL in a PHP application. You’ve learned how to connect to a MySQL database, perform create, read, update, and delete operations on data in a table. MySQL’s widespread usage and robustness make it a top choice for various web applications. As you continue your journey in web development, you can explore more complex MySQL operations and best practices for secure and efficient data management.