Mis Algoritmos

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 :)

1. The first thing that we need is to include the mysql handler (or connection), and the pagination class.

   require_once('mysql_connection.php');
   include_once('pagination.class.php');

2. How many items we will show per page?

   $items = 10;

3. By default we are in the first page

   $page = 1;

4. 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..

   if(isset($_GET['page']) and is_numeric($_GET['page']) and $page = $_GET['page'])
   			$limit = " LIMIT ".(($page-1)*$items).",$items";
   		else
   			$limit = " LIMIT $items";

5. 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)

   $sqlStr = "SELECT * FROM registros";
   $sqlStrAux = "SELECT count(*) as total FROM registros";

6. Fetching all the rows

   $aux = Mysql_Fetch_Assoc(mysql_query($sqlStrAux));

7. 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.

   $query = mysql_query($sqlStr.$limit, $mysql);

8. 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.

    
   	if($aux['total']>0){
   			$p = new pagination;
   			$p->Items($aux['total']);
   			$p->limit($items);
   			$p->target("paginate_query.php");
   			$p->currentPage($page);
   			$p->show();
   			echo '<ul>';
   			while($row = mysql_fetch_assoc($query)){
   					echo "<li>{$row['id']} - {$row['nombre']}</li>";
   				}
   			echo '</ul>';
   			$p->show();
    
   		}else
   			echo "Rows not found in the DB.";
    

• Download the example

• Pagination - what it is and how to do it
• Pagination of MySQL Query Results
• Package Information: Pager
• PEAR::Pager tutorials. - Paginate database results
• Pagination: Easy as PREV 1 2 3 NEXT
• PHP Pagination with MySQL
• PaginateIt - A PHP Pagination Class
• A pagination class
• PHP Pagination

Comentarios

2007-06-10 09:42:42
Hiro
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?

2007-06-10 11:04:56
Victor Bracco
Habrá sido Hiro Nakamura? :D

2007-06-10 16:16:48
Victor De la Rocha
¡Victor! jeje de verdad afectan esos capitulos :P

2007-06-10 16:27:39
Victor De la Rocha
@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?

2007-06-10 17:01:50
Hi brother
Your Spoken Inglish Suckkkkk Study in USA or CA or UK and later Write

2007-06-10 18:45:13
pablasso
anda, te lo dice don gringo troll

2007-06-10 21:49:42
Hiro
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?

2007-06-12 18:53:52
pecesama
Jeje el troll ni siquiera sabe escribir English :P

2007-06-26 22:04:46
karen geni
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
:(

2007-06-26 22:05:12
karen geni
AVECES APESTAS

2007-06-27 00:15:42
Victor De la Rocha
Eugenia que milagro :)

2007-06-27 00:24:36
pecesama
Huy ya te cayeron :P

2007-07-03 21:36:33
xtreme
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

2007-07-04 00:16:16
Victor De la Rocha
ah si? ¿Que es un algorito?, yo necesito mi http://abbrr.com/PY y una cuenta Pro de Flickr.

2007-07-04 11:30:37
pecesama
no sabia que un diagrama de flujo fuera lo mismo que un algorito :P

2007-07-04 14:53:44
Jesús
@pecesama: déjalo man, ese we sigue pensando que 512 es parte de hipertextual,,,

2007-07-04 17:04:09
pablasso
@Jesus: a que no?

2007-07-04 17:18:40
Victor De la Rocha
Este post ya parece libro de visitas ñ_ñ

2007-07-04 20:01:08
Jesús
@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..

2007-09-27 04:00:46
bahodir
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.

2007-09-27 04:03:24
bahodir
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) {

2007-10-05 16:46:43
Paginación para Wordpress y tus scripts en PHP
[...] How to paginate your SQL Querys [...]

2007-11-09 14:11:23
insane
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 ????