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.
php:require_once('mysql_connection.php');
include_once('pagination.class.php');How many items we will show per page?
php:$items = 10;By default we are in the first page
php:$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..
php:if(isset($_GET['page']) and is_numeric($_GET['page']) and $page = $_GET['page'])
$limit = " LIMIT ".(($page-1)*$items).",$items";
else
$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)
php:$sqlStr = "SELECT * FROM registros";
$sqlStrAux = "SELECT count(*) as total FROM registros";Fetching all the rows
php: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.
php: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.
php:
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.";
23 Comments
Make A CommentComments RSS Feed TrackBack URL
June 10th, 2007 at 9:42 am
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?
June 10th, 2007 at 11:04 am
Habrá sido Hiro Nakamura?
June 10th, 2007 at 4:16 pm
¡Victor! jeje de verdad afectan esos capitulos
June 10th, 2007 at 4:27 pm
@Hiro I will look for a solution with the plugin of pagination. my installation works correctly http://www.mis-algoritmos.com/category/php/page/3/
Can you give me the direction URL of your WordPress?
June 10th, 2007 at 5:01 pm
Your Spoken Inglish Suckkkkk Study in USA or CA or UK and later Write
June 10th, 2007 at 6:45 pm
anda, te lo dice don gringo troll
June 10th, 2007 at 9:49 pm
LOL
@Victor Bracco, i'm a big fan of hiro nakamura
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?
June 12th, 2007 at 6:53 pm
Jeje el troll ni siquiera sabe escribir English
June 26th, 2007 at 10:04 pm
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
June 26th, 2007 at 10:05 pm
AVECES APESTAS
June 27th, 2007 at 12:15 am
Eugenia que milagro
June 27th, 2007 at 12:24 am
Huy ya te cayeron
July 3rd, 2007 at 9:36 pm
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
July 4th, 2007 at 12:16 am
ah si? ¿Que es un algorito?, yo necesito mi http://abbrr.com/PY y una cuenta Pro de Flickr.
July 4th, 2007 at 11:30 am
no sabia que un diagrama de flujo fuera lo mismo que un algorito
July 4th, 2007 at 2:53 pm
@pecesama: déjalo man, ese we sigue pensando que 512 es parte de hipertextual,,,
July 4th, 2007 at 5:04 pm
@Jesus: a que no?
July 4th, 2007 at 5:18 pm
Este post ya parece libro de visitas ñ_ñ
July 4th, 2007 at 8:01 pm
@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..
September 27th, 2007 at 4:00 am
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.
September 27th, 2007 at 4:03 am
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) {
October 5th, 2007 at 4:46 pm
[...] How to paginate your SQL Querys [...]
November 9th, 2007 at 2:11 pm
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 ????