Connecting to a Database in PHP

Connecting to a database is a crucial step in building dynamic and data-driven web applications. In this tutorial, we'll cover how to connect PHP to a MySQL database and perform basic operations.

Step 1: Setting Up a Database

Before connecting PHP to a database, ensure you have a MySQL database set up. You can use tools like XAMPP or phpMyAdmin to manage your database. Create a new database and a table for this tutorial:

CREATE DATABASE testdb;
    USE testdb;
    CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50),
        email VARCHAR(100)
    );

Step 2: Connecting to the Database

Use PHP's mysqli or PDO extensions to connect to the database. Here's an example using mysqli:

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "testdb";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

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

Step 3: Inserting Data

Use an SQL INSERT statement to add data to the database:

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

Step 4: Retrieving Data

Use an SQL SELECT statement to fetch data:

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

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

Step 5: Updating Data

Use an SQL UPDATE statement to modify existing records:

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

Step 6: Deleting Data

Use an SQL DELETE statement to remove records:

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

Step 7: Closing the Connection

Always close the connection when you're done:

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

Next Steps

Practice performing different operations with your database. Consider using prepared statements for secure database interactions to prevent SQL injection. Understanding database connections is fundamental for building robust PHP applications.