Hi
I am using MySQL db and trying to build a search for Country, state city and areas similar to the one in www.instantoffices.com
In my database, I have 4 different tables (Countries, States, Cities and Areas). I have added FULLTEXT index for the "name" field in country, state, city and area tables.
Table structure
Countries:
http://pastebin.com/raw/BYHyXiFx
States:
http://pastebin.com/raw/g1fzS7m1
Cities:
http://pastebin.com/raw/Yj8YaRVC
Areas:
http://pastebin.com/raw/gsQuSq2K
How can I join these four tables so that when I search for a string it should search in the "name" column in all the four tables and return results in a single row per match. The match should be in a specific format (Area, City, State, Country) For example: "Bourke Street, Melbourne, Victoria, Australia"
I am trying to achieve this by using the UNION syntax.
SELECT name AS area_name
FROM areas
WHERE MATCH name AGAINST('Bourke Street')
UNION
SELECT name AS city_name
FROM cities
WHERE MATCH name AGAINST('Melbourne')
UNION
SELECT name AS state_name
FROM states
WHERE MATCH name AGAINST('Victoria')
UNION
SELECT name AS country_name
FROM countries
WHERE MATCH name AGAINST('Australia')
This yields results but there are two problems with this:
1. Each result is fetched in a new row. I want the result to be like this "Bourke Street, Melbourne, Victoria, Australia".
2. If I search for "B" instead of "Bourke Street", it does not reflect any match.
Any help will be highly appreciated.
Thanks in advance