How to Connect to a Database Using PHP

Connecting to a database is a fundamental step when building dynamic websites or applications using PHP. This article will walk you through the process of establishing a database connection using PHP with MySQL as an example.

What is PHP Database Connection?

PHP database connection refers to the process of linking your PHP application to a database (e.g., MySQL). Once connected, you can perform operations such as:

Connect PHP with Database
Connecting PHP to a Database
  • Creating and managing tables.
  • Inserting, updating, and deleting data.
  • Fetching records for display or further processing.

Prerequisites

Before connecting to a database, ensure you have:

  1. PHP Installed: PHP must be installed and configured on your server.
  2. MySQL Server: A MySQL database must be running.
  3. Database and Table: A pre-created database with at least one table.
  4. Database Credentials: Required details include:
    Hostname (e.g., localhost), Username (e.g., root), Password, Database name

Methods to Connect PHP to a Database

PHP offers two main methods to connect to a database:

  1. MySQLi (MySQL Improved): Procedural or object-oriented interface for MySQL.
  2. PDO (PHP Data Objects):PDO (PHP Data Objects): A database-independent approach supporting multiple database types.

1.1 Connecting Using MySQLi (Procedural)

The following example demonstrates how to connect to a MySQL database using the MySQLi procedural method:

Example
<?php
// Database credentials
$servername = "localhost";
$username = "root";
$password = "";
$database = "testdb";

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

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

Explanation

  1. Database Credentials: Replace localhost, root, and other values with your database details.
  2. mysqli_connect(): Establishes a connection to the MySQL server.
  3. Error Handling: If the connection fails, an error message is displayed.

1.2 Connecting Using MySQLi (Object-Oriented)

The object-oriented approach offers better readability and is often preferred.

Example
<?php
// Database credentials
$servername = "localhost";
$username = "root";
$password = "";
$database = "testdb";

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

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

Explanation

  • new mysqli(): Creates a new MySQLi object and establishes a connection.
  • Error Handling: Checks if the $conn->connect_error property contains an error message.

2. Connecting Using PDO (PHP Data Objects)

PDO is a versatile method for database connection that supports multiple database types, such as MySQL, PostgreSQL, and SQLite.

Example
<?php
// Database credentials
$dsn = "mysql:host=localhost;dbname=testdb";
$username = "root";
$password = "";

try {
    // Create a PDO instance
    $conn = new PDO($dsn, $username, $password);

    // Set error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Explanation

  1. DSN (Data Source Name): Combines the database type (mysql), host (localhost), and database name (testdb).
  2. PDO Class: Instantiates a new connection.
  3. Error Handling: Uses a try-catch block to manage connection errors gracefully.

Best Practices for Database Connection

  • Secure Your Credentials: Never hardcode credentials directly in the script. Use environment variables or configuration files.
  • Use Prepared Statements: Prevent SQL injection by using prepared statements or parameterized queries.
  • Close Connections: Explicitly close the connection when it's no longer needed. MySQLi Procedural: mysqli_close($conn);, PDO: $conn = null;
  • Enable Error Reporting: Use error handling to debug connection issues during development.

Complete Example with CRUD Operations

Here’s how you can integrate the connection into a simple CRUD operation:

Example
<?php
// Database credentials
$servername = "localhost";
$username = "root";
$password = "";
$database = "testdb";

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

    // Create a table
    $conn->exec("CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50),
        email VARCHAR(50)
    )");

    // Insert data
    $stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
    $stmt->execute(['name' => 'John Doe', 'email' => 'johndoe@example.com']);

    // Fetch data
    $stmt = $conn->query("SELECT * FROM users");
    while ($row = $stmt->fetch()) {
        echo "ID: {$row['id']}, Name: {$row['name']}, Email: {$row['email']}
";
    }

    // Close connection
    $conn = null;
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Conclusion

Establishing a connection to a database in PHP is straightforward with the MySQLi and PDO methods. Each method has its advantages, but PDO is often preferred for its flexibility and secure practices. By mastering these techniques, you can create robust PHP applications capable of interacting with a variety of databases. Happy coding! ?

Next Article ❯
Next ❯ ❮ Previous