Connecting to a MySQL Database

Welcome to the next lesson! In this lesson, you’ll learn how to connect PHP to a MySQL database. This is a crucial step for creating dynamic, data-driven web applications. Let’s get started!

Why Connect to a Database?

Connecting to a database allows your PHP scripts to:

• Store and retrieve user data.
• Display dynamic content based on database queries.
• Save application settings and logs.

Key Point: A database connection bridges your application and the stored data.

Requirements for Connecting to a Database

Before connecting, ensure you have:

• A MySQL database installed and running.
• Database credentials (hostname, username, password, and database name).
• PHP extensions for database connections (PDO or MySQLi).

Tip: Use PDO for database-agnostic connections.

Connecting Using PDO

PDO (PHP Data Objects) is a flexible, object-oriented approach to database connections. Example:

Explanation:

• $dsn: Data Source Name, specifies the database type, host, and name.
• $username and $password: Credentials to access the database.
• setAttribute(): Configures connection options (e.g., error handling).

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

  try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully!";
  } catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
  }
?>

Connecting Using MySQLi

MySQLi is a MySQL-specific extension for database connections. Example:

Tip: Use MySQLi for simpler projects that only require MySQL.

<?php
  $servername = "localhost";
  $username = "root";
  $password = "";
  $dbname = "testdb";

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

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

Handling Connection Errors

Always handle connection errors gracefully to improve user experience. Example:

Key Point: Avoid displaying detailed error messages to users.

<?php
  try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  } catch (PDOException $e) {
    error_log("Database connection error: " . $e->getMessage());
    echo "Sorry, something went wrong. Please try again later.";
  }
?>

Best Practices for Database Connections

Follow these best practices:

• Store database credentials in a secure, configuration file.
• Use environment variables for sensitive information.
• Close the connection when it’s no longer needed.

Example:

<?php
  $pdo = null; // Closes the PDO connection
?>

Checking the Database Connection

After setting up the connection, ensure it works by running a simple query. Example:

Tip: Use this query to confirm the connection is successful.

<?php
  $stmt = $pdo->query("SELECT DATABASE()");
  $result = $stmt->fetchColumn();
  echo "Connected to the database: $result";
?>

Great Job!

Congratulations! You’ve successfully learned how to connect PHP to a MySQL database using PDO and MySQLi. In the next lesson, we’ll explore how to create and populate tables in a database. Let’s keep coding!