Filtering Product

**amitpatel66** · Mar 4 '08, 12:18 PM

Originally posted by ovmysql

Hi All,
I cant product filtering. Please help.

CREATE TABLE filter (
product_no int(11) unsigned NOT NULL,
category_no int(11) unsigned NOT NULL,
PRIMARY KEY (product_no,cat egory_no)
);

INSERT INTO filter (product_no,cat egory_no)VALUES (1,68),(1,72),( 1,88),(1,97),(1 ,104),(1,117),( 1,124),(1,126), (1,130),(1,133) ,(2,68),(2,72), (2,88),(2,97),( 2,104),(2,117), (2,124),(2,127) ,(2,130),(2,133 ),(3,68),(3,73) ,(3,89),(3,97), (3,104),(3,117) ,(3,123),(3,126 ),(3,129),(3,13 3),(4,68),(4,72 ),(4,90),(4,95) ,(4,103),(4,117 ),(4,124),(4,12 7),(4,129),(4,1 33),(5,68),(5,7 3),(5,90),(5,95 ),(5,102),(5,11 7),(5,124),(5,1 27),(5,129),(5, 133),(6,68),(6, 73),(6,90),(6,9 5),(6,102),(6,1 17),(6,123),(6, 126),(6,129),(6 ,132);

select category_no,cou nt(product_no) from filter group by category_no;

Code:

-------------------------------
category_no	count(product_no)
68                       6
72                       3
73                       3
88                       2
89                       1
90                       3
95                       3
97                       3
102                       2
103                       1
104                       3
117                       6
123                       2
124                       4
126                       3
127                       3
129                       4
130                       2
132                       1
133                       5
-------------------------------

For example
AMD 68 6
2ghz 117 6
2GB RAM 124 4
xyz ... .
abc ... .

i want filtering and count products (2ghz and AMD) ... (2BG and AMD)
or
(AMD and 2ghz and 2 BG) and (AMD and 2ghz and xyz)

AMD>2GHZ>200GB HD> .....

Your requirement is not clear. Your table contains only two columns and the example sample data that you have shown contains data like AMD,2GHZ etc. Could you please be more descriptive in your explanation?

**ovmysql** · Mar 4 '08, 12:44 PM

Thanks for reply and my bad english sorry.
i want like above

(picture 1) i click installed ram, al least 1 gb then recount other filters with al least 1 gb ram.

in my table i cant count products. my theroical sql

[CODE=mysql]
select category_no,cou nt(product_no) from filter
where HOW together? category_no=68
group by category_no
[/CODE]

[CODE=mysql]
select category_no,cou nt(product_no) from filter
where HOW together? category_no=68 and category_no=117
group by category_no
[/CODE]

Computers> Hardware> PC Laptops

Computers> Hardware> PC Laptops > At least 1 GB

**amitpatel66** · Mar 4 '08, 01:23 PM

Are you looking at counting the number of items in each product then you can try this:

[code=sql]

SELECT product_no,COUN T(category_no) FROM filter
GROUP BY product_no

[/code]

If you are looking at counting the number of -products for aparticular category then try this:

[code=sql]

select category_no,COU NT(product_no) FROM filter WHERE category_no IN (68,117) GROUP BY category_no

[/code]

**amitpatel66** · Mar 4 '08, 01:31 PM

Are you looking at something like this:

[code=sql]

select category_no,pro duct_no,count(c ategory_no) FROM filter WHERE category_no = 68 AND category_no = 117 GROUP BY product_no,cate gory_no

[/code]

The above query will give you the product that contains category 68 and 117 and its count

**ovmysql** · Mar 4 '08, 03:14 PM

New simply table.

[CODE=mysql]

CREATE TABLE z (
product_no int(11) unsigned NOT NULL,
category_no int(11) unsigned NOT NULL,
PRIMARY KEY (`product_no`,` category_no`)
);

INSERT INTO z(product_no, category_no)
VALUES (1, 68),
(1, 72),
(2, 68),
(2, 72),
(2, 127),
(2, 130),
(2, 133),
(3, 126),
(3, 129),
(3, 133),
(4, 127),
(4, 129),
(4, 133);
[/CODE]

**amitpatel66** · Mar 4 '08, 04:10 PM

The images are not gettting displayed in the post and I am not able to access the URL since it is banned here.

Anyways, What excactly the image contains, I think you can provide more description of the problem in words rather images.

I have given few queries in my above posts if they are useful

**ovmysql** · Mar 5 '08, 12:34 PM

--------------------------------
[product_no]_________ [filter]
1______________ _______2BG RAM
1______________ _______INTEL
1______________ _______100 GBHD
2______________ _______2GB RAM
2______________ _______AMD
3______________ _______120 GBHD
4______________ _______4GB RAM
4______________ ________AMD

1. problem
i need like this:
[filter]_____[product count]
2BG RAM_________(2)
4BG RAM_________(1)
100 GBHD________(1)
AMD____________ _(2)
INTEL__________ _(1)

2. problem
and how many product with 2GB RAM:
2GB RAM and 100 GBHD
2GB RAM and AMD
2GB RAM and INTEL

i need like this: (in web navigation Computer>Laptop >2GB RAM)
[multi_filter]_____________[count]
2GB RAM + 100 GBHD___________ (1)
2GB RAM + AMD____________ ____(1)
2GB RAM + INTEL__________ ____(0)
.............

for web:
Laptop
_____2GB RAM (2)
___________100 GBHD (1)
____________AMD (1)

Thanks

Filtering Product

Filtering Product

Comment

Comment

Comment

Comment

Comment

Comment

Comment