Hi all...
I've not been here for AGES... but I hope many of you trusted clever
people are still out there... I need help! ;-p
I'm building a shop for a client.
I've got 3 tables I'm trying to talk to.
products
stock
categories
- Each product can be flagged with any numebr of cateogories using a
single field, with csv (12, 52, 101) (it's a T-shirt shop, so the
categories are for eg: film, quotes, funny, sports etc)
- in the stock table, each product is asigned several stock fields, of
each size/gender type.
- and the categories are all stored in a seperate table, for admin purposes.
What I want to do
is to only get a category name from the database, IF one or any of the
products are in stock.
Here's what I've got so far..
Any MySQL gurus out there? :-(
=
SELECT categories.displayName, stock.count FROM categories, products,
stock WHERE products.styleCategory LIKE '%categories.id%' AND
(stock.count != 0 AND stock.size = '4') OR (stock.count != 0 AND
stock.size = '5') OR (stock.count != 0 AND stock.size = '6') OR
(stock.count != 0 AND stock.size = '7') OR ( stock.count != 0 AND
stock.size = '8') OR (stock.count != 0 AND stock.size = '9') AND
categories.type = '5'
=
my table structure if it helps:
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',
`active` int(1) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=116 ;
--
--
-- Table structure for table `products`
--
CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`productCode` varchar(100) NOT NULL default '',
`styleCategory` varchar(255) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`desc` mediumtext NOT NULL,
`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',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
--
-- Table structure for table `stock`
--
CREATE TABLE `stock` (
`id` int(7) 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=1192 ;
� 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: wdvltalk@lists.wdvl.com
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: archive@jab.org
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.
