How could I paginate the result set?

[Man-wai Chang]

I would like to do something like this:

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)

--
iTech Consulting Services Limited
Expert of ePOS solutions
Website: http://www.itech.com.hk (IE only)
Tel: [phone] Fax: [phone]

[pritaeas]

"Man-wai Chang" <to***********@gmail.comwrote in message
news:46********@127.0.0.1...

I would like to do something like this:

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)

--
iTech Consulting Services Limited
Expert of ePOS solutions
Website: http://www.itech.com.hk (IE only)
Tel: [phone] Fax: [phone]

Hi. You can use the query to do it for you, without PHP or stored
procedures.

SELECT * FROM table LIMIT 0, 10

Where 0 is the start record, and 10 your pagesize.

Regards, Hans

[Tyno Gendo]

Man-wai Chang wrote:

I would like to do something like this:

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)

Take a look at LIMIT in MySQL, and use a page offset variable in your PHP.

I usually do two queries, one to get how many results there would be
without pagination, and another to get the results within the page range.

Don't know if this is the best way to do it though, i'd be interested in
others replies myself.

[Man-wai Chang]

I usually do two queries, one to get how many results there would be

without pagination, and another to get the results within the page range.

Don't know if this is the best way to do it though, i'd be interested in
others replies myself.

Mind to publish your codes? :)

--
iTech Consulting Services Limited
Expert of ePOS solutions
Website: http://www.itech.com.hk (IE only)
Tel: [phone] Fax: [phone]

[Man-wai Chang]

Hi. You can use the query to do it for you, without PHP or stored

procedures.
SELECT * FROM table LIMIT 0, 10

page 1 is easy. What about pageno 1?

--
iTech Consulting Services Limited
Expert of ePOS solutions
Website: http://www.itech.com.hk (IE only)
Tel: [phone] Fax: [phone]

[Tyno Gendo]

Man-wai Chang wrote:

>I usually do two queries, one to get how many results there would be
without pagination, and another to get the results within the page range.

Don't know if this is the best way to do it though, i'd be interested
in others replies myself.

Mind to publish your codes? :)

define('PAGE_SIZE', 5);

$cat_id = @(int)$_REQUEST["cat_id"];
$page = @(int)$_REQUEST["page"];

/*
* find out how many recipes in total for paging, if request
* page>total pages then reset page variable to 0
*
*/
$sql_pagesize = "SELECT COUNT(cr.catID) AS count FROM catrecipe cr,
recipe WHERE cr.catID = recipe.catID AND cr.catID = " . (int)$cat_id;
$ds = mysql_query($sql_pagesize) or trigger_error("Unable to query
database: " . mysql_error());
$dr = mysql_fetch_assoc($ds);
$article_count = $dr["count"];
$pages_count = ceil($article_count/PAGE_SIZE);
if ( $page $pages_count ) {
$page = 0;
}
@mysql_free_result($ds);
unset($dr);
unset($ds);

/*
* work out the start and end for the SQL LIMIT in the paging option
*/
$start = 0+($page*PAGE_SIZE);
$end = $start+PAGE_SIZE;

/*
* look up the categories for the current page being viewed and display
them
*
*/

$sql = "SELECT cr.catID, cat_title, recipeID, recipe_title
FROM catrecipe cr, recipe
WHERE cr.catID = recipe.catID AND cr.catID = " . (int)$cat_id
.. " ORDER BY recipeID DESC LIMIT {$start}, {$end}";
$ds = mysql_query($sql) or trigger_error("Unable to query database: "
.. mysql_error());

[Tyno Gendo]

Tyno Gendo wrote:
<snip>

>
$sql = "SELECT cr.catID, cat_title, recipeID, recipe_title
FROM catrecipe cr, recipe
WHERE cr.catID = recipe.catID AND cr.catID = " . (int)$cat_id .
" ORDER BY recipeID DESC LIMIT {$start}, {$end}";
$ds = mysql_query($sql) or trigger_error("Unable to query database: "
. mysql_error());

note that my $page var is 0 based. so if you display page to the user,
use $page+1 so it doesn't say 'You are viewing Page 0' :)

[Rafe Culpin]

In article <46********@127.0.0.1>, to***********@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

[=?iso-8859-1?Q?Kim_Andr=E9_Aker=F8?=]

Man-wai Chang wrote:

Hi. You can use the query to do it for you, without PHP or stored
procedures.
SELECT * FROM table LIMIT 0, 10

page 1 is easy. What about pageno 1?

Using PHP (with explanations):

// Number of items per page.
$pagesize = 10;

// Get the page number from the user input.
$pageno = intval($_GET["page"]);

// In case "page=" in the URL query string was set to 0,
// a negative number, or not set at all.
if ($pageno < 1) { $pageno = 1; }

// Calculate the offset based on the page size and given page number.
$offset = ($pageno - 1) * $pagesize;

// Perform the
$query = "SELECT * FROM myTable LIMIT ".$offset.", ".$pagesize;
$result = mysql_query($query);

--
Kim André Akerø
- ki******@NOSPAMbetadome.com
(remove NOSPAM to contact me directly)

[Man-wai Chang]

thank you all.

--
iTech Consulting Services Limited
Expert of ePOS solutions
Website: http://www.itech.com.hk (IE only)
Tel: [phone] Fax: [phone]