Help with query, results 2-6 descending

[cmccomas]

Hey,

I have the following query. What I am trying to do is get the 2nd through 6th most recent entries, based on the date, from the table.

What am I doing wrong?

$query = "SELECT newsID, news_headline, date_format(news_date, '%c.%d.%y')as news_date FROM news ORDER BY news_date DESC LIMIT 2, 6"; 

[BuzzLY]

Don't know. What results are you getting? Are you getting an error?

You would never tell your mechanic, "I want my car to drive down the road when I press the pedal. What's wrong with it?" If you don't tell your mechanic what the car does when you DO press the pedal, how does he know what's wrong with it?

[cmccomas]

Well I've tried several different combinations.

With the query displayed, I do not get an error, I get the results, but only one, and it is the most recent.

[BuzzLY]

Show us the code where you are pulling the data from your result set.

[cmccomas]

<? 
									// includes 
									include("conf.php");

								// open database connection 
								$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!"); 

								// select database 
								mysql_select_db($db) or die ("Unable to select database!"); 

								// generate and execute query 
								$query = "SELECT newsID, news_headline, date_format(news_date, '%c.%d.%y')as news_date FROM news ORDER BY news_date DESC LIMIT 2, 6"; 
								$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error()); 

								// if records present
								if (mysql_num_rows($result) > 0)
								{
									// iterate through resultset
									// print article titles
									while($row = mysql_fetch_object($result))
									{
									?>
									<tr>
										<td width="5"></td>
										<td align="right"><font class="text-date"><? echo $row->news_date; ?></font></td>
										<td><a href="news/news.php?newsID=<?php echo $row->newsID; ?>" class="link-news"><? echo $row->news_headline; ?></a></td>
									</tr>
							<?
									}
								}
								// if no records present
								// display message
								else
								{
								?>
									<font size="-1">no news items currently available</font>
								<?
								}

								// close database connection
								mysql_close($connection);
								?>

[BuzzLY]

Hmmm... seems to be ok. I would suggest removing the LIMIT portion of your SQL statement and see if you get more than one record that way.

One of three things is happening here:

1. Your SQL is returning only one row

2. For some reason your code is not iterating through each record

3. You only have 3 records in your database, and you are returning the last one

Your code looks fine, but we need to be sure -- so try echoing mysql_num_rows to see what it contains. If greater than 1, then you know the SQL is ok, and there's a problem in your code. If only 1, then you know the code is probably fine, and you need to fix the SQL.

A couple of other points... Try removing the space in your limit (although having the space there still works on my server):

LIMIT 2,6

Your SQL will return the 3rd through the 8th row... is that what you want? If you are trying to return 5 records, starting with the 2nd record, then your statement should read:

LIMIT 1,5

You might also try LIMIT 6 just to see if multiple records are returned that way. Have you tried entering your SQL statement in mysql through PHPMyAdmin? That's a good way to fine tune your SQL before you use it in the code.