Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

having problem in advanced search with attributes


jampack

Recommended Posts

hi all,

i have some problem with my search page

i am working on a search option in my website

but it is not working correctly.

actually i have a laptop search option in my web.

like user can select processor type, memory, harddrive, screen size, weight

but right now if i select 1 of above options query works fine and i get results according to it.

but if i select more than 1 option like i selected processor type, memory and harddrive from options list

then i will get results in which machines listed with matching processor type or matching memory or matching harddrive

but i want to show only those machines which have selected process type with selected memory and harddive.

i have tried differnet quries but those didnt worked for me.

i am posting my query here

----------------->

select count(distinct p.products_id) as total from ((products p) left join manufacturers m

using(manufacturers_id), products_description pd) left join specials s on p.products_id = s.products_id,

categories c, products_to_categories p2c,products_attributes pa where p.products_status = '1' and

p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and

p2c.categories_id = c.categories_id and m.manufacturers_id = '14' and pa.options_values_id in

(3,23,14,9,27,37) and p.products_id = pa.products_id

<-----------------

pa_options_values.id in (3,23,14,9,27,37)

the values in above bracket are the ids of my products options

3 is the id of my processor type and 23 is for memory and so on

but this query shows all the laptops with matching processor type or matching memory or matching harddrive etc

please tell me what should i change in above query that will only show that machines with selected processer type

having selected memory with selected harddrive and etc.

thanks

Link to comment
Share on other sites

The in() clause means to select any records that contain *any* of the specified values.

"Great spirits have always found violent opposition from mediocre minds. The latter cannot understand it when a man does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses his intelligence." - A. Einstein

Link to comment
Share on other sites

You will need to provide me with a breakdown of the products_options_ids for the hardware (e.g. 1 = cpu, 2 = memory, 3= hard drive, etc.) and then a breakdown of the products_options_values_ids for the option values (e.g. 3 is the cpu, 23 = ram, etc.) so that I can correlate them properly. ;)

"Great spirits have always found violent opposition from mediocre minds. The latter cannot understand it when a man does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses his intelligence." - A. Einstein

Link to comment
Share on other sites

hmm okies then below are the option with their id's

in product_optins table i have a column for

products_options_id, language_id and products_options_name

1 1 Processor

2 1 Memory

3 1 Harddrive

4 1 Graphics

5 1 Screen

6 1 Weight

============================

then in products_options_values table i have

6 1 AMD Athlon

5 1 Intel Celeron

3 1 Intel Centrino

4 1 Intel Celeron M

2 1 Intel Core Duo

1 1 Intel Pentium M

7 1 AMD Sempron

8 1 AMD Turion 64

9 1 Integrated

10 1 ATI Dedicated

11 1 nVidia Dedicated

12 1 20GB

13 1 40GB

14 1 60GB

15 1 80GB

16 1 100GB

17 1 120GB

18 1 140GB

19 1 160GB

20 1 180GB

21 1 200GB

22 1 256MB

23 1 512MB

24 1 1GB

25 1 2GB

27 1 16 in - 16.9 in

26 1 17 in - 17.9 in

28 1 15 in - 15.9 in

29 1 14 in - 14.9 in

30 1 13 in - 13.9 in

31 1 12 in - 12.9 in

32 1 12 in - 12.9 in

33 1 11 in - 11.9 in

34 1 10 in - 10.9 in

35 1 8.6 in

36 1 8 lbs and above

37 1 7 - 7.9 lbs

38 1 6 - 6.9 lbs

39 1 5 - 5.9 lbs

40 1 4 - 4.9 lbs

41 1 3 - 3.9 lbs

42 1 Under 3 lbs

===========================

then in products_options_values_to_products_options tables i have

24 4 11

23 4 10

22 4 9

21 1 8

20 1 7

19 1 6

18 1 5

17 1 4

15 1 2

16 1 3

14 1 1

25 3 12

26 3 13

27 3 14

28 3 15

29 3 16

30 3 17

31 3 18

32 3 19

33 3 20

34 3 21

35 2 22

36 2 23

37 2 24

38 2 25

43 5 27

42 5 26

44 5 28

45 5 29

46 5 30

47 5 31

48 5 32

49 5 33

50 5 34

51 5 35

52 6 36

53 6 37

54 6 38

55 6 39

56 6 40

57 6 41

58 6 42

 

 

now please tell me how can i solve my problem

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...