If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > PHP > how do i create an if clause within a mySQL query sub

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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 
Reply With Quote
  #2 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,655
?
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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:

Quote:
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          ); 
Reply With Quote
  #4 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,655
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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     ); 
Reply With Quote
  #6 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,655
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On