How to paginate your SQL querys

On this post you will learn how to paginate an SQL query instead of showing all the rows, in order to avoid wasting time on the page load and at the same time save bandwidth.

Basically, we have to limit the SQL query and generate new queries similar to the original that gather the rest of rows to paginate.

Maybe this is not the best way to do this, but at least I will try to show you my own way.

If you have any suggestion, go ahead to the comments, I would be really grateful :)

The first thing that we need is to include the mysql handler (or connection), and the pagination class.
1. require_once('mysql_connection.php');
2. include_once('pagination.class.php');
How many items we will show per page?
1. $items = 10;
By default we are in the first page
1. $page = 1;
If the current page number is defined on the url ($_GET['page']), we will generate the SQL query fragment that will limit the rows according to the current page number..
1. if(isset($_GET['page']) and is_numeric($_GET['page']) and $page = $_GET['page'])
2. $limit = " LIMIT ".(($page-1)*$items).",$items";
3. else
4. $limit = " LIMIT $items";
We will generate the SQL query to fetch all the rows, and at the same time the auxiliar query to gather the total rows (this is to limit the pagination)
1. $sqlStr = "SELECT * FROM registros";
2. $sqlStrAux = "SELECT count(*) as total FROM registros";
Fetching all the rows
1. $aux = Mysql_Fetch_Assoc(mysql_query($sqlStrAux));
We will execute the SQL query, adding the $limit variable (generated on the step 4) this is for bring only the corresponding rows to the current page.
1. $query = mysql_query($sqlStr.$limit, $mysql);
Now, we need to verify that the query has returned rows. If not, we need to show a message to warn the user about the empty result.
1. if($aux['total']>0){
2. $p = new pagination;
3. $p->Items($aux['total']);
4. $p->limit($items);
5. $p->target("paginate_query.php");
6. $p->currentPage($page);
7. $p->show();
8. echo '<ul>';
9. while($row = mysql_fetch_assoc($query)){
10. echo "<li>{$row['id']} - {$row['nombre']}</li>";
11. }
12. echo '</ul>';
13. $p->show();
15. }else
16. echo "Rows not found in the DB.";

Download the example

External Links

23 comentarios en How to paginate your SQL querys

Hiro

2007-06-10 09:42:42

Hello buddy. :)

I'm using your Digg style pagination plugin, but...it's not working in "categories" pages in wp 2.2

In index pages is all working well, only in archives and categories, plugin just don't show anything

any "light" on this?

Victor Bracco

2007-06-10 11:04:56

Habrá sido <a href="http://en.wikipedia.org/wiki/Hiro_Nakamura" rel="nofollow">Hiro Nakamura</a>? :D

Victor De la Rocha

2007-06-10 16:16:48

¡Victor! jeje de verdad afectan esos capitulos :P

Victor De la Rocha

2007-06-10 16:27:39

@Hiro I will look for a solution with the plugin of pagination. my installation works correctly www.mis-algoritmos.com/category/php/page/3/

Can you give me the direction URL of your WordPress?

Hi brother

2007-06-10 17:01:50

Your Spoken Inglish Suckkkkk Study in USA or CA or UK and later Write

pablasso

2007-06-10 18:45:13

anda, te lo dice don gringo troll

Hiro

2007-06-10 21:49:42

LOL

@Victor Bracco, i'm a big fan of hiro nakamura :D
Actually, my name is Hiroshi...but all my friends call me hiro :)

@Victor De la Rocha
I'm using in localhost, maybe it's template problem? Because it works in "archives", the problem is when i click to browse category pages.

There is no "categories.php" or something...is just archives.php...
What am i missing?

pecesama

2007-06-12 18:53:52

Jeje el troll ni siquiera sabe escribir English :P

karen geni

2007-06-26 22:04:46

QUE MALA ONDA NO VINISTESSSSSSSS
Y YO QUE TE IBA A DAR DE TRAGAR
POZOLE
JAJA NO TE CREAS BUENO SI TE IVA A INVITAR
PERO TENIA K ABLARTE DE OTRAS COAS
:(

AVECES APESTAS

Eugenia que milagro :)

Huy ya te cayeron :P

necesito ayuda en un algorito, el algorito dice: hacer un diagrama de flujo que calcule una tabla de multiplicacion, tomando en cuenta que la tabla se calcula del 1 al 12

Victor De la Rocha

2007-07-04 00:16:16

ah si? ¿Que es <strong>un algorito</strong>?, yo necesito mi http://abbrr.com/PY y una <a href="http://www.flickr.com/gift" rel="nofollow">cuenta Pro de Flickr</a>.

pecesama

2007-07-04 11:30:37

no sabia que un diagrama de flujo fuera lo mismo que un algorito :P

Jesús

2007-07-04 14:53:44

@pecesama: déjalo man, ese we sigue pensando que 512 es parte de hipertextual,,,

@Jesus: a que no?

Este post ya parece libro de visitas ñ_ñ

Jesús

2007-07-04 20:01:08

@pablasso: ¬¬

@vitor de la colcha: parece más un twitter ñ_ñ deberías hacer un jotolog... va con tu personalidá y recibirías 'firmas' de todos lados..

bahodir

2007-09-27 04:00:46

Thank you. Great pagination and example.
As you said it is ok to post any suggestions, I will post my version. Though it also does the same thing...

So the main page, where I want to show some data and paginate it, looks like this.

0) {
// Start Pagination
$p = new pagination();
$p->items($total_items);
$p->limit(5); // Limit entries per page
$p->currentPage($_GET[$p->parameterName]); // Gets and validates the current page
$p->calculate(); // calculates what to show
$p->adjacents(3);
// End Pagination

// Start Showing some data
$query = "SELECT * FROM users LIMIT ".($p->page - 1) * $p->limit . ", " . $p->limit;
$result = mysql_query($query);
echo "";
while ($row = mysql_fetch_assoc($result)) {
echo "{$row['id']}";
}
echo "";
// End Showing some data

$p->show(); // Show pages
} else {
echo "There is no record to paginate.";
}
?>

And a little change in pagination class file's current page function: validates the current page number
function currentPage($value)
{
if ($value ceil($this->total_items/$this->limit)) {
$value = ceil($this->total_items/$this->limit);
}
$this->page = intval($value);
}

So, that's all.
And thanx once more, it saved me a lot of time.

bahodir

2007-09-27 04:03:24

In my previous comment, I missed the beginning. It should be like this:
$total_items = mysql_numrows(mysql_query("SELECT id FROM users;")); // number of total news in the database
if ($total_items > 0) {

Paginación para Wordpress y tus scripts en PHP

2007-10-05 16:46:43

[...] How to paginate your SQL Querys [...]

insane

2007-11-09 14:11:23

u_u bastante simple y bien para los que empiezan,
una buena practica con usuarios que no necesitan su informacion precisamente igual que como esta en su db seria que al login o inicio de la visita del usuario ( en caso de que no exista login, podriamos checar que hiciera este proceso nose, cada 4 min o siempre, whatever) algo del estilo.

//suponiendo $idcon contenga una conexion valida devuelta x mysql_connect
$_=mysql_query('select * from users',$idcon);
$facadeTblUsers=mysql_fetch_array($_);

con esto tendriamos todo guardadito en nuestro poder. y para el caso de pagina pues la tenemos mas facil, manejamos los registros como items en nuestro array, del modo de

$resultados = array_splice($facadeTblUsers,$limit); //solo elementos.
$_SESSION['t_users']=$resultados;//mas elegante ?? guardalos en tu session.(se da por implicito que ya la iniciaste )

!!Cuidado!! sugiero esto como una buena practica de programacion , mas no recomiendo que se utilize en grandes cantidades de informacion o queryes mounstrosos. hay que saber como implementar esto, el refresh o llamada de nuevo a este proceso podria ser cuando en alguna parte de nuestra aplicacion alteremos directamente a la tabla "users".

enfin. Saludos.
El background de esta pagina es el de suicidegirls.com ????

Mis Algoritmos

El sitio que en su tiempo Macedonian Dark Security le dió en su madre

How to paginate your SQL querys

23 comentarios en How to paginate your SQL querys

Deja un comentario