Sorting by dates

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

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

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

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?

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 ;)

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!

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]."<br>".$row[1]."<br>".$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.

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]."<br>".$row[1]." ".$row[2]." ".$row[3]."<br>".$row[4]."</p>";
  }
}