SQL Overview

SQL is the language PHP applications use to work with relational databases such as MySQL, MariaDB, PostgreSQL, and SQLite. A relational database stores data in tables. Tables contain rows. Rows contain columns.

Most web applications rely on SQL somewhere: users, orders, products, posts, invoices, permissions, audit logs, password reset tokens, sessions, and background jobs are all commonly stored in database tables.

Tables, Rows, And Columns

A table describes one kind of thing. For example, a users table might have columns for id, email, name, created_at, and is_active.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    is_active BOOLEAN NOT NULL,
    created_at DATETIME NOT NULL
);

The exact column types vary between database engines, but the idea is the same: define the shape of the data before storing rows.

Reading Data With SELECT

SELECT reads data from one or more tables.

SELECT id, email, name
FROM users
WHERE is_active = 1
ORDER BY created_at DESC
LIMIT 20;

Read this slowly:

• SELECT id, email, name chooses columns.
• FROM users chooses the table.
• WHERE is_active = 1 filters rows.
• ORDER BY created_at DESC sorts newest first.
• LIMIT 20 keeps the result size under control.

A PHP application should usually select only the columns it needs. SELECT * is convenient while exploring, but it makes application code depend on every column and can move unnecessary data across the database connection.

Writing Data

The common write operations are INSERT, UPDATE, and DELETE.

INSERT INTO users (email, name, is_active, created_at)
VALUES (:email, :name, :is_active, :created_at);

UPDATE users
SET name = :name
WHERE id = :id;

DELETE FROM users
WHERE id = :id;

The WHERE clause is critical on updates and deletes. An update without a WHERE can change every row in the table.

Parameters, Not String Concatenation

Values from forms, URLs, JSON bodies, cookies, and jobs must not be concatenated into SQL strings. Use placeholders and bind parameters through PDO or your framework.

<?php

declare(strict_types=1);

$sql = 'SELECT id, email FROM users WHERE email = :email';
$parameters = ['email' => 'a@example.com'];

echo $sql . PHP_EOL;
echo json_encode($parameters, JSON_THROW_ON_ERROR) . PHP_EOL;

// Prints:
// SELECT id, email FROM users WHERE email = :email
// {"email":"a@example.com"}

The SQL command and the value are separate. This is the normal defence against SQL injection and also makes queries easier to review.

Joins

A join reads related data from more than one table. For example, orders may belong to users.

SELECT orders.id, users.email, orders.total
FROM orders
INNER JOIN users ON users.id = orders.user_id
WHERE orders.status = :status;

The join condition matters. A missing or wrong join condition can produce duplicate rows, incorrect totals, or very slow queries.

Aggregates

Aggregate functions summarise rows.

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;

Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX. They are used in dashboards, reports, admin screens, and background checks.

NULL

NULL means missing or unknown. It is not the same as an empty string, zero, or false.

SELECT id, email
FROM users
WHERE deleted_at IS NULL;

Use IS NULL and IS NOT NULL. Do not write deleted_at = NULL; that does not behave like normal equality.

SQL In PHP Work

In real PHP projects, SQL may appear directly in repository classes, inside framework query builders, in migrations, in ORM-generated queries, in debug logs, and in database consoles. Even when an ORM writes SQL for you, understanding SQL helps you spot slow queries, incorrect filters, missing indexes, and unsafe writes.

What To Check

Before moving on, make sure you can:

• Explain tables, rows, and columns.
• Read a basic SELECT, INSERT, UPDATE, and DELETE.
• Use WHERE, ORDER BY, and LIMIT.
• Explain why placeholders are safer than string concatenation.
• Recognise joins and aggregates.
• Treat NULL deliberately.