Php & MySql Pagination Script

WebSavvy · 04-08-2009, 10:29 PM

Code:

<?php

$newp = $_GET['p'];
$plimit = "10";

$strSQL = mysql_query("SELECT * FROM `your_table` WHERE(`field1` LIKE \"$variable%\" AND `field2`=\"whatever\")");

$totalrows = mysql_num_rows($strSQL);
$pnums = ceil ($totalrows/$plimit);

if ($newp==''){ $newp='1'; }

$start = ($newp-1) * $plimit;
$starting_no = $start + 1;

if ($totalrows - $start < $plimit) { $end_count = $totalrows;
} elseif ($totalrows - $start >= $plimit) { $end_count = $start + $plimit; }

?>

<div>viewing <?php print_r("$starting_no");?> - <?php print_r("$end_count");?> of <?php print_r("$totalrows");?> entries</div>

<?php

if ($totalrows - $end_count > $plimit) { $var2 = $plimit;
} elseif ($totalrows - $end_count <= $plimit) { $var2 = $totalrows - $end_count; }

?>

<ul>
   <li id="pages">pages: </li>
<?php if ($newp>1) { ?>
   <li><a href="<?php echo "http://yourdomain.com/page.php?p=".($newp-1);?>">&laquo;</a></li>
<?php } for ($i=1; $i<=$pnums; $i++) { if ($i!=$newp){ ?>
   <li><a href="<?php echo "http://yourdomain.com/page.php?p=$i";?>"><?php print_r("$i");?></a></li>
<?php } else { ?>
   <li id="current"><?php print_r("$i");?></li>
<?php }} if ($newp<$pnums) { ?>
   <li><a href="<?php echo "http://yourdomain.com/page.php?p=".($newp+1);?>">&raquo;</a></li>
<?php } ?>
</ul>


<ul>
<?php

$strSQL = mysql_query("SELECT `field1`, `field2`, `field3`, `field4` FROM `your_table` WHERE(`field1`=\"whatever\" AND `field2`=\"that\") ORDER BY `field1` DESC, `field2` ASC LIMIT $start,$plimit");

while ($row = mysql_fetch_array($strSQL)) {
   $field1 = $row["field1"];
   $field2 = $row["field2"];
   $field3 = $row["field3"];
   $field4 = $row["field4"];

?>

   <li>This is all I know about <?=$field1?> and <?=$field2?> ... though <?=$field3?> and <?=$field4?> interest me too.</li>

<?php 
}//END SQL
?>

</ul>

allworknoplay · 04-09-2009, 12:10 AM

Looks pretty straightforward, let me see if I can implement it and make it work!

WebSavvy · 04-09-2009, 12:20 AM

OK.

I removed all of my own actual urls, paths, etc. from it and replaced it with generic info so you'll need to replace the generic info with your own actual stuff.

Let me know if it works for you or not. OK?

allworknoplay · 04-09-2009, 01:18 AM

Quote:

Originally Posted by WebSavvy

OK.

I removed all of my own actual urls, paths, etc. from it and replaced it with generic info so you'll need to replace the generic info with your own actual stuff.

Let me know if it works for you or not. OK?

Any specific reason for using print_r instead of just echo?

Code:

<?php print_r("$starting_no");?> - <?php print_r("$end_count");?>

WebSavvy · 04-09-2009, 01:32 AM

Nope, other than it's just my own preference.

You can change it to whatever you want to. Did the script work for you?

allworknoplay · 04-09-2009, 01:37 AM

Quote:

Originally Posted by WebSavvy

Nope, other than it's just my own preference.

You can change it to whatever you want to. Did the script work for you?

Still working on it, I got about 90% of it working. My co-worker wrote his own pagination script and it's about 50 lines long and I just don't understand what the heck he's doing!

Yours is 1000% shorter and still working just as good! So once I get it working 100%, I am going to replace his code with his..haha..

Also, I see that you have 2 queries. Is the first query just to find the total number of entries?

WebSavvy · 04-09-2009, 01:46 AM

Yes, the first query gets the total number of entries. The second query finds all data related to the first query.

The second query is where you'd do your SORT and so forth.

I've used this same pagination method for 10 years now and never had any problems with it.

Note that:

Code:

$newp = $_GET['p'];

must be in the top of the page before any other codes.

Also:

Code:

$plimit = "10";

can be changed to however many items you wish to show per page (e.g., 10, 20, 50, and so forth).

If you hit any problems, just ask.

allworknoplay · 04-09-2009, 01:48 AM

Quote:

Originally Posted by WebSavvy

Yes, the first query gets the total number of entries. The second query finds all data related to the first query.

The second query is where you'd do your SORT and so forth.

I've used this same pagination method for 10 years now and never had any problems with it.

Note that:

Code:

$newp = $_GET['p'];

must be in the top of the page before any other codes.

Also:

Code:

$plimit = "10";

can be changed to however many items you wish to show per page (e.g., 10, 20, 50, and so forth).

If you hit any problems, just ask.

ahhh it's like you read my mind, I was going to ask about

$plimit = "10";

But you brought it up already!

Back to the grind!! Will let you know how it goes...hopefully soon!

WebSavvy · 04-09-2009, 01:55 AM

@LOL -- yeah, I figured that'd be coming next so thought I'd answer that for you now.

OK, good luck with it. I'll be sitting at my desk working for a few more hours yet so if you run into any problems, I'll be around & checking the thread for progress.

allworknoplay · 04-09-2009, 02:11 AM

I got it working!! And it works great!! The code is so efficient compared to my co-workers code...God I hated his code!!!

Anyways, one thing, I attache a screenshot of what I get with a lot of entries.... I wonder if it is possible to limit this to just 3 pages at a time? Like:

pages: « 1 2 3 »

Then if you are on page 3, it shows this:

pages: « 3 4 5 »

Anyways, no big deal, I don't want you to go out of your way to write code for me, this is probably something I should be able to figure out....but if you have any quick thoughts, let me know!

WebSavvy · 04-09-2009, 02:20 AM

I never had a need to do it like that, but I imagine it wouldn't be too hard.

You could probably insert something like this inside the pagination section:

Code:

if($newp <= 3){
 // pagination code here
}

perhaps?

allworknoplay · 04-09-2009, 02:22 AM

Quote:

Originally Posted by WebSavvy

I never had a need to do it like that, but I imagine it wouldn't be too hard.

You could probably insert something like this inside the pagination section:

Code:

if($newp <= 3){
 // pagination code here
}

perhaps?

Yup, I'll figure it out, and when I do, I'll post how I did it.

How well do you know OO? I was thinking maybe turning this into a function or class....it would be a good project for me since I'm very new to OO...

WebSavvy · 04-09-2009, 02:34 AM

I'm semi-introduced to OO ... I still have a lot of things yet to learn though. But yeah, it'd be a great project and one we'd all be able to benefit from together.

allworknoplay · 04-09-2009, 02:37 AM

Quote:

Originally Posted by WebSavvy

I'm semi-introduced to OO ... I still have a lot of things yet to learn though. But yeah, it'd be a great project and one we'd all be able to benefit from together.

Well I'm pretty close.

I decided to display 1 2 3 4 5 listings instead of just 1 2 3.

Now I need to be able to just have it dynamically adjust based on the page I'm on....

WebSavvy · 04-09-2009, 02:46 AM

I suppose it'd be easy to grab the current page from $newp based on location (e.g., page.php?p=xx)

then add + or - to the display before and after current number.

After the weekend I might have some free time to work on this and see what I'm able to come up with. However, if you come up with anything prior to that please do update the thread. I'd be interested in seeing what you did with it too.

allworknoplay · 04-09-2009, 02:48 AM

Quote:

Originally Posted by WebSavvy

I suppose it'd be easy to grab the current page from $newp based on location (e.g., page.php?p=xx)

then add + or - to the display before and after current number.

After the weekend I might have some free time to work on this and see what I'm able to come up with. However, if you come up with anything prior to that please do update the thread. I'd be interested in seeing what you did with it too.

Yes you're right, that's basically what I just did. It's kind of dirty, so hopefully I can have time later and clean it up via OO or even just make a nice function out of it. Here's what I got working and it works great!!

Code:

<?
if(!$newp2) $newp2 = 5;
if(!$_GET[p]) $p = 1;

?>

  pages:
<?php if ($newp > 1) { ?>
  <a href="<?php echo "events.html?p=".($newp-1);?>">&laquo;</a>
<?php } for ($i=$_GET[p]; $i<=($newp2 + $_GET[p]); $i++) { if ($i != $newp){ ?>
   <a href="<?php echo "events.html?p=$i";?>"><?php print_r("$i");?></a>
<?php } else { ?>
   <?php print_r("$i");?>
<?php }} if ($newp < $pnums) { ?>
   <a href="<?php echo "events.html?p=".($newp+1);?>">&raquo;</a>
<?php } ?>

WebSavvy · 04-09-2009, 02:52 AM

Hey, not too shabby!

Glad the script is working for you the way you want it to. So, later when you make any changes to it, please update that here because I'm sure it'll help someone else at some future date.

allworknoplay · 04-09-2009, 02:57 AM

Quote:

Originally Posted by WebSavvy

Hey, not too shabby!

Glad the script is working for you the way you want it to. So, later when you make any changes to it, please update that here because I'm sure it'll help someone else at some future date.

Thanks, we should give your code a name and version# for fun. And whatever update I add to it, we can increase the version...

Here's the format that I eventually want to get us to.

HTML Code:

[b]Viewing Page:[/b] 1 of 50 First | Prev 1 2 3 Next | Last

I can first turn this into a function? And then after getting it done as a function, try to see if we can make a class out of this?

I always feel functions gets me one step closer to classes and OO development...

WebSavvy · 04-09-2009, 03:03 AM

Quote:

Thanks, we should give your code a name and version# for fun. And whatever update I add to it, we can increase the version...

@LOL -- OK.

Quote:

Here's the format that I eventually want to get us to.

Code:

Viewing Page: 1 of 50 First | Prev 1 2 3 Next | Last

OK, I can help with this probably after the end of the month. Sorry to put it off like this but I'm working on 11,000 new entries in my db.

allworknoplay · 04-09-2009, 03:09 AM

Quote:

Originally Posted by WebSavvy

@LOL -- OK.

OK, I can help with this probably after the end of the month. Sorry to put it off like this but I'm working on 11,000 new entries in my db.

I got the First and Last working. It's easy. For the First, I just hardcoded the $p to 1 since we know that it will never be anything but 1.

For the Last, I just set the $p variable to $totalrows...
And it works!!

Now as for you, 11,000 DB entries!! wow...you are busy tonite huh? I am done with pagination for tonite....thanks to you!