CODEWITHSUNDEEP
phpmysqlsearch
pixi
pixi

Reputation: 5

sql search with like or contains

I have a table with 2 columns named column_A and column_B .

One row in this table contains in column_A the Value AAA and the column_B the value BBB.

If I search for AAA or BBB with

SELECT * FROM table WHERE column_A LIKE '%AAA%' OR column_B LIKE '%AAA%';

or

SELECT * FROM table WHERE column_A LIKE '%BBB%' OR column_B LIKE '%BBB%';

I get a result.

However if I serach for '%AAA BBB%' or '%AAA xyz%' I get no result.

The value I search for comes from a search form. As long the user types in only BBB or AAA all is fine, the moment he uses a combination of AAA or BBB and something else the search does nor work.

How can i fix that? Do i need to split up the search term like mentioned here: http://www.iamcal.com/publish/articles/php/search/

Upvotes: 0

Views: 161

Answers (3)

pixi
pixi

Reputation: 5

Thanks for all comments, i finally solved like that:

$string = $_GET['searchterm'];

$words = EXPLODE(" ",$string);

FOR($i=0;$i<count($words);$i++){$SQLOPTION  .= "AND CONCAT(column_A,column_B) LIKE '%".$words[$i]."%'";}

and then added the $SQLOPTION value to my query.

Upvotes: 0

Suchit kumar
Suchit kumar

Reputation: 11859

Since you have tagged php then i can suggest something like this:

 <?php 
 $res ="AAA BBB CCC";// your input from use
 $res=explode(' ', $res);
 $sql="SELECT * FROM table where column_A";
 $flag=false;
 for($i =0;$i< sizeof($res);$i++){

    if(!$flag){
        $flag=true;
        $sql .=" like '%".$res[$i]."%'";
    }else{

        $sql .=" or like '%".$res[$i]."%'";
    }


 }
 echo $sql;// resultant sql need not to echo only to show

Upvotes: 0

SMA
SMA

Reputation: 37023

You can do it with regexp like below to search either for AAA or BBB in column_A:

 SELECT * FROM table WHERE column_A REGEXP '.*[A|B]{3}.*$'

Upvotes: 2

Related Questions