Basic Usage of MariaDB in a PHP Application

MariaDB, a popular open-source relational database management system (RDBMS), is often used in web development alongside PHP to store and manage data efficiently. In this article, we’ll explore the fundamental steps to use MariaDB in a PHP application. We’ll cover database connection, data manipulation, and error handling to help you get started.

Prerequisites

Before we begin, ensure you have the following prerequisites:

MariaDB: MariaDB should be installed and running on your server or local development environment. You can download MariaDB from the official MariaDB website.
PHP: Make sure you have PHP installed. You can download PHP from the official PHP website.
Web Server: A web server like Apache or Nginx is required to run PHP scripts. If you don’t have one installed, consider using a prepackaged solution like XAMPP or WAMP.

Step 1: Connecting to MariaDB

To connect to a MariaDB database from a PHP application, you’ll need to use the mysqli extension, which provides functions for interacting with the database.

<?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: Executing SQL Queries

Once connected, you can execute SQL queries to interact with the database. Let’s start with a simple example: inserting data into a table.

<?php
// SQL query to insert data into a "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: Querying Data

You can also retrieve data from the database using SQL queries. Here’s an example of selecting data from a table:

<?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: Updating and Deleting Data

Updating and deleting 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;
}
?>

And 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 5: Error Handling and Closing the Connection

Always handle errors gracefully and close the database connection when done.

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

Conclusion

In this article, we’ve covered the basic usage of MariaDB in a PHP application. You’ve learned how to connect to a MariaDB database, execute SQL queries for data manipulation, retrieve data, update records, and delete records. 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.