[thelist] Re: MYSQL: getting distinct dates from a DATETIME field

[Dunstan Orchard]

> Rudy wrote:
>
> DISTINCT will work

Hey rudy, well as you suggested, my query now reads:

SELECT DISTINCT
DATE_FORMAT(event_date, '%b %D') AS event_dates,
event_name, event_cost
FROM event
WHERE (event_type = 'exhibit hall')
ORDER BY DATE_FORMAT(event_date, '%b %D') ASC

But the output hasn't changed - it's still returning all three records :o/

Here's a dump from PhpMyAdmin showing the table structure and the three rows in
question:

CREATE TABLE event (
  event_id int(11) NOT NULL default '0',
  event_name varchar(100) NOT NULL default '',
  event_path varchar(50) NOT NULL default '',
  event_date datetime default NULL,
  event_duration time NOT NULL default '00:00:00',
  event_cost int(5) NOT NULL default '0',
  event_part int(1) NOT NULL default '0',
  event_parts int(1) NOT NULL default '0',
  event_note varchar(100) NOT NULL default '',
  event_type varchar(20) NOT NULL default '',
  PRIMARY KEY  (event_id)
) TYPE=MyISAM;

INSERT INTO event VALUES (13, 'Exhibit Hall Open', '', '2003-05-15 16:00:00',
'04:00:00', 2500, 0, 0, '(Reception 4 – 6 PM)', 'exhibit hall');
INSERT INTO event VALUES (15, 'Exhibit Hall Open', '', '2003-05-16 08:00:00',
'05:00:00', 2500, 0, 0, '', 'exhibit hall');
INSERT INTO event VALUES (21, 'Exhibit Hall Open', '', '2003-05-16 16:00:00',
'03:00:00', 2500, 0, 0, '', 'exhibit hall');


And the data being outputted to the page:

Date 	        Cost 	  	Title
May 15th 	$25 		Exhibit Hall Open
May 16th 	$25 		Exhibit Hall Open
May 16th 	$25 		Exhibit Hall Open

Sorry to keep asking the same question, but if everyone thinks if _should_ work,
I'd love to get it fixed :o)

Thanks again for the continued help.

- Dunstan

<tip type="vector graphics">
If you can't afford, or (as in my case) can't get your head around Adobe
Illustrator and Macromedia Freehand, go and take a close look at Xara X:

http://www.xara.com/products/xarax/

It's very fast, very powerful, and ever so simple to use.

If you ever used ArtWorks on RISC OS machines then this is the PC version, way
down the line.

Highly recommended.
</tip>

---------------------------
Dorset, England
http://www.1976design.com/
http://www.1976design.com/blog/