php search through mysql fields for string match
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 p开发者_如何学编程hp 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!
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.
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$%') ...
精彩评论