SQL QUERY needed to get POST category (taxonomy) ? – MUST be SQL statement

0 Views

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:

Read More
    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?

Post Views: 0

Related posts

Leave a Reply

1 comment

  1. 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