Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts

    Help with availability sql query

    Hi All,

    I am working on what I consider to be a fairly complex query (at least by my standards anyway!).

    I have the following tbl structure:

    availability_calendar
    =================
    id | candidate_id | day | month | year | availability | previously_set

    Sample data might be something like;

    1 | 1 | 10 | 11 | 2011 | Available | 1

    What I am trying to do is allow people to search on peoples availability. I have the following SQL so far:

    Code:
    $availability = '
    			SELECT * FROM availability_calendar 
    			LEFT JOIN indivdual_teachers 
    			ON indivdual_teachers.teacher_id = availability_calendar.candidate_id 
    			WHERE (1=0';
    		
    				foreach($_POST as $key=>$value){
    						
    					if(strpos($key, 'availability_') == 0 and $value=='Available'){
    						$day = (int)str_replace('availability_', '', $key);
    						$availability .= ' OR (day='.$day.' AND month='.$month.' AND year='.$year.' AND availability="Available")';
    					}
    				}
    			
    			$availability .= ')';
    This isn't too far from the mark, but the problem is if I specify that a person must be available two days and they are only available one of those days then the person will be returned in the results anyway!

    I have tried changing the OR to an AND but that just complete breaks the code.

    Anyone able to advise on this one?

    Thanks in advance,

    Greens85

  • #2
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Quote Originally Posted by greens85 View Post
    I have tried changing the OR to an AND but that just complete breaks the code.
    The error you receive when you attempt that is? Also, add a exit($availability); after that code and post the output so that it's easier to visualise the completed query string.

  • #3
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Hi Matt,

    I have since realised that the code wasn't breaking but it was rather just returning a blank set... I'm not sure why though as there is at least one record that should match.

    Looking at the following query ran though phpmyadmin:

    Code:
    SELECT * 
    FROM availability_calendar
    LEFT JOIN indivdual_teachers ON indivdual_teachers.teacher_id = availability_calendar.candidate_id
    WHERE ( 1 =1
    AND (
    
    DAY =10
    AND MONTH =11
    AND year =2011
    AND availability = "Available"
    )
    AND (
    
    DAY =11
    AND MONTH =11
    AND year =2011
    AND availability = "Available"
    ) ) 
    GROUP BY candidate_id
    LIMIT 0 , 30
    I believe it should match with the image I have attached.

    Regards,

    Greens85
    Attached Thumbnails Attached Thumbnails Help with availability sql query-untitled-3.gif  

  • #4
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Code:
    SELECT * 
    FROM availability_calendar
    LEFT JOIN indivdual_teachers ON indivdual_teachers.teacher_id = availability_calendar.candidate_id
    WHERE (
    DAY =10
    AND MONTH =11
    AND year =2011
    AND availability = "Available"
    ) AND (
    DAY =11
    AND MONTH =11
    AND year =2011
    AND availability = "Available"
    )
    GROUP BY candidate_id
    LIMIT 0 , 30;
    Try running that query directly and see what happens.

    p.s: I'm extremely rusty with code, so that may be nowhere near.

  • #5
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Hi Matt,

    Ran that directly in PHPMYAdmin and got an empty result set again

    SELECT *
    FROM availability_calendar
    LEFT JOIN indivdual_teachers ON indivdual_teachers.teacher_id = availability_calendar.candidate_id
    WHERE (

    DAY =10
    AND MONTH =11
    AND year =2011
    AND availability = "Available"
    )
    AND (

    DAY =11
    AND MONTH =11
    AND year =2011
    AND availability = "Available"
    )
    GROUP BY candidate_id
    LIMIT 0 , 30

    From the image I posted, do you agree that it should be matching?

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    No.

    *THINK* about what you are asking. For now, forget about the LEFT JOIN to the other table.

    You are saying to SQL:
    Find me all records where Day/Month/Year is 10/11/2011 *AND* where Day/Month/Year is 11/11/2011
    THERE WILL NEVER BE A SINGLE RECORD that will match that!

    Because there will never be any record that has Day/Month/Year equal to *BOTH* of those values!!!!!!

    By the way, can I ask why in the heck you used separate fields for Day/Month/Year instead of using a single DATETIME field? Which would simplify the [censored] out of many many future queries??? I really think that was a bad design decision on your part.

    Just for starters, how would you ask your table a question such as[quote]
    What candidates are available on Fridays during November through January?
    [quote]
    TRIVIAL to do if you used a single DATETIME field. Incredibly complex with your design.

    *********

    ANYWAY...to answer your question:

    Code:
    SELECT t1.candidate_id, t3.*
    FROM availability_calendar AS t1 INNER JOIN availability_calendar AS t2
    ON ( t1.candidate_id = t2.candidate_id
         AND t1.`day`=10 AND t1.`month`=11 and t1.`year`=2011 
         AND t2.`day`=11 AND t2.`month`=11 and t2.`year`=2011 )
    LEFT JOIN indivdual_teachers AS t3
    ON t3.teacher_id = t1.candidate_id
    You really should use the back tick marks (` which shares keyboard key wiht ~) around day, month, and year as those are built-in functions and thus keywords in MySQL. (When preceded by a period, as in the above, you can get away without the back ticks, but omitting them is a bad habit to get into.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    If you might need to write this query for 2 or 3 or 5 days in a row, then there's another way to do it. But, again, your decision to use separate fields for day/month/year complicates it.

    I'm going to show it to you using a single DATETIME field. You can figure out how to convert it to your design.

    Code:
    SELECT C.candidate_id, T.*
    FROM 
         ( SELECT candidate_id, COUNT(*) AS dayCount
           FROM availability_calendar 
           WHERE availablity_date BETWEEN '2011-11-29' AND '2011-12-02'
           GROUP BY candidate_id 
           HAVING COUNT(*) = 4 ) AS C
    LEFT JOIN LEFT JOIN indivdual_teachers AS T
    ON T.teacher_id = C.candidate_id
    See that? I can just give a range of dates (4 days, in this case) and my HAVING clause will only allow those that have a count during those 4 days of (what else) 4, meaning that the given candidate_id is available all 4 days!

    You see how much using a single DATETIME field simplifies it?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •