🎓 A1Learner
🔍
0 of 38 lessons completed

PDO Basics

PDO is a PHP tool that connects a program to a database. A database is an organised store of data in tables, and each table holds rows of values.

Connect, create, insert and read

The new PDO line opens a connection to a memory database. The exec method creates the books table and adds two rows, the query method reads the rows back, and the fetch method reads one row at a time as an array keyed by column name.

<?php
$db = new PDO("sqlite::memory:");
$db->exec("CREATE TABLE books (title TEXT, price REAL)");
$db->exec("INSERT INTO books (title, price) VALUES ('Dune', 12)");
$db->exec("INSERT INTO books (title, price) VALUES ('Emma', 7)");
$result = $db->query("SELECT title, price FROM books");
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
  echo $row["title"] . " - " . $row["price"] . "\n";
}

Reading all rows at once

The fetchAll method reads all the rows of a result into one array at once. The FETCH_ASSOC setting makes each row an array keyed by column names, and print_r shows the whole nested array.

<?php
$db = new PDO("sqlite::memory:");
$db->exec("CREATE TABLE books (title TEXT, price REAL)");
$db->exec("INSERT INTO books (title, price) VALUES ('Dune', 12)");
$db->exec("INSERT INTO books (title, price) VALUES ('Emma', 7)");
$rows = $db->query("SELECT title, price FROM books")->fetchAll(PDO::FETCH_ASSOC);
print_r($rows);

A WHERE clause narrows the rows

A WHERE clause keeps only the rows that pass its test. The test here keeps books with a price below 10, so two titles print.

<?php
$db = new PDO("sqlite::memory:");
$db->exec("CREATE TABLE books (title TEXT, price REAL)");
$db->exec("INSERT INTO books (title, price) VALUES ('Dune', 12)");
$db->exec("INSERT INTO books (title, price) VALUES ('Emma', 7)");
$db->exec("INSERT INTO books (title, price) VALUES ('Hamlet', 5)");
$result = $db->query("SELECT title FROM books WHERE price < 10");
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
  echo $row["title"] . "\n";
}

Example

<?php
$db = new PDO("sqlite::memory:");
$db->exec("CREATE TABLE books (title TEXT, author TEXT)");
$db->exec("INSERT INTO books (title, author) VALUES ('Dune', 'Frank Herbert')");
$db->exec("INSERT INTO books (title, author) VALUES ('Emma', 'Jane Austen')");
$db->exec("INSERT INTO books (title, author) VALUES ('Foundation', 'Isaac Asimov')");
$rows = $db->query("SELECT title, author FROM books")->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
  echo $row["title"] . " by " . $row["author"] . "\n";
}

Key points

  • PDO gives one set of methods for many kinds of database.
  • A data source name, shortened to DSN, names the kind of database and where it lives.
  • The text "sqlite::memory:" is the DSN for a database that lives only in memory.
  • The exec method runs a statement that changes the database.
  • The query method runs a SELECT statement and hands back its rows.
  • A memory database vanishes when the program ends.

In this example, a memory database is opened and a books table is created in it. Three exec lines add one book each, with a title and an author. The query reads all three rows back at once, and the loop prints one line per book.

Note

A memory database leaves no file behind, so it suits short practice programs.

Try it Yourself

Edit the code, then press Run.

Running is simulated

Exercise

What does the DSN text "sqlite::memory:" create?