Working with Databases - Autocodewizard Ebooks - PHP Essentials: Building Dynamic Web Applications

Chapter 11: Working with Databases

Learn how to connect to and interact with databases in PHP, using MySQL to store, retrieve, and manage data.

In this chapter, we’ll cover the basics of working with databases in PHP. Using a database allows you to store, retrieve, and manage data efficiently. We’ll use MySQL, one of the most popular relational database management systems, to demonstrate how to connect to a database, execute queries, and handle results.

Connecting to a MySQL Database

PHP provides the mysqli extension for connecting to MySQL databases. Here’s an example of establishing a connection:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "test_db";

$conn = new mysqli($servername, $username, $password, $database);

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

In this example, the mysqli object is used to connect to the MySQL database. If the connection fails, an error message is displayed; otherwise, a success message is shown.

Executing SQL Queries

Once connected, you can execute SQL queries to interact with the database. Here’s an example of creating a table:

<?php
$sql = "CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    email VARCHAR(50) NOT NULL
)";

if ($conn->query($sql) === TRUE) {
    echo "Table created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}
?>

This example creates a users table with three columns: id, username, and email. If the query is successful, a confirmation message is displayed.

Inserting Data into a Database

To add data to a table, use the INSERT INTO SQL statement:

<?php
$sql = "INSERT INTO users (username, email) VALUES ('Alice', '[email protected]')";

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

This code inserts a new record with a username and email into the users table.

Retrieving Data from a Database

To retrieve data, use the SELECT SQL statement and fetch the results:

<?php
$sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);

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

This example retrieves all records from the users table and displays each user's ID, username, and email.

Updating Data in a Database

To update existing records, use the UPDATE SQL statement:

<?php
$sql = "UPDATE users SET email='[email protected]' WHERE username='Alice'";

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

This example updates Alice's email in the users table.

Deleting Data from a Database

To delete records, use the DELETE FROM SQL statement:

<?php
$sql = "DELETE FROM users WHERE username='Alice'";

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

This example deletes the record where the username is "Alice" from the users table.

Closing the Database Connection

After completing database operations, close the connection to free up resources:

<?php
$conn->close();
?>

This command closes the database connection established earlier.

Summary and Next Steps

In this chapter, we covered database operations in PHP, including connecting to a MySQL database, executing queries, and performing CRUD operations. In the next chapter, we’ll explore asynchronous programming techniques in PHP, enabling you to handle background tasks and manage long-running operations efficiently.