PHP/MySQL Problems

[greens85]

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:

 

// 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') 

[color=red]AND[/color] id IN 

(SELECT teacher_id FROM test_cv_tags WHERE skills = 'golf')

[color=red]AND[/color] 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(" [color=green]AND[/color] ", $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.

[Muddy_Funster]

why not make a little function to build the WHERE for you? it would make the page much easier to manage.  Here's a simple example, this function takes in a 2 tear multidimentional array(so it's an array of arrays) with the 2nd tear havin the following in this order :

'PREFIX' | 'FIELD' | 'OPERATOR' | 'VALUE'

function buildWhere($arrVals){
$out = '';
foreach($arrVals as $vals){
$out .= "{$vals['0']} {$vals['1']} {$vals['2']} {$vals['3']} ";
}
return $out;
}

$where[] = array('WHERE', 'teacher_id', 'IN', '(SELECT candidate_id FROM availability_calendar WHERE (DAY = 19 AND MONTH = 04 AND YEAR = 2012 AND availability = "Available"))');
$where[] = array('AND', 'role_level', '=', '\'\'');
$where[] = array('OR', 'religion', '=', '\'North East\'');
$where[] = array('AND', 'id', 'IN', '(SELECT teacher_id FROM teacher_agencies WHERE agency = \'Education World\')');

$sql = "SELECT * FROM indivdual_teachers ";
$append = buildWhere($where);
$sql = $sql.$append;

echo $sql;

[greens85]

Hi Muddy_Funster,

 

Many thanks for your reply.

 

I guess in answer to your question, the only reason I won't have done it in that way is because I simply don't have the knowledge of PHP. I just put it together in a way I understood and one where I could get it to work. I have looked at your example and to be honest, not really fully understanding it just yet.

 

That said, if I did get it working in that manner.. I still wouldn't understand how to integrate the OR parts... In between my post and your response I did managed to change the AND's to OR's however it seems like it is just picking anything where the skills match and ignoring the rest of the query.

 

e.g.

 

SELECT * FROM indivdual_teachers 

WHERE 1=1 

AND role="" 

AND region = 'North West' 

AND id IN 

(SELECT teacher_id FROM teacher_agencies WHERE agency = 'Academic Appointments') 

AND id IN 

(SELECT teacher_id FROM teacher_specialisms WHERE specalism = 'Early Years') 

AND id IN 

(SELECT teacher_id FROM teacher_additional_subjects WHERE subject = 'Drama') 

AND avg_behaviour_management >= 4 

AND avg_teaching_ability >= 5 

AND avg_preperation_planning >= 5 

AND avg_professionalism >= 5 

AND id IN 

(SELECT teacher_id FROM test_cv_tags WHERE skills = 'golf') 

OR id IN 

(SELECT teacher_id FROM test_cv_tags WHERE skills = 'swimming') 

OR id IN 

(SELECT teacher_id FROM test_cv_tags WHERE skills = 'chess') 

AND public_profile = 'Yes' 

ORDER BY avg_overall DESC

 

In this example, if they have a skills of swimming they will be returned in the results despite the fact that their region isn't North West and they don't specialise in Early Years or Drama.

 

[Muddy_Funster]

ok, let me comment the code and see if that helps:

function buildWhere($arrVals){ // declare the function, and the name of the variable within it
$out = ''; // set the $out variable to empty
foreach($arrVals as $vals){ // loop through each of the contents of the $arrVals array, assiging the content to $vals
$out .= "{$vals['0']} {$vals['1']} {$vals['2']} {$vals['3']} "; // add the contnents of each of the $vals array values to the $out variable
}//end of loop
return $out; // send the $out variable out of the function
}//end of function

//now we make an array to hold each of the combinations of the where clause that we want to process, then pass in these parts as individual arrays in the order that we want them to be processed.
//we build these using the format PREFIX(WHERE AND or OR), FIELD(field from the table that we are using in the comparison), OPERATOR(things like =, LIKE, >, <, IN, HAVING etc), VALUE(the value for the comparison)
$where[] = array('WHERE', 'teacher_id', 'IN', '(SELECT candidate_id FROM availability_calendar WHERE (DAY = 19 AND MONTH = 04 AND YEAR = 2012 AND availability = "Available"))'); //assign first part of WHERE clause
$where[] = array('AND', 'role_level', '=', '\'\''); //assign another part
$where[] = array('OR', 'religion', '=', '\'North East\''); //and another
$where[] = array('AND', 'id', 'IN', '(SELECT teacher_id FROM teacher_agencies WHERE agency = \'Education World\')'); //and another

$sql = "SELECT * FROM indivdual_teachers "; // start of the SELECT query
$append = buildWhere($where); // run the buildWhere function, passing in the $where array we just made above, and assigning the output of that function to the $append variable
$sql = $sql.$append; // make the $sql variable = to the combined value of the inital $sql value and the value now in $append

echo $sql; // show the value now in $sql

Does that make it easier to follow? This is a self contained snippet of code so you can stick it in a new .php file and view the output of $sql on the screen as well if that helps.

[greens85]

Thanks for taking time to comment it... A couple of things;

 

foreach($arrVals as $vals){ // loop through each of the contents of the $arrVals array, assiging the content to $vals

 

I presume here $arrVals will be each of my $_GET var... as it stands it's something like;

 

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 Type
			if($_GET['staff_required'] == 'Non Teaching Staff') {

				$search_str[] = "role !=\"\"";

			} elseif($_GET['staff_required'] == 'Teaching Staff') {

				$search_str[] = "role=\"\"";

			} else {

			}

 

Obviously there is a $_GET for each of my form elements.

 

$where[] = array('WHERE', 'teacher_id', 'IN', '(SELECT candidate_id FROM availability_calendar WHERE (DAY = 19 AND MONTH = 04 AND YEAR = 2012 AND availability = "Available"))'); //assign first part of WHERE clause

 

$where[] = array('OR', 'religion', '=', '\'North East\''); //and another

 

Here I am a little confused, you have:

 

(SELECT candidate_id FROM availability_calendar WHERE (DAY = 19 AND MONTH = 04 AND YEAR = 2012 AND availability = "Available")

 

'\'North East\'

 

These appear to be hard coded but obviously they are variables and can change depending on what was submitted as part of the form.

 

(SELECT candidate_id FROM availability_calendar WHERE (DAY = 19 AND MONTH = 04 AND YEAR = 2012 AND availability = "Available")

[Muddy_Funster]

you would need to build the $where array using your $_GET varaibles, the $arrVals is the $where array.  functions have what's called limited scope. it meens that variables outside them don't affect them, and likewise variables insede them don't affect anything outside them.  when we call the function we pass the $where array into it like so

buildWhere($where)

but in order for the function to know that it's going to have information passed into it, and for the function to be able to address that information we need to declare a varable name that the function can use at the time we are creating it i.e.

function buildWhere($arrVals){

so you see, $arrVals is just what the function calls $where - it maps it if you like.

 

I used hard coded values so that it would be stand alone.  you could just as easily do the following:

$religion = mysql_real_escape_string($_GET['religion']);
...
$where[] = array('OR', 'religion', '=', "'$religion'"); //and another
...

 

remember to use double quotes if using variables directly within strings.

[greens85]

Thanks for that...

 

Ok, so I've managed to produce some sql output using your method.

 

My SQL output now looks like this;

 

SELECT * FROM indivdual_teachers WHERE teacher_id IN (SELECT candidate_id FROM availability_calendar WHERE (DAY = 19 AND MONTH = 04 AND YEAR = 2012 AND availability = "Available")) AND role = 'Teaching Staff' AND region = 'London' AND id IN (SELECT teacher_id FROM teacher_agencies WHERE agency = 'Education World')

 

However going back to my original problem, I am still no clearer on how I can add a statement along the lines of;

AND id IN 

(SELECT teacher_id FROM test_cv_tags WHERE skills = 'golf') 

OR id IN 

(SELECT teacher_id FROM test_cv_tags WHERE skills = 'swimming') 

OR id IN 

(SELECT teacher_id FROM test_cv_tags WHERE skills = 'chess') 

 

That will return results based on those keywords but also take the rest of the query into account.

 

I presume it's got to be along the lines of

 

$where[] = array('AND', 'id', 'IN', '(SELECT teacher_id FROM test_cv_tags WHERE skills = '$value')');

?

 

[Muddy_Funster]

yip :

$where[] = array('AND', 'id', 'IN', "(SELECT teacher_id FROM test_cv_tags WHERE skills = '$value1')");
$where[] = array('OR', 'id', 'IN', "(SELECT teacher_id FROM test_cv_tags WHERE skills = '$value2')");
$where[] = array('OR', 'id', 'IN', "(SELECT teacher_id FROM test_cv_tags WHERE skills = '$value3')");