WordPress.org

Ready to get started?Download WordPress

Forums

mysql headache nightmare! (multiple meta_keys) (17 posts)

  1. Robert Simpson
    Member
    Posted 3 years ago #

    Hi folks,

    A bit of background info:
    I'm building a theme for a band and this theme is going to include tour posters as featured images within the custom post type of 'concerts'.
    I already have the concert listings working as I'd like, using a custom field '_event_date' to determine the order in which they're displayed and whether they're displayed at all (no concerts prior to today's date are displayed).

    This was all well and good until I tried to have it check for the existence of '_thumbnail_id' as a meta_key also and now within the query the meta_key for _thumbnail_id is cancelling out the meta_key for _event_date.

    Not good.

    Here's the code I'm using just now to get the upcoming concerts out of the database:

    global $wpdb;
    $blog_prefix = $wpdb->prefix;
    $today = strtotime("today");
    
    $upcoming_concert_posters_query = "
    	SELECT *
    	FROM ".$blog_prefix."posts, ".$blog_prefix."postmeta
    	WHERE ID = post_id
    	AND post_type = 'concerts'
    	AND meta_key = '_event_date'
    	AND meta_value >= ".$today."
    	AND post_status = 'publish'
    	ORDER BY meta_value ASC
    	LIMIT 100
    ";
    
    $upcoming_concert_posters = $wpdb->get_results($upcoming_concert_posters_query, OBJECT);

    How can I change this to retain this current functionality but add an additional conditional which checks for the existence of _thumbnail_id?

    As you might have guessed, I suck with MySQL queries and any help would be greatly appreciated :)

    Cheers,
    Rab

  2. Robert Simpson
    Member
    Posted 3 years ago #

    Nobody?

  3. Robert Simpson
    Member
    Posted 3 years ago #

    *bump*

  4. Robert Simpson
    Member
    Posted 3 years ago #

    *bump*

  5. Robert Simpson
    Member
    Posted 3 years ago #

    *bump*

    Nobody at all?

  6. Spencer Hill
    Member
    Posted 3 years ago #

    It appears that you're ordering by today's date, not the date of the concert. Correct me if I'm wrong though.

    My team and I developed this website for a small local band, if you'd like I'd be happy to give you access to the code as we spent a LOT of time preparing the plugins and themes.

  7. Robert Simpson
    Member
    Posted 3 years ago #

    Hi Spencer,

    Thanks for your reply. My order by uses the meta_value. The AND meta_value >= ".$today." part you're noticing just makes sure that the value of _event_date is greater than or equal to $today which is defined at the top.

    The big problem here I think is that I'm trying to narrow the query down further using an additional meta_key (_thumbnail_id), I don't know how to narrow what I've got (all posts where _event_date's value is greater than or equal to today's timestamp) down to just those where _event_date's value is greater than or equal to today's timestamp AND has a _thumbnail_id value.

    Any ideas how I could trim down the results?

    Cheers,
    Rab

  8. Robert Simpson
    Member
    Posted 3 years ago #

    *bump*

  9. Robert Simpson
    Member
    Posted 3 years ago #

    *bump*

  10. Robert Simpson
    Member
    Posted 2 years ago #

    *bump*

  11. popper
    Member
    Posted 2 years ago #

    The problem is each of those values you are checking for is a different entry on the wp_postmeta table, so you'll never find a row with both '_thumbnail_id' and '_event_date' set at the same time. You'll need to check for the existence of the _thumbnail_id with EXISTS(). Like:

    AND EXISTS( EXISTS(SELECT *
                        FROM   wp_postmeta
                        WHERE  wp_postmeta.meta_key = '_thumbnail_id'
                               AND wp_postmeta.post_id = wp_posts.ID)
  12. Robert Simpson
    Member
    Posted 2 years ago #

    Hi popper,

    Thanks for your reply. Where abouts should I add that to the query? Just before LIMIT?

    Cheers,
    Rab

  13. popper
    Member
    Posted 2 years ago #

    It should go before the order by, since it'll be part of WHERE:

    $upcoming_concert_posters_query = "
    	SELECT *
    	FROM ".$blog_prefix."posts, ".$blog_prefix."postmeta
    	WHERE ID = post_id
    	AND post_type = 'concerts'
    	AND meta_key = '_event_date'
    	AND meta_value >= ".$today."
    	AND post_status = 'publish'
            AND EXISTS( SELECT *
                        FROM   ".$blog_prefix."postmeta
                        WHERE  ".$blog_prefix.".meta_key = '_thumbnail_id'
                               AND ".$blog_prefix.".post_id = ".$blog_prefix."posts.ID)
    	ORDER BY meta_value ASC
    	LIMIT 100
    ";

    That's untested, but it should work.

  14. Robert Simpson
    Member
    Posted 2 years ago #

    Ah excellent!

    I'll give it a bash and report back, thanks again :)

  15. Robert Simpson
    Member
    Posted 2 years ago #

    While I'm here actually, if I wanted to use this multiple times (with a different meta_key for each one) within the same query could I just add them as extra AND EXISTS lines?

    Like this?:

    $upcoming_concert_posters_query = "
    	SELECT *
    	FROM ".$blog_prefix."posts, ".$blog_prefix."postmeta
    	WHERE ID = post_id
    	AND post_type = 'concerts'
    	AND meta_key = '_event_date'
    	AND meta_value >= ".$today."
    	AND post_status = 'publish'
            AND EXISTS( SELECT *
                        FROM   ".$blog_prefix."postmeta
                        WHERE  ".$blog_prefix.".meta_key = '_thumbnail_id'
                               AND ".$blog_prefix.".post_id = ".$blog_prefix."posts.ID)
            AND EXISTS( SELECT *
                        FROM   ".$blog_prefix."postmeta
                        WHERE  ".$blog_prefix.".meta_key = '_something_else'
                               AND ".$blog_prefix.".post_id = ".$blog_prefix."posts.ID)
            AND EXISTS( SELECT *
                        FROM   ".$blog_prefix."postmeta
                        WHERE  ".$blog_prefix.".meta_key = '_another_thing'
                               AND ".$blog_prefix.".post_id = ".$blog_prefix."posts.ID)
    	ORDER BY meta_value ASC
    	LIMIT 100
    ";
  16. popper
    Member
    Posted 2 years ago #

    Sure. There's probably a better way somewhere, but that should work.

  17. Robert Simpson
    Member
    Posted 2 years ago #

    Hi popper,

    Just a quick update to let you know that your code worked, I only had to make a couple of tiny minor adjustments (the term 'postmeta' needed to be in front of .meta_key and .post_id)

    :)

    I'm going to try adding multiple keys and let you know how I get on.

    Thanks again!

    Cheers,
    Robert

Topic Closed

This topic has been closed to new replies.

About this Topic