Extract multiabple values based on id

Databases
1

hi i need some help,
i’m working on a search page for a client and i need to extract multiple values based on id.

in db

±--------±---------±---------------+
| post_id | meta_key | meta_value |
±--------±---------±---------------+
| 400 | book | mysql book |
| 500 | book | php book |
| 500 | dvd | animation |
| 400 | dvd | action |
±--------±------------±---------+

i want to extract meta_key = ‘book’ with the meta_value ‘mysql book’ and meta_key = ‘dvd’ with meta_value ‘action’.
they all have the same ‘post_id’ so i thought doing some JOIN but it didn’t work for me.

2 

WHERE
   (meta_key = 'book' AND meta_value = 'mysql book')
OR (meta_key = 'dvd'  AND meta_value = 'action')
3

if a user search for ‘mysql book’ and ‘animation’ it needs to return 0 results, now it returns 2 results, that’s why i need to check the post_id to match the value or key, any idea?

4

That’s not what you asked. You asked:

Please describe the process. What exactly can the user do on the search page? Choose one value? Choose multiple values?

What data do you get from the form to perform the query with? meta_key? meta_value? post_id?

5

guido, you forgot GROUP BY post_id HAVING COUNT(*)=2 in post #2

the post_id wants to have both attributes

6

‘Forgot’ isn’t the right word… since I don’t understand what the OP needs :smiley:

So that could well be the solution. I have no idea.

7 
SELECT someOtherTable.stuff
  FROM ( SELECT post_id
           FROM attributesTable
          WHERE meta_key = 'book' AND meta_value = 'mysql book'
             OR meta_key = 'dvd'  AND meta_value = 'action'
         GROUP
             BY post_id
         HAVING COUNT(*) = 2 ) AS a
INNER
  JOIN someOtherTable
    ON someOtherTable.post_id = a.post_id
8

i think to make it easier i just need to select meta_key and meta_value that have the same post_id. so if the user searches for dvd = ‘animation’ and book = ‘php book’ it will return no results because they don’t share the same post_id.

if the user search for dvd = ‘action’ and book = ‘mysql book’ in that case it would return both values, because they share the same post_id.

i hope i explained it better, it much more difficult to me to explain it in english.

edited: @r937 just saw your query, gonna try it now.

9

are you doing these searches in multiple input or combo boxes?

10

well i managed to solve it using HAVING COUNT(*) = 2.

thanks a lot.