Just Enough SQL

The SQL you need from PHP: SELECT with WHERE and ORDER BY, the three write statements, a JOIN, and what an index does.

SyntaxSELECT columns FROM table WHERE condition ORDER BY column LIMIT n

SQL is a second language, read by the database, not by PHP. PHP only carries the text across. A table is a grid: columns with fixed names and types, rows of data. Keywords are case-insensitive, and written in capitals by convention so the data stands out from the instruction.

Reading

SELECT title, price FROM products WHERE price < 100 ORDER BY price DESC LIMIT 10. SELECT names the columns, FROM the table, WHERE which rows, ORDER BY the order, LIMIT how many. Without ORDER BY the order is undefined, even if it looks stable.

Writing

INSERT INTO products (title, price) VALUES (:title, :price) adds a row. UPDATE products SET price = :price WHERE id = :id changes matching rows. DELETE FROM products WHERE id = :id removes them. The WHERE is the whole safety mechanism.

JOIN

Data is split across tables and stitched back together on the way out. SELECT p.title, c.name FROM products p JOIN categories c ON c.id = p.category_id pairs each product with its category. JOIN is shorthand for INNER JOIN, which drops rows with no match; LEFT JOIN keeps them and fills the other side with NULL. Likewise ORDER BY price is shorthand for ORDER BY price ASC.

Indexes

An index is a sorted lookup the database keeps beside a column. Without one, a WHERE reads every row. With one, it jumps straight to the matches. Index the columns you search and join on, and no more: each index makes writes a little slower.

Example

Try it yourself
Loading editor…
Press Run to execute the code.

When to use it

  • Write the query behind a filtered, sorted product listing page.
  • Add the index that fixes a login page which got slow as the users table grew.
  • Pull a record and its related rows in one query instead of one query per row.
  • Read a colleague's JOIN and work out which rows it will leave out.

More examples

Two tables and the statements that use them

This is the whole vocabulary in one place, written with the ? placeholders PDO will fill. Two things in the schema are doing security and correctness work: UNIQUE KEY on sku makes the database reject a duplicate rather than trusting PHP to check, and KEY on category_id is the index that keeps the join and the filter fast. The AS in the join renames a column for the result, which matters when both tables have a name column.

Example · sql
CREATE TABLE categories (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL
);

CREATE TABLE products (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    sku         VARCHAR(40)  NOT NULL,
    title       VARCHAR(160) NOT NULL,
    price_cents INT UNSIGNED NOT NULL,
    UNIQUE KEY products_sku_unique (sku),
    KEY products_category_id_index (category_id)
);

-- Read: the clauses in the order they are written.
SELECT id, title, price_cents
FROM products
WHERE category_id = ? AND price_cents < ?
ORDER BY price_cents ASC
LIMIT 20;

-- Write: one row in, one row changed, one row gone.
INSERT INTO products (category_id, sku, title, price_cents) VALUES (?, ?, ?, ?);
UPDATE products SET price_cents = ? WHERE id = ?;
DELETE FROM products WHERE id = ?;

-- Join: product rows with their category name attached.
SELECT p.title, c.name AS category
FROM products p
JOIN categories c ON c.id = p.category_id
ORDER BY c.name, p.title;

What an index changes

EXPLAIN in front of a SELECT asks the database what it intends to do instead of doing it, and the rows column is the estimate of how many it will read. This is the first thing to run when a page that was fast at a thousand rows is slow at a million. The last two lines are the trap worth remembering: wrapping an indexed column in a function hides it from the index, so store the value in the form you search for.

Example · sql
-- 500,000 rows, no index on email.
EXPLAIN SELECT id FROM users WHERE email = '[email protected]';
-- type: ALL, rows: 500000  -> every row is read

CREATE UNIQUE INDEX users_email_unique ON users (email);

EXPLAIN SELECT id FROM users WHERE email = '[email protected]';
-- type: const, rows: 1     -> one lookup

-- An index is only used when the column is compared as it is stored.
SELECT id FROM users WHERE email = '[email protected]';        -- uses the index
SELECT id FROM users WHERE LOWER(email) = '[email protected]'; -- cannot use it

The same queries as PHP calls

This is how SQL actually reaches the database from PHP: as a string with placeholders, handed to prepare(), with the values passed separately to execute(). The multi-line string is worth the space, because a query you can read is a query you can fix. The sort direction is written into the SQL rather than bound, because a placeholder can only stand in for a value and the ASC/DESC in ORDER BY is not one. The limit is written in only because it never varies here: a LIMIT is a value, so a page size that changed would be bound with bindValue() and PDO::PARAM_INT.

Example · php
<?php

declare(strict_types=1);

// Every value in the SQL above arrives through a placeholder, never in the text.
$stmt = $pdo->prepare(
    'SELECT id, title, price_cents
     FROM products
     WHERE category_id = :category_id AND price_cents < :max_price
     ORDER BY price_cents ASC
     LIMIT 20'
);

$stmt->execute([
    'category_id' => (int) $categoryId,
    'max_price'   => (int) $maxPrice,
]);

foreach ($stmt as $row) {
    // Out of the database and into a page: escape it.
    echo htmlspecialchars($row['title'], ENT_QUOTES, 'UTF-8'), "<br>\n";
}

Related

php pdo preparedphp pdo fetchphp pdo writephp sql injectionphp n plus one

Discussion

  • Be the first to comment on this lesson.