SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    extract multiabple values based on id

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    WHERE
       (meta_key = 'book' AND meta_value = 'mysql book')
    OR (meta_key = 'dvd'  AND meta_value = 'action')

  3. #3
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by eben0 View Post
    if a user search for 'mysql book' and 'animation'
    That's not what you asked. You asked:
    Quote Originally Posted by eben0 View Post
    i want to extract meta_key = 'book' with the meta_value 'mysql book' and meta_key = 'dvd' with meta_value 'action'
    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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    guido, you forgot GROUP BY post_id HAVING COUNT(*)=2 in post #2

    the post_id wants to have both attributes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    guido, you forgot GROUP BY post_id HAVING COUNT(*)=2 in post #2

    the post_id wants to have both attributes
    'Forgot' isn't the right word... since I don't understand what the OP needs

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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    SitePoint Wizard rguy84's Avatar
    Join Date
    Sep 2005
    Location
    Durham, NC
    Posts
    1,659
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    are you doing these searches in multiple input or combo boxes?
    Ryan B | My Blog | Twitter

  10. #10
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well i managed to solve it using HAVING COUNT(*) = 2.

    thanks a lot.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •