How to match specific elements that user enters with database? -php/mysql/ajax

Question

I have a database of users. Each user has to enter during registration a street address, state, city, zipcode.

I also have in my database tables for States, Cities, Counties, Zipcodes. These tables are filled with all the US states, counties, cities and zipcodes available.

-Zipcodes are linked to cities only. 
-Cities are linked to counties and states. Each city has unique city_id
-Counties are linked to states only. Each county has unique county_id
-Each state has unique state_id

My PHP page specifically for addresses consists of:

-SELECT for States (state_name as text; state_id as value)
-TEXTBOX for City (with only city_name as value)
-TEXTBOX for Zipcode

I use PHP Session to store values entered by user, in case of failed form validation.

When the user submits I want to be able to put in the database the city_id (since that id will also contain county_id and state_id). But what the user submits is a city_name. How can I match that?

Do i need a search in the database on the form validation and see if I can find the city based on what the user entered? I suppose I would need a perfect match.

Use autocomplete and retrieve with jquery and ajax cities (including the city_id) based on what the user types and then store somewhere the city_id value?

How should I do it?

peterm · Accepted Answer · 2013-07-28 05:15:28Z

1

Your query to get city_id based on either city_name or zipcode might look like this

SELECT c.city_id 
  FROM cities c JOIN zipcodes z
    ON c.city_id = z.city_id
 WHERE c.city_name = 'Baltimore' 
    OR z.zipcode = '21201'
 GROUP BY c.city_id

UPDATE To get city_id by one of its zipcodes

SELECT city_id 
  FROM zipcodes
 WHERE zipcode = '21201'
 GROUP BY city_id

Here is SQLFiddle demo

edited Jul 28, 2013 at 5:15

answered Jul 20, 2013 at 21:28

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

peterm Over a year ago

@Cristian Did it help? Do you need more help with your question?

Cristian Over a year ago

It did help but I'd use some more help. Basically I just want to make sure that when I do that search for the city_name with the specified zipcode, I should be able to get only that city_id and make sure it is the right one. Is there anything else I can do except for this to make it better?

peterm Over a year ago

@Cristian See updated answer. If it was what you were looking for please, consider to upvote/accept the answer.

Collectives™ on Stack Overflow

How to match specific elements that user enters with database? -php/mysql/ajax

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related