Fulltext search in multiple(2) tables

Databases
1

I would like to use the fulltext search facility over multiple tables(3). Those three tables are companies, activities, cities. companies and activities are related through a fourth table (companies_activities) and companies and cities are related by a foreign key from cities in the companies table.

The fields I would like to include in the search are:

tblCompanies: (company_name, company_description)
tblActivities: (activity)
tblCities: (city)

I have two questions:

  1. Do I need to set a fulltext key on the separate tables?
  2. How would the query ideally look like?

Thank you in advance

2

you cannot use fulltext indexing across tables

you’ll have to fulltext search them individually

3

That was quick :slight_smile: Is there a way that I can link a certain Value(activity) from one table to a company while doing a search?

4

huh?

perhaps you could illustrate what you mean?

5

I mean. When someone is doing a search for Scuba Diving(activity) Nafplion(city) I would like to return the scuba dive companies in Nafplion. As I told before. Companies are related to both activities and cities.

6

that’s not obvious at all from the data you posted

please do a SHOW CREATE TABLE for each table

7

Hi rudi here are the create tables activities, cities, companies and companies activities:


CREATE TABLE IF NOT EXISTS `activities` (
  `activity_id` int(4) NOT NULL auto_increment,
  `activity_eng` varchar(32) default NULL,
  `activity_gr` varchar(32) default NULL,
  `activity_definition_eng` text,
  `activity_definition_gr` text,
  `definition_url` varchar(128) default NULL,
  `activity_text_eng` text,
  `activity_text_gr` text,
  `page` varchar(20) default 'activities.cfm',
  `visits` int(11) NOT NULL default '0',
  PRIMARY KEY  (`activity_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `cities` (
  `city_id` int(3) NOT NULL auto_increment,
  `county_id` int(3) NOT NULL,
  `city_eng` varchar(12) default NULL,
  `city_gr` varchar(12) default NULL,
  `city_text_eng` text,
  `city_text_gr` text,
  `page` varchar(20) default 'cities.cfm',
  PRIMARY KEY  (`city_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `companies` (
  `company_id` int(6) unsigned NOT NULL auto_increment,
  `category_id` int(2) NOT NULL,
  `county_id` int(2) NOT NULL,
  `city_id` int(2) NOT NULL,
  `company_name` varchar(128) NOT NULL,
  `company_contact` varchar(64) default NULL,
  `company_address` varchar(128) default NULL,
  `company_zip` varchar(10) default NULL,
  `company_email` varchar(128) default NULL,
  `company_telephone` varchar(15) default NULL,
  `company_fax` varchar(15) default NULL,
  `company_mobile` varchar(15) default NULL,
  `company_website` varchar(128) cdefault NULL,
  `company_description_eng` textL,
  `company_description_gr` text,
  `company_banner` varchar(128) default NULL,
  `company_header` varchar(128) default NULL,
  `isAdvertiser` tinyint(1) NOT NULL default '0',
  `page_id` tinyint(2) NOT NULL default '0',
  `page` varchar(20) NOT NULL default 'company_details.cfm',
  `start_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`company_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `company_activities` (
  `company_id` int(6) unsigned NOT NULL,
  `activity_id` int(6) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I hope this gives you some idea.

Thank you

8 
SELECT companies.company_id
     , companies.category_id
     , companies.company_name` 
     , companies.other_columns
  FROM activities
INNER
  JOIN company_activities
    ON company_activities.activity_id = activities.activity_id
INNER
  JOIN companies
    ON companies.company_id = company_activities.company_id
   AND companies.city_id =
       ( SELECT city_id 
           FROM cities
          WHERE city_eng = 'Nafplion' )
 WHERE activities.activity_eng = 'Scuba Diving'

see? no fulltext needed

:slight_smile:

9

Please forgive me my ignorance, but I would like to understand this right! If I replace the values of both WHERES with:


WHERE city_eng LIKE '%#txtSearch#%'

WHERE activities.activity_eng LIKE '%#txtSearch#%'

I should get the right results?

10

what happened when you tested it?

:cool:

11

Hi rudi. I just found the time to test it, but it didn’t return any results. It didn’t give any error either which is a plus :). For the purpose of this post I have set up a test environment with just ten companies in the database, from which three are scuba diving schools in Nafplion.

12

and the exact query that you ran… ?

13

The exact same one you gave me in post #9 with companies_city_id as addition in the first select because it was trowing an error without

14

okay, could you dump your test environment for me please, so i can test the query myself and see why it isn’t returning anything

15

Hi rudi. I narrowed everything down a bit too, I hope, make it a bit more readable (I took out all Greek text related fields). This is a dump of the tables involved:


CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` int(4) NOT NULL auto_increment,
  `category_eng` varchar(32) default NULL,
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` 
(`category_id`, `category_eng`) 
VALUES
(1, 'Active Sports'),
(2, 'Agro Tourism'),
(3, 'Children Activities'),
(4, 'Culinary Activities'),
(5, 'Cultural Activities'),
(6, 'Educational Activities'),
(7, 'Entertainment'),
(8, 'Excursions and Tours'),
(9, 'Extreme Sports'),
(10, 'Historical Activities'),
(11, 'Nature Activities'),
(12, 'Outdoor Activities'),
(13, 'Religious Tourism'),
(14, 'Therapeutic Activities'),
(15, 'Transportation'),
(16, 'Water Sports'),
(17, 'Other Activities'),
(18, 'Indoor Activities');


CREATE TABLE IF NOT EXISTS `activities` (
  `activity_id` int(4) NOT NULL auto_increment,
  `activity_eng` varchar(32) default NULL,
  PRIMARY KEY  (`activity_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `activities`
--

INSERT INTO `activities` (`activity_id`, `activity_eng`) 
VALUES
(1, 'Bungee Jumping'),
(2, 'Canoeing'),
(3, 'Jet Skiing'),
(4, 'Kite Surfing'),
(5, 'Scuba Diving'),
(6, 'Wakeboarding'),
(7, 'Waterskiing'),
(8, 'Archaeological Sites'),
(9, 'Art Galleries'),
(10, 'Cinema '),
(11, 'Museum '),
(12, 'Wind Surfing'),
(13, 'Sailing');


CREATE TABLE IF NOT EXISTS `categories_activities` (
  `activity_id` int(4) NOT NULL,
  `category_id` int(2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `categories_activities`
--

INSERT INTO `categories_activities` 
(`activity_id`, `category_id`) 
VALUES
(1, 1),
(1, 9),
(2, 16),
(3, 16),
(4, 16),
(5, 16),
(6, 16),
(7, 16),
(8, 5),
(8, 10),
(9, 5),
(10, 5),
(10, 7),
(11, 5),
(12, 16)
(13, 16);


CREATE TABLE IF NOT EXISTS `counties` (
  `county_id` int(3) NOT NULL auto_increment,
  `county_eng` varchar(12) default NULL,
  PRIMARY KEY  (`county_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `counties`
--

INSERT INTO `counties` 
(`county_id`, `county_eng`) 
VALUES
(1, 'Achaia'),
(2, 'Arcadia'),
(3, 'Argolida'),
(4, 'Ileia'),
(5, 'Korinthos'),
(6, 'Lakonia'),
(7, 'Messinia');



CREATE TABLE IF NOT EXISTS `cities` (
  `city_id` int(3) NOT NULL auto_increment,
  `county_id` int(2) NOT NULL,
  `city_eng` varchar(20) default NULL,
  PRIMARY KEY  (`city_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `cities`
--

INSERT INTO `cities` 
(`city_id`, `county_id`, `city_eng`) 
VALUES
(1, 1, 'Aigion'),
(2, 1, 'Ovrya'),
(3, 1, 'Patras'),
(4, 2, 'Leonidio'),
(5, 2, 'Levidi'),
(6, 2, 'Megalópoli'),
(7, 2, 'Tripoli'),
(8, 3, 'Argos'),
(9, 3, 'Asini'),
(10, 3, 'Epidaurus'),
(11, 3, 'Ermioni'),
(12, 3, 'Kranidi'),
(13, 3, 'Nafplio'),
(14, 3, 'Tolo');


CREATE TABLE IF NOT EXISTS `companies` (
  `company_id` int(6) unsigned NOT NULL auto_increment,
  `county_id` int(2) NOT NULL,
  `city_id` int(2) NOT NULL,
  `company_name` varchar(64) NOT NULL,
  `company_description_eng` text default NULL,
  PRIMARY KEY  (`company_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


--
-- Dumping data for table `companies`
--

INSERT INTO `companies` 
(`company_id`, `county_id`, `city_id`, `company_name`, `company_description_eng`) 
VALUES
(1, 3, 14, 'Poseidon Watersports', 'Posiedon Watersports in Tolo is bla bla bla'),
(2, 3, 13, 'Nafplio Diving', 'Welcome to Nafplio Diving Center.'),
(3, 3, 13, 'Argos Diving Club', 'The Peloponnese offers a diverse range of diving experiences.'),
(4, 3, 13, 'Nafplio Marine', 'Nafplio Marine is a full-service dive center Bla Bla Bla'),
(5,  3, 8, 'Zulu Bunge', 'Zulu bungee team uses the bridge of Korinthos..'),
(6, 1, 3, 'Watersports Patras', 'Watersports offers all kind of water sports'),
(7, 3, 13, 'Ouzo Museum', 'Karonis Distilleries are situated in a privately owned area of Agia Paraskevi, Nafplion'),
(8, 3, 8, 'Argos Etchings and Paintings', 'Since its inception, in 1987, Argos Etchings and Paintings, has ..'),
(9, 1, 3, 'Patras Yacht Club', 'The Patras Sailing Club has been offering its services in the learning and promotion of sailing in Patra for 29 years.'),
(10, 1, 3, 'New Archaeological Museum of Patras','The New Archaeological Museum of Patras is located in the city of Patras');


CREATE TABLE IF NOT EXISTS `company_activities` (
  `company_id` int(6) unsigned NOT NULL,
  `activity_id` int(6) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `company_activities`
--

INSERT INTO `company_activities` 
(`company_id`, `activity_id`) 
VALUES
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 7),
(1, 12),
(2, 5),
(3, 5),
(4, 5),
(5, 1),
(6, 3),
(6, 4),
(6, 7),
(6, 12),
(7, 11),
(8, 9),
(9, 13),
(10, 11);

It is probably not needed, but let me give you a short summary:

The two most important tables related to the companies table are activities and cities. activities is related through a third table company_activities. The relation to the cities table is through a foreign key in the companies table.

Each of these two tables have a relation with another table as well.

activities with the table categories, e.g. a certain activity can be in different categories. For this I use a third relational table categories_activities

cities with the table counties through a foreign key in the cities table. I have to mention that I’m not sure about this one?

Brings us back to the initial reason, why I posted this in the first place, the search.

When someone uses the search string Nafplion Scuba Diving or Scuba Diving in Nafplion, I would like to return all scuba diving centers in Nafplion
(relation through city_id and activity_id) and the same for the company name and city(relation through company_name and city_id)

I also like to know if it is possible, the way the database is now, to return results when someone would use the category in the search string instead of the activity, e.g. Water Sports in this test environment should return companies 1, 2, 3, 4, 6 and 9.

Thank you in advance

16

thanks for the data

this query worked fine –

SELECT companies.company_id
     , companies.county_id
     , companies.city_id
     , companies.company_name
     , companies.company_description_eng
  FROM activities
INNER
  JOIN company_activities
    ON company_activities.activity_id = activities.activity_id
INNER
  JOIN companies
    ON companies.company_id = company_activities.company_id
   AND companies.city_id =
       ( SELECT city_id 
           FROM cities
          WHERE city_eng = [COLOR="Red"]'Nafplio' [/COLOR])
 WHERE activities.activity_eng = 'Scuba Diving'

the reason it did not work earlier is because you said the city name was Nafplion, when in fact it was stored in the database as Nafplio

it’s the silly little things that trip us up, isn’t it :slight_smile:

17

Hi rudi thank you for testing and patience! This brings me one more time back to post #9. In the where clause I need to use the name of the textfield used for searching because when I used that IT didn’t return any results while testing:


WHERE city_eng LIKE '%#txtSearch#%'
 
WHERE activities.activity_eng LIKE '%#txtSearch#%'
18

what value of #txtSearch# are you using?

19

People are on the site. On every page is the search facility! So I never know what they will enter as search string? It is just a textfield

20

well, i’m sorry to tell you this, but you’re going to have to do a bit of work on what they enter into the search box

the first thing you should do is prevent SQL injection

second, you have to decide how to break it down… if they enter just one word (e.g. Nafplio) then obviousy you cannot use the query i gave you, because it won’t find ~both~ a city and an activity

so i am afraid that you will need to completely re-think what kind of query to run based on what they enter