How paginate a specific query?

Question

I've had a really hard time understanding even the "simple" pagination tutorials on the internet.

I am in the process of creating an Intranet site for my company and the only step in order to complete it is to paginate all of the queries as they would all contain thousands of results, especially search results.

My question is would I be able to paginate a query the way it is below:

SELECT * FROM hdticket WHERE id = 22;

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — ThiefMaster, Dec 31 '12 at 15:19
Let me be the first of many to say: never put `$_GET['anything']` directly into your queries. This is a recipe for disaster. Google "SQL Injection" for an idea as to why. — glomad, Dec 31 '12 at 15:19
well pagination means getting data using a LIMIT clause, i don't see that in your query ... as per your styling problem, you could do tables or css or whatever you fancy — cristi _b, Dec 31 '12 at 15:22
Not to make things more complicated than it need be, I removed anything related to PHP because the simple way it starts is with SQL only. I hope this is towards your needs. Also double check your mysql usage, I highly suggest you're switching to something you can use more easily than the bare `mysql_*` functions. Please see http://stackoverflow.com/a/11580420/367456 — hakre, Dec 31 '12 at 15:24

score 2 · Accepted Answer · answered Dec 31 '12 at 15:22

You need to add a LIMIT clause to your MySQL query. This is as simple as:

SELECT * FROM hdticket WHERE id = :ticknum LIMIT 0, 10

This will fetch ten records, starting from the beginning (imagining your records are zero index–based). The first number is when to start fetching from, and the second number is how many thereafter to fetch. So from 0, it’s fetching the next ten records. This would be page 1.

To get subsequent pages of records, you only need to increment the first number. So, to get page two, you would need to start counting from 10, and fetch the next 10 records. This query would look like this:

SELECT * FROM hdticket WHERE id = :ticknum LIMIT 10, 10

And then page 3:

SELECT * FROM hdticket WHERE id = :ticknum LIMIT 20, 10

And so on. Hopefully you can see the pattern emerging.

As you’re using $_GET parameters, you could then just tailor your URLs to page your records. So a URL of http://intranet/index.php?page=2 would give:

<?php

$page = intval($_GET['page']);
$limit = 10;
$offset = (($page - 1) * $limit);

$sql = "SELECT * FROM hdticket WHERE id = :ticknum LIMIT $offset, $limit";

Hope this helps.

Also, a couple of asides. Don’t use the mysql_ functions as they’re deprecated. You can see this from the official manual page: http://php.net/manual/en/function.mysql-query.php.

Furthermore, your code is vulnerable to SQL injection attacks, because you’re taking what’s in the URL and using it in the SQL query directly. This is all good and well if you’re putting numbers for pagination in there, but if someone nefarious puts SQL in the URL, they can do all sorts of things such as truncating your tables or your entire database, and it will happen because you’re just passing it through without checking it.

To combat this, you can prepare variables in SQL statements if you use an alternative to the mysql_ functions, like PDO. Your code would look something like this:

<?php

$sql = "SELECT * FROM hdticket WHERE id = :ticknum LIMIT :offset, :limit";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':ticknum', $ticknum, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();

This ensures that the data passed to your SQL string is what you expect, and automatically escaped to prevent SQL injection.

Agreed that you need LIMIT to do server-side pagination. However you should always include an ORDER BY clause if you use LIMIT. This will ensure that the rows are returned in the same order each time, which is necessary for consistent pagination. In practice, MySQL will probably return the rows in a consistent order anyway, but it's better not to rely on that. — jfrank, Dec 31 '12 at 15:27
@jfrank Seconded. Was going to make a note of that but forgot falling over myself to warn about SQL injection vulnerabilities! — Martin Bean, Dec 31 '12 at 15:31
Thank you Martin and everyone who contributed! This answers my question perfectly! I am aware of the deprecated mysql_ functions, though I am taking it a step at a time to learn OOP, though I started this when I was VERY green. Also since this is an Internal site I am developing for the computer illiterate on a secure network, I am not too worried about injections, though I will definitely keep this in mind for future projects! Thanks all! — Efrain Anthony Negron, Dec 31 '12 at 15:47
I’d still address the injection vulnerabilities. It’s a good habit to get into, and you never know *who* will be accessing your code. Just because today computer-illiterate people are accessing doesn’t mean tomorrow a curious 16 year old intern will be let loose on it and kills your database. — Martin Bean, Jan 01 '13 at 18:54

score -2 · Answer 2 · answered Dec 31 '12 at 15:20

-2

you can use top / limit to do so..

if you want only top 10 then :

  select top 10,* from table;

or if you want to go to pages then:

  select * from table limit 10 , 20;

answered Dec 31 '12 at 15:20

sourcecode

1,802
2
15
17

How paginate a specific query?

2 Answers2