How to check if a row already exists?

Gary_King

How can I check if a row already exists?
For example, I have the following table:
Table name: cities

cityName   |   country   |   continent
---------------------------------------------------
New York   |   U.S.A.    |  North America
Toronto    |   Canada    | North America
London     |   U.K.      |  Europe
Sydney     |  Australia  | Australia

Now, what I want to do, is to have some code to check if a city already exists, before submitting it (using a form) again.
For example, a user wants to submit the city Toronto, but as you can see, it already exists in the database. So basically, my question is, how can I make a script to check the table to see if Toronto is already there?

ahundiak

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10234820

In your case use

CREATE UNIQUE INDEX index_cityName ON cities(cityName);

Gary_King

Thanks for the help!

nancy-jo

Depending on what you need to do, the solution might be too restrictive: keep in mind that you won't have the possibility to insert London (Ohio) USA or two of any other city name which exists in two different countries.

It also will not eliminate the possibility of mispellings: if someone inserts Sidney Australia, it would be accepted just the same.

The other example that is cited uses the Uniqueness correctly (log-in usernames shouldn't be duplicated unless you want overlapping and confused log-ins) but here it might be too much, unless you want it that restrictive (first city to sign up gets to keep the name).

ahundiak

The unique index can cover multiple fields.

CREATE UNIQUE INDEX xxx ON table(cityNames,country);

Correcting for misspelling is a tad bit more difficult.
You might want to present country and continent as a pick list and eliminate that part of the problem.

Gary_King

Alright, thanks for the advice. I'll keep it in mind.