MySQL for City State Autocomplete

[webteam]

Hi,

I'm putting an autocomplete textbox on my website to help users type in a location. I want users to be able to type a city or just a state (ex. Seattle, WA or Washington) kind of like what you see on Indeed (http://www.indeed.com). How do I get the states to show up by themselves(ex. Washington)? Right now, if there's a job in Seattle then "Washington" by itself will not show up in the suggestions. If we have no jobs in Montana then "Montana" shows up in the suggestions. I want all the states to show up in the suggestions whether we have jobs in the state or not. Hopefully, that makes sense. Please help me tweak the MySQL statement below. I have two tables:

jobs
job_type
city
state_id

states
state_id
name
postal

Current MySQL - $location is the value that the user types in the text box

SELECT j.city AS city,st.postal AS state_prefix,st.name AS state FROM states st LEFT JOIN jobs j ON j.state_id=st.state_id WHERE j.city LIKE '%$location%' OR st.name LIKE '%$location%' GROUP BY j.state_id,j.city ORDER BY j.city ASC,st.name ASC LIMIT 10

[jlego]

get rid of group by j.state_id (im assuming that is the id for a state name)

your essentially grouping everything under one state if im right

i live in pa
if im from pittsburgh, but you group state, for all i know altoona or philidelphia could be the only viewable option.

i could be wrong though and am not looking at ur query right

[webteam]

Jeff,

Thanks for the reply. I went ahead and tried changing the way you say, but it shows the same. I have it group by state_id as well as city, so that way Kansas City, MO and Kansas City, KS would both show up and not just one of them if I just grouped it by city. The city part seems to be fine. I'm just trying to figure out how to add all of the state names to the suggestions. When I use any type of JOIN in the MySQL statement it combines the results. I need it tweaked, so that it throws all of the state names (ex. Washington) from the states table as well as all the city combinations with their respective state (ex. Seattle, WA).

[jlego]

SELECT 

j.city AS city,
st.postal AS state_prefix,
st.name AS state

FROM states st

LEFT JOIN states ON states.State_ID = jobs.state_id 

WHERE j.city LIKE '%$location%' OR st.name LIKE '%$location%'

ORDER BY j.city ASC,st.name ASC 

LIMIT 10

[jlego]

let meknow the outcome and ill look at it again

[webteam]

Hi Jeff,

I got an error running what you put there. It looks like you never linked up the jobs table. I tried this below using "Arkansas" as an example for someone typing that in.

SELECT 

j.city AS city,
st.postal AS state_prefix,
st.name AS state

FROM jobs j

LEFT JOIN states st ON st.state_id = j.state_id 

WHERE j.city LIKE '%Arkansas%' OR st.name LIKE '%Arkansas%'

ORDER BY j.city ASC,st.name ASC 

LIMIT 10

It gave me back these suggestions:

Camden, AR
Harrison, AR
Harrison, AR
Hot Springs, AR

As you can see Harrison is in there twice. Also, the suggestion "Arkansas" does not show. It looks like putting the GROUP BY in there will eliminate duplicates, but I need a way to have the state name show in the suggestions.

[smantscheff]

I want all the states to show up in the suggestions whether we have jobs in the state or not. Hopefully, that makes sense.

No it doesn't. Why would you show states for which you don't have jobs? To make the user believe that you have a large database?
To show states regardless of their presence in the job table you can combine state and town names in a union query in which you pull all towns with states from the job table and all states which are not linked to the job table from the states table.

[jlego]

SELECT  DISTINCT jobs.city AS city, state.postal AS state_prefix, state.name AS state FROM jobs LEFT JOIN states ON states.State_ID = jobs.state_id  WHERE j.city LIKE '%$location%' OR st.name LIKE '%$location%' GROUP BY state.name ORDER BY j.city ASC,st.name ASC  LIMIT 10

if you want everything to show up no matter what, remove the j.city LIKE '%location%'
and do the state.name LIKE '%location%'

that will show everything in the current state that they enter?

[webteam]

Jeff, I tried what you entered there and all I get back is Camden, AR when typing "Arkansas". When I remove the GROUP BY I get back all the city combinations, but I don't see "Arkansas" in the list of suggestions. smantscheff, the reason I want the state names to show is because the user might want to the search the entire state for jobs and not a specific city. I don't need it to show the state name if I don't have a job in the state. I was trying to get the state names to be independent of the city combinations since right now anytime I have a job in a state it won't show the state name in the suggestion list as someone types something like "Arkansas".

[jlego]

sorry i got confused when i put the group by. yeah that would group everything under AR. group by city will make sure a city - state combination only shows up once.

so, you want all the city - state cominations + just a state to show up?

like,

if i type arkaansa
suggestions:
arkansa
whatver arkansa
whatever2 arkansa
whatever3 arkansa

is this what you mean?

[webteam]

Yep, that's what I'm looking for.

[jlego]

in that case, not sure actually. i dont think you can atleast in that query

your joining the table based on the jobs in the location that they entered.. that will only return stats that match the job location.

maybe add it manually to the suggestions. not sure someone else might know how to do that, but i dont

[smantscheff]

You can do that with a union query (I'm ad-libbing table and field names here):

select state as location from states where state like "%AR%"
union
( 
  select concat(city,', ',state) as location 
  from cities join states 
  where cities.state_id=states.state_id and city like"%AR%" or state like "%AR%"
)

[webteam]

Thank you smantscheff! I only had to tweak one main thing by putting parentheses around

(city like "%AR%" or state like "%AR%")

. It works now. Thanks Jeff for trying to help me as well. Here's what the MySQL looks like (Arkansas as an example of a user typing it in):

SELECT name AS location FROM states WHERE name LIKE "%Arkansas%"
UNION
( 
  SELECT CONCAT(jobs.city,', ',states.postal) AS location 
  FROM jobs JOIN states 
  WHERE jobs.state_id=states.state_id AND (jobs.city LIKE"%Arkansas%" OR states.name LIKE "%Arkansas%")
)

[jlego]

No it doesn't. Why would you show states for which you don't have jobs? To make the user believe that you have a large database?
To show states regardless of their presence in the job table you can combine state and town names in a union query in which you pull all towns with states from the job table and all states which are not linked to the job table from the states table.

smantscheff said the same thing awhile back - but i didnt bother reading the entire post after i started reading why would you show states for whcih you dont have jobs.

glad you got it working tho