Altering PHP to pick content with a particular value

[Jamie]

I'm using some PHP which outputs the latest post from a database.

I want to display the number of comments (field name "comment_count" inside the "wp_posts" table) for the latest post within the "wp_posts" table which has a value of "1" inside the "post_category" field. Any post with a different value inside "post_category" should be ignored.

Here is the code I'm using which brings in the latest post (including number of comments) by looking at all categories.

<?php
$how_many=1; //How many posts do you want to show
require_once("http://www.mydomain.com/wp-config.php"); // Change this for your path to wp-config.php file
?>

<?
$news=$wpdb->get_results("SELECT ID,post_title, post_content, post_date, post_name, comment_count FROM wp_posts WHERE post_status = 'publish' and post_type = 'post' ORDER BY ID DESC LIMIT ".$how_many);

foreach($news as $np) {

$d = strtotime($np->post_date);
$cc = $np->comment_count;
$link_text = ($cc == 1) ? "$cc comment" : "$cc comments";

echo "<p class=\"smalltext\">" . date('M d, Y', strtotime($np->post_date) . "");
echo "<br /><a href='http://www.jamieharrop.com/" . date('Y/m/d',$d) . "/" . $np->post_name . "/#comments'>" . $link_text . "</a></p>\n";
echo "<p>" . $np->post_content . "<br />";
}
?>

I have stripped it down to it's most basic form (I removed some of the echo's that were irrelavant).

Thanks in advance.

[inimino]

What's the question? :)

[Jamie]

Here ya go...

I want to output the value inside field name "comment_count" which is inside the "wp_posts" table. It should only display this value if the value inside the "post_category" field mathes "1" and it should only display the most recent record out of those with the value of "1".

I hope that makes some sense.

I guess the question is... how would I do that? :blush:

[Jamie]

Right now the following line outputs the "comment_count" value of the latest record, but it outputs records which don't have a value of "1" in the "post_category" field.

echo "<br /><a href='http://www.jamieharrop.com/" . date('Y/m/d',$d) . "/" . $np->post_name . "/#comments'>" . $link_text . "</a></p>\n";

[inimino]

Thanks, that makes sense now.

You need to change this:

SELECT [...] FROM wp_posts WHERE post_status = 'publish' and post_type = 'post' ORDER BY ID DESC LIMIT

to this:

SELECT [...] FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' AND post_category = 1 ORDER BY...

[Jamie]

That simple? Cool. :)

Thanks Mj. I'll give it a try later.

[Jamie]

It works perfectly. Thanks Michaeljohn. :D

[Jamie]

Ok, it seems it doesn't work how I intended.

In fact, the database isn't setup how I thought it was, which is why it doesn't work as intended.

Here is how I think posts are assigned to a category:

I have a table called "wp_post2cat"

There are three fields inside this table:

rel_id
post_id
category_id

It then creates a record for each time a post is defined to a category, so a record may look like this:

rel_id: 1
post_id: 4
category_id: 2

Or a post could be defined to two categories, which would create two records like this:

rel_id: 1
post_id: 4
category_id: 2

rel_id: 2
post_id: 4
category_id: 3

I'm not entirely sure where I go from here. Maybe somebody with a little better logical thinking skills than I has an idea of how I can get the comment count to show for just posts with a category_id of X. :?

[Dan]

SELECT `wp_posts`.`comment_count`
FROM `wp_posts`
LEFT JOIN `wp_post2cat`
ON `wp_post2cat`.`rel_id` = `wp_posts`.`rel_id`
WHERE `wp_post2cat`.`rel_id` = $id
ORDER BY `wp_posts`.`id` DESC
LIMIT 1;


Try something like that.

[Jamie]

Thanks for the reply, Dan.

If I even had an ounce of knowledge with PHP, that code you gave would probably make some sense. But... I don't have an ounce of knowledge.

Here is what I have right now, and it is giving the following error:

Fatal error: Call to a member function on a non-object in /home/jamieh/public_html/test.php on line 14

<?php
$how_many=1; //How many posts do you want to show
require_once("http://www.jamieharrop.com/wp-config.php"); // Change this for your path to wp-config.php file
?>

<?
$news=$wpdb->get_results("SELECT `wp_posts`.`comment_count` FROM `wp_posts` LEFT JOIN `wp_post2cat` ON `wp_post2cat`.`rel_id` = `wp_posts`.`rel_id` WHERE `wp_post2cat`.`rel_id` = $id ORDER BY `wp_posts`.`id` DESC LIMIT 1;".$how_many);

foreach($news as $np) {

$d = strtotime($np->post_date);
$cc = $np->comment_count;
$link_text = ($cc == 1) ? "$cc comment" : "$cc comments";

echo "<p class=\"smalltext\">" . date('M d, Y', strtotime($np->post_date) . "");
echo "<br /><a href='http://www.jamieharrop.com/" . date('Y/m/d',$d) . "/" . $np->post_name . "/#comments'>" . $link_text . "</a></p>\n";
echo "<p>" . $np->post_content . "<br />";
}
?>

If it is going to be a relatively easy fix (Less than an hour or two) I'll gladly pay for somebody to do this, and I'll be able to give you access to the database to see how it is setup.

[inimino]

I sent you a PM, Jamie.