Connecting PHP to MySQL is essential for building dynamic web applications that store and manage data efficiently. This guide explains how to establish a connection between PHP and MySQL using both MySQLi (MySQL Improved) and PDO (PHP Data Objects).

1. Prerequisites

Before proceeding, ensure that:
✅ You have PHP and MySQL installed on your server or local machine (e.g., using XAMPP, WAMP, or MAMP).
✅ You have created a MySQL database and a user account with appropriate permissions.
✅ You are familiar with basic PHP syntax.

2. Methods to Connect PHP to MySQL

There are two primary ways to connect PHP to MySQL:

1. MySQLi (MySQL Improved) Extension – Works only with MySQL databases.
2. PDO (PHP Data Objects) – Supports multiple database types and is more flexible.

3. Connecting Using MySQLi (Procedural and Object-Oriented)

A. Using MySQLi (Procedural Method)

The procedural approach uses simple function calls.

<?php
$server = "localhost"; // Change to your MySQL server (e.g., 127.0.0.1)
$username = "root";    // MySQL username (default is "root" for local development)
$password = "";        // MySQL password (leave empty if using XAMPP)
$database = "test_db"; // Name of the database

// Create connection
$conn = mysqli_connect($server, $username, $password, $database);

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

👉 Explanation:

• mysqli_connect() establishes a connection.
• If the connection fails, mysqli_connect_error() displays the error.
• If successful, "Connected successfully" is displayed.

B. Using MySQLi (Object-Oriented Method)

This approach is more structured and preferred for larger projects.

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

// Create connection
$conn = new mysqli($server, $username, $password, $database);

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

👉 Key Differences:

• Uses the new mysqli() object.
• Connection errors are checked using $conn->connect_error.

4. Connecting Using PDO (More Secure & Flexible)

PDO provides a more secure and flexible way to connect to MySQL and other databases.

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

try {
    $conn = new PDO("mysql:host=$server;dbname=$database", $username, $password);
    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

👉 Why Use PDO?
✅ Supports multiple databases (MySQL, PostgreSQL, SQLite, etc.)
✅ Uses prepared statements to prevent SQL injection
✅ Handles errors using try-catch blocks

5. Closing the Connection

While PHP automatically closes connections when a script ends, you can manually close it:

For MySQLi (Procedural)

mysqli_close($conn);

For MySQLi (Object-Oriented)

$conn->close();

For PDO

$conn = null;  // Closes the connection

6. Handling Errors Securely

To prevent exposing database errors to users, use proper error handling:

MySQLi

if (!$conn) {
    error_log("Connection failed: " . mysqli_connect_error());
    die("Database connection issue. Please try again later.");
}

PDO

try {
    $conn = new PDO("mysql:host=$server;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); // Suppresses errors from displaying
} catch(PDOException $e) {
    error_log("Connection failed: " . $e->getMessage());
    die("Database connection issue. Please try again later.");
}

7. Which One Should You Use?

Recommendation:

• Use MySQLi if working only with MySQL and prefer a simple approach.
• Use PDO for better security, flexibility, and support for multiple databases.

8. Testing the Connection

To verify that your connection works, save the PHP script as connect.php, run it on your server, and open the file in a browser:

http://localhost/connect.php

If successful, you’ll see "Connected successfully". Otherwise, troubleshoot using the error messages.

Final Thoughts

Connecting PHP to MySQL is crucial for database-driven applications. Choosing between MySQLi and PDO depends on your project’s requirements, but PDO is the best choice for security and flexibility.

Would you like guidance on performing CRUD (Create, Read, Update, Delete) operations after connecting?