warning: this is VERY UGLY CODE. i wrote it 3-4 years ago now i think
and it just keeps working.
you call it this way:
# pagination.
if(isset($_GET['pg'])) {
$page = intval($_GET['pg']);
} else {
$page = 1;
}
$page = sprintf("%02d",$page);
$query = Array(
'query' => "SELECT foo FROM bar",
'itemsperpage' => 50,
'maxdisplay' => 20,
'linkprefix' => "$_SERVER[PHP_SELF]?f=$forum_id&pg="
);
list($pages,$numpages,$items,$threads) = paginate_sql($page,$query);
$pages is the html formatted links
$numpages is the total number of pages
i dont actually use $items much but i think it just tells you how many
items (which is the same as itemsperpage... i believe)
$threads is the mysql rows resource (you can use mysql_fetch_rows on it)
function paginate_sql(&$thispage,$options) {
$offset = $options['itemsperpage'] * ($thispage - 1);
if(isset($options['server'])) {
$results = db_query(eregi_replace("^SELECT","SELECT
SQL_CALC_FOUND_ROWS",$options['query'])."LIMIT
$offset,$options[itemsperpage]",$options['server']);
$rows = db_query("SELECT FOUND_ROWS()",$options['server']);
} else {
$results = db_query(eregi_replace("^SELECT","SELECT
SQL_CALC_FOUND_ROWS",$options['query'])."LIMIT
$offset,$options[itemsperpage]");
$rows = db_query("SELECT FOUND_ROWS()");
}
list($totalitems) = db_rows($rows);
db_free($rows);
$html = "";
$numpages = ceil($totalitems / $options['itemsperpage']);
if(isset($options['maxpages']) && $numpages >
$options['maxpages']) { $numpages = $options['maxpages']; }
if($thispage < 1 || $totalitems < $options['itemsperpage']) {
$thispage = sprintf("%02d",1); }
if($thispage > $numpages && $numpages > 0) {
header(sprintf("Location: %s01",
str_replace("&","&",$options['linkprefix'])));
exit();
}
$start = (ceil($thispage / $options['maxdisplay'])-1) *
$options['maxdisplay'] + 1;
if($start == 1) {
$html .= "«";
} else {
$html .= sprintf("<a href=\"%s%02d\">«</a>",
$options['linkprefix'], $start-1);
}
if(isset($options['maxdisplay']) && $options['maxdisplay'] !=
0 && $numpages > $options['maxdisplay']) {
$numlist = $options['maxdisplay'] + $start - 1;
} else {
$numlist = $numpages + $start - 1;
}
if($numlist > $numpages) { $numlist = $numpages; }
for($x=$start; $x<=$numlist; $x++) {
if($x == $thispage) {
$html .= sprintf(" %02d", $x);
} else {
$html .= sprintf(" <a
href=\"%s%02d\">%02d</a>", $options['linkprefix'], $x, $x);
}
}
if($numlist == $numpages) {
$html .= " »";
} else {
$html .= sprintf(" <a href=\"%s%02d\">»</a>",
$options['linkprefix'], $numlist+1);
}
if($totalitems <= $options['itemsperpage']) { $html = ""; }
return Array($html,sprintf("%02d",$numpages),$totalitems,$results);
}
On 9/13/07, Patrik Hasibuan <patrikh@xxxxxxxxxxxxxxxxxxxxx> wrote:
> Hi Mike,
>
> I am intrested for the solution you gave me. But I am confused of the way in implementing "select FOUND_ROWS()".
>
> I wrote another very simple codes as the first step for understanding this threat:
> <?php
> $konek=mysql_connect("127.0.0.1","root","mypassword");
> if ($konek){
> for ($i=0;$i<2;$i++){
> $sqlku="select SQL_CALC_FOUND_ROWS id_iklan from lowongan limit 5;";
> $sqlkupage="select FOUND_ROWS()";
> $mybd=mysql_select_db("headhunter",$konek);
> $kueri=mysql_query($sqlku,$konek) or die('MYSQL QUERY ERROR ['.mysql_errno($konek).'] '.mysql_error($konek));
> $kueri=mysql_query($sqlkupage,$konek) or die('MYSQL QUERY ERROR ['.mysql_errno($konek).'] '.mysql_error($konek));
> while($brs=mysql_fetch_row($kueri)){
> list($idiklan)=$brs;
> echo "FirstItem <a href=\"showit.php?id=$idiklan\">$idiklan</a><br>";
> }
> }
> }else{
> echo "I can't talk to the server<br>";
> exit();
> }
> ?>
>
> I've put nine records into the table of "lowongan", idiklan 1 to 9.
> Look I get of course only one record, namely the: '9'.
>
> This is the output of my codes in my internet browser.
> ===
> FirstItem 9
> FirstItem 9
> ===
> The output what I am expecting suppose to be:
> on the $i=0
> "
> FirstItem 1
> FirstItem 2
> FirstItem 3
> FirstItem 4
> FirstItem 5
> "
> and on the $i=1
> "
> FirstItem 6
> FirstItem 7
> FirstItem 8
> FirstItem 9
> FirstItem 9
> "
>
> I read on the documentation of MySQL but I couldn't find any code of sample also in the php.net.
>
> Would be so kind to give me a very simple code of sample?
>
> Thank you very much in advance.
> On Wed, 12 Sep 2007 11:58:04 -0700
> mike <mike503@xxxxxxxxx> wrote:
>
> > On 9/12/07, Patrik Hasibuan <patrikh@xxxxxxxxxxxxxxxxxxxxx> wrote:
> > > Dear my friends...
> > >
> > > I am trying to display the content of a table. Each page must content only 5 records maximum. Each page has "Previous" and "Next" buttons (made from anchor).
> > >
> > > I dump the primary of the working table and keep it in a cookie. So than the paging task work with the index of cookie array. But the $curguruescomidiklan stays "0" each time I click "Next" button.
> >
> > yeah there is no reason to be using cookies for this.
> >
> > just using query string parameters should work. for pagination you need to know:
> >
> > total number of items
> > number of items per page
> >
> > after that you know how many pages (numitems / itemsperpage) and work
> > out the links appropriately (i.e. page1 has no previous, $maxpage has
> > no next) and you can put the number of pages down how you want (if you
> > want to do prev 3 4 5 next, or next 1 2 3 4 5 6 7 8 9 prev, there's a
> > ton of strategies to print out what pages to show, and next, previous,
> > jump to end, jump to beginning, etc)
> >
> > i've never used the PEAR class or anything else, i made a function a
> > long time ago that actually works in conjunction with mysql's SELECT
> > FOUND_ROWS() and uses a LIMIT offset,number to save the overhead of
> > fetching every row from the database to only show the ones for that
> > page. it's actually worked well for 4 years now without a single
> > change...
> >
> > at some point perhaps i'll clean it up and post it somewhere, and
> > someone might be able to make it even better.
> >
> > --
> > PHP General Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
> >
>
>
> --
> Patrik Hasibuan <patrikh@xxxxxxxxxxxxxxxxxxxxx>
> Junior Programmer
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
