Page 1 of 1

Displaying set number of results from MySQL

Posted: Sun Dec 01, 2002 12:28 pm
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 :)

Posted: Sun Dec 01, 2002 1:22 pm
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.

Posted: Sun Dec 01, 2002 1:22 pm
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;

Posted: Sun Dec 01, 2002 6:28 pm
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;