Page 1 of 1

Limited results MySQL query - Newbie

Posted: Thu Mar 20, 2003 12:49 am
by Cpt_veerhoek
Hello,

I want to add a box to the main page of my site, so everybody can see what the latest additions are. I already have a good script that lists all entries. My problem is that I don't know how to limit the entries shown, to let's say 10 results.
Can anybody tell me how to make a good script or how to modify my current script so it will show only the latest 10 results?

Listing script:
<?php
require "ezine_db.inc.php";
$ezine_db = ezine_connecte_db();

$str_requete = "SELECT id,stof,auteur,grootte,vak FROM artikel WHERE affichage='$affichage' ORDER BY date DESC";

$result_articles = mysql_query ($str_requete,$ezine_db) or ezine_mysql_die();

print ('<center><table border="1" cellspacing="0">');
print("
<tr>
<td><center><b>Vak</b></td>
<td><center><b>Stof</b></td>
<td><center><B>Auteur</B></td>
<td><center><b>Grootte</b></td>
<td><center><b>Download Link</b></td>
</tr>
");
while ($articleDb =mysql_fetch_object($result_articles))
{
print("
<tr>
<td><center>$articleDb->vak</td>
<td><center>$articleDb->stof</td>
<td><center>$articleDb->auteur</td>
<td><center>$articleDb->grootte</td>
<td><center><a href='download.php?id=$articleDb->id'>Download nu!</a></td>
</tr>
");
}

print ('</table></center>');

?>


Thanx in advance

Posted: Thu Mar 20, 2003 1:37 am
by Ebula
First of all, you should read the helpfiles to MySQL.
To your problem, use "SELECT * FROM Table LIMIT 10", for 10 results. LIMIT works as follows:
LIMIT (int Amount),(int start).
i.e. LIMIT 10, 30 returns results 31-40.

Posted: Thu Mar 20, 2003 1:48 am
by Cpt_veerhoek
Thats nice, but how do i make it show the LATEST 10 entries, without having to manuall change the number in my script?

Posted: Thu Mar 20, 2003 2:02 am
by Ebula
You have to format your ORDER BY so that you order the rows by the date (as you already do). then just use LIMIT 10 at the end of your Clause. i.e. SELECT * FROM Table WHERE Field = Value ORDER BY Date DESC LIMIT 10.

Posted: Thu Mar 20, 2003 3:21 am
by Cpt_veerhoek
Ok, thnx mate, but i figured it ou myself... (i'm kinda lazy) I also replaced the date colomn with the id colomn, so it shows the latest releases (auto increment)

I've modified it like this:
$str_requete = "SELECT id,stof,auteur,grootte,vak FROM artikel ORDER BY id DESC LIMIT 10";