If you’re trying to get all categories in WordPress with a real, raw, mysql sql statement, this is the type of query you can use:
SELECT *
FROM wp_term_relationships
LEFT JOIN wp_term_taxonomy
ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.taxonomy = 'category'
GROUP BY wp_term_taxonomy.term_id
This is how you can get posts of a certain category / taxonomy. I have a few extra lines commented out that could also be used:
SELECT *
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE 1
#AND wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN (13)
#AND wp_term_taxonomy.term_id IN (1,2,3,4,5,6,7)
GROUP BY wp_posts.ID
ORDER BY ID
Update: Omar asks:
cool.. but how can I get the NAME of those categories ??
Well, try this:
SELECT *
FROM wp_term_relationships
LEFT JOIN wp_term_taxonomy
ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
LEFT JOIN wp_terms on wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id
WHERE wp_term_taxonomy.taxonomy = 'category'
GROUP BY wp_term_taxonomy.term_id
or just for the names:
SELECT wp_terms.name
FROM wp_term_relationships
LEFT JOIN wp_term_taxonomy
ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
LEFT JOIN wp_terms on wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id
WHERE wp_term_taxonomy.taxonomy = 'category'
GROUP BY wp_term_taxonomy.term_id
How could I get the names for multiple categories? So in my case : SELECT wp_terms.name FROM wp_term_relationships LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) LEFT JOIN wp_terms on wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id WHERE wp_term_taxonomy.taxonomy = ‘property-type’ GROUP BY wp_term_taxonomy.term_id
I want names for both wp_term_taxonomy.taxonomy = ‘property-type’ & wp_term_taxonomy.taxonomy = ‘listing-type’
not just the one.
Where i can define de name of custom post?
SELECT wp_terms.name FROM wp_term_relationships LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) LEFT JOIN wp_terms on wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id WHERE wp_term_taxonomy.taxonomy = ‘category’ GROUP BY wp_term_taxonomy.term_id
@Michael DeMutis I suggest posting you question on either stackoverflow.com or wordpress.stackexchange.com
Thank you this really helped me. But I have a further question.. what if I wanted to select all posts in a category but then also by a price range of a meta_key and only return the values of a specific_meta key.
My application I am trying to do 3 select boxes through jquery, and chain them. So the first one selects a neighbourhood (this is category, the second one selects a price range) .. i need my SQL to return only the number_of_rooms meta_key values..
To just select the number_of_rooms based on category I’m using this:
$sql = "SELECT DISTINCT meta_value FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1 #AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ($_POST[id]) #AND wp_term_taxonomy.term_id IN (1,2,3,4,5,6,7) AND meta_key = '_lwi_bedroomsCount' GROUP BY wp_posts.ID ORDER BY meta_value ASC";Excelent thanks… I’ve read that some people uses wordpress API.. like: query_posts(array(‘category__and’ => array(2,6))); and stuff like that. http://codex.wordpress.org/Template_Tags/query_posts
Seems to be a better way to deal with wordpress db. still I don’t know how to execute those functions in php. What do u think is best?
Hi omar, I updated the post. Take a look at the update
cool.. but how can I get the NAME of those categories ??