Gossamer Forum
Home : General : Databases and SQL :

sql query to select last 5 entries

Quote Reply
sql query to select last 5 entries
Hi guys,

I'm a mySQL n00b and had a quick question. I am building a simple news section whilst learning PHP/mySQL and was wondering if I can do a simple SQL query to select the last 5 entries in the database. Is there any way of counting how many rows exist in the database and decrementing some counter to display the last 5 entries from the last one.

At the moment I am doing it in a very "inelegant" way which does not reallyt solve my problem anyway.
Here's my PHP
Code:
// Request the text of all the news items
$result = mysql_query('SELECT newsText FROM newsitems');
if (!$result) {
die('<p>Error performing query: '.mysql_error().'</p>');
}
// Display the text of each news item in a paragraph
$min = 1;
$max = 5;
$i = 0;
while ( $row = mysql_fetch_array($result) ) {
if($i >= $min && $i <= $max) {
echo('<p>'.$row['newsText'].'</p>');
}
$i++;
}
TIA.
Quote Reply
Re: [aragee] sql query to select last 5 entries In reply to
Do you have a field (such as add_date) or similar? This would make it simple, as you could just do something like;

SELECT * FROM Table ORDER BY add_date DESC LIMIT 5

...or similar.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [aragee] sql query to select last 5 entries In reply to
Hello aragee , If you have an auto_increment field in the table, you might try :

SELECT newsText FROM newsitems where id >= (LAST_INSERT_ID()-6)

where id is the name of an auto increment field, should return the last 5 rows from the table.


-- assuming the auto increment field has no deletions , so numbers are 1,2,3,4,5,6... not 1,2,10,11,12....


Thanks

cornball

Last edited by:

cornball: Oct 7, 2003, 10:47 PM
Quote Reply
Re: [cornball] sql query to select last 5 entries In reply to
Thank you, I now have the last 5 entries shown just fine... now I need an SQL query to grab everything before the last 5 entries and display them in descending order.

newsitem1
newsitem2
newsitem3
newsitem4
newsitem5
newsitem6
newsitem7

In this instance, newsitems7-3 would be displayed as per normal in descending order, then the archive needs to display items newsitems2-1.

So, for example, what I am doing is displaying a news page with the last 5 (or however many) news items entered in the database and also an archive to show everything else.

Code:
$itemsPerPage = 5;
if(isset($maxID)) {
$result = @mysql_query('SELECT id, newsText FROM newsitems WHERE id < '.$maxID.' ORDER BY ID DESC LIMIT '.$itemsPerPage);
$archive = 1;
} else {
// Request the text of all the news items before the start of the last 5
$result = @mysql_query('SELECT id, newsText FROM newsitems ORDER BY ID DESC LIMIT '.$itemsPerPage);
$archive = 0;
// $result = @mysql_query('SELECT newsText FROM newsitems ORDER BY ID DESC LIMIT $numItems');
}


I hope that makes sense.
TIA
Quote Reply
Re: [aragee] sql query to select last 5 entries In reply to
Is this MS SQL or MySQL? In MySQL you can do:

LIMIT OFFSET, COUNT

so to grab entrie 6-10 you would do:

LIMIT 5,5

Cheers,

Alex
--
Gossamer Threads Inc.