WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Search Custom Fields [more than one at a time] (13 posts)

  1. Zaphod
    Member
    Posted 4 years ago #

    Hi - I'm using the excellent tutorial here [http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query] to write custom queries to search through posts for a car site I'm creating.

    It's working perfectly if I just want to search using one meta key / value pair. But, I want to use several - e.g. Colour Red, Year 2007, Mileage 20k etc.... Here's a sample query:


    $querystr = "
    SELECT wposts.*
    FROM $wpdb->posts wposts
    LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
    WHERE wposts.ID = wpostmeta.post_id
    AND wpostmeta.meta_key = 'Make'
    AND wpostmeta.meta_value = 'Fiat'
    AND wpostmeta.meta_key = 'Colour'
    AND wpostmeta.meta_value = 'Red'
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    AND wposts.post_date < NOW()
    ORDER BY wposts.post_date DESC
    ";

    but it returns an empty set. Again, if I just use one key value pair (e.g. Make / Fiat from the example above) it works perfectly. Any suggestions?

  2. Mark / t31os
    Moderator
    Posted 4 years ago #

    Maybe try

    WHERE wposts.ID = wpostmeta.post_id
    AND wpostmeta.meta_key IN('Make','Colour')
    AND wpostmeta.meta_value IN('Fiat','Red')
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    AND wposts.post_date < NOW()
  3. MichaelH
    Member
    Posted 4 years ago #

    See if the example here helps:
    http://codex.wordpress.org/wpdb#SELECT_a_Column

  4. Zaphod
    Member
    Posted 4 years ago #

    t31os_,

    Many thanks for that post. It kinda helps, but is presenting a few problems. This query:


    SELECT wposts.*
    FROM cc_posts wposts, cc_postmeta wpostmeta
    WHERE wposts.ID = wpostmeta.post_id
    AND wpostmeta.meta_key IN ('Colour','Make','Model')
    AND wpostmeta.meta_value IN ('Silver','Renault','Laguna')
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    AND wposts.post_date < NOW()
    ORDER BY wposts.post_date DESC

    Returns a correct result but returns it three times. It seems that for each match (Colour, Make, Model) it's returning a row. So although there is only one Renault on the system, it's showing three times.

    Also if I change the colour to Blue (there *isn't* a blue Renault on system) it shows two rows (again, I'm guessing the two matches for Make and Model). It should show none as there are no Blue Renault Laguna's on the system...

    Huge thanks for the help though - you've got me further than I was.

    MichaelH - will be trying your solution shortly as well. I think I may need something more like this as some of my search critia will be comparison based - i.e. price more than 3k and less than 4k etc so....

    Will post back how I get on.

    Again... HUGE THANKS!

  5. Mark / t31os
    Moderator
    Posted 4 years ago #

    Sorry it was late for me when i wrote that reply (just before bed).

    I'm thinking you'll need a required join on the meta table.. My head's not really in SQL mode right now though..

    I'd suggest running your test queries in phpmyadmin's mysql line, it's far quicker for testing queries and seeing the result(s), or at least i find it to be.

  6. MichaelH
    Member
    Posted 4 years ago #

    I'd suggest running your test queries in phpmyadmin's mysql line, it's far quicker for testing queries and seeing the result(s), or at least i find it to be.

    Good advice!

  7. Zaphod
    Member
    Posted 4 years ago #

    Thanks for that - do I need to do a join for each extra variable I want though? I'll logon to the PHP MySqlAdmin to test the queries...

    You'd think (imho) that wordpress would have a feature that'd allow you to easily show the custom fields though....

  8. MichaelH
    Member
    Posted 4 years ago #

    You'd think (imho) that wordpress would have a feature that'd allow you to easily show the custom fields though....

    See Custom Fields for ideas.

  9. Zaphod
    Member
    Posted 4 years ago #

    Sorry - I should've said you'd think it'd be easier to do queries and show results based on custom field data..... it is easy enough to just show them....

  10. vtxyzzy
    Member
    Posted 4 years ago #

    I really don't have a good way to test this, but I think this is on the right track of what you want:

    SELECT wposts.*
    FROM cc_posts wposts
     LEFT JOIN cc_postmeta cc_color ON (wposts.ID = cc_color.post_id)
     LEFT JOIN cc_postmeta cc_make ON (wposts.ID = cc_make.post_id)
     LEFT JOIN cc_postmeta cc_model ON (wposts.ID = cc_model.post_id)
    WHERE 1=1
    AND (cc_color.meta_key = 'Colour' AND cc_color.meta_value = 'SILVER)
    AND (cc_make.meta_key = 'Make' AND cc_make.meta_value = 'Renault')
    AND (cc-model.meta_key = 'Model' AND cc_model.meta_value = 'Laguna')
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    AND wposts.post_date < NOW()
    ORDER BY wposts.post_date DESC
  11. Zaphod
    Member
    Posted 4 years ago #

    Thanks a million, that seems to be doing the trick. Just for anyone who might copy and paste and change variable names.... you missed a quote after Silver..

    AND (cc_color.meta_key = 'Colour' AND cc_color.meta_value = 'SILVER)

    and a - rather than a _

    AND (cc-model.meta_key = 'Model' AND cc_model.meta_value = 'Laguna')

    I feel bad even pointing that out as I think you've cracked it for me - thank you SO MUCH. I must get a book on SQL as while I'm pretty handy at the basic SQL, joins boil my brain.

    THANKS AGAIN. [I'll update on progress later just to hopefully confirm that this is the right tack]

  12. vtxyzzy
    Member
    Posted 4 years ago #

    You are welcome. And I take full responsibility for the typos - as I said, I had no way to test it! Please mark this topic 'Resolved'.

  13. Zaphod
    Member
    Posted 4 years ago #

    Just to confirm - works perfectly. Now I just need to dynamically write the queries and I'm done. THANKS SO MUCH.

Topic Closed

This topic has been closed to new replies.

About this Topic