Hi,
I'm not sure if this should be posted in the PHP forum or the mySQL forum as it's a bit of both to be honest.
Basically I am generating what I think is a fairly complex SQL statement (although I'm by no means a mySQL master so it might not be that complex
) through the use of PHP.
The PHP that is generating the statement looks like so:
PHP Code:
// Availability
$availability = 'SELECT * FROM indivdual_teachers WHERE 1=1';
foreach($_GET as $key=>$value){
if(strpos($key, 'availability_') == 0 and $value=='Available'){
$day = (int)str_replace('availability_', '', $key);
$availability .= ' AND teacher_id IN (SELECT candidate_id FROM availability_calendar WHERE (DAY = '.$day.'
AND MONTH = '.$month.' AND YEAR = '.$year.' AND availability = "Available")) ';
}
}
// Staff Yype
if($_GET['staff_required'] == 'Non Teaching Staff') {
$search_str[] = "role !=\"\"";
} elseif($_GET['staff_required'] == 'Teaching Staff') {
$search_str[] = "role=\"\"";
} else {
}
// Lives in...
if(!empty($_GET['location'])) {
$location = mysql_real_escape_string($_GET['location']);
$search_str[] = "address2 LIKE '%$location%' OR city LIKE '%$location%'";
} elseif(!empty($_GET['region'])) {
$region = mysql_real_escape_string($_GET['region']);
$search_str[] = "region = '$region'";
} else {
// They don't want to search on lives in... so do nothing
}
// Registered with...
if(!empty($_GET['LA'])) {
$local_authority = mysql_real_escape_string($_GET['LA']);
$search_str[] = "id IN (SELECT teacher_id FROM teacher_LEAs WHERE LEA = '$local_authority')";
} elseif (!empty($_GET['Agency'])) {
$agency = mysql_real_escape_string($_GET['Agency']);
$search_str[] = "id IN (SELECT teacher_id FROM teacher_agencies WHERE agency = '$agency')";
} else {
// They don't want to search on registered with... so do nothing
}
$total = $_GET["keywords"];
$keyarr = explode(',',$total);
// In order to process this array values here is the code
foreach($keyarr as $key=>$value) {
// becareful to check the value for empty line
$value = trim($value);
if (!empty($value)) {
$search_str[] = "id IN (SELECT teacher_id FROM test_cv_tags WHERE skills = '$value')";
}
}
// Subject/Specalism
if(!empty($_GET['subject'])) {
$subject = mysql_real_escape_string($_GET['subject']);
$search_str[] = "id IN (SELECT teacher_id FROM teacher_specialisms WHERE specalism = '$subject')";
}
if(!empty($_GET['additional_subject'])) {
$additional_subject = mysql_real_escape_string($_GET['additional_subject']);
$search_str[] = "id IN (SELECT teacher_id FROM teacher_additional_subjects WHERE subject = '$additional_subject')";
}
// Ranking
if(!empty($_GET['overall'])) {
$overall = mysql_real_escape_string($_GET['overall']);
$search_str[] = "avg_overall >= $overall ";
} else {
if(!empty($_GET['behaviour'])) {
$behaviour = mysql_real_escape_string($_GET['behaviour']);
$search_str[] = "avg_behaviour_management >= $behaviour ";
}
if(!empty($_GET['ability'])) {
$ability = mysql_real_escape_string($_GET['ability']);
$search_str[] = "avg_teaching_ability >= $ability ";
}
if(!empty($_GET['planning'])) {
$planning = mysql_real_escape_string($_GET['planning']);
$search_str[] = "avg_preperation_planning >= $planning ";
}
if(!empty($_GET['professional'])) {
$professionalism = mysql_real_escape_string($_GET['professional']);
$search_str[] = "avg_professionalism >= $professionalism ";
}
}
This is all brought together like so;
PHP Code:
if(!empty($search_str)){
$sql = "$availability AND ".join(" AND ", $search_str)." AND public_profile = 'Yes' ORDER BY avg_overall DESC";
echo $sql;
} else {
$sql = "$availability AND public_profile = 'Yes' ORDER BY avg_overall DESC";
echo $sql;
}
When I complete my form and echo the completed query it looks something like this;
Code:
SELECT * FROM indivdual_teachers
WHERE 1=1
AND teacher_id IN
(SELECT candidate_id FROM availability_calendar WHERE (DAY = 19 AND MONTH = 04 AND YEAR = 2012 AND availability = "Available"))
AND role=""
AND region = 'North East'
AND id IN
(SELECT teacher_id FROM teacher_agencies WHERE agency = 'Education World')
AND id IN
(SELECT teacher_id FROM test_cv_tags WHERE skills = 'french')
AND id IN
(SELECT teacher_id FROM test_cv_tags WHERE skills = 'golf')
AND id IN
(SELECT teacher_id FROM test_cv_tags WHERE skills = 'spanish')
AND id IN
(SELECT teacher_id FROM teacher_specialisms WHERE specalism = 'Accounting')
AND id IN
(SELECT teacher_id FROM teacher_additional_subjects WHERE subject = 'Art')
AND avg_behaviour_management >= 5
AND avg_teaching_ability >= 5
AND avg_preperation_planning >= 5
AND avg_professionalism >= 5
AND public_profile = 'Yes'
ORDER BY avg_overall DESC
The problem I have is that the part I have highlighted in red should actually be an OR statement in order for the query to work as intended.
I realise why it is an AND statement, simply because it is using this (highlighted in green). I can't simply change the green AND to an OR because that would cause the rest of my query to misfunction;
Code:
$sql = "$availability AND ".join(" AND ", $search_str)." AND public_profile = 'Yes' ORDER BY avg_overall DESC";
However I have been unable to resolve the issue.
Would someone be kind enough to offer me some advice/guidance here?
Many thanks
Before you post, read our: 
