Page 1 of 1
search suggest in string selection
Posted: Fri Jan 26, 2007 8:52 am
by louie35
Hi,
this is my first post here so take easy on me.
I need help in selecting a certain part of the sentence while looking to display a sugestion to the user.
It's an ajax based search suggest that anytime somebody start typing a word it drops down a list with 15 result starting with the letters typed in.
It works perfect because overtime I stored some searches previous customers did so I am looking into that table at the moment, but I have another table (products) with over 10.000 records that I would like to use instead getting better results.
The problem I have is that the query string looks into the product name field from the begining and if found it displays it but I would like to look inside and start from where it found a match.
eg.
Product Name = I am a developer with a lot of things in my mind
The user start typing "developer" so I would like the query to the database to pick that and only show in the suggestion list "developer with"
The query I am using at the moment is:
Code: Select all
"SELECT search_query,search_results FROM ".$oData->requesting." WHERE search_query like '".
$oData->text."%' and search_results > 0 order by search_results DESC limit 0,".$oData->limit;
Is this possible, because I am stressing my brains out at the moment and the more I think the less solutions I find.
Posted: Fri Jan 26, 2007 9:45 am
by mikeq
From reading your post it sounds like you want to bring back only the text from the word that the user types
So user types 'developer' you then do a search on your database to bring back all records with 'developer' in the product name.
So in your example where the product is "I am a developer with a lot of things in my mind" you only want to return "developer with a lot of things in my mind", is that correct?
If so...
Forgetting all the ajax stuff etc for clarity
Code: Select all
$UserEnteredData = "developer";
$Query = "SELECT product_name FROM product WHERE product_name LIKE '%$UserEnteredData%';
$Result = mysql_query($Query);
while ($Record = mysql_fetch_array($Result)){
//get position of string in database field
$PositionOfString = strpos($Record['product_name'],$UserEnteredData);
//then copy the value in the database field from the position to end
$ChoppedString = substr($Record['product_name'], $PositionOfString);
//so $ChoppedString would now contain "developer with a lot of things in my mind" in the example
//you could also combine that all into one line but it is more difficult to read
//$ChoppedString = substr($Record['product_name'], strpos($Record['product_name'],$UserEnteredData));
}
You would just apply this before your Ajax return or perform it on the results from your Ajax call.
I would be wary of using this though, as your query needs to be a LIKE with a starting wild card (... LIKE '%developer%') then the database will not be able to use an index. As soon as you use a wild card at the beginning of a field you are searching on the database needs to perform a full table scan. You have 10,000 records at the moment, you may notice poor performance with this.
You will need to handle the user entering multiple search terms also, if I enter 'developer PHP javascript' how are you going to search the database for that info. Maybe you should think about using some kind of keywords thing, rather than searching on your product name field.
Mike
Posted: Fri Jan 26, 2007 10:11 am
by louie35
That looks like it could do the job alright, I will give it a try and let you know.
This is just a suggestion box to help user find the best posibilty for the search phrase they are going to use - like google sugestion search. They might or they might not want to make use of it.
What I like about it is the fact that i can use the words already in the database and searches are not mis-spelled.
I have collected over a period of time in a table all the searches users did and every now and then i need to go in and delete the bad ones like words that doesn't make any sense to the website content and also miss-spelled ones. You'll be suprised how bad some users are.
eg. "sterio" instead of "stereo"
The search facility works without the ajax and we aslo have a drop down menu that lets the user select what type of search they want to look for like all words or any word.
The results are returned afterwards by another query to the database based on the selections they make.
Posted: Fri Jan 26, 2007 1:53 pm
by louie35
I gave it a try. It doesn't work as supposed to. There is the code I have now:
Code: Select all
//plain text header
header("Content-Type: text/plain; charset=UTF-8");
//database information
$sDBServer = "localhost";
$sDBName = "";
$sDBUsername = "";
$sDBPassword = "";
//include JSON-PHP and instantiate the object
require_once("JSON.php");
$oJSON = new JSON();
//get the data that was posted
$oData = $oJSON->decode($HTTP_RAW_POST_DATA);
$aSuggestions = array();
//make sure there's text
if (strlen($oData->text) > 0) {
$sQuery = "Select `product_name` from `products_tbl` where product_name like '%".
$oData->text."%' order by `product_name` ASC limit 0,".$oData->limit;//".$oData->requesting."
//make the database connection
$oLink = mysql_connect($sDBServer,$sDBUsername,$sDBPassword);
@mysql_select_db($sDBName) or die("Unable to open database");
if($oResult = mysql_query($sQuery)) {
$UserEnteredData = $oData->text;
while ($aValues = mysql_fetch_array($oResult,MYSQL_ASSOC)) {
$PositionOfString = strpos($aValues['product_name'],$UserEnteredData);//new
$ChoppedString = substr($aValues['product_name'], $PositionOfString); //new
array_push($aSuggestions, $ChoppedString);// . " (".$aValues['search_results'].")");//new
}
}
mysql_free_result($oResult);
mysql_close($oLink);
}
echo($oJSON->encode($aSuggestions));
?>
Posted: Sat Jan 27, 2007 4:37 am
by louie35
I managed to get it working right by converting the string to lower case (obviously is case sensitive for some reason) like this:
Code: Select all
$PositionOfString = strpos(strtolower($aValues['product_name']),$UserEnteredData);//new
$ChoppedString = substr(strtolower($aValues['product_name']), $PositionOfString, 30); //new
array_push($aSuggestions, $ChoppedString);
and I am getting a lot of duplicates array.
Code: Select all
["cd player","cd player,black 45w,pre,sq,rds","cd player,black 45w,pre,sq,rds","cd player,black 45w,pre,sq,rds","cd player","cd player","cd player","cd player green illum.","cd player","cd player","cd player","cd player","cd player","cd player","cd player and tuner"]
tried to remove the duplicates by using array_unique() function but it gives very strange results:
Code: Select all
{"0":"cd player","1":"cd player,black 45w,pre,sq,rds","7":"cd player green illum.","14":"cd player and tuner"}
any suggestions how to get rid of duplicates?
Posted: Sat Jan 27, 2007 9:26 am
by feyd
Posted: Sat Jan 27, 2007 10:15 am
by louie35
you are a genius.
worked perfect
Code: Select all
$aSuggestions= array_unique(array_values($aSuggestions));