php search through mysql fields for string match

Question

I have an input box where multiple words can be entered and searched for in my table.

supposing the user enters 'people places things' as their search criteria, these should get submitted to my php function which searches through my 'nouns' table and the 'description' and 'title' fields for possible matches. The function should then sort the results in descending order by number of matches found.

what I've got so far (missing a lot, and possibly not even the correct method):

$lcSearchVal = "people places things";
$lcSearchVal = explode( ' ', $lcSearchVal );

foreach( $lcSearchVal as $lcSearchWord ){
   $qry_suggest = mysql_query( 'SELECT * FROM nouns
                            WHERE Active 
                            AND ( ' . strpos($lcSearchWord, "description") > 0 . '
                               OR ' . strpos($lcSearchWord, "title") > 0 . ' ) '
                            );
}

I'm not sure what to do or where to go from here though....please help!

You should probably take a look at this before trying to solve this problem: http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html — Dereleased, Apr 21 '11 at 21:20

score 4 · Accepted Answer · answered Apr 21 '11 at 21:26

The query you are using will not work - you're using PHP functions to do the comparison to a plain string and appending the result of that comparison in your query. All the DB will see is this:

SELECT * FROM nouns WHERE Active AND (-1 OR 0)

...because the php strpos function returns the numeric position of the found string (or -1 if not found. You need to use comparison functions within the database itself, therefore, you aren't after php functions at all, rather you want mySQL comparison functions.

For reference, here is a list of them: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

Here is a sample that , very simply, searches for matches. You can improve this code quite a bit, but this should give you an idea:

$lcSearchVal = "people places things";
$lcSearchVal = explode( ' ', $lcSearchVal );

$sql = 'SELECT * FROM nouns WHERE Active=1 AND (';

$parts = array();
foreach( $lcSearchVal as $lcSearchWord ){
    $parts[] = '`description` LIKE "%'.$lcSearchWord.'%"';
    $parts[] = '`title` LIKE "%'.$lcSearchWord.'%"';
}

$sql .= implode(' OR ', $parts).')';
print $sql;

Mess around with it: http://codepad.org/fcNH5qyu

Also note, your WHERE Active clause is not valid - you have to give it a comparison. I've assumed in my sample that Active field is a numeric 1 or 0.

think this is what I want, I just need to sort the results by number of matches..do you know how to add that? — sadmicrowave, Apr 21 '11 at 21:33

score 0 · Answer 2 · answered Apr 21 '11 at 21:21

You can't mix PHP functions with SQL like that. Your query is going to look something like:

... WHERE Active AND (1 or 0) ...

I'm guessing you'd want someting like this:

$lcSearchWord = mysql_real_escape_string($lcSearchWord);

... WHERE Active=1 AND (description LIKE '%$lcSearchWord$%' OR title LIKE '%$lcSearchWord$%') ...

php search through mysql fields for string match

2 Answers2