mysql query - help

[ricmetal]

hi dudes

how do i write a mysql query with 3 columns, where the first column is 'year', the second is 'month' (integer) and the third is 'day' (integer), ordered by desc, but with an extra quirk, where if any of the three columns is zero (which means there is no data for that date column - assume i have a year and a month, but no day)?

 

my code looks like the following

ORDER BY exhib_date_year DESC, exhib_date_month DESC, exhib_date_day DESC

[searls03]

I don't think you finished your sentence.....what do you want it to do if one of them is 0?

[ricmetal]

right,

i want to select the rows that have a zero on any or all three columns before any other rows.

 

[searls03]

how are they displayed if all of them were filled?  by year, month, or day?

[ricmetal]

by all three

 

this is how i have set up my code

ORDER BY date_year DESC, date_month DESC, date_day DESC

[searls03]

but how will it look on your site?  will there be three columns on your site, one for each?  like column one is ordered by year, second by month, third by day?

[ricmetal]

what appears on my website is not the data i fetch from these three date database columns (the columns are on the database!).

what i am trying to do is to display (on my website) the data fetched from other columns, and ordered by these three date columns, by descent, with the extra quick where if any of the three date columns is zero, those rows will be fetched first.

 

[searls03]

the only thought I have is if you have the year, month, and day in different columns, you could make it so that if the number is 0, use php to make it so that it is then changed into a number higher that what is possible.  that is the only thought I really have on this. 

[ricmetal]

yes, i had that thought too but i was hoping not to have to go around the problem.

[litebearer]

is there a reason you are NOT using a date or datetime field in your database? in other words is there a reason why you are allowing what appear to be invalid dates?

 

also does 2008-00-01 come before or after 2008-01-00

[PFMaBiSmAd]

You can make custom ORDER BY terms using any mysql statement that produces a value. The following should work (you might need to change the DESC to ASC) -

ORDER BY 0 IN (exhib_date_year,exhib_date_month,exhib_date_day) DESC, exhib_date_year DESC, exhib_date_month DESC, exhib_date_day DESC

 

[ricmetal]

@litebearer

i designed the database using separate columns because i thought the data would be easier to fetch, in case i wanted to select these date columns'data. as to the invalid date columns, sometimes i insert a row with data regarding an event, and not always do i know when the event is going to exactly happen so i need to let the zeros be accepted.

 

im not sure how you are formatting your dates. in the USA you place the month in a different location than in other countries, so.

anyway, im not sure, but according to my current query, rows are being selected be year first, then month, then day.

 

@PFM

thanks, i will have to look into that and see if that works.thanks

[litebearer]

PFM's is most likely way better than mine (no sarcasm - he really is way better than I) but concatenating year.month.day then sorting that value should get you very close (0 years first, years with 0 month next, years and months with days, then normal dates

[ricmetal]

dang, worked like a charm FM. thanks again.

 

@lite thanks but FM has proven you remark with flying colors. lol

 

thanks y'all