PDA

View Full Version : Show Newest Entry First, Pulling From Database.



alexjewell
01-06-2008, 05:42 PM
I have a page that displays all blog posts for a certain site. This is what the code looks like:



<?php
$query = "SELECT Id,Date,Title,Post FROM blogs";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)){ ?>
<h2><a href="index.php?page=blogs&id=<?=$row['Id']?>" class="ta"><?=$row['Title']?></a> <i><?=$row['Date']?></i></h2>
<?=$row['Post']?>
<div class="lorum"></div>
<?php }


Is there any way that I can display those posts shown at the bottom, at the top? I'd like the newest entries to be shown first, before the older. Any ideas on how to flip it to make that possible?

thetestingsite
01-06-2008, 07:19 PM
$query = "SELECT Id,Date,Title,Post FROM blogs ORDER BY `Id` DESC";


Hope this helps.

alexjewell
01-06-2008, 09:01 PM
Is there someway I can just ORDER BY DESC without using the Id? The Id is not based on order. The Id is dynamically created this way:



$id = date("mdYHis");


So, the ORDER BY `Id` would only work for a year...then once the next year started, it wouldn't work anymore.
Any ideas?

thetestingsite
01-06-2008, 09:10 PM
In order for it to order the results desc, you would need something to go off of (such as an auto-incrementing id). Perhaps you could add a column in your db called "order" and have it auto-increment or something.

Hope this helps.

boogyman
01-07-2008, 02:53 PM
Is there someway I can just ORDER BY DESC without using the Id? The Id is not based on order. The Id is dynamically created this way:



$id = date("mdYHis");


So, the ORDER BY `Id` would only work for a year...then once the next year started, it wouldn't work anymore.
Any ideas?

that would still work, the date function in php is generated based upon time(), and time() counts up so while its not the usual auto-incrementing field, it would still work.

kosi
01-07-2008, 09:42 PM
that would still work, the date function in php is generated based upon time(), and time() counts up so while its not the usual auto-incrementing field, it would still work.

it would work if it were date(YmdHis) because then the year would be first, but since he has it with the month first, date("mdYHis"), it would not. For example, look at this:

dec 27, 2007 7:30:00 = 12272007073000
jan 27, 2008 7:30:00 = 01272008073000

a php or sql sort engine would list jan 27, 2008 first even though it's a later date.

php.net documentation for mysql_data_seek() offers this solution:

for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--) {
if (!mysql_data_seek($result, $i)) {
echo "Cannot seek to row $i: " . mysql_error() . "\n";
continue;
}

if (!($row = mysql_fetch_assoc($result))) {
continue;
}
<h2><a href="index.php?page=blogs&id=<?=$row['Id']?>" class="ta"><?=$row['Title']?></a> <i><?=$row['Date']?></i></h2>
<?=$row['Post']?>
<div class="lorum"></div>
}

alexjewell
01-08-2008, 01:09 AM
Thank you Kosi - so the code you offered above would work without me having to change the way I'm saving blog posts in the database?