PHP Connect to MySQL Database

Welcome to The Coding College! Connecting PHP to a MySQL database is one of the first steps in building dynamic and data-driven web applications. In this guide, we will explore the methods to connect PHP to MySQL using MySQLi (MySQL Improved) and PDO (PHP Data Objects).

This tutorial will focus on secure coding practices to protect your application from vulnerabilities like SQL Injection while ensuring optimal performance.

Why Connect PHP to MySQL?

Connecting PHP to a MySQL database allows you to:

  1. Store user data, such as login credentials, profiles, or preferences.
  2. Create dynamic websites where content is fetched and displayed from a database.
  3. Manage and manipulate structured data efficiently using SQL commands.

Prerequisites

Before you proceed, ensure you have:

  1. A web server like Apache or Nginx with PHP installed (e.g., XAMPP, WAMP, or LAMP).
  2. MySQL installed and running.
  3. Basic knowledge of PHP and SQL.

Methods to Connect PHP to MySQL

PHP offers two main ways to connect to a MySQL database:

  1. MySQLi (MySQL Improved)
  2. PDO (PHP Data Objects)

1. Connecting with MySQLi

The MySQLi extension provides both procedural and object-oriented interfaces for interacting with MySQL databases.

Procedural Approach

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

Object-Oriented Approach

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

2. Connecting with PDO

PDO is a database access abstraction layer that supports multiple database systems, making it more versatile than MySQLi.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

try {
    // Create a PDO instance
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Which Method Should You Use?

  • Use MySQLi if you’re working specifically with MySQL and don’t need to support other database systems.
  • Use PDO if your application might switch to a different database in the future (e.g., PostgreSQL or SQLite). PDO also provides better support for prepared statements.

Securing Your MySQL Connection

Security is critical when dealing with databases. Here are some best practices:

  1. Use Prepared Statements
    Prevent SQL Injection by using prepared statements for queries that involve user inputs.
  2. Validate User Inputs
    Sanitize and validate all user data before using it in queries.
  3. Limit Database User Privileges
    Create a dedicated MySQL user account with limited permissions (e.g., only SELECT, INSERT, UPDATE, and DELETE).
  4. Avoid Hardcoding Credentials
    Use environment variables or configuration files to store sensitive information.
  5. Close Database Connections
    Free up resources by closing the connection after the script finishes execution.

Closing the Database Connection

In PHP, the database connection automatically closes at the end of the script. However, you can explicitly close it using the following methods:

MySQLi

$conn->close(); // Object-oriented
mysqli_close($conn); // Procedural

PDO

$conn = null; // Closing a PDO connection

Example: Connecting and Querying MySQL Database

Here’s a complete example that connects to a MySQL database and retrieves data from a table called Users.

Using MySQLi (Object-Oriented)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

// Query to fetch data
$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 "No records found!";
}

$conn->close();
?>

Using PDO

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Query to fetch data
    $stmt = $conn->prepare("SELECT id, username, email FROM Users");
    $stmt->execute();

    // Fetch results
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($result as $row) {
        echo "ID: " . $row["id"] . " - Name: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$conn = null;
?>

Common Connection Errors

Here are some common errors and how to handle them:

  1. Incorrect Credentials
    • Double-check the $servername, $username, $password, and $database values.
  2. MySQL Server Not Running
    • Ensure the MySQL server is running on your machine.
  3. Unsupported PHP Extensions
    • Make sure the MySQLi or PDO extensions are enabled in your PHP installation.
  4. Firewall Issues
    • If connecting to a remote database, ensure the firewall allows access to the MySQL port (default: 3306).

Conclusion

Connecting PHP to a MySQL database is a foundational skill for any web developer. Whether you use MySQLi or PDO, both methods offer robust ways to interact with your database. For further tutorials on database management and web development.

Leave a Comment