Please support our MySQL advertiser: Programming Forums
 |
•
•
Join Date: Oct 2007
Posts: 194
Reputation: 
Rep Power: 2
Solved Threads: 2
Junior Poster
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.
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: 433
Reputation:
Rep Power: 6
Solved Threads: 9
Posting Pro in Training
Why don't you put the date in one column?
What's the idea behind destributing it in 3 different columns?
What's the idea behind destributing it in 3 different columns?
•
•
Join Date: Oct 2007
Posts: 194
Reputation:
Rep Power: 2
Solved Threads: 2
Junior Poster
•
•
•
•
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: 433
Reputation:
Rep Power: 6
Solved Threads: 9
Posting Pro in Training
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?
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: 194
Reputation:
Rep Power: 2
Solved Threads: 2
Junior Poster
•
•
•
•
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: 433
Reputation:
Rep Power: 6
Solved Threads: 9
Posting Pro in Training
Re: Sorting by dates You can format your date the way you want, right?
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!
<?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: 194
Reputation:
Rep Power: 2
Solved Threads: 2
Junior Poster
•
•
•
•
Originally Posted by 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
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.
•
•
Join Date: Oct 2007
Posts: 194
Reputation:
Rep Power: 2
Solved Threads: 2
Junior Poster
Hi sulley
Thanks for your help. I managed to solve it with the following code with help from ryan_vietnow:
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>";
}
} |
Similar Threads
Other Threads in the MySQL Forum
- I need help sorting dates (Java)
- Hey Just started new topic with arrays, and having some trouble please help (C++)
- Sorting has me out of sorts. (Visual Basic 4 / 5 / 6)
- sorting flexgrid by date column (Visual Basic 4 / 5 / 6)
- Comparing and sorting of Dates. (C++)
- dates (Visual Basic 4 / 5 / 6)
Other Threads in the MySQL Forum
- Previous Thread: how to set mysql5 max connection
- Next Thread: how to update a field in mysql with txt file.
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
