PHP/mysql problems

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 :D) through the use of PHP.

The PHP that is generating the statement looks like so:

// 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;

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;

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;

$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

Still not solved this one...

Can anyone help?