Sorting by dates

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 456,555 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,473 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MySQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 1313 | Replies: 7 | Solved

•

Join Date: Oct 2007

Posts: 189

Reputation: Venom Rush is an unknown quantity at this point

Rep Power: 2

Solved Threads: 2

Venom Rush Venom Rush is offline

Offline

Junior Poster

Sorting by dates

Oct 17th, 2007

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.

•

Join Date: Dec 2004

Location: UAE

Posts: 428

Reputation: Sulley's Boo will become famous soon enough

Rep Power: 5

Solved Threads: 9

Sulley's Boo Sulley's Boo is offline

Offline

Posting Pro in Training

Re: Sorting by dates

Oct 17th, 2007

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

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

•

Join Date: Oct 2007

Posts: 189

Reputation: Venom Rush is an unknown quantity at this point

Rep Power: 2

Solved Threads: 2

Venom Rush Venom Rush is offline

Offline

Junior Poster

Re: Sorting by dates

Oct 17th, 2007

•

Originally Posted by Sulley's Boo

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

•

Join Date: Dec 2004

Location: UAE

Posts: 428

Reputation: Sulley's Boo will become famous soon enough

Rep Power: 5

Solved Threads: 9

Sulley's Boo Sulley's Boo is offline

Offline

Posting Pro in Training

Re: Sorting by dates

Oct 17th, 2007

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?

•

Join Date: Oct 2007

Posts: 189

Reputation: Venom Rush is an unknown quantity at this point

Rep Power: 2

Solved Threads: 2

Venom Rush Venom Rush is offline

Offline

Junior Poster

Re: Sorting by dates

Oct 17th, 2007

•

Originally Posted by 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?

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

Last edited by Venom Rush : Oct 17th, 2007 at 9:03 am.

•

Join Date: Dec 2004

Location: UAE

Posts: 428

Reputation: Sulley's Boo will become famous soon enough

Rep Power: 5

Solved Threads: 9

Sulley's Boo Sulley's Boo is offline

Offline

Posting Pro in Training

Re: Sorting by dates

Oct 17th, 2007

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

Help with Code Tags

(Toggle Plain Text)

 
<?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

not nice, riiiight?

All the best!

•

Join Date: Oct 2007

Posts: 189

Reputation: Venom Rush is an unknown quantity at this point

Rep Power: 2

Solved Threads: 2

Venom Rush Venom Rush is offline

Offline

Junior Poster

Re: Sorting by dates

Oct 18th, 2007

•

Originally Posted by Sulley's Boo

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

Help with Code Tags

(Toggle Plain Text)

<?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 ?>

<?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 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:

Help with Code Tags

(Toggle Plain Text)

$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.

•

Join Date: Oct 2007

Posts: 189

Reputation: Venom Rush is an unknown quantity at this point

Rep Power: 2

Solved Threads: 2

Venom Rush Venom Rush is offline

Offline

Junior Poster

Re: Sorting by dates

Oct 18th, 2007

Hi sulley

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

Help with Code Tags

(Toggle Plain Text)

$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>";
  }
}