query based on multiple custom fields

  • I would like to know how to display my posts using a query based on multiple custom fields. I have been through the initial example in the codex and it worked quite well for just one custom field, but I need a query based on 4 or 5.

    I tried to just add more in (as seen below) but that didn’t work.

    `$querystr = "
    SELECT wposts.*
    FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
    WHERE wposts.ID = wpostmeta.post_id
    AND wpostmeta.meta_key = 'model'
    AND wpostmeta.meta_value = '".$model."'
    AND wpostmeta.meta_key = 'city'
    AND wpostmeta.meta_value = '".$city."'
    AND wpostmeta.meta_key = 'state'
    AND wpostmeta.meta_value = '".$state."'
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    ORDER BY wposts.post_date DESC
    ";    ‘

    Any help would be awesome. Thanks.

Viewing 2 replies - 1 through 2 (of 2 total)

  • Your SQL logic is contradictory/impossible.

    Your tables have a one to many relationship… for a post with ID = foo, you’ll have three separate items in the postmeta table.

    ie, 

    posts                | postmeta
id      title        | id    meta_key   meta_value
----+----------------+----+-----------+-------------
17       'foobar'      17    'model'    'bar'
17       'foobar'      17    'city'     'smallville'
17       'foobar'      17    'state'    'FL'

    so you can’t possibly have a meta_key = ‘model’ AND = ‘city’ (in the same record)

    Here’s a rudimentary solution that will result in 3 records (with the same id and posts data but different meta data) for each unique id, it’s a start … and a tip – test the SQL out on your DB first manually, it’s easier to debug. (I shortened up the SQL using p for posts and m for the meta table)

    SELECT wposts.*
FROM $wpdb->posts p, $wpdb->postmeta m
WHERE p.ID = m.post_id
AND (
  (m.meta_key = 'model' AND m.meta_value = '".$model."')
  OR
  (m.meta_key = 'city' AND m.meta_value = '".$city."')
  OR
  (m.meta_key = 'state' AND m.meta_value = '".$state."')
)
AND p.post_status = 'publish' AND p.post_type = 'post'
ORDER BY p.post_date DESC

    This is the simple answer, but depending on how many $model matches you have, and what you’re doing with the results, determines your choice of how to improve it. Google for one to many SQL queries and you’ll see a lot of opinions.

    I noticed you weren’t retrieving the meta with the posts… so here’s an option to modify what I just posted.

    Use the SELECT to only return one unique row

    replace

    SELECT wposts.*

    with

    SELECT DISTINCT wposts.*

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘query based on multiple custom fields’ is closed to new replies.