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 2 of 2
  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts

    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 ) 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_') == 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
    Last edited by greens85; 04-18-2012 at 12:01 PM.

  • #2
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Still not solved this one...

    Can anyone help?


  •  

    Posting Permissions

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