Advanced Mysql query help

[DJ-Sound]

I need help with a section of my query that handles the "availability checking" aspect of my system. Unfortunately this has to be done in one query, I know how to do it in several with php if statements.

(oc_calendar.username = balances.uid) AND
         ($day != DATE_FORMAT(oc_calendar.start_date, '%d')) AND
          ($day != DATE_FORMAT(oc_calendar.end_date, '%d')) AND
           ($month != DATE_FORMAT(oc_calendar.start_date, '%m')) AND
            ($month != DATE_FORMAT(oc_calendar.end_date, '%m')) AND
             ($year != DATE_FORMAT(oc_calendar.start_date, '%Y')) AND
              ($year != DATE_FORMAT(oc_calendar.end_date, '%Y'))

Currently if ANY of the entries are of the same year or month or day it will not return the results. I need it to not return results ONLY if the entry matches the year, day, and month.

How do I go about doing that?

Thanks,
-Ryan

 

[qbert220]

I'm not quite sure what you are trying to do. You seem to be comparing one date ($day, $month, $year) with 2 dates (start_date and end_date). Is it supposed to match a range? In which case you probably want:

    (oc_calendar.username = balances.uid) AND
    ($day >= DATE_FORMAT(oc_calendar.start_date, '%d')) AND
    ($day <= DATE_FORMAT(oc_calendar.end_date, '%d')) AND
    ($month >= DATE_FORMAT(oc_calendar.start_date, '%m')) AND
    ($month <= DATE_FORMAT(oc_calendar.end_date, '%m')) AND
    ($year >= DATE_FORMAT(oc_calendar.start_date, '%Y')) AND
    ($year <= DATE_FORMAT(oc_calendar.end_date, '%Y'))

This returns results on a match. To get it to not return results on a match use:

    (oc_calendar.username = balances.uid) AND NOT
    (($day >= DATE_FORMAT(oc_calendar.start_date, '%d')) AND
     ($day <= DATE_FORMAT(oc_calendar.end_date, '%d')) AND
     ($month >= DATE_FORMAT(oc_calendar.start_date, '%m')) AND
     ($month <= DATE_FORMAT(oc_calendar.end_date, '%m')) AND
     ($year >= DATE_FORMAT(oc_calendar.start_date, '%Y')) AND
     ($year <= DATE_FORMAT(oc_calendar.end_date, '%Y')))