sql statement for wordpress query

0
votes

im trying to extract the following fields from a local wordpress installation

  • post_title
  • image_url
  • post_excerpt

other criteria for the SELECT is:

  1. the status is publish
  2. it's a post item
  3. and the results only come from the projects parent category or any subcategory of project

i've got this far already but am finding it difficult to workout the rest

SELECT DISTINCT wp_posts.post_title, wp_postmeta.meta_value
FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_postmeta
WHERE wp_term_relationships.object_id = wp_posts.ID
AND post_status = 'publish'
AND post_type = 'post'
AND wp_postmeta.meta_key = 'image_url'
AND wp_term_taxonomy.parent = 5
ORDER BY wp_posts.post_date DESC

as per the below comment, table for wp_term_taxonomy is: term_taxonomy_id || term_id || taxonomy || description || parent || count

as per the below comment, table for wp_posts is: ID || post_author || post_date || post_content || post_title|| post_excerpt || post_status there are more, but not relevant here

as per the below comment, table for wp_term_relationships is: object_id ||term_taxonomy_id || term_order

as per the below comment, table for wp_postmeta is: meta_id || post_id || meta_key || meta_value

1
mysqlsqlwordpressmysql-workbenchdatabase-table
would you mind sharing with those who don't have the wordpress tables memorized the table names and columns? We can then help you much easier :) – TheOneWhoPrograms
sorry, thats the majority of my problem. im not sure how to get a list of the categories, nor the subs :/ – Showcase Imagery
You only provided the one table D=... Really wanted to help you man :(. I need to know all the tables involved. If I know that I can likely help much more! – TheOneWhoPrograms
as per request, hope this helps you help me :D – Showcase Imagery

1 Answers

0
votes

Always try to use the functions provided by WordPress to fulfill the requirements. Yes, the images( image urls ) are stored inside wp_posts table as attachments.

<?php
$args = array( 
'post_type' => 'attachment', 
'post_mime_type' => 'image',
'numberposts' => -1, 
'post_status' => null, 
'post_parent' => $post->ID 
); 
$attached_images = get_posts( $args );
?>

use this wordpress function to get post image url pass the post id to function it result the url of image. http://codex.wordpress.org/Function_Reference/wp_get_attachment_url

wp_get_attachment_url( $id );

However, if you want to go against something that is built in, you can try the following, however I must say I have no local version of wordpress to test against at the moment.

SELECT p.post_title as post_title, p.post_excerpt as post_excerpt,( SELECT guid FROM wp_posts WHERE id = m.meta_value ) AS url FROM wp_posts p, wp_postmeta m WHERE p.post_type = 'post' AND p.post_status = 'publish' AND p.id = m.post_id AND m.meta_key = '_thumbnail_id'

Let me know if you need anything more and I will install a local wordpress installation and do it with you through a chat session.