Displaying recent posts on external non-blog page…

(@mojolocollc)

I’m trying to display the 3 most recent posts from my blog on my main site’s home page. I’m using this test page as my home page and it’s working beautifully so far. My blog is located at http://www.mojolocoblog.com.

I’m using the following code on my external non-blog page….

<?php
//db parameters
$db_username = '###';
$db_password = '###';
$db_database = '###';

//connect to the database
mysql_connect(localhost, $db_username, $db_password);
@mysql_select_db($db_database) or die("Unable to select database");

//get data from database -- !IMPORTANT, the "LIMIT 5" means how many posts will appear. Change the 5 to any whole number.
$query = "Select * FROM wp_posts WHERE post_type='post' AND post_status='publish' AND post_category='News Flash' ORDER BY id DESC LIMIT 3"; 

$query_result = mysql_query($query);
$num_rows = mysql_numrows($query_result);

//close database connection
mysql_close();

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<title>test wordpress connect...</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<ul>

<?php

for($i=0; $i< $num_rows; $i++){ 

$blog_date = mysql_result($query_result, $i, "post_date");
$blog_title = mysql_result($query_result, $i, "post_title");
$blog_content = mysql_result($query_result, $i, "post_content");
$blog_permalink = mysql_result($query_result, $i, "guid");

$blog_date = strtotime($blog_date);
$blog_date = strftime("%b %e", $blog_date);

?>

<li class="home-list"><a href=”<?php echo $blog_permalink; ?>”><?php echo $blog_date; ?>: <?php echo $blog_title; ?></a></li>

<?php
}
?>

</ul>

</body>
</html>

My question is this:

how can I call entries ONLY from one specific category? I don’t want to display random blog entries- only entries from the News Flash category. I’ve tried several things, but can’t seem to get it to work.

Any ideas?

Viewing 14 replies - 1 through 14 (of 14 total)

leogermani
(@leogermani)

15 years, 10 months ago

the category of a post is stored in term_relationships table. You will have to make something like:

SELECT * from wp_posts WHERE ID IN (SELECT object_id FROM term_relationships WHERE term_id $category_id)

Leo,,

Thread Starter mojolocollc
(@mojolocollc)

15 years, 10 months ago

Ok, we’re getting there, I think….still not working, though… In my code, i have:

$query = "Select * FROM wp_posts WHERE post_type='post' AND post_status='publish' AND ID IN (SELECT object_id FROM term_relationships WHERE term_id $category_id) ORDER BY id DESC LIMIT 3";

It’s bombing out…do I have your code in there correctly? please forgive my ignorance here….

thanks!

whooami
(@whooami)

15 years, 10 months ago

you are REALLY going about that the hard way. why are you rewriting a query, when all you have to do is include wp-blog-header.php and use the WP’s internal functions?

you apparantly didnt do any searching..cuz its not like this isnt one of the more popular things to do.

http://wordpress.org/support/topic/148851?replies=3

Thread Starter mojolocollc
(@mojolocollc)

15 years, 10 months ago

I did search….and I saw that post. But it doesn’t solve my issue of needing to display recent posts from a SPECIFIC CATEGORY. That is the source of my issue.

leogermani
(@leogermani)

15 years, 10 months ago

You should have checked the database and your query

. its not term_id, but term_taxonomy_id

. a “=” was missing

. a lowecase id was at the end of it where it should be uppercase

$query = “Select * FROM wp_posts WHERE post_type=’post’ AND post_status=’publish’ AND ID IN (SELECT object_id FROM term_relationships WHERE term_taxonomy_id = $category_id) ORDER BY ID DESC LIMIT 3”;

Thread Starter mojolocollc
(@mojolocollc)

15 years, 10 months ago

thank you for the input….and patience. I know enough about php to be quite dangerous, it seems. I wish there was more specific info on the boards about this specific issue…..

ok, so anyway, in your line of code above, i changed “$category_id” to “4” since 4 is the id of the category I want to limit the results to. The rest of your snippet I left as-is.

I uploaded, and now I get the following error:

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /home/mojoloco/public_html/blogtest.php on line 15

If I take your snippet out, then it works (just without listing results from that specific category- it lists the 3 most recent results from all categories).

Any ideas?

Thread Starter mojolocollc
(@mojolocollc)

15 years, 10 months ago

ok, after debugging, I’m getting this:

Table ‘mojoblog_wordpress.term_relationships’ doesn’t exist

I understand what it’s saying, but I have no idea how to fix it. I’M SO CLOSE!!!!! I’m assuming I have to reference the taxonomy table somewhere…..right?

leogermani
(@leogermani)

15 years, 10 months ago

Its saying that there is no table in the database with that name. And, indeed, there is not. Again, have you ever had a look to your database? its good…

The name of the table usually is is wp_term_relationships.

but the prefix “wp_” may change.. if you want to have it in a nice way;

call the $wpdb variable

global $wpdb;

and do the query like this:

$query = “Select * FROM wp_posts WHERE post_type=’post’ AND post_status=’publish’ AND ID IN (SELECT object_id FROM $wpdb->term_relationships WHERE term_taxonomy_id = $category_id) ORDER BY ID DESC LIMIT 3”;

Thread Starter mojolocollc
(@mojolocollc)

15 years, 10 months ago

yeah, i googled it and had just pegged it as you responded. that fixed the error, and it’s working….thanks a bunch for your help!

esoterics
(@esoterics)

15 years, 8 months ago

Hello,

I am using the same code above and am looking to do the same thing however I am running WordPress 2.2.2 (I know I need to upgrade). How can I make this work for my installation?

$query = "Select * FROM wp_posts WHERE post_type='post' AND post_status='publish' AND ID IN (SELECT object_id FROM $wpdb->term_relationships WHERE term_taxonomy_id = $category_id) ORDER BY ID DESC LIMIT 3";

Thanks in advance!
esoterics
(@esoterics)

15 years, 8 months ago
actually i was jsut able to accomplish the task with this…
```
Select * FROM wp_posts WHERE post_type='post' AND post_status='publish' AND ID
IN (SELECT post_id FROM wp_post2cat WHERE category_id= 20 ) ORDER BY ID DESC LIMIT 3
```
drwigginsjr
(@drwigginsjr)

15 years, 7 months ago

I’m running WordPress 2.6.2 and I cant get this to work, can anyone help?

Many Thanks ahead of time.

ajfuk
(@ajfuk)

15 years, 2 months ago

Right im using the coding as provided above

$query = "Select * FROM wp_posts WHERE post_type='post' AND post_status='publish' AND ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id = '112') ORDER BY ID DESC LIMIT 12";

All it shows me is a random post not even from the category id i’ve specified above.

Im using wordpress 2.7 can someone please tell me how to fix this ive spent 3 whole days trying to get this work and its driving me insane.

ajfuk
(@ajfuk)

15 years, 2 months ago

can anyone help?