Connecting to a database

Files suit small amounts of data. When there are thousands of records and you need fast lookups, data goes into a database: it lives in tables, and queries are written in SQL. PHP talks to databases through PDO — one set of commands for MySQL, PostgreSQL, SQLite and others.

The examples in this lesson use an in-memory SQLite database. The site's browser sandbox has no database support, so they have no "Run" button — run them on your own machine.

Connecting

A PDO object is created from a connection string:

<?php
 
$pdo = new PDO('sqlite::memory:');

The string sqlite::memory: means a temporary database that disappears when the script ends — handy for learning and tests. For MySQL the connection string looks different and comes with a login and password: new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', $user, $password). The rest of the code stays the same.

Creating a table and adding rows

Queries that return nothing are run with the exec() method:

<?php
 
$pdo = new PDO('sqlite::memory:');
 
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');
$pdo->exec("INSERT INTO users (name, age) VALUES ('Anna', 25), ('Oleg', 31)");
 
echo $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn(); // 2

Selecting rows

The query() method runs a SELECT, and fetchAll() returns every matching row. With the PDO::FETCH_ASSOC mode each row is an associative array:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');
$pdo->exec("INSERT INTO users (name, age) VALUES ('Anna', 25), ('Oleg', 31)");
 
$users = $pdo->query('SELECT name, age FROM users ORDER BY age')->fetchAll(PDO::FETCH_ASSOC);
 
foreach ($users as $user) {
    echo "{$user['name']}: {$user['age']}\n";
}

A single row

When you need one record, call fetch() instead of fetchAll(). It returns false when nothing is found:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');
$pdo->exec("INSERT INTO users (name, age) VALUES ('Anna', 25)");
 
$user = $pdo->query('SELECT name FROM users WHERE id = 1')->fetch(PDO::FETCH_ASSOC);
 
echo $user['name']; // Anna

Errors

When a query fails, PDO throws a PDOException:

<?php
 
$pdo = new PDO('sqlite::memory:');
 
try {
    $pdo->query('SELECT * FROM missing_table');
} catch (PDOException $e) {
    echo 'Query failed';
}

The exception text is not shown to visitors: it may contain table names and other details. It goes to a log, while the page shows a generic message.

A common mistake

Every value in this lesson's queries is written directly in the SQL text. That is acceptable only for data you wrote yourself. User data must never be put into a query by joining strings — the next lesson shows how to do it properly.