CODEWITHSUNDEEP
mysqlsqlwordpressmysql-workbenchdatabase-table
Sunrise Joz
Sunrise Joz

Reputation: 372

sql statement for wordpress query

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

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

Upvotes: 1

Views: 579

Answers (1)

TheOneWhoPrograms
TheOneWhoPrograms

Reputation: 659

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.

Upvotes: 1

Related Questions