CODEWITHSUNDEEP
mysqlsqlinternationalization
user3807877
user3807877

Reputation: 123

Internationalization of sql query

I have the following SQL tables:

-- Create syntax for TABLE 'companies'
CREATE TABLE `companies` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `country` varchar(255) NOT NULL DEFAULT '',
  `city` varchar(255) NOT NULL DEFAULT '',
  `address` text NOT NULL,
  `logo` varchar(255) NOT NULL DEFAULT 'empty',
  `size` int(11) NOT NULL DEFAULT '32',
  PRIMARY KEY (`id`),
  KEY `city` (`city`),
  KEY `country` (`country`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- Create syntax for TABLE 'i18n'
CREATE TABLE `i18n` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lang` varchar(2) DEFAULT NULL,
  `word` text,
  `english` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- Create syntax for TABLE 'tags'
CREATE TABLE `tags` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- Create syntax for TABLE 'tagsForCompany'
CREATE TABLE `tagsForCompany` (
  `company` int(11) DEFAULT NULL,
  `tid` int(11) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have the following sql query (thanks to @Rockse): 

SELECT c.*
FROM 
  (SELECT *
   FROM companies
   WHERE city = "<Some City>" AND country = "<Some Country>") AS c
  INNER JOIN tagsForCompany AS tc ON c.id = tc.Company
  INNER JOIN tags AS t ON t.id = tc.TID
WHERE t.Name REGEXP '<a keyword>'

I need to be able to search for Companies with a keyword written in another language by searching in the i18n table for a translation. So that searching for restaurant will give the same results as searching for レストラン (restaurant in Japanese) or ristorante (restaurant in italian).

Honestly I have no idea what to edit in the query, my SQL knowledge is a bit limited.

Upvotes: 1

Views: 515

Answers (1)

user3807877
user3807877

Reputation: 123

The solution was easier than I thought: If someone would improve, I would be more than happy 

SELECT c.*
FROM i18n, (SELECT *
   FROM companies
   WHERE city = "<some city>" AND country = "<some country>") AS c
  INNER JOIN tagsForCompany AS tc ON c.id = tc.Company
  INNER JOIN tags AS t ON t.id = tc.TID
WHERE t.Name = i18n.`english` AND i18n.word REGEXP "<some word>" OR t.Name REGEXP "<the same word as before>"

But for doing that I had to change all the tables to utf-8

Upvotes: 2

Related Questions