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

MariaDB is a popular open-source relational database management system (RDBMS) often used in web development. In this tutorial, we’ll explore the fundamental steps to perform CRUD (Create, Read, Update, Delete) operations using MariaDB 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. MariaDB: MariaDB should be installed and running on your server or local development environment. You can download MariaDB from the official MariaDB website.

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

Step 1: Connecting to MariaDB

To connect to a MariaDB database from a PHP application, you’ll use the MySQLi extension. Create a connection to the MariaDB 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 MariaDB 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 MariaDB 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 and Closing the Connection

Always handle errors gracefully and close the MariaDB connection when done:

<?php
// Closing the MariaDB connection
$connection->close();
?>

Conclusion

In this tutorial, we’ve covered the basic CRUD operations using MariaDB in a PHP application. You’ve learned how to connect to a MariaDB database, perform create, read, update, and delete operations on data in a table. MariaDB is a versatile RDBMS, and these fundamental skills are essential for building database-driven PHP web applications. As you continue your journey in web development, you’ll explore more complex database operations and best practices for secure and efficient data management.