Anyone here know their MySQL?

red_ice

$querystr = "
       SELECT wposts.*
       FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
       WHERE wposts.ID = wpostmeta.post_id
            AND wpostmeta.meta_key = 'Date'
    AND STR_TO_DATE(wpostmeta.meta_value,'%d/%m/%Y') >= CURDATE()
     
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    ORDER BY STR_TO_DATE(wpostmeta.meta_value,'%d/%m/%Y') ASC
    LIMIT 1000
    ";

This is a lump of code I found on this site. The author says that its meant to select posts from a certain category, but its not, its selecting any auld post that has a date on it!

Im really just looking to see if anyone can give me some insight as to how I can make that sql query just select from the 'events' category.

I've looked into referencing taxonomies from a mysql query, but Im just getting my else value when I try add it.

Can anyone help out?

Cheers!

Creamy Goodness

not a big wp head but it looks like it's just selecting posts based on the wpostmeta table with the value of 'date'

AND wpostmeta.meta_key = 'Date'

see if you can input other things in there like maybe

AND wpostmeta.meta_key = 'category' AND wpostmeta.meta_value = 'Events'

using the ERD diagram here - http://codex.wordpress.org/File:WP3.0-ERD.png - to see the schema, yours might be slightly different depending on wp version

red_ice

Creamy Goodness wrote: »

not a big wp head but it looks like it's just selecting posts based on the wpostmeta table with the value of 'date'

AND wpostmeta.meta_key = 'Date'

see if you can input other things in there like maybe

AND wpostmeta.meta_key = 'category' AND wpostmeta.meta_value = 'Events'

using the ERD diagram here - http://codex.wordpress.org/File:WP3.0-ERD.png - to see the schema, yours might be slightly different depending on wp

The query i have is great as its pulling a custom db field and organising it (with other code). I just need to refine it to one category. but that erd is very useful,thatnks for pointing me at it!

komodosp

EDIT: Just saw the OP had the category name rather than the category ID.

This is a wordpress question rather than a MySQL question...

You need to be looking at the $wpdb->terms, $wpdb->term-taxonomy & $wpdb->term-relationships tables. I haven't tested this code, BTW.

$querystr = "SELECT wposts.*
       FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->term_relationships tr, $wpdb->term-taxonomy tt, $wpdb->terms t
       WHERE wposts.ID = wpostmeta.post_id
            AND tr.object_id = wposts.ID 
            AND tt.term_taxonomy_id = tr.term_taxonomy_id
            AND tt.taxonomy='category'
            AND tt.term_id = t.term_id
            AND t.name='events'

            AND wpostmeta.meta_key = 'Date'
    AND STR_TO_DATE(wpostmeta.meta_value,'%d/%m/%Y') >= CURDATE()
     
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    ORDER BY STR_TO_DATE(wpostmeta.meta_value,'%d/%m/%Y') ASC
    LIMIT 1000";

I'm not sure what all that date stuff is about you might be able to take it out depending on the purpose of your query.



If you already know the category ID in a variable called $category_id, you need something like...

$querystr = "SELECT wposts.*
       FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->term_relationships tr, $wpdb->term-taxonomy tt
       WHERE wposts.ID = wpostmeta.post_id
            AND tr.object_id = wposts.ID 
            AND tt.term_taxonomy_id = tr.term_taxonomy_id
            AND tt.term_id = '" . (int)$category_id . "'
            AND wpostmeta.meta_key = 'Date'
    AND STR_TO_DATE(wpostmeta.meta_value,'%d/%m/%Y') >= CURDATE()
     
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    ORDER BY STR_TO_DATE(wpostmeta.meta_value,'%d/%m/%Y') ASC
    LIMIT 1000";