Hi,
We have a custom "shop" system, where we parse products from other sites (advertisers) and show them in our site.
The data in each site is always different so we have a field's table where the data fields of each site (and the regex to get them) are listed. We use a standard form field's name so we query for a name, regardless of the site. Then, we store the product information (site, url, date of parsing, etc) in a products table and the product data (each field) in a metadata table. Here is the schema:
Code:
CREATE TABLE `parsing_fields` (
`field_id` int(11) NOT NULL AUTO_INCREMENT,
`sites_id` int(11) NOT NULL,
`name` varchar(32) NOT NULL,
`regex` text NOT NULL,
PRIMARY KEY (`field_id`),
KEY `sites_id` (`sites_id`)
) ENGINE=MyISAM;
CREATE TABLE `parsing_products` (
`productID` int(11) NOT NULL AUTO_INCREMENT,
`product_id` varchar(64) NOT NULL,
`sites_id` int(11) NOT NULL,
`url` varchar(256) NOT NULL,
`fecha` date NOT NULL,
`revisado` tinyint(1) NOT NULL,
`mostrar` tinyint(4) NOT NULL,
`templates_id` int(11) NOT NULL DEFAULT '-1',
PRIMARY KEY (`productID`),
KEY `product_id` (`product_id`),
KEY `sites_id` (`sites_id`)
) ENGINE=MyISAM;
CREATE TABLE `parsing_products_meta` (
`productID` int(11) NOT NULL,
`field_id` int(11) NOT NULL,
`value` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`productID`,`field_id`),
KEY `field_id` (`field_id`),
FULLTEXT KEY `value` (`value`)
) ENGINE=MyISAM;
Now, we are making improvements in the system, and I need the value of two fields (product_name and product_short_description) of a productID in the parsing_products_meta table, and we need them in the same row :P
I tried a few things but couldn't made it. It can be done ? Any help with it ?
Also, after getting the rows, we are going to search related products using MATCH-AGAINST. I try this and seems to work:
Code:
SELECT productID, MATCH (value) AGAINST ('te negro') AS score
FROM parsing_products_meta
WHERE MATCH (value) AGAINST ('te negro') AND field_id IN ( 1, 2, 6, 7 )
ORDER BY score DESC
# 1 & 6 -> product_name of sites_id 1 & 2
# 2 & 7 -> product_short_descripcion of sites_id 1 & 2
Any better (or that works if this not work like I want) idea ?
Thank you in advance
dBforums
>
Help searching in metadata table
11-15-11, 06:55
Linear Mode
