PHP MySQL Database Connection

Most real-world web applications store data in a database. PHP connects to MySQL (and its drop-in replacement MariaDB) to create, read, update, and delete records persistently. PHP provides two ways to interact with MySQL: the MySQLi extension and PDO (PHP Data Objects). PDO is the modern, recommended approach because it supports multiple database systems and provides better security features.

Connecting with PDO

PDO (PHP Data Objects) creates a connection to the database through a Data Source Name (DSN), a formatted string that specifies the database driver, host, database name, and character set.

<?php
  $host    = "localhost";
  $dbname  = "myapp";
  $user    = "root";
  $pass    = "yourpassword";
  $charset = "utf8mb4";

  $dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";

  $options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,   // Throw exceptions on error
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,         // Return arrays with column names
    PDO::ATTR_EMULATE_PREPARES   => false,                     // Use real prepared statements
  ];

  try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected successfully!";
  } catch (PDOException $e) {
    // Never display the raw error to users in production
    error_log($e->getMessage());
    die("Database connection failed. Please try again later.");
  }
?>

PDO Options Explained

• ERRMODE_EXCEPTION — PDO throws a PDOException when a query fails, which forces errors to be handled properly.
• FETCH_ASSOC — query results are returned as associative arrays using column names as keys.
• EMULATE_PREPARES false — uses real prepared statements on the database server, providing genuine protection against SQL injection.

Storing the Connection in a Separate File

Best practice is to keep the database connection in a dedicated file, which other scripts include.

<?php
  // config/database.php
  $host    = "localhost";
  $dbname  = "myapp";
  $user    = "root";
  $pass    = "yourpassword";

  $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

  $options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
  ];

  try {
    $pdo = new PDO($dsn, $user, $pass, $options);
  } catch (PDOException $e) {
    error_log($e->getMessage());
    die("Connection failed.");
  }
?>

<?php
  // any other page
  require_once __DIR__ . "/config/database.php";

  // $pdo is now available to use
?>

Running a Simple Query

<?php
  require_once "config/database.php";

  // query() is used for simple queries with no user-supplied data
  $result = $pdo->query("SELECT * FROM users");
  $users = $result->fetchAll();

  foreach ($users as $user) {
    echo $user['username'] . " - " . $user['email'] . "<br>";
  }
?>

Prepared Statements — The Safe Way to Use Dynamic Data

When user-supplied data (such as form input) is included in a query, it must always use prepared statements. A prepared statement separates the SQL structure from the data, making SQL injection attacks impossible.

<?php
  require_once "config/database.php";

  $searchEmail = $_GET['email'] ?? "";

  // Prepare the statement with a placeholder
  $stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");

  // Execute with the actual value — PDO handles escaping
  $stmt->execute([$searchEmail]);

  $user = $stmt->fetch();

  if ($user) {
    echo "Found: " . $user['username'];
  } else {
    echo "No user found.";
  }
?>

Named Placeholders

Named placeholders make complex queries more readable than positional ? placeholders.

<?php
  $stmt = $pdo->prepare("SELECT * FROM products WHERE category = :category AND price < :maxPrice");

  $stmt->execute([
    ':category' => 'electronics',
    ':maxPrice' => 500
  ]);

  $products = $stmt->fetchAll();

  foreach ($products as $product) {
    echo $product['name'] . " - $" . $product['price'] . "<br>";
  }
?>

Creating a Database and Table

<?php
  require_once "config/database.php";

  // Create a table (typically done in migrations, not in application code)
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(50) NOT NULL UNIQUE,
      email VARCHAR(100) NOT NULL UNIQUE,
      password VARCHAR(255) NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  ");

  echo "Table created.";
?>

Connecting with MySQLi (Alternative)

MySQLi is another option that works specifically with MySQL. It supports both procedural and object-oriented styles. PDO is generally preferred for new projects due to its database-agnostic nature.

<?php
  // Object-oriented style
  $mysqli = new mysqli("localhost", "root", "password", "myapp");

  if ($mysqli->connect_errno) {
    die("Connection failed: " . $mysqli->connect_error);
  }

  echo "Connected with MySQLi.";

  $mysqli->close();
?>

Key Points

• PDO is the recommended way to connect to MySQL in PHP — it supports multiple databases and provides better security.
• Set ERRMODE_EXCEPTION so database errors throw exceptions that can be caught and handled properly.
• Always store database credentials in a separate config file, not scattered through your application code.
• Never interpolate user input directly into SQL strings — always use prepared statements with placeholders (? or :name).
• Prepared statements prevent SQL injection by separating the SQL structure from user-supplied data.
• Use query() only for static queries with no user data; use prepare() and execute() for everything else.