WordPress.org

Ready to get started?Download WordPress

Forums

query based on multiple custom fields (3 posts)

  1. Josh Byers
    Member
    Posted 6 years ago #

    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.

  2. Roger Theriault
    Member
    Posted 6 years ago #

    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.

  3. Roger Theriault
    Member
    Posted 6 years ago #

    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.*

Topic Closed

This topic has been closed to new replies.

About this Topic