PHP and MySQL: Database Connection

Almost every modern web application—from a simple blog to a complex e-commerce site—relies on a database to store its content, user information, and settings. MySQL is the most popular open-source database used with PHP.

To make PHP talk to MySQL, we use specific PHP extensions. The most modern and recommended method is using PDO or MySQLi.

1. Understanding the Database Stack

You must have three components running (which you set up in the “Environment Setup” chapter):

1. PHP Interpreter: Executes your scripts.
2. Web Server (Apache/Nginx): Handles HTTP requests.
3. MySQL Server: Stores the data.

2. The MySQLi Extension (Recommended for Beginners)

The MySQLi extension (MySQL Improved) is a PHP library specifically designed to work with MySQL databases. It offers both a procedural and an object-oriented way of connecting. We will focus on the Object-Oriented (OO) approach as it is cleaner and more robust.

Step 1: Connecting to the Database

You use the new mysqli() constructor to establish the connection. This requires four essential parameters:

Example: The Connection Script

PHP

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "tutorial_users"; // Create this database in phpMyAdmin first!

    // Create connection using the Object-Oriented approach
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection for errors
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 
    echo "Connected successfully to " . $dbname;
    
    // Close the connection (best practice)
    $conn->close();
?>

3. Executing a Query (Creating a Table)

Once the connection is established, you can execute SQL queries using the $conn->query() method. SQL (Structured Query Language) is the language used to talk to the database.

Example: Creating a Users Table

PHP

<?php
    // ... (Connection code from above goes here) ...

    // Define the SQL statement to create a table
    $sql = "CREATE TABLE Students (
        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        firstname VARCHAR(30) NOT NULL,
        email VARCHAR(50),
        reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )";

    // Execute the query
    if ($conn->query($sql) === TRUE) {
        echo "<br>Table Students created successfully";
    } else {
        echo "<br>Error creating table: " . $conn->error;
    }
    
    $conn->close();
?>

4. Handling Connection Errors

Using the object-oriented approach is safer because you can access the $conn->connect_error property. The die() function is essential here, as it immediately stops the script if the database connection fails, preventing sensitive error information from being displayed to the public.

PHP

<?php
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
?>

5. What is phpMyAdmin?

When working locally (using XAMPP or MAMP), you will manage your databases using phpMyAdmin.

• It is a web-based interface that runs on your server.
• You access it by navigating to http://localhost/phpmyadmin in your browser.
• It allows you to visually create databases (like tutorial_users), browse tables, and manually run SQL queries without writing PHP code first.

Next Steps

Establishing the connection is the foundation. The next chapter will focus on the four core database operations, often called CRUD, which stands for Create, Read, Update, and Delete data.