Displaying set number of results from MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Jim
Forum Contributor
Posts: 238
Joined: Fri Apr 19, 2002 5:26 am
Location: Near Austin, Texas

Displaying set number of results from MySQL

Post by Jim »

I have a news program in which I want to only display the 10 most recent news posts on the database.

How do I do that?

Thanks :)
User avatar
BigE
Site Admin
Posts: 139
Joined: Fri Apr 19, 2002 9:49 am
Location: Missouri, USA
Contact:

Post by BigE »

That would require using the limit command in MySQL. Please search the MySQL manual about the LIMIT command. http://www.mysql.com/doc Hope that helps.
MeOnTheW3
Forum Commoner
Posts: 48
Joined: Wed Nov 13, 2002 3:28 pm
Location: Calgary, AB

Post by MeOnTheW3 »

Well you would start by having some way of tracking when the posts were made. Such as a datetime column stored with the posting or in a linked table. Depends on which would most satisfy the purpose of your entire system.

Then, just make your SELECT query ORDER BY the datetime-coloumn DESC and LIMIT 0,10. That will return to you the ten most recent posts.

You can then also make you LIMIT dynamic in the sense that you can carry a variable around the holds the starting value (the 0 in 0,10) and/or the limiting value (the 10 in 0,10) so visitors could be given a link to the next set or even request to view more per page:

Code: Select all

$startVal = 0;
$perPage = 10;

$sql = 'SELECT * FROM posts ORDER BY dtDateTime DESC LIMIT '.$startVal.','.$perPage;

...

<a href="ShowPosts.php?startVal=<?php echo $startVal+1; ?>&perPage=<?php echo $perPage; ?>">Next Ten</a>
Hopefull this helps;
oldtimer
Forum Contributor
Posts: 204
Joined: Sun Nov 03, 2002 8:21 pm
Location: Washington State

Post by oldtimer »

If you are inputing your posts into a db then sort by the id(if you have one) in descending order. Limit it to 10 This is a sample I use for limiting a page to 5.

Code: Select all

&lt;?php
mysql_connect($DBhost,$DBuser,$DBpass) or
 die("Unable to connect to database");
 @mysql_select_db("$DBname") or die("Unable to select database");
 $sqlquery = "SELECT * From posts order by 'post_id' DESC";
 $result=mysql_query($sqlquery);
 $num=mysql_num_rows($result);
 $i=0;
 while ($i &lt; 5) {
 $post_id=mysql_result($result,$i,'post_id'); 
 $post=mysql_result($result,$i,'post'); 
 ++$i;  }

?&gt;
Post Reply