当前语句是查询商品分类1下的所有商品.(可放在你的数据库里查询)
select
products_to_categories.products_id, categories_id, products_price, products_image, products_model, products_name, products_description
from
products_to_categories, products_description, products
where
products.products_id = products_to_categories.products_id
AND products_description.products_id = products_to_categories.products_id
AND categories_id = " 1 "//这里是商品分类id为1的分类.
AND products_description.language_id = $languages_id
AND products_price != 0
AND products_status = '1'
order by products_name
但是如果商品分类一下还有其他分类. 如:
商品分类1-1
产品1
商品分类1-2
产品2
产品3
查询的结果是产品3.
如果我要列出商品分类1下的所有商品包括子目录下的
那么
sql语句该是怎样的呢?
该用一个怎样的函数得出分类1,和他下面其他分类的categories_id
谁能帮我写这个函数,并给出sql查询语句我将万分感激.
谢谢.
======================================================
当前的表结构
# phpMyAdmin SQL Dump
# version 2.5.6
# http://www.phpmyadmin.net
#
# 主机: localhost
# 生成日期: 2004 年 10 月 09 日 19:51
# 服务器版本: 3.23.42
# PHP 版本: 4.1.2
#
# 数据库 : `ylforlixin`
#
# --------------------------------------------------------
#
# 表的结构 `categories`
#
CREATE TABLE `categories` (
`categories_id` int(11) NOT NULL auto_increment,
`categories_image` varchar(64) default NULL,
`parent_id` int(11) NOT NULL default '0',
`sort_order` int(3) default NULL,
`date_added` datetime default NULL,
`last_modified` datetime default NULL,
PRIMARY KEY (`categories_id`),
KEY `idx_categories_parent_id` (`parent_id`)
) TYPE=MyISAM AUTO_INCREMENT=39 ;
#
# 导出表中的数据 `categories`
#
INSERT INTO `categories` VALUES (1, NULL, 0, 1, '2004-10-08 10:42:30', '2004-10-08 11:51:13');
INSERT INTO `categories` VALUES (2, NULL, 0, 2, '2004-10-08 10:42:57', '2004-10-08 11:51:22');
INSERT INTO `categories` VALUES (3, NULL, 0, 3, '2004-10-08 10:43:24', '2004-10-08 11:51:31');
INSERT INTO `categories` VALUES (4, NULL, 0, 6, '2004-10-08 10:44:15', '2004-10-08 11:58:04');
INSERT INTO `categories` VALUES (5, NULL, 0, 4, '2004-10-08 10:44:42', '2004-10-08 11:51:44');
INSERT INTO `categories` VALUES (35, NULL, 1, 0, '2004-10-09 17:08:58', NULL);
INSERT INTO `categories` VALUES (36, NULL, 1, 0, '2004-10-09 17:33:19', NULL);
INSERT INTO `categories` VALUES (37, NULL, 2, 0, '2004-10-09 17:35:46', NULL);
INSERT INTO `categories` VALUES (38, NULL, 2, 0, '2004-10-09 17:35:58', NULL);
# --------------------------------------------------------
#
# 表的结构 `categories_description`
#
CREATE TABLE `categories_description` (
`categories_id` int(11) NOT NULL default '0',
`language_id` int(11) NOT NULL default '1',
`categories_name` varchar(32) NOT NULL default '',
PRIMARY KEY (`categories_id`,`language_id`),
KEY `idx_categories_name` (`categories_name`)
) TYPE=MyISAM;
#
# 导出表中的数据 `categories_description`
#
INSERT INTO `categories_description` VALUES (1, 4, '主板');
INSERT INTO `categories_description` VALUES (1, 6, 'Motherboards');
INSERT INTO `categories_description` VALUES (2, 4, '处理器');
INSERT INTO `categories_description` VALUES (2, 6, 'Processors');
INSERT INTO `categories_description` VALUES (3, 4, '内存');
INSERT INTO `categories_description` VALUES (3, 6, 'Memory');
INSERT INTO `categories_description` VALUES (4, 4, '移动存储器');
INSERT INTO `categories_description` VALUES (4, 6, 'More Memory');
INSERT INTO `categories_description` VALUES (5, 4, '硬盘');
INSERT INTO `categories_description` VALUES (5, 6, 'Hard Drives');
INSERT INTO `categories_description` VALUES (35, 4, '华硕主板');
INSERT INTO `categories_description` VALUES (35, 6, 'Asus Mainbord');
INSERT INTO `categories_description` VALUES (36, 4, '微星主板');
INSERT INTO `categories_description` VALUES (36, 6, 'MSI Mainbord');
INSERT INTO `categories_description` VALUES (37, 4, 'interl');
INSERT INTO `categories_description` VALUES (37, 6, '');
INSERT INTO `categories_description` VALUES (38, 4, 'AMD');
INSERT INTO `categories_description` VALUES (38, 6, '');
# --------------------------------------------------------
#
# 表的结构 `languages`
#
CREATE TABLE `languages` (
`languages_id` int(11) NOT NULL auto_increment,
`name` varchar(32) NOT NULL default '',
`code` char(2) NOT NULL default '',
`image` varchar(64) default NULL,
`directory` varchar(32) default NULL,
`sort_order` int(3) default NULL,
PRIMARY KEY (`languages_id`),
KEY `IDX_LANGUAGES_NAME` (`name`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;
#
# 导出表中的数据 `languages`
#
INSERT INTO `languages` VALUES (6, 'English', 'en', 'icon.gif', 'english', 2);
INSERT INTO `languages` VALUES (4, '简体中文', 'zh', 'icon.gif', 'chinese', 0);
# --------------------------------------------------------
#
# 表的结构 `products`
#
CREATE TABLE `products` (
`products_id` int(11) NOT NULL auto_increment,
`products_quantity` int(4) NOT NULL default '0',
`products_model` varchar(12) default NULL,
`products_image` varchar(64) default NULL,
`products_bimage` varchar(64) default NULL,
`products_price` decimal(15,4) NOT NULL default '0.0000',
`products_date_added` datetime NOT NULL default '0000-00-00 00:00:00',
`products_last_modified` datetime default NULL,
`products_date_available` datetime default NULL,
`products_weight` decimal(5,2) NOT NULL default '0.00',
`products_status` tinyint(1) NOT NULL default '0',
`products_tax_class_id` int(11) NOT NULL default '0',
`manufacturers_id` int(11) default NULL,
`products_ordered` int(11) NOT NULL default '0',
PRIMARY KEY (`products_id`),
KEY `idx_products_date_added` (`products_date_added`)
) TYPE=MyISAM AUTO_INCREMENT=15 ;
#
# 导出表中的数据 `products`
#
INSERT INTO `products` VALUES (4, 0, '', 'ce1yat7CRe0k.jpg', NULL, '570.0000', '2004-10-08 11:35:11', NULL, NULL, '0.00', 1, 0, 0, 0);
INSERT INTO `products` VALUES (3, 0, '', 'ce1yat7CRe0k.jpg', NULL, '670.0000', '2004-10-08 11:05:47', '2004-10-08 11:37:16', NULL, '10.00', 1, 0, 0, 0);
INSERT INTO `products` VALUES (5, 0, '', 'ceYiKYmP3CyFM.jpg', NULL, '1550.0000', '2004-10-08 11:42:46', '2004-10-09 17:34:40', NULL, '0.00', 1, 0, 0, 0);
INSERT INTO `products` VALUES (6, 0, '', 'ce6m5L824Zks.jpg', NULL, '2030.0000', '2004-10-08 11:50:01', NULL, NULL, '0.00', 1, 0, 0, 0);
INSERT INTO `products` VALUES (7, 0, '', 'ceNWgDtVAMUds.jpg', NULL, '4040.0000', '2004-10-08 12:05:42', '2004-10-08 12:07:30', NULL, '0.00', 1, 0, 0, 0);
INSERT INTO `products` VALUES (9, 10, 'Custom', '', NULL, '0.0000', '0000-00-00 00:00:00', NULL, NULL, '30.00', 1, 1, NULL, 0);
INSERT INTO `products` VALUES (10, 10, 'Custom', '', NULL, '7680.0000', '0000-00-00 00:00:00', NULL, NULL, '30.00', 1, 1, NULL, 0);
INSERT INTO `products` VALUES (11, 10, 'Custom', '', NULL, '7680.0000', '0000-00-00 00:00:00', NULL, NULL, '30.00', 1, 1, NULL, 0);
INSERT INTO `products` VALUES (12, 0, '', '1865.jpg', NULL, '20.0000', '2004-10-09 17:11:12', '2004-10-09 17:35:26', NULL, '0.00', 1, 0, 0, 0);
INSERT INTO `products` VALUES (13, 0, '', NULL, NULL, '0.0000', '2004-10-09 17:39:21', NULL, NULL, '0.00', 1, 0, 0, 0);
INSERT INTO `products` VALUES (14, 0, '', NULL, NULL, '0.0000', '2004-10-09 17:39:49', NULL, NULL, '0.00', 1, 0, 0, 0);
# --------------------------------------------------------
#
# 表的结构 `products_description`
#
CREATE TABLE `products_description` (
`products_id` int(11) NOT NULL auto_increment,
`language_id` int(11) NOT NULL default '1',
`products_name` varchar(64) NOT NULL default '',
`products_description` text,
`products_url` varchar(255) default NULL,
`products_viewed` int(5) default '0',
PRIMARY KEY (`products_id`,`language_id`),
KEY `products_name` (`products_name`)
) TYPE=MyISAM AUTO_INCREMENT=41 ;
#
# 导出表中的数据 `products_description`
#
INSERT INTO `products_description` VALUES (5, 4, '二微星目录下的', '
\r\n\r\n\r\n | \r\n\r\n\r\n\r\n\r\n\r\n | \r\n\r\n\r\n\r\n\r\n\r\n | \r\n | \r\n\r\n\r\n\r\n | |
', '', 4);
INSERT INTO `products_description` VALUES (5, 6, 'MSI 875P Neo-FISR', '\r\n\r\n\r\n | \r\n\r\n\r\n\r\n\r\n\r\n | \r\n\r\n\r\n\r\n\r\n\r\n | \r\n | \r\n\r\n\r\n\r\n | |
', '', 1);
INSERT INTO `products_description` VALUES (4, 4, '华硕 P4PE-X/TE', '\r\n\r\n\r\n | \r\n\r\n\r\n\r\n\r\n\r\n | \r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n| | \r\n\r\n\r\n\r\n\r\n| 产品参数详细信息--软体管理 | \r\n | | \r\n\r\n| | \r\nBIOS性能 | \r\n2Mb Flash EEPROM Award BIOS支持增强型ACPI,DMI2,3,PnP,Green,TCAV 华硕 EZ Flash, 华硕 C.P.R, SM BIOS 2,3 华硕Crash-Free BIOS2 | \r\naram_id=272" target=_blank>揪错 | \r\n\r\n| | \r\n频率调整 | \r\n华硕 JumperFree免跳线功能 | \r\naram_id=886" target=_blank>揪错 | \r\n\r\n| | \r\n管理功能 | \r\nWOL by PME, WOR by PME | \r\naram_id=887" target=_blank>揪错 | \r\n\r\n| | \r\n电压 | \r\nCPU,电压调节功能 | \r\naram_id=888" target=_blank>揪错 | \r\n\r\n | \r\n | \r\n\r\n\r\n\r\n | |
', '', 0);
INSERT INTO `products_description` VALUES (3, 6, 'Asus P4PE-qqq', '\r\n\r\n\r\n | \r\n\r\n\r\n\r\n\r\n\r\n | \r\n\r\n\r\n\r\n\r\n | \r\n产品参数(enlish) | \r\n我要揪错 参数填充 | \r\n\r\n\r\n\r\n\r\n| | \r\n
提问者：zzyanglin 08-15 09:09
答复
| 我也来回答： |
不管你有没有帮助我们，瓦岗寨8万村民将感谢你。。。。。
|
[]
©2007 PhpRes.COM | | |
订阅该问题
悬赏分：20 - 2007年08月15日
