Posted by Rafe Culpin on 04/19/07 14:22

In article <462733e6$1@127.0.0.1>, toylet.toylet@gmail.com (Man-wai Chang)
wrote:

>
> select * from myTable where ... pagesize 10 page 1
>
> How could I do it elegantly in:
>
> 1. PHP+MySQL before MySQL 5 (without stored procedures)
> 2. MySQL alone (with stored procedures)

Look at SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS()

These tell you how many rows would have been retrieved without a LIMIT
So when you do a query for (RECORDS_PER_PAGE) records, you know what the
total would have been and can set your navigation etc.

For a page part-way through, you take the record or page number that is
input to tell you which page, to tell you which records to retrieve.

So SQL_CALC_FOUND_ROWS and something like this goes in your query:

$this->pageno = 1;
// Sanitise
if (isset($_GET['page'])) $this->pageno=max((int)$_GET['page'],1);
// Offset is 0
$firstrecord = ($this->pageno - 1) * self::RECORDS_PER_PAGE;
$query .= " LIMIT $firstrecord, ". self::RECORDS_PER_PAGE;

And your navigation links are something like this: (watch out for the
wrapped lines) (SqlRun is a utility subroutine that runs and error traps a
query)

$max = SqlRun("SELECT FOUND_ROWS()");
$total_records = mysql_result($max, 0);
mysql_free_result ($max);
if ($total_records > self::RECORDS_PER_PAGE) { // Pagination required
$this->count = $total_records;
$pages = (int)ceil($total_records / self::RECORDS_PER_PAGE);
$prev = $this->pageno - 1;
$next = $this->pageno + 1;
$work = 1==$this->pageno ? '|&lt;- First &lt;-Prev' : "<a
href=\"{$_SERVER['SCRIPT_NAME']}?page=1\">|&lt;- First</a> <a
href=\"{$_SERVER['SCRIPT_NAME']}?page=$prev\">&lt;-Prev</a>";
$work .= "&nbsp;&nbsp;&nbsp;<strong>Page {$this->pageno} of
$pages</strong>&nbsp;&nbsp;&nbsp;";
$work .= $pages==$this->pageno ? 'Next-&gt; Last-&gt;|' : "<a
href=\"{$_SERVER['SCRIPT_NAME']}?page=$next\">Next-&gt;</a> <a
href=\"{$_SERVER['SCRIPT_NAME']}?page=$pages\">Last-&gt;|</a>";
$this->pagination = "<p class=\"pagination\">$work</p>\n";
}

Then $this->pagination can be output as required.

--
To reply email rafe, at the address cix co uk

[Back to original message]