I have a concern with four mysql tables: "users, sections, products(fullext) and content(fulltext)".
"Users" table store the personal information of each registered users like City -> "users.city".
"Sections" table are for separate the content of the shopping cart in each user and is supposed to have inherited users.idusers = sections.idusers.
"Content" fulltext table for flat web page and "products" fulltext table with price, etc (different type of information), both have inherited idsections key sections.idsections = content.idsections and sections.idsections = products.idsections.
I want to filter the content of the two full text table together by users.city LIKE "%Taxco%".
TABLES IN ORDER OF INHERITANCE:
users-> sections->products,content
So i want to query the next but i receive nothing back:
set @products_multiplier=2;
set @content_multiplier=1;
SELECT * FROM (
SELECT 'products' AS 'table_name', idproducts as id, @products_multiplier * (MATCH(products.name, products.description) AGAINST ('TACOS')) AS relevance
FROM products, sections, users WHERE users.city like "%Taxco%" AND sections.idusers=users.idusers AND products.idsections = sections.idsections
UNION
SELECT 'content' AS 'table_name', idcontent as id, @content_multiplier * (MATCH(content.name, content.description) AGAINST('TACOS')) AS relevance
FROM content,sections,users WHERE users.city like "%Taxco%" AND sections.idusers = users.idusers AND content.idsections = sections.idsections
) as sitewide WHERE relevance > 0;
The full sql text of the four tables are below:
--
-- Table structure for table content
CREATE TABLE IF NOT EXISTS content (
idcontent int(11) NOT NULL AUTO_INCREMENT,
idsections int(11) NOT NULL,
name varchar(100) NOT NULL,
description text NOT NULL,
picture varchar(55) NOT NULL,
PRIMARY KEY (idcontent,idsections),
UNIQUE KEY idcontent_UNIQUE (idcontent),
KEY idsections_idx (idsections),
FULLTEXT KEY name (name,description)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
-- Dumping data for table content
INSERT INTO content (idcontent, idsections, name, description, picture) VALUES
(1, 1, 'TACOS DE CHULETA', 'VENDEMOS TACOS DE CHULETA CON FRIJOLES', ''),
(2, 2, 'TACOS DE PASTOR PERO NO SON DE AQUI', 'TACOS DE PASTOR PERO NO SON DE AQUI', ''),
(3, 1, 'ELECTRODOMESTICOS', 'GRABADORAS, LICUADORAS, REFRIGERADORES', ''),
(4, 2, 'ELECTRODOMESTICOS', 'GRABADORAS DE VIDEO, TV SKY', ''),
(5, 1, 'JUEGUETES PARA NIÑOS', 'VENDO JUGUETES PARA NIÑOS', ''),
(6, 2, 'JUGUETES PARA NIÑOS', 'VENDO JUGUETES PARA NIÑOS', ''),
(7, 1, 'JUEGOS Y DEMÁS', 'JUEGOS Y PIÑATAS', '');
--
-- Table structure for table products
CREATE TABLE IF NOT EXISTS products (
idproducts int(11) NOT NULL AUTO_INCREMENT,
idsections int(11) NOT NULL,
name varchar(100) NOT NULL,
description text NOT NULL,
picture varchar(55) NOT NULL,
cost decimal(7,2) NOT NULL,
PRIMARY KEY (idproducts,idsections),
UNIQUE KEY idproducts_UNIQUE (idproducts),
FULLTEXT KEY name (name,description)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
-- Dumping data for table products
INSERT INTO products (idproducts, idsections, name, description, picture, cost) VALUES
(1, 1, 'TACOS', 'TACOS DE PASTOR Y SUADERO', '', '0.00'),
(2, 1, 'Jicamas y frutales', 'Venta de fruta con yogurt, etc', '', '0.00'),
(3, 1, 'TACOS DE BISTEK', 'TACOS DE BISTEK Y CARNITAS', '', '0.00'),
(4, 1, 'JUGUETES', 'JUGUETES DE NIÑOS', '', '0.00'),
(5, 1, 'TELEVISIONES Y MICROONDAS', 'VENDEMOS TELEVISIONES Y MICROONDAS', '', '0.00'),
(6, 2, 'TACOS PERO NO SON DE AQUI', 'TACOS PERO NO SON DE AQUI', '', '0.00'),
(7, 2, 'grabadoras y microondas', '', '', '0.00');
--
-- Table structure for table sections
CREATE TABLE IF NOT EXISTS sections (
idsections int(11) NOT NULL AUTO_INCREMENT,
idusers int(11) NOT NULL,
type tinyint(4) NOT NULL COMMENT 'type = 1 = catalog\ntype = 2 = single product\ntype = 3 = generic content',
name varchar(50) NOT NULL,
PRIMARY KEY (idsections,idusers),
UNIQUE KEY idsections_UNIQUE (idsections),
KEY idusers_idx (idusers)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table sections
INSERT INTO sections (idsections, idusers, type, name) VALUES
(1, 5, 0, 'TACOS CENTRO'),
(2, 7, 0, 'UNRELETAED CONTENT');
--
-- Table structure for table users
CREATE TABLE IF NOT EXISTS users (
idusers int(11) NOT NULL AUTO_INCREMENT,
path varchar(55) NOT NULL,
username varchar(50) NOT NULL,
email varchar(100) NOT NULL,
password varchar(255) NOT NULL,
type int(1) NOT NULL DEFAULT '0',
creation timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
lastpayment date NOT NULL DEFAULT '0000-00-00',
active tinyint(1) NOT NULL DEFAULT '1',
name varchar(100) NOT NULL,
lastname varchar(100) NOT NULL,
state varchar(30) NOT NULL,
city varchar(50) NOT NULL,
community varchar(50) NOT NULL,
neighborhood varchar(50) NOT NULL,
cp varchar(5) NOT NULL,
street varchar(50) NOT NULL,
noex varchar(10) NOT NULL,
noint varchar(10) NOT NULL,
phone varchar(10) NOT NULL,
cellphone varchar(10) NOT NULL,
url varchar(250) NOT NULL,
logo varchar(55) NOT NULL,
PRIMARY KEY (idusers),
UNIQUE KEY idusers_UNIQUE (idusers),
UNIQUE KEY email_UNIQUE (email),
UNIQUE KEY username_UNIQUE (username)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
--
-- Dumping data for table users
INSERT INTO users (idusers, path, username, email, password, type, creation, lastpayment, active, name, lastname, state, city, community, neighborhood, cp, street, noex, noint, phone, cellphone, url, logo) VALUES
(1, '', 'jorgeortega11', 'jorgeortega11@hotmail.com', '123456', 1, '2014-05-26 05:03:13', '0000-00-00', 1, '', '', 'Guerrero', 'Taxco de Alarcón', '', '', '', '', '', '', '', '', '', ''),
(2, '', 'jorgeortega22', 'jorgeortega22@hotmail.com', '123456', 1, '2014-05-26 05:44:39', '0000-00-00', 1, '', '', '', '', '', '', '', '', '', '', '', '', '', ''),
(3, '', 'jorgeortega56', 'jorgeortega56@hotmail.com', '123456', 1, '2014-05-27 04:15:16', '0000-00-00', 1, '', '', '', '', '', '', '', '', '', '', '', '', '', ''),
(4, '', 'otroyo', 'otroyo@hotmail.com', '123456', 1, '2014-05-27 06:04:07', '0000-00-00', 1, '', '', '', '', '', '', '', '', '', '', '', '', '', ''),
(5, 'imagesbd/users/org/', 'jorgeortega36', 'jorgeortega36@hotmail.com', '123456', 1, '2014-05-27 06:41:54', '0000-00-00', 1, 'JORGE ', 'ORTEGA', 'Guerrero', 'Taxco de Alarcón', 'Atzala (Atzala de la Asunción) ', '', '', '', '', '', '762622', '762106', 'http://www.akimero.com.mx', '1401177355_logo.jpg'),
(6, 'imagesbd/users/org/', 'expec', 'expec@hotmail.com', '123456', 1, '2014-05-27 08:06:32', '0000-00-00', 1, 'JORGE', 'ORTEGA', 'Guerrero', 'Taxco de Alarcón', 'Atzala (Atzala de la Asunción) ', 'x', 'x', 'x', 'x', 'x', '762622', '', '', ''),
(7, 'imagesbd/users/org/', 'expecti1', 'expecti@gmail.com', '123456', 1, '2014-05-28 04:04:27', '0000-00-00', 1, 'JORGE PATRICIO', 'ORTEGA LÓPEZ', 'Aguascalientes', 'Aguascalientes', 'Aguascalientes ', 'AGUASCALIENTES', '40200', 'AGUASCALIENTES', '457 BIS', 'A BIS', '7626222', '', '', ''),
(8, 'imagesbd/users/org/', 'goerge', 'george@hotmail.com', '123456', 1, '2014-05-30 23:27:10', '0000-00-00', 1, 'JORGE', 'ORTEGA', 'Durango', 'Cuencamé', 'Minerales de Avino S.A. de C.V. ', 'x', 'x', 'x', 'x', 'x', '762622', '7621067', 'http://www.akimero.com.mx', '1401492520_logo.jpg');