* Petre Agenbag > can someone help me with general search syntax. I can try. :)
> I want to search a table with a search string obtained from a form input > ( PHP/Mysql app) > > The problem is: > > Lets say there is an entry in the table: "John Doe" > > When I search for that, with something like: > > select * from table where name like '%$name%' it generally works fine, > BUT, when they make a typo, something like "John Do", it doesn't return > a result. That's strange... they should find the entry when searching for "John Do", because of the "%" in the search criteria... it effectively becomes: " like '%John Do%'". Other typos, like "Jonh Doe" or "John De" would not return a result. > What is the best way of searching through a table to ensure the "best" > possible hit? That would depend on a definition of what is "best". Is speed an issue? Would it be problematic if you got many false hits? How many records do you have? > I thought of splitting the $name search string into the seperate words > "john" and "doe" and then searching for both with a like'%%', and then > combining the result of both as the result for the search, but I was > hoping there might be a "better" way to do this, specially if the search > string contains only one word. This is one approach. You could index each word, and you would get a hit for "John" and/or "Doe". Your query could use a faster syntax: "... LIKE '$word%'" (without a leading %). > So, I would like the results to be rather "relaxed", if they search for > johnn, it should return john as well as a posibility. Yes, this is a neat trick: you need to programatically 'normalize' each word: "John" -> "JOHN", "Doe" -> "DOE", "Johnny" -> "JOHNY", "Günther" -> "GUNTHER" and so on (some national variations may apply, in Sweden "Ä" -> "E"). This must be done with all your data when it goes into your system, both for INSERT's and for the searh criteria used for SELECTS. I also sometimes use a concatenation trick. Consider the company name "John Doe Bar & Saloon". It could be split and 'normalized' into "JOHN", "DOE", "BAR" and "SALON". These words are added to you searchword table, but also these concatenated values: "JOHNDOEBARSALON", "DOEBARSALON" and "BARSALON". A search for "John Doe", "Doe Bar" or "Bar & Saloon" would all give a hit. "John Bar" or "John Doe Saloon" would however not give a hit, that would require separate lookups for each word (or multiple joins), like you mentioned in your post. The concatenation trick could eliminate the need for this for most cases, if the result is good enough for you. You would also need a separate table to keep the original data values, with the correct spelling and letter case. In other words, you would keep the names table you have today, but you would add a 'searchword' table and a link table between the searchwords and the actual names. > Hope this won't be too complicated to do. Well... all is relative, as we say in the world of RDBMS. ;) The easy way out is to use FULLTEXT indexing, but I don't think that could be used to solve the "Johnn" -> "John" problem: <URL: http://www.mysql.com/doc/en/Fulltext_Search.html > I should also mention the SOUNDEX() function, it may be used to find names with similar spelling, but it is far from perfect: <URL: http://www.mysql.com/doc/en/String_functions.html > HTH, -- Roger --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
