MySQL Query - order after date

Junior Spellweaver
Joined
Jul 26, 2011
Messages
139
Reaction score
15
Hi again. I'm still working on my topstory and news items and I've got everything as I wanted. Except the ordering. I have ordered the news-items after date, but as you can see on the picture below, the article from yesterday shows first, I want the newest one to be shown first.
TheNikolas - MySQL Query - order after date - RaGEZONE Forums


The code for news-items:
PHP:
<?php
		$latest = mysql_query("SELECT * FROM site_news WHERE id!='$not_id' ORDER BY date LIMIT 1, 2");
		
		while($row = mysql_fetch_array($latest))
			{
				$title = $row['title'];
				$short_text = $row['short_text'];
				$article_id = $row['id'];
				$article_image = $row['image'];
				$date = $row['date'];
				
				echo "<a class='news-item' href='article.php?id=$article_id'>
					<div style='float:left;margin-top:5px;margin-left:10px;'>
						<span class='title'><strong>$title</strong></span> »<br /> 
						<span style='color:#8C8C8C'>$date</span>
					</div>
				</a>";
			}
	?>
 
You could make new field like n_id with auto increment.

And then just ORDER BY n_id DESC LIMIT 1,2
 
When creating the table you should have created a column with a name remotely close to ID with the value set to auto increment. That way you just have to order by ID and bam ordered how you want it

I was going to say this if you didn't.

This way is much easier since it makes the first post's ID: 1, and the second's: 2, and so on. this way you know which ones were posted before the others, and you can easily use DESC (descending) in mysql queries to display them starting with the most recent.

This doesn't mean you can't have a "date" field if you still want to display date, but there's a lot more room for error when using the date to sort them.

:thumbup1:
 
Also, doing the variables
Code:
$title = $row['title']; 
                $short_text = $row['short_text']; 
                $article_id = $row['id']; 
                $article_image = $row['image']; 
                $date = $row['date'];
Just bulks up your code and is un-needed.
You could simply use the rows as is, just thought I'd say that :P
 
Back