ORDER BY DAY,MONTH,YEAR

[datoshway]

I am having to build on a database structure I did not develop, we have 3 fields that set the date day, month, year.  How can I set up a query to order these chronological?  I tried this but does not work.

 

$sql = "SELECT * FROM calendar_event GROUP BY day, month, year ORDER BY month ASC"; 

[Pikachu2000]

Can you add a field to the table, and insert the date properly, or is it far too late for that?

[trinaryoc]

Try this out:

 

SELECT CAST(CONCAT_WS("-", year, day, month) AS DATE) as 'ymd' FROM calendar_event GROUP BY ymd ORDER BY ymd ASC

 

will give you a date output as a data type DATE and that's easy enough to ORDER BY. there's other ways to do it but that's a quick and dirty one.

 

persionaly if you cant adjust the table, I'd create a VIEW and with the CASTed CONCAT instead of the year, month, day, so you dont have to run the CAST and CONCAT everytime time you need to sort by dates

[datoshway]

That didn't work, got the following error when trying to use that query

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in.....

[PFMaBiSmAd]

That is a php error and it generally means that your query failed for some reason, but it could also mean you are using the wrong variable name in your php statements or you are overwriting the php variable that did contain the result resource from the query.

 

What's your actual php code from the line where you are forming the query in the $sql variable through to the line where the mysql_num_rows() statement is at?