Search code examples
mysqlsqlsql-likeautosuggest

Mysql multiple LIKE not working

I have a stores table. Some fields are name,website,description,etc.

I have an autosuggest search bar. I want to match input and display some related stores. Right now it works perfectly matching user input with a LIKE statement for name.

$input = $_GET["q"];
$data = array();
// query your DataBase here looking for a match to $input
include_once "scripts/connect_to_mysql.php";
$query = mysql_query("SELECT name,linklabel,permalink FROM tblMerchants WHERE name LIKE '%$input%'");
while ($row = mysql_fetch_assoc($query)) {
    $json = array();
    $json['name'] = ucwords($row['linklabel']);
    $json['value'] = $row['permalink'];
    $data[] = $json;
}
echo json_encode($data);

However, I'm trying to match user input with multiple fields in the table. I'm trying the following and it isn't working:

SELECT linklabel,permalink
FROM tblMerchants
WHERE name LIKE '%$input%'
 OR website LIKE '%$input%'
 OR description LIKE '%$input%'

Trying to figure this out. Any input is appreciated!

Sorry, should have mentioned this. By not working I mean that when using the multiple LIKE statement its still only returning results that are like 'name'

Solution

  • likes and ands are notorius in not playing well with each others.

    Use parentherses to clarify, e.g.

    SELECT linklabel,permalink 
FROM tblMerchants WHERE 
(name LIKE '%$input%') OR 
(website LIKE '%$input%')  OR 
(description LIKE '%$input%')

    Otherwise it can be trying to guess and do things like

    SELECT linklabel,permalink 
FROM tblMerchants WHERE 
name LIKE ('%$input%' OR 
website LIKE '%$input%')  OR 
(description LIKE '%$input%')

    I don't don;t if the above is an actual query that it would try, but basically the way it would combine/chain them could be 'undefined' and subject to interpretation.