Sorting by dates

[Venom Rush]

Hi there

I"m building a page that displays events for example:

Event heading
05 June 2007
Event Description

Another Event
19 June 2008
Event Description

What I'd like to know is how do I sort the events by date in the format shown above. At the moment I have a db with a table with the columns "day", "month" and "year" but can only order by one of them.

[Sulley's Boo]

Why don't you put the date in one column?

What's the idea behind destributing it in 3 different columns?

[Venom Rush]

Why don't you put the date in one column? What's the idea behind destributing it in 3 different columns?

I've tried that but what happens is it gets sorted by the days eg:

5 June 2007

7 April 2008

29 January 2006

[Sulley's Boo]

How come? ..

Put the WHOLE date in one column ..

and say, order by date ASC or DESC

Did you define the date column as DATE in your table structure?

[Venom Rush]

How come? .. Put the WHOLE date in one column .. and say, order by date ASC or DESC Did you define the date column as DATE in your table structure?

No, because I want the month to be shown as a word and not a number which is why I made the date column VARCHAR.

05 June 2007

I was told a little while ago that there might be a way through php to spit the date out in the format shown above while having my table column set to DATE. If you know how that would help alot ;)

[Sulley's Boo]

You can format your date the way you want, right?

<?php
// Assuming today is: March 10th, 2001, 5:16:18 pm
 
$today = date("F j, Y, g:i a");   // March 10, 2001, 5:16 pm
$today = date("m.d.y");   // 03.10.01
$today = date("j, n, Y");   // 10, 3, 2001
$today = date("Ymd");   // 20010310
$today = date('h-i-s, j-m-y, it is w Day z ');   // 05-16-17, 10-03-01, 1631 1618 6 Fripm01
$today = date('\i\t \i\s \t\h\e jS \d\a\y.');   // It is the 10th day.
$today = date("D M j G:i:s T Y");   // Sat Mar 10 15:16:08 MST 2001
$today = date('H:m:s \m \i\s\ \m\o\n\t\h');   // 17:03:17 m is month
$today = date("H:i:s");   // 17:16:17
?>

http://www.php.net/date :?:

But you should keep your date in one column as DATE? No point in keeping it in 3 columns as varchar for sorting? Coz then it will sort the dates like this for you (sort by first date column):

01 June 2010
02 June 2001
03 October 2000
04 February 1990
05 May 1983


awwww, something like that :-O not nice, riiiight?

All the best!

[Venom Rush]

You can format your date the way you want, right?

<?php
// Assuming today is: March 10th, 2001, 5:16:18 pm
 
$today = date("F j, Y, g:i a");   // March 10, 2001, 5:16 pm
$today = date("m.d.y");   // 03.10.01
$today = date("j, n, Y");   // 10, 3, 2001
$today = date("Ymd");   // 20010310
$today = date('h-i-s, j-m-y, it is w Day z ');   // 05-16-17, 10-03-01, 1631 1618 6 Fripm01
$today = date('\i\t \i\s \t\h\e jS \d\a\y.');   // It is the 10th day.
$today = date("D M j G:i:s T Y");   // Sat Mar 10 15:16:08 MST 2001
$today = date('H:m:s \m \i\s\ \m\o\n\t\h');   // 17:03:17 m is month
$today = date("H:i:s");   // 17:16:17
?>

http://www.php.net/date :?: But you should keep your date in one column as DATE? No point in keeping it in 3 columns as varchar for sorting? Coz then it will sort the dates like this for you (sort by first date column): 01 June 2010 02 June 2001 03 October 2000 04 February 1990 05 May 1983 awwww, something like that :-O not nice, riiiight? All the best!

Hi Sulley

I've tried what you gave me and the only date I get is today's date. Here is my code:

$query = "SELECT heading, date, description FROM event ORDER BY date";
$result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
			
if (mysql_num_rows($result) > 0 )
{
  while($row = mysql_fetch_row($result))
  {
    $today = date("j F Y");
    $row[1] = $today;
    echo "<p>".$row[0]."".$row[1]."".$row[2]."</p>";
  }
}

I had a look at the php manual but there doesn't seem to be anything that sorts dates from a db that I could find.

[Venom Rush]

Hi sulley

Thanks for your help. I managed to solve it with the following code with help from ryan_vietnow:

$query = "SELECT heading, day(date), monthname(date), year(date), description FROM event ORDER BY date";
$result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
						
if (mysql_num_rows($result) > 0 )
{
  while($row = mysql_fetch_row($result))
  {
  echo "<p>".$row[0]."".$row[1]." ".$row[2]." ".$row[3]."".$row[4]."</p>";
  }
}