foreach select query how to shorten

Question

hello i have a select query that is integrated in a search function: i have this code:

foreach ($keywords as $key=>$keyword){
$where .= "`column_a` LIKE '%$keyword%'";
$where .= " OR ";
$where .= "`column_b` LIKE '%$keyword%'";
$where .= " OR ";   
$where .= "`column_c` LIKE '%$keyword%'";
$where .= " OR ";
$where .= "`column_d` LIKE '%$keyword%'";
$where .= " OR ";
$where .= "`column_e` LIKE '%$keyword%'";

    if ($key != ($total_keywords - 1)){ 
        $where .= " AND ";
    }
}

$results = "SELECT `column_a`, `column_b`, `column_c`, `column_d`, `column_e` FROM `table` WHERE $where";

okay, in case of searching for just one keyword the query looks like:

SELECT `column_a`, `column_b`, `column_c`, `column_d`, `column_e` FROM `table` WHERE `column_a` LIKE '%one%' OR `column_b` LIKE '%one%' OR `column_c` LIKE '%one%' OR `column_d` LIKE '%one%' OR `column_e` LIKE '%one%'

what is pretty long. now in case of having two searchterms it will look like:

SELECT `column_a`, `column_b`, `column_c`, column_d`, `column_e` FROM `table` WHERE `column_a` LIKE '%one%' OR `column_b` LIKE '%one%' OR `column_c` LIKE '%one%' OR `column_d` LIKE '%one%' OR `column_e` LIKE '%one%' AND `column_a` LIKE '%two%' OR `column_b` LIKE '%two%' OR `column_c` LIKE '%two%' OR `column_d` LIKE '%two%' OR `column_e` `LIKE` '%two%'

what is definetly too long. so my question is, is there another way to get the same result? i already know that there is MATCH() AGAINST() but this hasnt worked for a unknown reason.

so if there is someone who could help me, i would really appreciate. thanks a lot.

score 0 · Accepted Answer · edited May 23 '17 at 10:24

0

Check out this answer.

It will shorten your query, up to 5 conditions will always be enough.

More than that, you can join all columns to one and search in it:

SELECT `column_a`, `column_b`, `column_c`, column_d`, `column_e` FROM `table` 
WHERE CONCAT(column_a, column_b, column_c, column_d, column_e) REGEXP 'one|two'

But full-text indexes (if you have them) possibly may not work in this query. Check if performance is appropriate.

edited May 23 '17 at 10:24

Community

1
1

answered Apr 22 '12 at 22:24

Pavel Strakhov

39,123
5
88
127

that is totally different to what i am looking for. regexp is made for the keyword expression and not for colums! i i'm looking for some method like : SELECT `column_a`, `column_b`, `column_c` FOR `table` WHERE -for each<- LIKE $keyword – bonny Apr 22 '12 at 22:33
But if you use regexp, it will actually shorten your query a bit. Anyway, I updated my answer with another solution. – Pavel Strakhov Apr 22 '12 at 22:41

foreach select query how to shorten

1 Answers1