CODEWITHSUNDEEP
phpmysql
acseven
acseven

Reputation: 39

Locate multiple strings in multiple fields

I am trying to get results from a MySQL database by searching multiple terms in multiple fields. The script below is working, but not quite the way I need it and I am having trouble finding out a way of doint it.

Currently if I search for "apples peaches" it will return every result that has "apples" OR "peaches" on any field. I.e. if a record has "apples" but not "peaches" it will yield a result.

With the intended behavior I should get a result only if in a given record it finds all the search terms on any field. I.e. "apples" could be found on field f1 and "peaches" on field f2. If only "apples" is found then it is not a result.

<?php
if (!isset($_REQUEST['term'])) exit;
$dblink = mysql_connect('localhost', 'root', 'pass') or die(mysql_error());
mysql_select_db('mytable');
mysql_set_charset('utf8', $dblink);
// query the database table for strings that match 'term'
$lcSearchVal = mysql_real_escape_string($_REQUEST['term']);
$lcSearchVal = explode(' ', $lcSearchVal);
$sql = 'SELECT id, f1, f2, f3, f4 FROM mytable 
        WHERE (';
$parts = array();
foreach($lcSearchVal as $lcSearchWord) {
    if ($lcSearchWord <> "") {
        $parts[] = '`id` LIKE "%' . $lcSearchWord . '%"';
    }
    if ($lcSearchWord <> "") {
        $parts[] = '`f1` LIKE "%' . $lcSearchWord . '%"';
    }
    if ($lcSearchWord <> "") {
        $parts[] = '`f2` LIKE "%' . $lcSearchWord . '%"';
    }
    if ($lcSearchWord <> "") {
        $parts[] = '`f3` LIKE "%' . $lcSearchWord . '%"';
    }
    if ($lcSearchWord <> "") {
        $parts[] = '`f4` LIKE "%' . $lcSearchWord . '%"';
    }
}
$sql.= implode(' OR ', $parts) . ') order BY id desc limit 0,10';
$rs = mysql_query($sql, $dblink);
// loop through each string returned and format the response for jQuery
$data = array();
$data[] = array('value' => "", 'label' => " >> Last 10 results:", 'desc' => "");
if ($rs && mysql_num_rows($rs)) {
    while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
        if (strlen($row['f3']) > 250) {
            $row['f3'] = substr($row['f3'], 0, 250) . " [...]";
        }
        $data[] = array('value' => $row['id'], 'label' => $row['id'] . '/' . $row['f1'] . ' (' . $row['f2'] . ') ' . $row['f4'], 'desc' => $row['f3']);
    }
}
// jQuery wants JSON data
echo json_encode($data);
flush();
?>

Thank you for your input

Upvotes: 0

Views: 118

Answers (2)

smithbh
smithbh

Reputation: 347

Consider the following:

$lcSearchVal = 'one two three';
$lcSearchVal = explode(' ', $str);
foreach ($lcSearchVal as &$lcSearchWord) {
    $lcSearchWord = '`id` LIKE "%' . $lcSearchWord . '%" AND ';
}
$conditions = rtrim(implode('', $val), ' AND ');

echo $conditions; //returns `id` LIKE "%one%" AND `id` LIKE "%two%" AND `id` LIKE "%three%"

$conditions can then be inserted into the query as the WHERE condition.

Upvotes: 0

invertedSpear
invertedSpear

Reputation: 11054

Look into doing full text indexes and queries:

http://www.devarticles.com/c/a/MySQL/Getting-Started-With-MySQLs-Full-Text-Search-Capabilities/

Way better than using like statements for this kind of stuff.

Upvotes: 1

Related Questions