MySQL Joins with PHP

MySQL Joins with PHP Why Joins Are Needed In simple terms: A well-designed database splits information into several tables: customers in one, orders in another, products in a third. An order row stores only customer_id, not the customer's name. To show "Order #501 by Asha", the two tables must be combined. A join does exactly that: it matches rows from different tables using their related columns.

Technically: A JOIN combines rows from two or more tables based on a condition, usually equality between a foreign key and a primary key (orders.customer_id = customers.id). The main types are INNER JOIN (only matching rows) and LEFT JOIN (all rows from the left table, with NULL where no match exists). Joins let one query return everything a page needs, instead of running separate queries for each row.

Sample Data for This Lesson In addition to order #501 (created in MySQL Get Last ID and marked as paid in MySQL Update Data), the examples use two more orders:

<?php require DIR . '/src/db.php';

$db = db();

$db->query("UPDATE orders SET created_at = '2026-10-10 10:15:00' WHERE id = 501"); $db->query("INSERT INTO orders (id, customer_id, status, total, created_at) VALUES (502, 3, 'paid', 1299.00, '2026-10-12 11:00:00'), (503, 1, 'shipped', 699.00, '2026-10-14 16:30:00')"); $db->query("INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (502, 3, 1, 1299.00), (503, 5, 1, 599.00), (503, 1, 5, 20.00)"); Order Customer Status Items Total 501 Asha (1) paid Notebook A5 ×3, Gel Pen ×10 380.00 502 Charu (3) paid Desk Lamp ×1 1299.00 503 Asha (1) shipped Wireless Mouse ×1, Gel Pen ×5 699.00 INNER JOIN: Matching Rows From Both Tables <?php require DIR . '/src/db.php';

$sql = ' SELECT o.id, c.name AS customer, o.status, o.total FROM orders AS o INNER JOIN customers AS c ON c.id = o.customer_id ORDER BY o.id ';

foreach (db()->query($sql) as $row) { printf("#%d %-6s %-8s ₹%8s\n", $row['id'], $row['customer'], $row['status'], $row['total']); } Output #501 Asha paid ₹ 380.00 #502 Charu paid ₹ 1299.00 #503 Asha shipped ₹ 699.00 How It Works FROM orders AS o starts from the orders table; o is a short alias. INNER JOIN customers AS c ON c.id = o.customer_id attaches the customer whose id equals the order's customer_id. The SELECT list can now use columns from both tables. c.name AS customer renames the column in the result. INNER JOIN returns only orders that have a matching customer (always the case here, thanks to the foreign key). Always Alias Duplicate Column Names Both tables have columns named id and created_at. With fetch_assoc(), columns with the same name overwrite each other in the result array — the last one wins. Select columns explicitly and give duplicates distinct aliases (o.id AS order_id, c.id AS customer_id).

Joining Several Tables An order's items require three tables: order_items links orders to products.

<?php require DIR . '/src/db.php';

$sql = ' SELECT p.name, oi.quantity, oi.unit_price, oi.quantity * oi.unit_price AS line_total FROM order_items AS oi INNER JOIN products AS p ON p.id = oi.product_id WHERE oi.order_id = ? ORDER BY p.name ';

foreach (db()->execute_query($sql, [503]) as $item) { printf("%-15s ×%-3d ₹%7s = ₹%7s\n", $item['name'], $item['quantity'], $item['unit_price'], $item['line_total']); } Output Gel Pen ×5 ₹ 20.00 = ₹ 100.00 Wireless Mouse ×1 ₹ 599.00 = ₹ 599.00 The order ID is a prepared-statement parameter; joins and WHERE conditions combine freely. The line total is calculated by MySQL, using the unit price stored with the order — not the product's current price, which may have changed since.

LEFT JOIN: Keeping Rows Without Matches To list customers with their number of orders — including customers who have never ordered — use LEFT JOIN. It keeps every row of the left table and fills the right table's columns with NULL when there is no match:

<?php require DIR . '/src/db.php';

$sql = ' SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.total), 0) AS spent FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id WHERE c.id IN (1, 2, 3) GROUP BY c.id, c.name ORDER BY c.id ';

foreach (db()->query($sql) as $row) { printf("%-6s %d order(s), ₹%s\n", $row['name'], $row['orders'], number_format((float) $row['spent'], 2)); } Output Asha 2 order(s), ₹1,079.00 Bala 0 order(s), ₹0.00 Charu 1 order(s), ₹1,299.00 How It Works LEFT JOIN keeps Bala, although no order matches her id; the order columns for her row are NULL. GROUP BY c.id, c.name combines each customer's rows into one result row. COUNT(o.id) counts only non-NULL order IDs, so Bala gets 0 (COUNT(*) would count her single row and return 1). SUM(o.total) is NULL for Bala; COALESCE(..., 0) replaces it with 0. With INNER JOIN, Bala would be missing entirely.

Join Type Result INNER JOIN Only rows with a match in both tables LEFT JOIN All rows from the left table; NULLs where the right table has no match RIGHT JOIN All rows from the right table (usually rewritten as a LEFT JOIN with tables swapped) To find customers who have never ordered: LEFT JOIN orders ... WHERE o.id IS NULL.

Avoiding the N+1 Query Problem A common performance mistake is loading a list, then running one extra query per row:

// N+1 queries: 1 for the orders, then 1 per order for its items foreach ($db->query('SELECT id FROM orders') as $order) { $items = $db->execute_query('SELECT ... FROM order_items WHERE order_id = ?', [$order['id']]); } With 50 orders, that is 51 queries. A single join retrieves everything at once; PHP then groups the rows:

<?php require DIR . '/src/db.php';

$sql = ' SELECT o.id AS order_id, c.name AS customer, p.name AS product, oi.quantity FROM orders AS o INNER JOIN customers AS c ON c.id = o.customer_id INNER JOIN order_items AS oi ON oi.order_id = o.id INNER JOIN products AS p ON p.id = oi.product_id ORDER BY o.id, p.name ';

$orders = [];

foreach (db()->query($sql) as $row) { $id = $row['order_id']; $orders[$id] ??= ['customer' => $row['customer'], 'items' => []]; $orders[$id]['items'][] = "{$row['product']} ×{$row['quantity']}"; }

foreach ($orders as $id => $order) { echo "Order #$id ({$order['customer']}): " . implode(', ', $order['items']) . "\n"; } Output Order #501 (Asha): Gel Pen ×10, Notebook A5 ×3 Order #502 (Charu): Desk Lamp ×1 Order #503 (Asha): Gel Pen ×5, Wireless Mouse ×1 Step-by-Step Explanation One query joins four tables and returns one row per order item, with the order and customer information repeated on each row. PHP groups the flat rows into a nested structure: $orders[$id]['items'][]. ??= creates the order entry the first time an order ID appears. The page now needs a single database round trip, regardless of the number of orders. ORMs solve the same problem with eager loading (for example, Laravel's Order::with('items')).

Common Mistakes Forgetting the ON Condition Effect: Every row of one table is combined with every row of the other (a "Cartesian product"), producing huge, meaningless results.

Fix: Always specify how tables relate.

Duplicate Column Names Overwriting Each Other Effect: $row['id'] contains the wrong table's ID.

Fix: Select explicit columns and use aliases.

Using INNER JOIN When Rows May Be Missing Effect: Records without related rows disappear from reports.

Fix: Use LEFT JOIN and handle NULLs.

COUNT(*) With LEFT JOIN Effect: Customers without orders are counted as having 1.

Fix: Count a column from the joined table: COUNT(o.id).

N+1 Queries Effect: Slow pages as data grows.

Fix: Fetch related data with joins (or a second query using IN (...)), then group in PHP.

Best Practices Join tables on their foreign keys, and index those columns. Use table aliases and alias duplicate column names. Choose INNER JOIN or LEFT JOIN deliberately based on whether unmatched rows must appear. Combine joins with GROUP BY and aggregate functions for summaries. Load lists with their related data in one query to avoid N+1 problems. Official reference: https://www.php.net/manual/en/mysqli.query.php