Connecting PHP to MySQL (PDO)

Connect with PDO

PDO (PHP Data Objects) is the modern, secure way to talk to a database. Before you can read or write any data, your script has to open a connection to MySQL. The flow is always the same:

1. Gather the connection details: the host, the database name, the username and password.
2. Wrap the attempt in a try block, because connecting can fail.
3. Create a new PDO(...) object with those details — this opens the connection.
4. Turn on error reporting so problems throw clear exceptions instead of failing silently.
5. If anything goes wrong, the catch block shows the error message.

<?php
  $host = "localhost";
  $db   = "codingclave";
  $user = "root";
  $pass = "";

  try {
    $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected!";
  } catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
  }
?>

The four variables hold the connection details (root with a blank password is the common XAMPP default). new PDO("mysql:host=$host;dbname=$db", $user, $pass) does the actual connecting — the first piece is the address of the database, then the login. The setAttribute(...) line tells PDO to throw an exception on any error, which is why we can catch failures. If the connection cannot be made, the catch runs instead.

Read data

Once connected, you can ask the database for data with a query. This reads every row from a users table and loops through the results:

<?php
  $stmt = $pdo->query("SELECT * FROM users");
  $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

  foreach ($users as $user) {
    echo $user["name"] . "<br>";
  }
?>

$pdo->query("SELECT * FROM users") sends the SQL to fetch all users and gives back a *statement* object. $stmt->fetchAll(PDO::FETCH_ASSOC) pulls every row into an array, where each row is an associative array (PDO::FETCH_ASSOC means "use the column names as keys"). The foreach then visits each row, and $user["name"] reads the name column.