query database with multiple words from explode function

query the db to find rows using like but repeated many times

[abushahin]

Hey what im trying to achieve is a word suggestion for users who enter mis spelt words so the user would enter "hell im her" then i use explode to seperate the words and query the db to find possible corrections which i already have in the db, thats all fine but at the moment i can only query one word i.e.

$fullword = $_GET['q'];
$words = explode(" ", $fullword);
$words[0]; // piece1
$words[1]; // piece2
$words[2]; // piece2
$words[3]; // piece2

$conn = @mysql_connect($dbHost, $dbUser, $dbPass) or die ('Error connecting to mysql, server busy please try back later');
mysql_select_db($dbName) or die('Error, cant find database, server busy please try back later');
$result = @mysql_query("SELECT correct FROM spellcheck WHERE wrong LIKE '$words[0]%'");
if (!$result) {
 echo 'Error retrieving data, Server busy please refresh your browser or try later';
?>
<br /><a href="index.php">home</a>
<?
 exit;
}
$row = @mysql_fetch_row($result);
if ($row == 0)
{
 echo '';
}else?>
<h4><b><?echo "Did you mean: <a href='searchresults.php?q=$row[0]'>". $row[0]."</a>";?></b></h4>

Is there a way to query all the words of the exploded word at the same time?

[CyBerAliEn]

This all sounds like an efficiency nightmare waiting to happen...

Let's see if I can offer some useful advice. What kinds of "suggestions" are you planning to do? Something like "green round widgets" could return the suggestion of "red round widgets"? Despite the "easiness" of knowing what you want to do, this is an incredibly complex algorithm to successfully pull off (as you're finding out).

What you could do is alter your query to:

$like = "%".trim(str_replace(' ','%',$_GET['q']))."%";
$query = "SELECT correct FROM spellcheck WHERE wrong LIKE '{$like}'";

This will put a query like "red green widgets" into the form "%red%green%widget%". This will provide a match to such entries as "blue red green widget", "red green round widget", etc. This might help you get closer to what you're looking for, while not becoming too complex.

You could also consider doing a regular expression check against the 'q' value from _GET so as to eliminate any non-alpha characters and space character. This can then disregard such issues as punctuation in a query (as well as helping to prevent potentially bad code from being injected into the DB).

You could test it out by having it output each result (because the above will output multiple results, since more than one entry in the DB might match). For example, if your "spellcheck" table has values in the "wrong" column such as:
"large elephant food"
"medium elephant food"
"small elephant food"

And your user searches "food"... it could return all 3 of the above records as matches to 'food' (for example). So you'll need to consider modifying your code to loop through the results array.

But this will return any possible match, and you seem to only want one match returned. You could run PHP's function similar_text() on the results to check how similar the query is to the "correction"... and then only display/use the one that is most similar.

You'll have a bit of work to do to make this very accurate/useful. :)


Note that this is a very simplistic approach.

[abushahin]

Hey thanks for your comments it may come in useful what ive done since posting this comments is did a fulltext search with mysql so that three different words are being taken as individual string so that if the user enters "dogg catt foood" this is checked against my db as individual strings and and then suggestigng did you mean "dog cat food", and for instance if the user entered "dog cat foood" then obviuosly the first two are correct spellings and would return no results but only one suggestion i.e. did you mean "food". To put the icing on the cake would be did you mean "dog cat food" as opposed to just "food" if you know what i mean.

[CyBerAliEn]

Your approach is certainly superior!

mySQL full text search is simply a "search engine" within the database engine. Whenever possible, utilizing its power is useful! Though it has its limitations, particularly if your table becomes too large/has too many columns. (FYI)

I was under the presumption you were trying to keep the code as simple as possible (hence a simple str_replace and simple query).

Kudos to your solution!