Skip to content
Tutorials Center
My learning

Topics

Lesson 87 of 147 ยท Databases

PHP and Databases

How PHP works with databases: relational databases, MySQL, PostgreSQL and SQLite, mysqli vs PDO, why PDO is the standard, DSNs and connecting safely.

Almost every dynamic website is a thin layer of code over a database. The blog posts, user accounts, orders, comments and settings all live there, and PHP's job is to read the right rows, turn them into HTML or JSON, and write changes back. PHP and MySQL grew up together in the early web, and connecting to a database is still one of the first "real" things most PHP developers learn.

This chapter teaches you to do it the modern, safe way. In this first lesson you'll meet the database systems PHP talks to, see which PHP extensions connect to them, learn why PDO is the standard choice, and open your first connections. The lessons that follow cover querying (PDO Basics), prepared statements that stop SQL injection, CRUD and transactions.

You'll write some SQL along the way. This chapter explains every statement it uses, but if you want to learn SQL itself in depth, our SQL tutorial starts from zero.

Why use a database at all?

You could store data in files, as in Reading and Writing Files and JSON. That works for a settings file or a tiny guestbook, but it falls apart quickly:

  • Concurrency. Two visitors saving at the same moment can overwrite each other's changes unless you lock files carefully.
  • Searching. Finding "all orders over 100 euros from last month" means reading and decoding every record.
  • Consistency. Nothing stops a file from containing an order for a customer that doesn't exist, or a price written as "ten".
  • Size. Loading a 2 GB JSON file into memory on every request isn't an option.

A database server solves all of these. It handles many simultaneous readers and writers, finds rows quickly using indexes, enforces rules (types, required fields, unique emails, valid references), and only sends you the rows you asked for.

Relational databases in two minutes

The databases PHP uses most are relational: data lives in tables, each with named columns and any number of rows. Each row usually has a primary key, a unique ID, and tables point at each other through those keys:

TEXT
customers                       orders
+----+--------------+           +-----+-------------+-------+
| id | name         |           | id  | customer_id | total |
+----+--------------+           +-----+-------------+-------+
|  1 | Amara Okafor |           | 101 |           1 | 59.90 |
|  2 | Lukas Brandt |           | 102 |           2 | 12.00 |
+----+--------------+           | 103 |           1 |  8.50 |
                                +-----+-------------+-------+

orders.customer_id refers to customers.id: orders 101 and 103 belong to Amara.

You talk to the database in SQL (Structured Query Language). Your PHP code builds a statement, sends it, and gets rows back:

SQL
SELECT customers.name, SUM(orders.total) AS spent
FROM customers
JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.name;

The database does the hard work (matching rows, adding up totals) and returns a small result. A good rule of thumb: let SQL filter, join, sort and aggregate, and let PHP present the result. The SQL tutorial explains joins, GROUP BY and the rest.

The database systems PHP talks to

Database Type Typical use with PHP PDO driver
MySQL Client-server The classic choice: WordPress, Laravel and most shared hosting pdo_mysql
MariaDB Client-server A community fork of MySQL; nearly a drop-in replacement pdo_mysql
PostgreSQL Client-server Strict, feature-rich; popular for new projects pdo_pgsql
SQLite A file (or memory), no server Tests, small sites, desktop and mobile apps, prototypes pdo_sqlite
SQL Server Client-server Microsoft environments pdo_sqlsrv (separate install)

Client-server databases run as their own program, often on a separate machine. PHP connects over the network (or a local socket), sends SQL and receives results. SQLite is different: it's a library built into PHP that reads and writes a single file directly. There's no server to install, configure or connect to, and a database can even live entirely in memory, which makes it perfect for tests and for learning.

That's why the runnable examples in this chapter use SQLite in memory. Our editor has no MySQL server, and SQLite speaks almost the same SQL. More importantly, PDO gives you the same PHP code for all of them: switching from SQLite to MySQL changes the connection string, not the rest of your program. Where SQL differs between systems, the lessons point it out; SQL Dialects compares them in detail.

mysql_*, mysqli and PDO

PHP has had three generations of database APIs. You'll meet all three in old tutorials and legacy code, so it helps to know which is which:

API Status Databases Prepared statements Style
mysql_* functions Removed in PHP 7.0 (2015) MySQL only No Functions
mysqli Maintained MySQL/MariaDB only Yes Objects or functions
PDO Maintained, the standard 12+ drivers Yes, with named placeholders Objects

If you find code calling mysql_connect() or mysql_query(), it can't run on any supported PHP version, and it was usually written in a way that's open to SQL injection. Don't port it line by line; rewrite the data access with PDO.

mysqli ("MySQL improved") is a fine, fast extension, and you'll see it in WordPress and older codebases. Its weakness is that it works only with MySQL and MariaDB, and its prepared-statement API is clumsier. The Using MySQLi lesson shows it side by side with PDO.

PDO (PHP Data Objects) is what this course uses, and what Laravel, Symfony's Doctrine and most modern libraries build on. It offers:

  • One API for many databases: MySQL, PostgreSQL, SQLite, SQL Server, Oracle and more, each through a driver.
  • Prepared statements with named placeholders (:email), the main defense against SQL injection.
  • Exceptions on errors (the default since PHP 8.0), so failures can't slip by unnoticed.
  • Flexible fetching: rows as associative arrays, objects of your own classes, single columns, key-value pairs.

Let's see which drivers this PHP installation has:

PHP Which PDO drivers are installed?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Our server has drivers for MySQL, PostgreSQL and SQLite. It doesn't have the mysqli extension, which is one reason the MySQLi lesson is read-only. On your own machine, php -m lists the loaded extensions, and on Linux you'd install drivers with packages such as php8.5-mysql or php8.5-pgsql.

Connecting: the DSN

You connect by creating a PDO object. Its first argument is a DSN (Data Source Name): a string that starts with the driver name and a colon, followed by driver-specific settings separated by semicolons. Client-server databases also need a user name and password:

PHP
<?php
// MySQL / MariaDB
$pdo = new PDO(
    'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4',
    'shop_app',
    'a-long-random-password'
);

// PostgreSQL
$pdo = new PDO('pgsql:host=127.0.0.1;port=5432;dbname=shop', 'shop_app', 'a-long-random-password');

// SQLite: a file, or a database that lives only in memory
$pdo = new PDO('sqlite:' . __DIR__ . '/../var/shop.sqlite');
$pdo = new PDO('sqlite::memory:');

For MySQL, always include charset=utf8mb4 in the DSN. It makes the connection use full UTF-8, including emoji and every script in the world. MySQL's older utf8 charset stores at most three bytes per character and silently fails on emoji like ๐ŸŽ‰.

Now for a real connection. The in-memory SQLite database below exists only while the script runs, so every run starts empty:

PHP A first connection and query

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

That's the whole cycle: connect, send SQL with exec() (for statements that return no rows) or query() (for statements that do), and loop over the result. PDO Basics explains each method and the fetch options in detail. One warning before then: these statements are fixed strings. As soon as a value comes from a user, you must use prepared statements, never string concatenation.

When the connection fails

Connections fail for ordinary reasons: the server is down, the password changed, the host name is wrong. PDO's constructor always throws a PDOException when it can't connect. Our sandbox has no database server, so connecting to MySQL fails, which makes it a good place to watch that happen:

PHP Handling connection errors

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Each message starts with an SQLSTATE code in brackets (HY000 means "general error", 08006 "connection failure"), often followed by the driver's own error number, such as MySQL's [2002] for "can't reach the server". The PostgreSQL message has a second line with a hint, which the demo cuts off with strtok(). "could not find driver" means the PDO driver for that database isn't installed, a common surprise on a fresh server.

What should a real application do with such an error? Log the details, show the user a generic message. The exception message can reveal host names and database names, which are useful to attackers. Errors, Warnings and Notices explains logging, and Custom Exceptions and Error Handling Strategy shows where to catch.

Since PHP 8.2, the password parameter of PDO::__construct() is marked with the #[\SensitiveParameter] attribute. If a connection error is uncaught, the stack trace shows Object(SensitiveParameterValue) instead of your database password. Older PHP versions printed the password in the trace, which is one more reason never to show stack traces to visitors.

Keeping credentials out of your code

Hard-coding a password in PHP source means it ends up in version control, in backups and on every developer's laptop. Instead, read connection settings from environment variables, set by the server, a container platform or a .env file that's never committed:

PHP A connection factory reading the environment

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The fourth argument of new PDO() is an array of options. These two are the ones you'll set on almost every connection: ERRMODE_EXCEPTION makes every failed query throw (already the default since PHP 8.0, but stating it documents your intent and protects you on older versions), and FETCH_ASSOC makes rows come back as plain associative arrays instead of PDO's default, which returns every column twice (by name and by number). The next lesson looks at both.

Because the rest of the app only ever calls createConnection(), moving from SQLite in development to MySQL in production is a configuration change, not a code change. Frameworks wrap exactly this idea in their database configuration files.

One connection per request

PHP follows a share-nothing model: each web request starts with a clean slate and ends by throwing everything away, including database connections. So the pattern is:

  • Create one PDO connection per request, when you first need it, and pass that object to the code that needs it (see OOP Design Principles for dependency injection). Don't open a new connection inside every function.
  • Don't bother closing it; PHP closes it when the request ends. (Setting the last reference to null closes it early, which long-running CLI workers sometimes do.)
  • Persistent connections (PDO::ATTR_PERSISTENT => true) reuse connections across requests handled by the same PHP-FPM worker. They save the connection setup time, but leftover state (open transactions, session variables, temporary tables) can leak from one request to the next. Measure before you use them; on a local network, connecting typically takes about a millisecond.

Database servers limit how many clients can connect at once (MySQL's default max_connections is 151), so with many PHP-FPM workers, connection counts are something to watch in production.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why do the runnable examples in this chapter use SQLite in memory?

    SQLite is a library inside PHP, so it works without a database server. Because PDO uses the same API for every driver, the lessons apply to MySQL and PostgreSQL too.
  2. 2. You find old code that calls mysql_query(). What is true about it?

    The original mysql extension was removed in PHP 7.0. Rewrite such code with PDO (or mysqli), using prepared statements.
  3. 3. Which DSN connects to a MySQL database named shop with full UTF-8 support?

    A PDO DSN is driver:key=value;key=value. utf8mb4 is MySQL's real UTF-8; the old utf8 charset can't store characters like emoji.
  4. 4. What does new PDO(...) do when it can't connect?

    The PDO constructor always throws a PDOException on failure, whatever the error mode. Catch it, log the details and show the user a generic message.
  5. 5. How should a PHP web application usually manage database connections?

    PHP's share-nothing model throws everything away after each request. One connection per request, passed around (dependency injection), is the standard pattern.

Summary

  • Databases handle concurrency, searching, consistency and size far better than files. Relational databases store rows in tables linked by keys, and you talk to them in SQL.
  • PHP works with MySQL/MariaDB, PostgreSQL, SQLite (a file or memory, no server) and others. This course runs examples on in-memory SQLite.
  • The old mysql_* functions were removed in PHP 7.0. mysqli works only with MySQL; PDO works with every major database and is the standard.
  • Connect with new PDO($dsn, $user, $password, $options). A DSN looks like mysql:host=โ€ฆ;dbname=โ€ฆ;charset=utf8mb4, pgsql:host=โ€ฆ;dbname=โ€ฆ or sqlite::memory:.
  • A failed connection throws PDOException: log the details, show users a generic message. PHP 8.2+ hides the password in stack traces.
  • Keep credentials in environment variables, create one connection per request, and set ERRMODE_EXCEPTION and FETCH_ASSOC options.

Next: PDO Basics: Connecting and Querying