Gossamer Forum: General: Databases and SQL: sql query to select last 5 entries

Oct 7, 2003, 7:49 AM

aragee

New User (2 posts)

Oct 7, 2003, 7:49 AM

Post #1 of 5

Shortcut

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.

Oct 7, 2003, 8:24 AM

Andy

Veteran / Moderator (17298 posts)

Oct 7, 2003, 8:24 AM

Post #2 of 5

Shortcut

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

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates

Oct 7, 2003, 10:39 PM

cornball

User (238 posts)

Oct 7, 2003, 10:39 PM

Post #3 of 5

Shortcut

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

Oct 24, 2003, 12:15 AM

aragee

New User (2 posts)

Oct 24, 2003, 12:15 AM

Post #4 of 5

Shortcut

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

Oct 24, 2003, 9:47 AM

Alex

Administrator (9386 posts)

Oct 24, 2003, 9:47 AM

Post #5 of 5

Shortcut

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.