Wordpress is great – so great, in fact, that I often want to display content from it on external webpages. Specifically, I wanted to pull posts out of a specific category and integrate them on my site. (While I use categories in this example, tags can be also be used) While there are great ways to parse RSS feeds for post data, I wanted to approach it a bit differently – directly accessing the wordpress mysql db.
There’s two things that must be done – Connect to the DB and run the query, and display the results (or is that 3?)
Once complete, this example will spit out 5 posts from a specified category.
Step 1 – Getting the post
Put this code, and the code in Step 2, on the same page.
<code>
<?php
//db parameters
$db_username = ‘username’;
$db_password = ‘yourpassword’;
$db_database = ‘blog_datbase’;
$db_host = ‘localhost’ //Probably won’t change
//connect to the database
mysql_connect($db_host, $db_username, $db_password);
@mysql_select_db($db_database) or die(“Unable to select database”);
$query = “SELECT DISTINCT ID, post_title, post_name, guid, post_date, post_content FROM wp_posts as p
INNER JOIN wp_term_relationships AS tr ON
(p.ID = tr.object_id AND
tr.term_taxonomy_id IN (2) )
INNER JOIN wp_term_taxonomy AS tt ON
(tr.term_taxonomy_id = tt.term_taxonomy_id AND
taxonomy = ‘category’)
ORDER BY id DESC LIMIT 5″;
$query_result = mysql_query($query);
$num_rows = mysql_numrows($query_result);
?>
</code>
The select statement I have is rather confusing. It basically grabs the 5 latest posts from a specified category or tag. To specify the category you want posts from, change the valueof tr.term_taxonomy_id IN (2) (in line 4 of the query string) from 2 to the ID of the desired category (or tag).
You can find this ID by looking in the wp_term_taxonomy table. The term_taxonomy_id is the one you’ll be specifing in the code, so make sure it matches up with the desired category or tag. You won’t see the category or tag name, but you’ll see the category description and a term_id. You can match up the category / tag name to the term_taxonomy_id by looking at the coresponding term_id in the wp_terms table.
Step 2 – Displaying the post
After we have the data, all we need is a little bit more code to display. At http://vasir.net I have the latest posts from two different categories of this blog displayed on each page. Although this method may or not be better than fetching posts via RSS feeds, I’m more fond of it.
This code will loop through the data retrieved in step one and output a link for each post.
<code>
<?php
//start a loop that starts $i at 0, and make increase until it’s at the number of rows
for($i=0; $i< $num_rows; $i++){
//blog posts
$post_title[$i] = mysql_result($query_result, $i, “post_title”);
$post_guid[$i] = mysql_result($query_result, $i, “guid”);
echo “<a href=’$post_guid[$i]‘ title=’$post_title[$i]‘>$post_title[$i]</a>”;
?>
</code>
Make sure $num_rows and $query_result are called the same thing as they were in the code from step 1.”guid” is url of the post.
mysql_result($query_result, $i, “post_title”);
“post_title” is the name of the column from the post table you want to retrieve.
UPDATE
With the newest version of wordpress, 2.6, the above code may not work as intended. Thanks to Jay for his code, which works well and with which I’ve implemented on another site already. His code is as follows:
SELECT DISTINCT post_name FROM wp_posts, wp_term_relationships, wp_term_taxonomy WHERE wp_term_relationships.object_id = wp_posts.id AND post_status = ‘publish’ and post_type = ‘post’ AND wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id = YOUR_CATEGORY_ID
You can find your category id using the instructions above. A slightly modified version which I use simply replaces “SELECT DISTINCT post_name FROM” with “SELECT DISTINCT * FROM” which will return every column.
Great post
Excellent blog.
Big thanks. Good news. I’ll become your constant visitor.
Hi,
thanks for great select statement.
I am interesting in backup of only one category with all related datas and tables to insert in other wordpress database on other server.
Do you know how to do this?
Thanks, Soshibi
I am not sure this query is infallible. It does not always work on all posts in all categories in WP 2.6. I am looking for another, simpler SQL statement that is more robust. Will post if I find it.
For this to work correctly, I believe you need to work with “term_id” instead of “term_taxonomy_id”
These may actually differ.
My SQL knowledge is extremely basic, but this worked for me:
SELECT DISTINCT post_name FROM `wp_posts`, wp_term_relationships, wp_term_taxonomy WHERE wp_term_relationships.object_id = wp_posts.id AND post_status = ‘publish’ and post_type = ‘post’ AND wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id = YOUR_CATEGORY_ID
Thanks Jay! You saved me a lot of time setting up a newer blog with the recent update! I’ve updated the post with your code.
You and Jay both rock. I couldn’t get it quite straight in my head how to do what I wanted to do, and you guys just sorted it right out. Awesome, thanks.
How about changing the query so that you select posts from ALL categories except a specific one?
Thanks
Thanks. Very helpful. One change that I would make, though is to use the already defined user, password, host and database from wp-config.php so you don’t have them defined multiple times. The following connect and select_db statements can be used instead.
mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db(DB_NAME) or die(“Unable to select database”);
Great Post! Thanks.
Quick question, how would I go about integrating “get_post_custom_values” with the code above. I’d like to include the thumbnail image of each post, and haven’t been able to get it to work.
the end code doesnt work all i keep getting are this error
unexpected T_STRING, expecting ‘,’ or ‘;’
which relates to this line
echo “$post_title[$i]”;
Hi Adam, if you’re just copying the code you will probably have to replace the quotes ( “$post_title[$i]” ) from “ and ” to “.
Wordpress has an annoying feature that automatically turns quotes into funky characters that php won’t process. Just turn those funky quotes to “.
The T_STRING error occurs because their is a missing semicolon after the localhost” on line 8. right before the phrase //prob wont change
Hello again. I made some slight modification to the code to satisfy chris. This will select all posts in your database, not just the ones of a certain category:
<?php
$db_username = 'username';
$db_password = 'password';
$db_database = 'database';
$db_host = 'localhost';
//connect to the database
mysql_connect($db_host, $db_username, $db_password);
@mysql_select_db($db_database) or die("Unable to select database");
$query = "SELECT DISTINCT * FROM wp_posts WHERE post_status = 'publish' and post_type = 'post'";
$query_result = mysql_query($query);
while($row = mysql_fetch_array($query_result))
{
$post_title = $row['post_title'];
$post_guid = $row['post_guid'];
echo "$post_title“;
}
?>