Categories > Your tips & tricks >

Efficient pagination with MySQL 4.0.x and TBS

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: Funky
Date: 2005-07-05
Time: 00:32

Efficient pagination with MySQL 4.0.x and TBS

I search a efficient way to do pagination with TBS with a query which returns more than 3500 rows... here is my solution :-)

PHP CODE:

// check the PageNum parameter
$pageNum = isset($_GET['PageNum'])?(int)$_GET['PageNum']:1;

// rows per page
$pageSize = 30;

// start offset for mySQL query
$startIndex = $pageSize*($pageNum-1);

// do your request with this LIMIT keyword
// SQL_CALC_FOUND_ROWS must be present in your request
$query = mysql_query("SELECT SQL_CALC_FOUND_ROWS column_1, column_2, ... column_n FROM my_table WHERE mycondition LIMIT startIndex,$pageSize");

// get the previous request row count (as if you have no LIMIT keyword)
$queryCount = mysql_query("SELECT FOUND_ROWS()");
$resultCount = mysql_fetch_row($queryCount);
$rowCount = $resultCount[0];
mysql_free_result($queryCount);

// merge datas and navigation bar
$TBS->MergeBlock('result', $query);
$TBS->MergeNavigationBar('nv',10,$pageNum,$rowCount,$pageSize);

HTML:


<!-- same code than the exemple navigation bar -->
<table border="1" align="center" cellpadding="2" cellspacing="0">
  <tr align="center">
    <td width="16"><a href="[var..script_name]?PageNum=[nv.first;endpoint;magnet=a;mtype=m+m]">|&lt;</a></td>
    <td width="16"><a href="[var..script_name]?PageNum=[nv.prev;endpoint;magnet=a;mtype=m+m]">&lt;</a></td>
    <td width="16"><a href="[var..script_name]?PageNum=[nv.page;block=td;navsize=3;navpos=centred]">[nv.page]</a></td>
    <td width="16" bgcolor="#C4E1E1"><strong>[nv.page;block=td;currpage]</strong></td>
    <td width="16"><a href="[var..script_name]?PageNum=[nv.next;endpoint;magnet=a;mtype=m+m]">&gt;</a></td>
    <td width="16"><a href="[var..script_name]?PageNum=[nv.last;endpoint;magnet=a;mtype=m+m]">&gt;|</a></td>
  </tr>
</table>

<div>Total number of records : [var.rowCount]</div>


Hope this helps, i'm a PHP beginner so i'm sure it is possible to improve this solution...
By: neverpanic
Date: 2005-11-19
Time: 00:07

Re: Efficient pagination with MySQL 4.0.x and TBS

Nice one. Hope this will be in the next TBS version!
These are actually 3 mysq queries but it's still better than loading all the recordsets.
By: Tom
Date: 2005-11-19
Time: 14:59

Re: Efficient pagination with MySQL 4.0.x and TBS

Sorry to be dense on this -- would you mind saying more about why this is "3 mysql queries" -- I'm not getting it.

Thx for any help you can give in explaining for a newbie.
By: neverpanic
Date: 2005-11-19
Time: 22:52

Re: Efficient pagination with MySQL 4.0.x and TBS

Sorry, I made a mistake... only 2 queries...

if you go through the php code you will see 2 mysql_query functions... This function sends a request to the mysql_server, it queries.
Hence, 2 mysql queries.
By: Tom
Date: 2005-11-20
Time: 13:16

Re: Efficient pagination with MySQL 4.0.x and TBS

I couldn't see "three" queries either...

AFAIK - the second "mysql_query" -- althought it looks like an additional query -- is actually a hit to the "query cache" not to the database itself.

From Mysql Doco's
The query cache works for SELECT SQL_CALC_FOUND_ROWS ... and SELECT FOUND_ROWS() type queries. FOUND_ROWS() returns the correct value even if the preceding query was fetched from the cache because the number of found rows is also stored in the cache.
By: neverpanic
Date: 2005-11-20
Time: 14:07

Re: Efficient pagination with MySQL 4.0.x and TBS

But it's still a connection to the MySQL server. And it's still putting some load on the MySQL server. And it still has to wait until the MySQL server responds.
By: chiry
Date: 2011-02-14
Time: 07:06

Re: Efficient pagination with MySQL 4.0.x and TBS

Some php pagination scripts you can check out .:http://www.phpkode.com/scripts/tag/pagination/
By: andy
Date: 2011-02-14
Time: 07:08

Re: Efficient pagination with MySQL 4.0.x and TBS

Thank you for your sharing! Chiry!
I found some tutorial about pagination in php  listed on the site  you submit.
It is great helpful for me!
PHPKode is a grant site!