#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3

    Help with the correct way to build a search facility in mysql


    Hello everyone

    i am trying to build a function to search a MYSQL database. the function should enable member to search;
    1. default search ( show all members.)
    2. members by the country they live in
    3. members by their nationality.

    I built the initial search function which showed all members by default.

    but i now want to, built into this fuction, the ability to refine the search.

    it might be best at this point if i show u what i have done.


    PHP Code:

    [U]THE DEFAULT SEARCH : [/U]

     
    function 
    members_search (  $start NULL ,  $display  NULL  )
        
         {              
                        
                 global 
    $dbc;
            
        
        
    $select " SELECT  u.first_name, u.user_id ,u.membership_type, l.nationality, l.country_loc,
                            CONCAT( l.startDate,'- ', l.enddate)traveldate, 
                   " 

                        
                      
        
    $from   " FROM 
                        users u  LEFT OUTER JOIN aupair_location l
                        ON u.user_id = l.user_id "
    ;
                   
                   
    $where      "  WHERE 
                                         u.membership_type = 'Traveller'  "

                      
         
    $order=  "ORDER BY
                          u.user_id DESC LIMIT 
    $start$display ";    
        
         
         
    $query $select.$from.$where$order ;
         
    $result = @mysqli_query ($dbc$query);
         
         return 
    $result 
             
             
        }


    //however i now want to alter this search function to enable //user to be able to refine their search.

    [U]THE REFINED SEARCH FUNCTION  [/U]



        
        function 
    members_search $natNULL,  $country_loc=null  ,  $start NULL ,  $display  NULL )
        
         {              
                        
                 global 
    $dbc;
            
        
    $select " SELECT  u.first_name, u.user_id , l.nationality, l.country_loc,
                        CONCAT( l.startDate,'- ', l.enddate)traveldate,u.membership_type
                       
                   
                   " 

                        
                      
        
    $from   " FROM 
                     users u  LEFT OUTER JOIN aupair_location l
                     ON u.user_id = l.user_id
                                     
                    "
    ;
                
                    if ( (!empty( 
    $nat))  && (!empty( $country_loc)) )
                    {                         
                   
                   
    $where      "  WHERE 
                        u.membership_type = 'Traveller' 
                   AND  l.nationality ='
    $nat'               
                   And  l.country_loc='
    $country_loc '      "
                     }
                    else   
                     {                         
                   
                   
    $where      "  WHERE 
                        u.membership_type = 'Traveller'  "

                       
                    }
                      
            
         
    $order=  "ORDER BY u.user_id DESC LIMIT $start$display ";    
         
    $query $select.$from.$where$order ;
        
         
    $result = @mysqli_query ($dbc$query);
            
              
             return 
    $result 
             
             
        } 


    You will note above that i attempted to refine the search by placing optional variables in the "WHERE " CLAUSE.

    The options actaully work. However the default 'WHERE clause' does not work i.e;

    PHP Code:

     
    else   
                     {                         
                   
    $where      "  WHERE 
                        u.membership_type = 'Traveller'  "


                    } 

    HOWEVER, i also have concerned regarding whether this is the best way to write this function. For example, what if i wanted to have 10 different search options: i.e search by time in country, search by age visitor etc. It would mean taht i would have to create 40 different variables to accomodate the fact that a user might imput data into one of the 39 varabiles and not the others.


    so, please, any help advice on the best way to build a search function would be greatly appriciated.

    warm regards

    Andreea
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7171
    Rather than having a block for each combination of input parameters, just have one block per input parameter. Just append conditions progressively to your existing ones.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3
    Hello Again Everyone

    and thankyou to E-Ereo for his suggestion.

    i did initally try this solution but it did not work becuase it would mean that i would have a lot of empty variables within the 'WHERE CLAUSE'.

    Is it possible for anyone to show me how to progressevly add condition to the where clause; i am not sure of the correct way to do it.
    PHP Code:
                    if(!empty( $nat)) 
                    {                         
                   
                   
    $where      "  WHERE 
                        u.membership_type = 'A' 
                   AND  l.nationality ='
    $nat'               
                        
                        "

                    } 
  6. #4
  7. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7171
    For this particular strategy to work you have to have one condition that is always present regardless of which optional parameters are defined. If you don't, you can use simple boolean algebra to introduce a condition that has no effect on your query (ie: when using AND, you would use 1=1; when using OR, you would use 0=1).

    PHP Code:
    $where "WHERE 1=1";
    if(
    $condition1)
    {
      
    $where .= " AND condition1 = '{$condition1}'";
    }
    if(
    $condition2)
    {
      
    $where .= " AND condition2 = '{$condition2}'";
    }
    // etc 
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3
    Hello Again Everyone

    and thankyou again to E-Ereo for his 2nd suggestion.

    i tried the suggestion but it only works if the conditions are NOT EMPTY'. However if the condition are left blank then the whole query return nill results.

    i am a bit confused about this because I would have thought that if, the condition of a block are NOT met then the SQL query would not take affect. It seems however that the sql query is still reading the condition, hence the nill result.

    please look below at my myql QUERY.

    i taand


    PHP Code:


    function members_search $natNULL ,   $start NULL ,  $display  NULL  )
        
         {              
                        
                 global 
    $dbc;
            
        
        
    $select " SELECT  u.first_name, u.user_id ,u.membership_type, l.nationality, l.country_loc,
                            CONCAT( l.startDate,'- ', l.enddate)traveldate, 
                   " 

                        
                      
        
    $from   " FROM 
                        users u  LEFT OUTER JOIN aupair_location l
                        ON u.user_id = l.user_id "
    ;
                   
        
    $where      "  WHERE 
                          u.membership_type = 'Traveller'  "

                      
                       
        if(!empty(
    $nat) )
                {
                  
    $where .= "    AND    l.nationality = '{$nat}'";
                }         
                       
                      
                                         
    $order=  "ORDER BY
                          u.user_id DESC LIMIT 
    $start$display ";    
        
         
         
    $query $select.$from.$where$order ;
         
    $result = @mysqli_query ($dbc$query);
         
         return 
    $result 
             
             
        }
        
        
    $nat=' '
            
         
         
    $detailedview     =   members_search ($nat   ,    1   ,  20     ); 
    you will note that the $nat is set as nill. thus the condition
    if(!empty($nat) )
    {
    $where .= " AND l.nationality = '{$nat}'";
    }

    should not actaully be read. but the SQL QUERY is still reading this condition.

    Any advice on what i have done wrong and how to proceed would be grately appriciated.

    warm regards


    Andreea
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jun 2007
    Posts
    1,513
    Rep Power
    1424
    ' ' isn't empty, but it is a string containing a space character.

    Regards, Jens
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3

    Help with the correct way to build a search facility in mysql


    Hello again everyone

    and thank you to jen for pointing out that

    $nat = ' ' ;

    was not an empty string . i was not aware of that, but it makes sence now.

    i was wondering therefore, how do i return an empty string.

    or how do i make the

    variable $nat return an empty string

    thank you everyone for your kind assistance

    warm regards

    Andreea
  14. #8
  15. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7171
    delete the space
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo