Connecting to Databases (MySQL as Reference) in PHP

Connecting PHP to a MySQL database is a crucial step in creating dynamic websites. By enabling interaction with stored data, developers can build login systems, blogs, shopping carts, and more. This guide covers how to connect to a MySQL database using the MySQLi extension in PHP.

Prerequisites

Before proceeding, make sure you have the following:

• A local or live server with PHP installed (like XAMPP, WAMP, or Apache/Nginx)

• A running MySQL database server

• Basic understanding of PHP and SQL

Methods to Connect PHP with MySQL

PHP provides two main methods to connect with MySQL:

• MySQLi (MySQL Improved)

• PDO (PHP Data Objects)

This topic focuses on the MySQLi extension, which supports both procedural and object-oriented styles.

Establishing a Connection Using MySQLi

Procedural Method

Example:

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "testdb";

$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

Object-Oriented Method

Example:

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "testdb";

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

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

Handling Errors

It is essential to handle errors in a clean way to avoid exposing sensitive details to users. Use:

• mysqli_connect_error() in procedural style

• $conn->connect_error in object-oriented style

These functions help you safely catch and display errors during database connection.

Executing SQL Queries

Once the connection is established, you can send SQL queries to the database. Below are two common operations:

Running SELECT Queries

Example:

$sql = "SELECT id, name FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}

Running INSERT Queries

Example:

$sql = "INSERT INTO users (name) VALUES ('John Doe')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

Closing the Connection

Always close the connection after completing operations.

Example:

mysqli_close($conn);

This frees up resources and avoids memory leaks.

Best Practices for Database Connection

• Never hardcode credentials in production code

• Always check for errors after each query

• Use prepared statements to avoid SQL injection

• Store database credentials securely (e.g., in environment files)

Common Real-World Example

Imagine a student portal where users log in using their credentials. The login system would first connect to a MySQL database using PHP and validate the credentials stored in the students table. This is how most education and e-commerce portals function behind the scenes.