PDA

View Full Version : php / mysql - pagination

Tjobbe
June 12th, 2007, 13:39
I have created a simple cms in tthe past and I'm now editing it to use it for a client's site.

I hope to have an archive page, showing x amount of article snippets, which link through to the full article. There is also to be a "< next | previous >" pagination on the page, which is what I am stuck on.

It has been 6 months since I last touched php and I'm really struggling, here is what I have right now in my <body>;


<?php

include "admin/dbconnect.php";
include "includes/format_date.php";

$result = mysql_query("SELECT * FROM news ORDER BY id DESC LIMIT 0,5");

//echo's the articles, and even properly indents the code
while($row = mysql_fetch_array($result))
{echo "
<p><em>".$row['shortdescr']."</em></p>
<p>".$row['title']."</p>
<p>".$row['source']."</p>
<p><strong>Posted on:</strong> ".$row['date']."</p>
<hr />
";
}

mysql_close($conn);
?>



This loops through the db and shows the latest 5 posts.

Trouble is, I haven't a clue where to go from here! Can anyone lend this poor man a hand?
inimino
June 12th, 2007, 14:25
You could add a query parameter to the URL with a page value, like "?page=1" etc. You'll need to modify the SQL to return the correct results, and then generate the correct next and previous links with an incremented or decremented page= parameter.

I wrote an example of this kind of thing which you probably know where to find. ;)
Tjobbe
June 12th, 2007, 14:33
You could add a query parameter to the URL with a page value, like "?page=1" etc. You'll need to modify the SQL to return the correct results, and then generate the correct next and previous links with an incremented or decremented page= parameter.

I wrote an example of this kind of thing which you probably know where to find. ;)

Ah I see, so the mysql would then use the id/ start from the url which is fed to it?

I do know where to find it, wish me luck, I'll try this out. no doubt I'll be back here if things go tits up! Thanks!
inimino
June 12th, 2007, 14:40
Ah I see, so the mysql would then use the id/ start from the url which is fed to it?

Yep, the PHP determines what page it's on from the URL and you use that to construct the SQL query (by multiplying the page number by the number of entries per page, which can be fixed). Have fun! :)
Tjobbe
June 12th, 2007, 14:43
..right!

Had a look, and nearly fainted! I've had far too long a break from php to be able to digest that in all honesty (we are talking about the search.php page on that page on the tpl site aren't we?).

In any case, what would I modify the SQL to? how would I create the next / previous links and how would it know to change the url automatically?
inimino
June 12th, 2007, 15:02
Yep that's the page I was thinking of. I don't have the code in front of me at the moment though. For the next and previous links, search for the string 'previous' and you'll find it in an instant. The SQL stuff is slightly more involved. You'll need to alter the LIMIT clause using PHP variables which you calculate for the beginning and ending of the range on that page.