mySQL: Splitting Results into groups of 'x'
I'm wondering how I can split the results up into groups of ten. For example, if I had 50 results, the first ten would show, giving a link to he next ten. Then on the next page there would be a link forward and back. I remember this topic being posted a while back but I cant seem to find it. Any help is greatly appreciated.
(Waits for php/mySQL guru Mark Hensler to reply..)
ROB posted this at 14:42 — 18th April 2003.
They have: 447 posts
Joined: Oct 1999
dont have time to explain in depth at the moment but basically what you'll want to do is something like this
SELECT * FROM table LIMIT 0,10;
now to get the second page you would
SELECT * FROM table LIMIT 10,10
third page
SELECT * FROM table LIMIT 20,10;
and so on
nuk3 posted this at 06:59 — 19th April 2003.
They have: 238 posts
Joined: May 2002
Yeah, but that doesn't answer my question, whats the automatic way to do it? I want the script to know how many results there are and it be able to go back and forth between them..
Busy posted this at 09:41 — 19th April 2003.
He has: 6,151 posts
Joined: May 2001
take a look in hotscripts.com I looked for one a while back but all i found were page length scripts, so created my own that found the total number of items and started displaying 100 at a time, if total number was over 100, add a next button, if not then just add previous and number of results, for page 2 display the results minus 100 (page 1 contents) and do the same check but this time add a previous button
nuk3 posted this at 09:43 — 19th April 2003.
They have: 238 posts
Joined: May 2002
Ok thanks Busy, I'll try that
ROB posted this at 18:15 — 25th April 2003.
They have: 447 posts
Joined: Oct 1999
here's a quick example
<?php
// records to display per page
$perpage = 10;
// total number of records
list($totalnum) = mysql_fetch_row(mysql_query("SELECT COUNT(*) FROM tablename"));
// figure out page navigation stuff
$numpages = intval($totalnum/$perpage);
if(!$numpages) $numpages = 1;
elseif($totalnum%$perpage) ++$numpages;
$startpage = (int)$_REQUEST['start'];
if(!$startpage) $startpage = 1;
if($startpage > $numpages) $startpage = $numpages;
$start = ($startpage - 1) * $perpage;
$showingstart = $start + 1;
$showingstop = $showingstart + $perpage - 1;
$showingtotal = $totalnum;
if($showingstop > $totalnum) $showingstop = $totalnum;
$results = mysql_query("SELECT * FROM tablename LIMIT $start,$perpage");
?>
<p>Showing records <?=$showingstart;?> - <?=$showingstop;?> of <?=$showingtotal;?></p>
<p>
<?for($i=0; $i<$numpages; ++$i): ?>
<a href="thispage.php?start=<?=$i;?>"><?=$i;?></a>
<?endfor;?>
</p>
<p>
<!-- show results here -->
<?while($row = mysql_fetch_assoc($results)): ?>
blah blah blah
<?endwhile?>
</p>
nuk3 posted this at 01:53 — 26th April 2003.
They have: 238 posts
Joined: May 2002
Thats the sorta thing I was after, thanks Rob.
SqlGuru posted this at 21:40 — 3rd May 2003.
They have: 11 posts
Joined: May 2003
Ahh, you poor guys...
In ASP.Net, use a Datagrid
It gives you paging, sorting, all sorts of neat stuff without the hassle.
Elemental posted this at 08:15 — 5th May 2003.
They have: 5 posts
Joined: Apr 2003
Heh heh... you poor guy. Using ASP.Net. How much did you pay for that, I wonder?
Mark Hensler posted this at 17:02 — 5th May 2003.
He has: 4,048 posts
Joined: Aug 2000
You can page results in ASP too. No reason to get ASP.NOT.
And if you wanted too, you could write a PHP object to do the same. No reason to submit to the control of the evil empire.
Mark Hensler
If there is no answer on Google, then there is no question.
boiler posted this at 18:18 — 5th May 2003.
They have: 10 posts
Joined: May 2003
I had made it under cgi.
you can look on http://www.n1xn0x.bz/?load=proxy
and i think the logic of php and cgi didn't different too much
Wired posted this at 03:06 — 9th May 2003.
They have: 4 posts
Joined: Apr 2003
http://www.devshed.com/Server_Side/PHP/Paginating/page1.html
This does it in PHP. Works like a charm!
nuk3 posted this at 11:14 — 9th May 2003.
They have: 238 posts
Joined: May 2002
Yep, thats even better. Thanks for that Wired.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.