Interacting with the Database

Introduction to Database Interaction

Interacting with a database is a crucial part of web development, allowing applications to store, retrieve, and manipulate data. In this tutorial, we'll cover how to connect to a MySQL database using PHP and perform basic CRUD operations.

Connecting to the Database

To interact with a MySQL database in PHP, you can use the mysqli or PDO (PHP Data Objects) extensions. Below is an example using mysqli:

    <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";
    
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    echo "Connected successfully";
    ?>
                

Expected Output

Creating a Record

To insert data into a table, use the INSERT INTO SQL statement. Here's an example:

    <?php
    $sql = "INSERT INTO Users (username, email) VALUES ('JohnDoe', 'john@example.com')";
    
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
    ?>
                

Expected Output

Reading Records

To retrieve data, use the SELECT SQL statement:

    <?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";
    }
    ?>
                

Expected Output

Updating a Record

To update existing data, use the UPDATE SQL statement:

    <?php
    $sql = "UPDATE Users SET email='john.doe@example.com' WHERE username='JohnDoe'";
    
    if ($conn->query($sql) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }
    ?>
                

Expected Output

Deleting a Record

To delete a record, use the DELETE FROM SQL statement:

    <?php
    $sql = "DELETE FROM Users WHERE username='JohnDoe'";
    
    if ($conn->query($sql) === TRUE) {
        echo "Record deleted successfully";
    } else {
        echo "Error deleting record: " . $conn->error;
    }
    ?>
                

Expected Output

Closing the Database Connection

Always remember to close the database connection after completing your operations:

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

Summary

This tutorial covered the basics of interacting with a MySQL database in PHP. You learned how to connect to the database, perform CRUD operations, and manage your connections. Understanding these concepts is vital for building dynamic web applications.