[SOLVED] How Do I Display Backwards?

[JayJ]

I want to display information backwards, like a blog, where Jan 28's data appears on top, then Jan 27's underneath, etc.

This is what I got, but I can't getting it to display yet...

[code]
$result = mysql_query("SELECT * FROM blogs")
or die(mysql_error());

$num=mysql_numrows($result);

$i = $result;
while ($i > 0)
{
$Title=mysql_result($result,$i,"Title");
$File=mysql_result($result,$i,"File");
$Description=mysql_result($result,$i,"Description");
$Time=mysql_result($result,$i,"Time");

DISPLAY INFO;

$i = $i - 1;
}
[/code]

Am I on the right track or way off?  Is there an easier way?

[HuggieBear]

You need to use the time column to order the posts, it's best to do this in MySQL

[code]<?php
$sql = "SELECT * FROM blogs ORDER BY time DESC"; // notice the order by clause
$result = mysql_query($sql) or die(mysql_error());

// This while loop is more effective and efficient than what you had
while ($blog = mysql_fetch_array($result, MYSQL_ASSOC)){
  // Display your blog here
  echo $blog['title'] . "<br>\n";
  echo $blog['file'] . "<br>\n";
  echo $blog['description'] . "<br>\n";
  echo $blog['time'] . "<br>\n";
}
?>[/code]

Regards
Huggie

[JayJ]

I did use the code you gave me, but when I ran it, nothing displayed.

[quote author=HuggieBear link=topic=124402.msg515359#msg515359 date=1169988287]
You need to use the time column to order the posts, it's best to do this in MySQL
[/quote]

I'm not sure exactly what you mean here.  Do you mean I have to change something within my database?  And like I mentioned my ['time'] is a VARCHAR.  WOuld you recommend changing that to something else?

Also, I do have an ID column for each blog.  Could I somehow use that, and run it backwards?

[trq]

You need to make your 'time' field to be the type [i]timestamp[/i], this way you can order your results from newest to oldest.

[HuggieBear]

[quote author=JayJ link=topic=124402.msg515380#msg515380 date=1169990866]
I'm not sure exactly what you mean here.  Do you mean I have to change something within my database?  And like I mentioned my ['time'] is a VARCHAR.  WOuld you recommend changing that to something else?
[/quote]

Where in your post did you mention that exactly :) Yes you need to change this column to DATETIME ideally.

As for it not displaying anything, check the case, I notice you capitalised your column names, something I didn't, so try this:

[code]<?php
$sql = "SELECT * FROM blogs ORDER BY time DESC"; // notice the order by clause
$result = mysql_query($sql) or die(mysql_error());

// This while loop is more effective and efficient than what you had
while ($blog = mysql_fetch_array($result, MYSQL_ASSOC)){
  // Display your blog here
  echo $blog['Title'] . "<br>\n";
  echo $blog['File'] . "<br>\n";
  echo $blog['Description'] . "<br>\n";
  echo $blog['Time'] . "<br>\n";
}
?>[/code]

[HuggieBear]

[quote author=thorpe link=topic=124402.msg515383#msg515383 date=1169991850]
You need to make your 'time' field to be the type [i]timestamp[/i], this way you can order your results from newest to oldest.
[/quote]

Thorpe, will timestamp update automatically if you run an UPDATE statement on the row, for example if you were editing the post?

This might not be ideal, hence I suggested a DATETIME field.

Regards
Huggie

[JayJ]

HuggieBear, you're right.  I got mixed up with another post.  Oops.  ::)

I did change it to DATETIME, and it worked like a charm.

But that leads me to another problem... what kind of function do I use to record the date when I create a blog?

I was using...

[code]
$time = date('M j Y');
[/code]

But that doesn't work anymore.

And one last thing.  If I want to display the date in a certain format in the blog, how would I do that from a DATETIME so it displays differently?

I want it to read [b]Jan 28, 2007[/b].

[HuggieBear]

OK, when you insert into the database, use the now() function, as that will insert the current time, and when you retrieve data, use MySQL's [url=http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html]DATE_FORMAT()[/url] function...

[code]<?php
// Here's a query showing the now() function
$sql = "INSERT INTO blogs (Title, File, Description, Time) VALUES ('$title', '$file', '$description', now())";

// Here's a query showing the date_format() function
$sql2 = "SELECT Title, File, Description, DATE_FORMAT(Time, '%b %e, %Y') AS Time FROM blogs";
?>[/code]

Regards
Huggie

[JayJ]

Thanks a million, Huggie.  Everything's working perfectly now!

[HuggieBear]

Excellent,

Don't forget to mark this thread as 'Solved'.

Regards
Huggie