MySQL Forums
Forum List  »  MyISAM

Location search in mysql joining 4 tables
Posted by: Soubhik Chatterjee
Date: January 21, 2017 07:27AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Location search in mysql joining 4 tables
1100
January 21, 2017 07:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.