Limited results MySQL query - Newbie

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Cpt_veerhoek
Forum Newbie
Posts: 3
Joined: Thu Mar 20, 2003 12:49 am
Location: Netherlands, Europe

Limited results MySQL query - Newbie

Post 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
User avatar
Ebula
Forum Newbie
Posts: 15
Joined: Wed Mar 19, 2003 7:21 am
Location: Hamburg, Germany

Post 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.
Cpt_veerhoek
Forum Newbie
Posts: 3
Joined: Thu Mar 20, 2003 12:49 am
Location: Netherlands, Europe

Post 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?
User avatar
Ebula
Forum Newbie
Posts: 15
Joined: Wed Mar 19, 2003 7:21 am
Location: Hamburg, Germany

Post 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.
Cpt_veerhoek
Forum Newbie
Posts: 3
Joined: Thu Mar 20, 2003 12:49 am
Location: Netherlands, Europe

Post 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";
Post Reply