Database Interaction with PHP and MySQL

February 16, 2024

Today, we’re diving into the world of database interaction with PHP and MySQL. This is an essential skill for any web developer, as it allows you to store and retrieve data from a database using PHP scripts. By the end of this post, you’ll have a solid understanding of how to connect to a MySQL database, perform queries, and execute CRUD operations using PHP.

Connecting to the Database

The first step in working with a MySQL database in PHP is establishing a connection. PHP provides several functions for this purpose, but the most commonly used one is mysqli_connect(). This function takes the database host, username, password, and database name as parameters and returns a connection object.

<?php
// Database configuration
$dbHost = 'localhost';
$dbUsername = 'username';
$dbPassword = 'password';
$dbName = 'database';
// Create a database connection
$conn = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName);
if (!$conn) {
    die('Could not connect to the database');
}
?>

Querying Data

Once the connection is established, you can start querying the database to retrieve data. The mysqli_query() function is used to execute SQL queries. It takes the database connection and the SQL query as parameters and returns a result set, which can be iterated over to fetch the data.

<?php
// Select data from a table$result = mysqli_query($conn, $query);
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        // Process the fetched data
    }
} else {
    echo 'No records found';
}
?>

CRUD Operations

CRUD stands for Create, Read, Update, and Delete, which are the basic operations performed on a database. In PHP, you can use INSERT, UPDATE, and DELETE SQL statements to perform these operations.

<?php
// Insert data into a tableif (mysqli_query($conn, $insertQuery)) {
    echo 'Record inserted successfully';
} else {
    echo 'Error: ' . mysqli_error($conn);
}
// Update data in a tableif (mysqli_query($conn, $updateQuery)) {
    echo 'Record updated successfully';
} else {
    echo 'Error: ' . mysqli_error($conn);
}
// Delete data from a tableif (mysqli_query($conn, $deleteQuery)) {
    echo 'Record deleted successfully';
} else {
    echo 'Error: ' . mysqli_error($conn);
}
?>

With these fundamentals in place, you’re well on your way to mastering database interaction with PHP and MySQL. Practice writing queries and performing CRUD operations to solidify your understanding. Stay tuned for more PHP tutorials and happy coding!