CODEWITHSUNDEEP
phpmysqlfaceted-search
user3727370
user3727370

Reputation: 35

Faceted Search using php

Is there any way to do faceted search without solr, lucene etc. Only php, MySQL and Javascript. I'm trying to implement at a classifieds website, but failed. Problem is when try to filter the attributes table. structure is as follows:

table items:

id    description user_id
1     my car       3
2     dream car    3
3     New car      3
4     Old car      4

Table Meta Fields:

id     meta name   
 1     Make
 2     Model
 3     Color
 4     Car Type
 5     Interior Color

Table Item Meta:

item_id    field_id     meta_value
 1           1          BMW
 1           2          3Series
 1           3          White
 1           4          Coupe
 1           5          Black
 2           1          BMW
 2           2          2Series
 2           3          Black
 2           4          Coupe
 2           5          Grey
 3           1          Honda
 3           2          Civic
 3           3          Red
 3           4          Sedan
 3           5          Black

Now i want to filter if someone selects Black BMW, but if i use IN its giving all black or BMW. But i want same with AND condition. If you know faceted search then you can easily know what i mean. I want to make query to do the same with the table structure above.

**Addition:**

Also i need to show count for each attribute searched and listed. Like:

Make:
BMW (2)
Color:
Black(2)
White(1)
...
Car Type:
....
Interior Color:
...

Upvotes: 1

Views: 985

Answers (1)

peterm
peterm

Reputation: 92815

Are you looking for something like this?

SELECT i.*, 
       MAX(CASE WHEN meta_name = 'Make' THEN meta_value END) make,
       MAX(CASE WHEN meta_name = 'Color' THEN meta_value END) color
  FROM item_meta im JOIN items i
    ON im.item_id = i.id JOIN meta m
    ON im.field_id = m.id
 GROUP BY i.id
HAVING MAX(meta_name = 'Make' AND meta_value = 'BMW') = 1
   AND MAX(meta_name = 'Color' AND meta_value = 'Black') = 1

Output:

| ID | DESCRIPTION | USER_ID | MAKE | COLOR |
|----|-------------|---------|------|-------|
|  2 |   dream car |       3 |  BMW | Black |

Here is SQLFiddle demo

Upvotes: 2

Related Questions