Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25

    how do i create an if clause within a mySQL query sub

    Hi everyone.

    i am trying to create an "if clause" within a sub query of a mySQL QUERY. i have enclosed the query below .


    This is the part of the query that i want to place in an "If Clause"

    PHP Code:

       WHERE   user_id IS NOT NULL
                           
    if(!empty($Co_lo) )
                    {
                         AND  
    job_id IN 1,2,3,4,5,6,7,8,9,10  )
                        } 



    The Search Query.

    PHP Code:


    function worker_general_search (   $Co_lonull$last_login=null ,  $limit=null    )

        
         {             
                 global 
    $dbc;
                          
        
        
    $select "      SELECT 
                     u.user_id, 
                      u.first_name, 
                      cl.country_id,
                      col.country,
                      jb.job_category_sort    
                   
                    " 

        
                      
        
    $from   "     FROM 
                              users u  
                     LEFT OUTER  JOIN   countrylocation cl
                        ON u.user_id = cl.user_id
                     LEFT OUTER  JOIN  countrylist col  
                        ON cl.country_id = col.country_id   
                     
                        
                LEFT OUTER  JOIN 
                    (  SELECT user_id,
                        GROUP_CONCAT(job_id) AS job_category_sort
                       FROM worker_category 
                    
                        WHERE   user_id IS NOT NULL
                           if(!empty(
    $Co_lo) )
                    {
                         AND  job_id IN ( 1,2,3,4,5,6,7,8,9,10  )
                        }
                          
                          GROUP
                         BY user_id
                                                 
                             ) AS jb
                    ON jb.user_id = u.user_id

                    
                    
                        "
    ;
              
                      
    $where      "     WHERE   u.live  = '1'" ;
            
                
    $order=  "     ORDER BY   u.first_name DESC $limit ";  
             
               
    $query $select.$from.$where$order ;
         
    //   $query = $select.$from.$where. $GROUPBY. $order ;
             
        
            
            
    $result = @mysqli_query ($dbc$query)or trigger_error("Query: $query\n<br />MySQL Error: " mysqli_error($dbc))    ;
            
              
             return 
    $result 
             
             
        }      
        
    the function is a search boxso i need the if clause beucase i only want the "AND" condition to apply if  the user specifies itthat is why it needs to be contained within an "IF CLAUSE"

    Thank you so much for your kind attention 

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,567
    ?
    PHP Code:
    $WhereClause '  WHERE   user_id IS NOT NULL';
    if(!empty(
    $Co_lo) )
    {  
    $WhereClause .= ' AND  job_id IN ( 1,2,3,4,5,6,7,8,9,10)';

    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hi everyone

    thank you healdem for his reply. However, the code is still not working. i keep getting the following error report:

    Notice: Query: SELECT u.user_id, u.first_name, cl.country_id, col.country, jb.job_category_sort FROM users u LEFT OUTER JOIN countrylocation cl ON u.user_id = cl.user_id LEFT OUTER JOIN countrylist col ON cl.country_id = col.country_id LEFT OUTER JOIN ( SELECT user_id, GROUP_CONCAT(job_id) AS job_category_sort FROM worker_category = ' WHERE user_id IS NOT NULL'; if(!empty( 1,2,3,4,5,6,7,8,9,10) ) { .= ' AND job_id IN ( 1,2,3,4,5,6,7,8,9,10)'; } GROUP BY user_id ) AS jb ON jb.user_id = u.user_id WHERE u.live = '1' ORDER BY u.first_name DESC
    MySQL Error: You have an error in your SQL syntax; check the manua in db_fns.php on line 112
    I have tested the code without the addiction of the variables and "if clause" and it works perfectly; so,the thing causing the syntax error is the addiction of the variables and the "if clause" within the subquery.

    i have enclosed my full SQL function below.


    Does anyone have any advice or recommendation of how i should proceed

    warm regards

    Andreea


    PHP Code:


    function worker_general_search (   $Co_lonull$jobs_cat=null,  $limit=null    )

        
         {             
                 global 
    $dbc;
                
    $WhereClause='';   
                            
                      
                      
        
    $select "      SELECT 
                     u.user_id, 
                      u.first_name, 
                      cl.country_id,
                      col.country,
                      jb.job_category_sort    
                   
                    " 

        
                      
        
    $from   "     FROM 
                              users u  
                     LEFT OUTER  JOIN   countrylocation cl
                        ON u.user_id = cl.user_id
                     LEFT OUTER  JOIN  countrylist col  
                        ON cl.country_id = col.country_id   
                        
                LEFT OUTER  JOIN 
                    (  SELECT user_id,
                        GROUP_CONCAT(job_id) AS job_category_sort
                       FROM worker_category 
                  
    $WhereClause = '  WHERE   user_id IS NOT NULL';
                if(!empty(
    $jobs_cat) )
                {  
    $WhereClause .= ' AND  job_id IN ( $jobs_cat)';
                }               
                          GROUP
                         BY user_id
                                                 
                             ) AS jb
                    ON jb.user_id = u.user_id

                        "
    ;
              
                      
    $where      "     WHERE   u.live  = '1'" ;
                  
                
                      
                      if(!empty(
    $Co_lo) )
                    {
                      
    $where .= "    AND    cl.country_id = '$Co_lo'";
                    }         
                       
            
               
                            
                
    $order=  "     ORDER BY   u.first_name DESC $limit ";  
             
               
    $query $select.$from.$where$order ;
            
            
    $result = @mysqli_query ($dbc$query)or trigger_error("Query: $query\n<br />MySQL Error: " mysqli_error($dbc))    ;
            
              
             return 
    $result 
             
             
        }              
        
        
    $jobs_cat    =" 1,2,3,4,5,6,7,8,9,10";
           
    $Co_lo='';            
           
    $worker_search   =    worker_general_search $Co_lo ,    $jobs_cat          ); 

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,567
    that 'if' clause is PHP building a fragment of SQL in your where clause it is not valid SQL.

    im ghuessing what you really want is soemthign sort of similar to:-

    PHP Code:
    if(!empty($Co_lo) )  //if the user has specified a co_lo add it
    $Where " AND cl.country_id = '$Co_lo'"
    } else 
    //set our defaults
    {  $Where .= ' AND  job_id IN ( 1,2,3,4,5,6,7,8,9,10)';

    which repalces
    PHP Code:
     if(!empty($Co_lo) ) 
                    { 
                      
    $where .= "    AND    cl.country_id = '$Co_lo'"
                    } 
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hello again everyone

    special thanks to healdem and to R937 for their help.

    i applied the changes and it indeed worked. i have enclosed the code below.

    i have one final question on this issue that i am not clear about. You will note from my code that i have applied the " if clause " ( equating multiple values to multiple values) in a subquery of my FROM CLAUSE of my query.


    but this is not acting as a filter. the reason for my equating the conditions was to be able to filter out users whose profiles do not match with the given multiple values.

    Should i not therefore be doing the equating of values ( the "if clause") in the WHERE CLAUSE of my query.

    i wold really appreciate some advice on this. sorry to take up so much of everyone time, but i do feel that this is the final hurdle on this matter.



    PHP Code:


    function worker_general_search (   $Co_lonull$jobs_cat=null ,  $limit=null    )

        
         {             
                 global 
    $dbc;
                
    $WhereClause='';   
                            
            
                       if(!empty(
    $jobs_cat) )  //if the user has specified a co_lo add it
                    
    $WhereClause "   AND  job_id IN ( $jobs_cat)"
                    } else 
    //set our defaults
                    
    {  $WhereClause '';
                    }  
                        
                      
        
    $select "      SELECT 
                     u.user_id, 
                      u.first_name, 
                      cl.country_id,
                      col.country,
                      jb.job_category_sort    
                   
                    " 

        
                      
        
    $from   "     FROM 
                              users u  
                     LEFT OUTER  JOIN   countrylocation cl
                        ON u.user_id = cl.user_id
                     LEFT OUTER  JOIN  countrylist col  
                        ON cl.country_id = col.country_id   
                        
                    LEFT OUTER  JOIN 
                    (  SELECT user_id,
                        GROUP_CONCAT(job_id) AS job_category_sort
                       FROM worker_category 
                       WHERE   user_id IS NOT NULL   
                       
    $WhereClause
                          GROUP
                         BY user_id
                     ) AS jb
                    ON jb.user_id = u.user_id

                        "
    ;
              
                      
    $where      "     WHERE   u.live  = '1'" ;
                  
                                  
                      if(!empty(
    $Co_lo) )
                    {
                      
    $where .= "    AND    cl.country_id = '$Co_lo'";
                    }         
                       
            
               
                            
                
    $order=  "     ORDER BY   u.first_name DESC $limit ";  
             
               
    $query $select.$from.$where$order ;
         
    //   $query = $select.$from.$where. $GROUPBY. $order ;
             
        
            
            
    $result = @mysqli_query ($dbc$query)or trigger_error("Query: $query\n<br />MySQL Error: " mysqli_error($dbc))    ;
            
              
             return 
    $result 
             
             
        }              

    //$Co_lo ,    $jobs_cat 

                                  
    $jobs_cat    =" 1,2,3,4,5,6,7,8,9,10";
                    
                                     
    //   $jobs_cat    ="'7'";$Co_lo,  $jobs_cat  
                                         
    $Co_lo='';
                                        
        
                                         
               
    $worker_search   =    worker_general_search (  $Co_lo$jobs_cat     ); 

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,567
    SQL select, selects the columns to be displayed
    the where clause filters the rows to be returned, essentially each part of the where clause must return true for a row to be returend, but as its using boolean logic you can use OR AND NOT and so on to make a compound where clause

    so you could use somethign like
    where (ProduceType = 'Citrus' AND Produce NOT in ('Orange', 'Lemon')) OR ProduceType = 'Legumes' OR Produce like 'Green%'
    to return all rows which
    are Citrus (fruits) but NOT Oramges or Lemons
    OR whose ProductType is Legumes
    OR whose Product starts with Green

    there is nothing stopping you putting in mutually exclusive terms such as
    where ProduceType = 'Citrus' AND Produce = like 'Potato'
    after all SQL isn't all knowing if you ask it a stupid question it will return what it can.

    so if this SQL statement isn't doing what you expect then look at your where clause(s). they are the ones that filter out the unedded rows. if you are not gettimng only the rows you expect then look to the where clause
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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