Hi there, I've built my first shooping basket.. yey me! (for a t-shirt Co) However the search results page takes ages. the query checks stock levels, and gender (mens, womens etc) and fetches the brand title... it then returns any items that have stock in that gender.
The prodcatmatch table is there, as I add a few extra filters on when the user refines their search later.. Anyhoo, here's my query and table structure... I've been using MySQL for a few years now, but never anthing this 'complicated' any advise would help me learn alot!! Tris... ==================== SELECT distinct products.id, products.title, brands.title as brandTitle, products.thumb2, products.price, products.salePrice, products.saleStatus FROM products, stock, prodcatmatch, categories as parent, categories as child, brands WHERE parent.gender = '1' AND stock.productId = products.id AND stock.count != "0" AND brands.id = products.brand AND parent.type = '2' AND parent.id = stock.size LIMIT 0,24 ==================== -- -- Table structure for table `brands` -- CREATE TABLE `brands` ( `id` int(11) NOT NULL auto_increment, `title` varchar(255) NOT NULL default '', `logo` varchar(255) NOT NULL default '', `desc` mediumtext NOT NULL, `type` int(1) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ; -- -------------------------------------------------------- -- -- Table structure for table `categories` -- // // Categories holds gender info AND size, so I've used two calls to it in my query. // CREATE TABLE `categories` ( `id` int(11) NOT NULL auto_increment, `shortName` varchar(255) NOT NULL default '', `displayName` varchar(255) NOT NULL default '', `type` int(2) NOT NULL default '0', `gender` int(2) NOT NULL default '0', `active` int(1) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=155 ; -- -------------------------------------------------------- -- -- Table structure for table `prodcatmatch` -- CREATE TABLE `prodcatmatch` ( `id` int(5) NOT NULL auto_increment, `product` int(5) NOT NULL default '0', `category` int(5) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=256 ; -- -------------------------------------------------------- -- -- Table structure for table `products` -- CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `productCode` varchar(100) NOT NULL default '', `color` int(3) NOT NULL default '0', `ethical` int(1) NOT NULL default '0', `bestSeller` int(1) NOT NULL default '0', `whatHot` int(1) NOT NULL default '0', `lastSeen` int(1) NOT NULL default '0', `getLook` int(1) NOT NULL default '0', `isNew` int(1) NOT NULL default '0', `inPress` int(1) NOT NULL default '0', `styleCategory` varchar(255) NOT NULL default '', `title` varchar(255) NOT NULL default '', `desc` mediumtext NOT NULL, `img1` varchar(255) NOT NULL default '', `img2` varchar(255) NOT NULL default '', `img3` varchar(255) NOT NULL default '', `img4` varchar(255) NOT NULL default '', `thumb1` varchar(255) NOT NULL default '', `thumb2` varchar(255) NOT NULL default '', `thumb3` varchar(255) NOT NULL default '', `thumb4` varchar(255) NOT NULL default '', `price` decimal(4,2) NOT NULL default '0.00', `salePrice` decimal(4,2) NOT NULL default '0.00', `saleStatus` int(1) NOT NULL default '0', `active` int(1) NOT NULL default '0', `brand` int(2) NOT NULL default '0', `relatedProd1` int(6) NOT NULL default '0', `relatedProd2` int(6) NOT NULL default '0', `relatedProd3` int(6) NOT NULL default '0', `relatedProd4` int(6) NOT NULL default '0', `relatedProd5` int(6) NOT NULL default '0', `relatedProd6` int(6) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=125 ; -- -------------------------------------------------------- -- -- Table structure for table `stock` -- CREATE TABLE `stock` ( `id` int(11) NOT NULL auto_increment, `productId` int(4) NOT NULL default '0', `size` int(2) NOT NULL default '0', `count` int(5) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10823 ; ____ � The WDVL Discussion List from WDVL.COM � ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: [email protected] To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
