CODEWITHSUNDEEP
mysqlwordpress
Kerby82
Kerby82

Reputation: 5146

Wordpress get all category from a post slug via my sql

I have a custom table that is used to track the access to a website. The table is as following:

date,post_slug,user

I want to create a query (mysql) that from this table add a 4th column containing all the categories of the post.

I don't want to create a new row for each category just a row with all the categories added to each row of this table.

The informations should be stored in wp_term_relationship and wp_terms but I don't know how to create just one row and not n rows for each category

Upvotes: 1

Views: 1040

Answers (1)

MohaMad
MohaMad

Reputation: 2855

Main thing you should do is group by wp_posts.id. This command merges all equal id to one row. Now, you should use group_concat( FIELDNAME ) to getting all desire fields in one column.

You need category names or category id?

If you wish have category name:

  SELECT tbl.*,GROUP_CONCAT(term.name) FROM
  YOURTABLE as tbl
  left join wp_posts as p
  on p.post_name = tbl.post_slug
  left join wp_term_relationships as rels
  on rels.object_id = p.ID
  left JOIN wp_term_taxonomy as tx
  on tx.term_taxonomy_id = rels.term_taxonomy_id
  left join wp_terms as term
  on term.term_id = tx.term_id
  where tx.taxonomy = "category"
  group by p.ID

If you need category id:

 SELECT tbl.*,GROUP_CONCAT(tx.term_taxonomy_id) FROM
 YOURTABLE as tbl
 left join wp_posts as p
 on p.post_name = tbl.post_slug
 left join wp_term_relationships as rels
 on rels.object_id = p.ID
 left JOIN wp_term_taxonomy as tx
 on tx.term_taxonomy_id = rels.term_taxonomy_id
 -- left join wp_terms as term
 -- on term.term_id = tx.term_id
 where tx.taxonomy = "category"
 group by p.ID

Note:

  • YOURTABLE is your 3-column table.

  • If you want get all tags, just edit where tx.taxonomy = "category" to where tx.taxonomy = "post_tag"

  • If you are using custom post types and you want wordpress custom post types tags or categories, add and p.post_type ="YOURPOSTTYPE" after where .. line.

Upvotes: 3

Related Questions