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