current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a form with the fields Name, Email, and City. City is an autocomplete field that calls 3 separate tables (Cities, Regions, Countries).

city field

What I'd like to do is to have these explode into an array so that City, Region, and Country are all defined values. This is my query right now for the autocomplete field:

   SELECT c.City, concat(r.Region, ', ', co.Country)
   FROM Cities c, Regions r, Countries co
   WHERE c.RegionID = r.RegionID and c.CountryID = co.CountryID and c.City like CONCAT( inCity, '%' )
   ORDER BY City;

As you can see, the entered value (inCity) is only compared to the City column in the Cities table.

The reason I need to have the City, Region, and Country defined is because I need to enter their IDs in another table called Events. This is my query to insert the CityID into my Events table:

   INSERT INTO Events (Events.CityID)
   VALUES ((SELECT CityID FROM Cities WHERE City = inCity LIMIT 1));

This also doesn't work because sometimes there are multiple cities with the same name.

I was able to do this before with PHP, but I'm currently using myDBR reports to do this and I can only use queries.

share|improve this question
    
Can't you just remove the concat? Like this: SELECT c.City, r.Region, co.Country –  Joel Lewis Aug 7 '13 at 18:21
    
@JoelLewiw - I could, but I have it like that so that they're all shown as one line in the autocomplete form. And even if I remove concat, I still don't know how to call Region & Country in my INSERT query. –  Michelle C Aug 7 '13 at 18:47

1 Answer 1

up vote 0 down vote

Things are a lot clearer when you keep your JOIN conditions out of your WHERE clause:

SELECT concat(c.City, ', ', r.Region, ', ', co.Country)
FROM Cities c
    INNER JOIN Regions r ON c.RegionID = r.RegioID
    INNER JOIN Countries co ON c.CountryID = co.CountryID
WHERE c.City like CONCAT( inCity, '%' )
ORDER BY City;
share|improve this answer
    
Thanks, fixed it. I'm still confused about how to get the Region & Country in my INSERT query though. –  Michelle C Aug 7 '13 at 18:45
    
If this answer fixed it, can you mark it as accepted, please? If not, you should add your own answer, for future reference. –  Dancrumb Aug 7 '13 at 18:52
    
Sorry, I meant I changed my query to what you provided to make things clearer, but I still have the same problem (can't get call the Region & Country in my INSERT query) –  Michelle C Aug 7 '13 at 18:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.