Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Working on a tool outside of Wordpress to query the wordpress database by POST_ID and return the Category Name associated. This is for a real estate website, and the Categories are called "mi_neighborhoods" and are referred to by wp_term_taxonomy.taxonomy.

In one of my other queries to get all the Category names I use:

    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 = 'mi_neighborhoods'  
    GROUP BY wp_term_taxonomy.term_id

So I have the POST_ID, I need to return the value of the "mi_neighborhoods" for that post.

Can anyone help?

share|improve this question
    
Before you say, "you should do this in Wordpress" this is an application running along side wordpres, I cannot use the wordpress structure here. Thank you –  Michael DeMutis Feb 15 '13 at 4:06
    
you want to return all the terms that belong to the custom taxonomy 'mi_neighborhoods' for a post with POST_ID ? –  birgire Feb 15 '13 at 10:31
    
Yes, I have the ID# of the post.. i want to return all the terms that belong to mi_neighborhoods –  Michael DeMutis Feb 15 '13 at 14:23
add comment

1 Answer

up vote 0 down vote accepted

You can try this SQL query for the taxonomy 'mi_neighborhoods' and let's take POST_ID = 1304

SELECT t.*, tt.* FROM wp_terms AS t 
INNER JOIN wp_term_taxonomy AS tt ON (tt.term_id = t.term_id) 
INNER JOIN wp_term_relationships AS tr ON (tr.term_taxonomy_id = tt.term_taxonomy_id) 
WHERE tt.taxonomy IN ('mi_neighborhoods') AND tr.object_id IN (1304) 
ORDER BY t.name ASC;

In general you can get it from this function:

wp_get_object_terms(1304, 'mi_neighborhoods'); 

EDIT:

Here is a query that gives you id/name/slug of all the terms belonging to the 'mi_neighborhoods' taxonomy:

SELECT t.term_id, t.name, t.slug  
FROM wp_terms AS t 
INNER JOIN wp_term_taxonomy AS tt ON (t.term_id = tt.term_id) 
WHERE tt.taxonomy IN ('mi_neighborhoods') 
ORDER BY t.name ASC

and here is the same for all non-empty terms:

SELECT t.term_id, t.name, t.slug 
FROM wp_terms AS t 
INNER JOIN wp_term_taxonomy AS tt ON (t.term_id = tt.term_id) 
WHERE tt.taxonomy IN ('mi_neighborhoods') AND tt.count > 0
ORDER BY t.name ASC
share|improve this answer
    
Yes thank you this works, one more thing while I have your attention - Can you give me a SQL Query to retrieve a list of the mi_neighborhoods taxonomy's and their respective IDs ? –  Michael DeMutis Feb 15 '13 at 17:11
    
ok great, I updated the answer above. –  birgire Feb 15 '13 at 17:38
    
Thank you very much! I can finish this project now! –  Michael DeMutis Feb 15 '13 at 18:01
    
ok great, good luck with your project. –  birgire Feb 15 '13 at 18:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.