Best way to compare strings

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bytte
Forum Commoner
Posts: 75
Joined: Sun Nov 23, 2003 8:20 am
Location: Belgium

Best way to compare strings

Post 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?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

dont allow the user to type in the city name. Give them a drop down to select a predefined city
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
bytte
Forum Commoner
Posts: 75
Joined: Sun Nov 23, 2003 8:20 am
Location: Belgium

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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()
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 :)
Post Reply