Member Avatar for Aeonix 
SELECT blabla
FROM a INNER JOIN b ON a.a = b.b

Is what I have. However I'd like to sort the results DESC by ID. I'd like to select last [variable x 10] of items sorted by ID DESC. Variable provided by PHP (I'll sort things out, I just need help with the query itself).

Edited by Aeonix
  • 3 Contributors
  • 3 Replies
  • 352 Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by hielo

Dani AI

Generated

was right to ask for the offset logic; and pointed you to LIMIT and the offset form. The missing bits are (1) how to compute the offset from your PHP variable (0‑based vs 1‑based paging), and (2) alternatives when offsets get large or your JOIN multiplies rows.

If your page index is 0‑based (first page = 0) use:
offset = pageIndex pageSize
If it’s 1‑based (first page = 1) use:
offset = (pageNumber - 1) pageSize

Example PHP (PDO) showing safe integer binding and 0‑based paging:

$pageSize = 10;
$page = max(0, (int) $_GET['p']);   // 0-based page index
$offset = $page * $pageSize;

$sql = "SELECT i.*, m.name
        FROM items i
        JOIN meta m ON i.meta_id = m.id
        ORDER BY i.id DESC
        LIMIT :limit OFFSET :offset";

$stmt = $pdo->prepare($sql);
$stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

If you expect very large offsets, prefer keyset (cursor) pagination instead of OFFSET — it’s faster and avoids scanning/skipping many rows:

-- load next page after last seen id (cursor)
SELECT i.*, m.name
FROM items i
JOIN meta m ON i.meta_id = m.id
WHERE i.id < :last_id
ORDER BY i.id DESC
LIMIT 10;

If the JOIN can duplicate rows (so LIMIT behaves on the joined set), limit first on the primary table via a subquery that selects ids, then join back to fetch full rows.

Notes: always cast/bind LIMIT/OFFSET to integers to avoid injection, and use a deterministic ORDER BY (e.g., timestamp DESC, id DESC) so pages remain stable.

Recommended Answers

All 3 Replies

Member Avatar for hericles 
SELECT blabla
FROM a INNER JOIN b ON a.a = b.b
ORDER BY a.id DESC LIMIT 10;

If you're creating the SQL statement in code just replace the 10 with whatever the result of (variable x 10) is.

Member Avatar for Aeonix

Here's the problem DESC LIMIT (variable x 10) in case of 5 it would be DESC LIMIT 50. It will give last 50 results, I need 10 results after these 50.

Something like DESC LIMIT from (variable x 10) to (variable x 10 + 10)

Member Avatar for hielo

You need ... LIMIT offset, rowCount -- ex:

SELECT blabla
FROM a INNER JOIN b ON a.a = b.b
ORDER BY a.id DESC LIMIT 50, 10

will give you at most 10 rows, starting with row 50.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.