WordPress SQL

WordPress uses MySQL tables to store the data.  The SQL below is a sample that selects those posts that are in a certain category.

To make a general query, use the following syntax:

global $wpdb;   // Give wpdb with global scope
$results = $wpdb->get_results(“SELECT m.*, p.* FROM $wpdb->postmeta m inner join $wpdb->posts p WHERE m.meta_key = ‘_wp_attached_file’ and m.post_id = p.id;”);
//$results = $wpdb->get_results(“SELECT * FROM $wpdb->postmeta as media WHERE media.meta_key = ‘_wp_attached_file’;”);

if ($results) {
$theString = “<ul>”;
foreach ($results as $theresult) {
$theString .= “<li>”.$theresult->meta_value.”</li>”;
}
$theString .= “</ul>”;
return $theString;
} else {
return ”;
}

SELECT e.* from wp_posts e, wp_term_relationships d where e.ID = d.object_id and d.term_taxonomy_id = 5

Media files
list media files:

SELECT * FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’
list media files linked with post data:
SELECT m.*, p.* FROM wp_postmeta m inner join wp_posts p WHERE m.meta_key = ‘_wp_attached_file’ and m.post_id = p.id

List all posts with categories

select p.post_title, wpr.object_id, wp_terms.name
from wp_terms
inner join wp_term_taxonomy on wp_terms.term_id =
wp_term_taxonomy.term_id
inner join wp_term_relationships wpr on wpr.term_taxonomy_id =
wp_term_taxonomy.term_taxonomy_id
inner join wp_posts p on p.ID = wpr.object_id
where taxonomy= “category” and p.post_type = ‘post’
order by object_id;

List Post Categories
Post categories are simply listed in the wp_terms table.
Select * from wp_terms