Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    14

    Help searching in metadata table

    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

  2. #2
    Join Date
    Oct 2011
    Posts
    14
    Any ideas ? Nobody

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nachitox View Post
    ... and we need them in the same row :P
    your php skillz are not sufficient to pull them off two separate rows?

    my condolences

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2011
    Posts
    14
    I need it in one because there several other tables where I get the same data and the code is the same for all the tables.

    I could make a class w/herence but too much trouble.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •