Locate multiple strings in multiple fields

Question

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

Please stop using mysql_ functions

Kermit
– Kermit

2012-08-30 22:47:06 +00:00
Commented Aug 30, 2012 at 22:47 — Kermit
– Kermit, Commented Aug 30, 2012 at 22:47

invertedSpear · Accepted Answer · 2012-08-30 22:40:05Z

1

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.

answered Aug 30, 2012 at 22:40

invertedSpear

11.1k6 gold badges43 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

smithbh · Accepted Answer · 2012-08-30 22:45:54Z

0

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.

answered Aug 30, 2012 at 22:45

smithbh

3471 silver badge7 bronze badges

Collectives™ on Stack Overflow

Locate multiple strings in multiple fields

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related