ORDER BY

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thoughtriot
Forum Commoner
Posts: 26
Joined: Thu Nov 07, 2002 9:32 pm

ORDER BY

Post by thoughtriot »

I'm having a little trouble. I have many band sites, one being http://www.thoughtriot.net, and I use mysql for adding, editing, deleting, and showing the tour dates. I use it for many other aspects of the site as well, but I'm having trouble with the tour dates.

When I display the dates on the site, I used to do this:
ORDER BY Date asc

But when I entered in some tour dates for this month and January, it got messed up. 01.10.03 would come before 12.28.02, because i guess that's how mysql would read them. I'm now displaying them by ID asc, that way for now it will temporarily place the 12.28.02 tour date at the top where it belongs, but if I had to now put in another date for let's say, 12.29.02 because the band just booked another show on that date, it would go to the bottom. So, how would I display the dates with ORDER BY Date ASC and make 12.28.02 be above all of those 01.??.03 and 02.??.03 dates? Is there some function or something that reads the full date before echoing them instead of just going by the first 1 or 2 digits?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

can't verify this behaviour

Code: Select all

CREATE TABLE `events` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `dtWhen` date NOT NULL default '0000-00-00',
  `description` tinytext NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`,`dtWhen`)
) TYPE=MyISAM;

INSERT INTO events VALUES("1", "2003-10-01", "future most entry");
INSERT INTO events VALUES("2", "2002-05-01", "past most entry");
INSERT INTO events VALUES("3", "2003-02-10", "entry 2003-2-10");
INSERT INTO events VALUES("4", "2002-12-28", "entry 2002-12-28");
INSERT INTO events VALUES("5", "2003-01-05", "entry 2003-01-05");
INSERT INTO events VALUES("6", "2002-12-29", "entry 2002-12-29");
gives me
1,2003-10-01,future most entry,
3,2003-02-10,entry 2003-2-10
5,2003-01-05,entry 2003-01-05
6,2002-12-29,entry 2002-12-29
4,2002-12-28,entry 2002-12-28
2,2002-05-01,past most entry
for SELECT * from events ORDER by dtWhen descand
2,2002-05-01,past most entry
4,2002-12-28,entry 2002-12-28
6,2002-12-29,entry 2002-12-29
5,2003-01-05,entry 2003-01-05
3,2003-02-10,entry 2003-2-10
1,2003-10-01,future most entry
for SELECT * from events ORDER by dtWhen asc ;)
thoughtriot
Forum Commoner
Posts: 26
Joined: Thu Nov 07, 2002 9:32 pm

Post by thoughtriot »

Is there anyway to do it using the mm.dd.yy format I use though?
evilcoder
Forum Contributor
Posts: 345
Joined: Tue Dec 17, 2002 5:37 am
Location: Sydney, Australia

Post by evilcoder »

try

in the MySQL query:

SELECT field,field, date_format(Date,$displaydate) FROM table ORDER BY Date ASC

But before that put this in <?php $displaydate = "mm.dd.yy" ?>

See if that works.
Post Reply