semantic search and multiple joins problem

[diegomedeiros]

Hello friends.

I’m building a semantic search in PHP5/MYSQL5 and needs help.
When someone types a word related to the activity of the client, even if it does not appear in your registration, mysql should look that word in a table of synonyms and, if found, return the record.

My tables are:
Synonym Table

CREATE TABLE `guia_words` (
`word_id` int(11) NOT NULL AUTO_INCREMENT,
`word_word` varchar(25) NOT NULL,
`word_synonym` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`word_id`)
)

INSERT INTO `guia_words` VALUES (1, 'system', 0);
INSERT INTO `guia_words` VALUES (2, 'software', 1);
INSERT INTO `guia_words` VALUES (3, 'site', 1);
INSERT INTO `guia_words` VALUES (4, 'flowers', 0);
INSERT INTO `guia_words` VALUES (5, 'floriculture', 4);
INSERT INTO `guia_words` VALUES (6, 'art', 0);
INSERT INTO `guia_words` VALUES (7, 'artistical', 6);
INSERT INTO `guia_words` VALUES (8, 'theater', 7);
INSERT INTO `guia_words` VALUES (9, ‘tulipa’, 4);


Customers table

CREATE TABLE `guia_ads` (
`ad_id` int(11) NOT NULL AUTO_INCREMENT,
`ad_name` varchar(25) NOT NULL,
`ad_category` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ad_id`)
)

INSERT INTO `guia_ads` VALUES (1, 'Grandel Systems', 1);
INSERT INTO `guia_ads` VALUES (2, 'Frorandel Flowers', 2);
INSERT INTO `guia_ads` VALUES (3, 'Artandel Arts', 3);

Activity table

CREATE TABLE `guia_category` (
`cat_id` int(11) NOT NULL AUTO_INCREMENT,
`cat_name` varchar(25) NOT NULL,
PRIMARY KEY (`ad_id`)
)

INSERT INTO `guia_ category ` VALUES (1, 'Systems');
INSERT INTO `guia_ category ` VALUES (2, 'Floriculture');
INSERT INTO `guia_ category ` VALUES (3, 'Artistical');


Examples:
• when the word "systems" is sought, will show "Grandel systems” (normal search, by name).
• when the word "artistical" is sought, will show 'Frorandel Flowers' (search by activity).
• when the word "tulipa" is sought, will show "Frorandel Flowers” (semantic search, by synonym).

My question: how to build a mysql query?

My tentatives:

$query = $_GET[‘query’];//query string variable

sprintf("SELECT *,
(SELECT COUNT(ad_id) FROM guia_ads WHERE ad_nome LIKE %s ) as total,
(SELECT word_word FROM guia_words WHERE word_word LIKE %s ) as word
FROM guia_ads WHERE (ad_cat = (SELECT category_id FROM guia_category WHERE category_nome LIKE %s)) or (ad_name = %s) or word LIKE %s ORDER BY ad_name ASC",
$query, $query, $query, $query);

(dont work)...