I have more than 4610219 product names (not unique). These numbers increase by atleast 3000+ each day. They are stored into the “products” table. I need to create a dashboard which would show:
1. Top 10 searched products (by Day, Month, Year)
2. Top 3 “Buy Now” prices or “Average Prices” from eBay for each of the product for that day
3. Associate the existing and new product with the the eBay Category id using eBay Api
4. For each of the product listed find the first site listed in Google and display on the Dashboard
Note: in the table below “product_category_id” is the eBay Category Id .
–
– Table structure for table `products`
–
CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL auto_increment,
`product_name` varchar(256) NOT NULL,
`product_category_id` int(11) NOT NULL default ’0′,
`search_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`status` char(1) NOT NULL default ‘Y’,
PRIMARY KEY (`id`),
KEY `product_name` (`product_name`),
KEY `search_timestamp` (`search_timestamp`),
KEY `product_category_id` (`product_category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=’Products’ AUTO_INCREMENT=4610219 ;
–
– Example of data dump for table `products`
–
INSERT INTO `products` (`id`, `product_name`, `product_category_id`, `search_timestamp`, `status`) VALUES
(1, ‘power wheels barbie’, 11746, ’2009-02-15 03:58:41′, ‘Y’),
(2, ‘basketball hoop’, 21196, ’2009-02-15 03:58:41′, ‘Y’),
(3, ‘dresser’, 20478, ’2009-02-15 03:58:41′, ‘Y’),
(4, ‘zen alarm clock’, 79643, ’2009-02-15 03:58:41′, ‘Y’),
(5, ‘flashlight’, 106987, ’2009-02-15 03:58:41′, ‘Y’),
(6, ‘dsl modems’, 62025, ’2009-02-15 03:58:41′, ‘Y’),
(7, ‘makeup brushes’, 36412, ’2009-02-15 03:58:41′, ‘Y’),
(8, ‘card table’, 25550, ’2009-02-15 03:58:41′, ‘Y’),
(9, ‘jewelry boxes’, 11707, ’2009-02-15 03:58:41′, ‘Y’),
(10, ‘cat collar’, 66763, ’2009-02-15 03:58:41′, ‘Y’),
(11, ‘pepper’, 86917, ’2009-02-15 03:58:41′, ‘Y’),
(12, ‘body glove wetsuit’, 20336, ’2009-02-15 03:58:41′, ‘Y’),
(13, ‘grandfather clocks’, 378, ’2009-02-15 03:58:41′, ‘Y’),
(14, ‘olive oil’, 11845, ’2009-02-15 03:58:41′, ‘Y’),
(15, ‘high heels’, 63889, ’2009-02-15 03:58:41′, ‘Y’);
