PHP SQL Select Where Like Search Item with Multiple Words

PHP SQL SELECT where like search item with multiple words

So you want an AND search using each of the words entered, rather than the exact string? Howabout something like this:

$searchTerms = explode(' ', $bucketsearch);
$searchTermBits = array();
foreach ($searchTerms as $term) {
    $term = trim($term);
    if (!empty($term)) {
        $searchTermBits[] = "bucketname LIKE '%$term%'";
    }
}

...

$result = mysql_query("SELECT * FROM buckets WHERE ".implode(' AND ', $searchTermBits).");

this will give you a query like:

SELECT * FROM buckets WHERE bucketname LIKE '%apple%' AND bucketname LIKE '%and%' AND bucketname LIKE '%pear%'

change the AND to an OR if you want to match any of the search terms rather than all. Further improvements could involve defining some stop words like 'and' to give better results.

Mysql, PHP, searching for multiple words

There are two ways to do this. The first is the rather obvious approach. Let's say you have all the words that need to appear in an array called $necessaryWords:

$sql = 'SELECT ... FROM ...'; // and so on
$sql .= ' WHERE 1';

foreach ($necessaryWords as $word)
    $sql .= ' AND concat(subject,body) LIKE "%' . $word . '%"'; //Quotes around string

However, using %foo% is rather slow, as no indexes can be used, so this query might cause performance issues with huge tables and/or a high number of necessary words.

The other approach would be a FULLTEXT index on subject and body. You could the use the fulltext MATCH IN BOOLEAN MODE like this:

$sql = 'SELECT ... FROM ...'; // and so on
$sql .= ' WHERE MATCH(subject,body) AGAINST("';

foreach ($necessaryWords as $word)
    $sql .= ' +' . $word;
$sql .= '")';

Note that your table must use MyISAM in order to use FULLTEXT indexes. UPDATE: As of MySQL 5.6, InnoDB supports FULLTEXT indexes as well. I guess this could be the better choice performance wise. Further documentation on the fulltext in boolean mode can be found in the manual.

SQL search for a string like with combination of sub-string of a String string

First trim any white spaces:

$address = trim($address);

Then simply use REGEXP in your query:

If you want to be OR LIKE, use following:

SELECT address FROM hospital WHERE address REGEXP REPLACE('$address',' ','|')

This is 'converting' your multiwords string into OR LIKE '%%' parts.

If you want to be AND LIKE, use following:

SELECT address FROM hospital WHERE address REGEXP REPLACE('$address',' ','.+')

This is 'converting' your multiwords string into AND LIKE '%%' parts.

More about REGEXP here: http://www.tutorialspoint.com/mysql/mysql-regexps.htm

search for multiple keywords with php and mysql (where X like)

To dynamically search all keywords, you can use the explode function to seperate all keywords;

$queried = mysql_real_escape_string($_POST['query']); // always escape

$keys = explode(" ",$queried);

$sql = "SELECT * FROM links WHERE name LIKE '%$queried%' ";

foreach($keys as $k){
    $sql .= " OR name LIKE '%$k%' ";
}

$result = mysql_query($sql);

Note 1: Always escape user input before using it in your query.

Note 2: mysql_* functions are deprecated, use Mysqli or PDO as an alternative

Update 2018 - Note 3: Don't forget to check the length of the $queried variable and set a limit. Otherwise the user can input a vary large string and crash your database.

SQL search multiple values in same field

Yes, you can use SQL IN operator to search multiple absolute values:

SELECT name FROM products WHERE name IN ( 'Value1', 'Value2', ... );

If you want to use LIKE you will need to use OR instead:

SELECT name FROM products WHERE name LIKE '%Value1' OR name LIKE '%Value2';

Using AND (as you tried) requires ALL conditions to be true, using OR requires at least one to be true.

---

Related Topics