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

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    Multiple Where Clauses


    I am Simplifying my Question here with data as well.

    *I have a String of keywords that i have to search with my database's fields.
    *I have 4 fields(variables) upon which i have to apply Where clause.
    * I wanted to have a query that fetch data either one of 4 where clauses or all of them(OR operation in Where)

    Here is the Php Code that extracts keywords from string and make query.

    PHP Code:
    $condition strtoupper($_GET['iname']); //for keywords string
    $operation $_GET['operation'];//Operation Type AND OR between Keywords
    //these are four flags determining that which where clause is present
    $titleflag "false";
    $descriptionflag "false";
    $productoldflag "false";
    $productnewflag "false";
    //if title present
    if(/*title present or Not*/)//if Not
    {
               
    $titleflag "false";
        
    $title ='';
                
    $titlearray[] = "upper(name) LIKE '%%'";       
    }
    else
    {
        
    $title $condition;
        
    $titleflag "true";
                
    //***************************
                
    $search strtoupper($title);
                
    $search trim($search);
                
    $terms explode(' '$search);
                
    $count 0;
                
    $line="";
                foreach (
    $terms as $term) {
                    if((
    $term == " "))
                    {                    
                        if(
    $count == 0)
                           {
                                
    $count++;
                                
    $titlearray[] = "upper(name) LIKE '%".$term."%'";                            
                            }
                        else            
                            continue;
                    }
                    else    
                    {
                        
    $titlearray[] = "upper(name) LIKE '%".$term."%'";                    
                    }
                }            
    }
    //This is it. i did for three other where clauses
    //description,misold,misnew
    //below down is my query where i construct the actual query
    $sql "Select distinct id,name from item WHERE 
                   ("
    .implode$operation ." "$titlearray).") AND ".$titleflag."  
                OR ("
    .implode$operation ." "$descriptionarray).") AND "$descriptionflag
                OR ("
    .implode$operation ." "$oldmisarray).") AND "$productoldflag
                OR ("
    .implode$operation ." "$newmisarray).") AND "$productnewflag
                order by name asc"


    //Provided Search String 
    /*
    $condition = "Altera EMP";
    */
    //If value in $operation = AND then produced query will be look like

    /*  AND OPeration
    Select distinct id,name from item 
    WHERE (upper(name) LIKE '%ALTERA%' AND upper(name) LIKE '%EMP%') AND true 
    OR (upper(description) LIKE '%ALTERA%' AND upper(description) LIKE '%EMP%') AND true 
    OR (upper(productnoold) LIKE '%%') AND false 
    OR (upper(productnonew) LIKE '%%') AND false 
    order by name asc
    */

    /* Or Operation
    Select distinct id,name from item 
    WHERE (upper(name) LIKE '%ALTERA%'OR upper(name) LIKE '%EMP%') AND true 
    OR (upper(description) LIKE '%ALTERA%'OR upper(description) LIKE '%EMP%') AND true 
    OR (upper(productnoold) LIKE '%%') AND false 
    OR (upper(productnonew) LIKE '%%') AND false 
    order by name asc
    */

    Now what the requirement is that I must use OR in every Keyword for all Where and hence forth for every keyword. and combine the result of all keyword search either AND or OR 
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,990
    Rep Power
    375
    this is too confusing for me, I would maybe do:

    PHP Code:

    $search 
    $_get['SEARCH']
    $operator $_get['operator']

    $search_terms explode (" ",$search);

    foreach ( 
    $search_terms as $t ) {
     
    $where[] = "field1 $operator $t"
    }

    $where implode (" OR "$where); 
    i might have made mistake somewhere in assuming things..
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    This is what i have done..


    PHP Code:
    //keywordlist is an array of from a string
    for ($row 0$row count($terms); $row++)
            {
                
    $sql "Select distinct id,name from item WHERE 
                 (upper(name) like '%"
    $keywordslist[$row]."%' AND ".$flagtitle.")  
                OR (upper(description) like '%"
    $keywordslist[$row]."%'  AND "$flagdesc") 
                OR (upper(productnoold) like '%"
    $keywordslist[$row]."%'  AND "$fladmisold") 
                OR (upper(productnonew) like '%"
    $keywordslist[$row]."%' AND "$flagmisnew") 
                 order by id asc"
    ;
                
    $result pg_query($connection,$sql);                
                
    $singlearray pg_fetch_all($result);
                
    //Adding to bigger dataset for AND OR Operation to each of the data set individualy
                
    if(count($singlearray) > 0)
                {
                    
                    
    $twodarray[$row] = $singlearray;
                }   
                     
            } 
    Thanks I have done it.
    I will try yours method as well

IMN logo majestic logo threadwatch logo seochat tools logo