mysql AND statement

Question

I am trying to search through the columns 'product', 'description', and 'keywords' in my database. The problem is that when I type in a word that is in the 'description' or 'keywords' column of the table, but not in the 'product' column, it doesn't display that result. It only displays the results if I type a word that is in the 'product' column of the table.

I'm not sure if the AND statement is doing what I want. I want to search for words within these 3 columns that are "like" what the user searched for, and sort the results by relevancy. Any help would be appreciated.

$search_exploded = explode(" ",$searchquery);

foreach($search_exploded as $search_each)
{
    $x++;
    if ($x==1)
        $construct .= "product LIKE '%$search_each%' AND description LIKE '%$search_each%' AND keywords LIKE '%$search_each%'";
    else
        $construct .= " OR product LIKE '%$search_each%' AND description LIKE '%$search_each%' AND keywords LIKE '%$search_each%'";
}

$sqlCommand = "SELECT * FROM Search WHERE $construct ORDER BY MATCH (product,description,keywords) AGAINST ('$searchquery') DESC";

Right now the word has to be in the description, keywords, `AND` product fields, but it sounds like you're wanting it to be in any of the fields (so you should be using `OR` instead). Or am I not following what you're asking about? — Kitsune, Dec 07 '12 at 04:43

score 0 · Answer 1 · answered Dec 07 '12 at 04:41

0

I want to search for words within these 3 columns

so you need to use OR instead of AND :

product LIKE '%$search_each%' OR description LIKE '%$search_each%' OR keywords LIKE '%$search_each%'

This will display the result if the word searched by user is in any of the three columns.

answered Dec 07 '12 at 04:41

Abubakkar

15,488
8
55
83

I just knew it was something simple like that. Thank you. – Jeff Dyer Dec 07 '12 at 04:51

score 0 · Answer 2 · answered Dec 07 '12 at 04:50

When I do this sort of thing, I like to split up my logic and then glue the pieces together using implode(). Something like:

$searchTerms = explode(" ",$searchquery);

$clauses = array();

foreach ($searchTerms as $searchTerm)
{
    $subClauses = array();
    $columns = array("product", "description", "keywords");
    foreach ($columns as $column)
    {
        $subClauses[] = " $column LIKE '%$searchTerm%' ";
    }

    $clauses[] = "(" . implode(" OR ", $subClauses) . ")";
}

$query = implode(" AND ", $clauses);

I find the logic easier to understand this way. Here, we are saying: For each search term, a match must be found in the product column or the description column, or the keywords column.

Here it is in action: http://eval.in/4335

mysql AND statement

2 Answers2