Page 1 of 1
Best way to compare strings
Posted: Thu Jun 02, 2005 4:37 am
by bytte
I have a database with all the cities of my region in it and the details of that city.
I want people to be able to search the database by entering the name of their city.
Of course I could use:
SELECT * FROM cities WHERE city='name_they_entered';
But that wouldn't always be good, cause there's some cities that are often spelled wrong such as:
- "Knokke-Heist", sometimes people spell it "Knokke Heist", sometimes "Knokkeheist" etc.
What's the best way to search the database in your opinion?
Posted: Thu Jun 02, 2005 4:52 am
by JayBird
dont allow the user to type in the city name. Give them a drop down to select a predefined city
Posted: Thu Jun 02, 2005 9:59 am
by pickle
That's probably your best bet, but perhaps an easier to implement solution would be FULLTEXT indexing. If you create a FULLTEXT index on the 'city' column, then with the appropriate query, MySQL will return all the cities that are similar in spelling, in order of relevancy.
Posted: Thu Jun 02, 2005 10:03 am
by bytte
Thanks guys. It are the two options I was thinking of, but I wanted to hear some other visions that I had probably overlooked on it as well.
Posted: Thu Jun 02, 2005 11:25 am
by timvw
I always provide them a regular text input...
But if the input contains % or _ i generate a query that uses LIKE instead of =
Code: Select all
if (strpos($_POST['place'], '%') !== false || strpos($_POST['place']) !== false)
{
$query .= "place LIKE '" . mysql_escape_string($_POST['place']) . "'";
}
else
{
$query .= "place = '" . mysql_escape_string($_POST['place']) . "'";
}
But the FULLTEXT search is very appealing too

(Should give it a try after exams...)
Posted: Thu Jun 02, 2005 11:29 am
by pickle
FYI ~timvw, we're supposed to use mysql_real_escape_string() now (as of 4.3.0) as opposed to mysql_escape_string()
Posted: Thu Jun 02, 2005 11:32 am
by Burrito
pickle wrote:FYI ~timvw, we're supposed to use mysql_real_escape_string() now (as of 4.3.0) as opposed to mysql_escape_string()
really? did the latter get depricated? If so, why? They just want to make us type more or what?
Posted: Thu Jun 02, 2005 2:21 pm
by pickle
Well, the difference is that mysql_real_escape_string() uses the current connection and escapes the string based on that connection's character set. I guess they deprecated mysql_escape_string() to improve coding practices and kind of force everyone to handle different character sets.
Posted: Thu Jun 02, 2005 4:07 pm
by timvw
<excuse>
I usually depend on ADODB to do this for me

</excuse>
(While i was typing the example, i wondered if i would type mysql_real_escape_string or mysql_escape_string... I choose for the second, because i was too lazy to type _real_ and provide a link to the db

Next time i'll be less lazy and more consistent
