...

View Full Version : Help with availability sql query



greens85
11-09-2011, 02:23 PM
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:


$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

MattF
11-09-2011, 02:39 PM
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.

greens85
11-09-2011, 03:00 PM
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:


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

MattF
11-09-2011, 03:24 PM
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. :D

greens85
11-09-2011, 03:30 PM
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?

Old Pedant
11-10-2011, 12:48 AM
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:



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.)

Old Pedant
11-10-2011, 12:55 AM
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.



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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum