number of comments for each post

[shinko_metsuo]

I would have this solved if google didn't send up a wordpress plugin on every search. :mad:

Okay! I have the comments under each post by the 'source' which is the 'id' of the post. And I want to count the comments for each post so I can have something like...

Comments(4)

you may need this
$query= query("SELECT * FROM `content` ORDER BY `id` DESC LIMIT $from, $max_results");
//Generates the list of chapters
while($array = mysql_fetch_array($query))
{
$id = $array["id"];
$text = $array["content"];
$topicname = $array["topicname"];
$name = $array["name"];
$time = $array["date"];

//This is what shows the list of post. :)
echo "
<div class=\"post\" id=\"p$id\">
<h2>$topicname</h2>
<p>$text</p>
<p class=\"post_foot\">$time<br /><a href=\"article.php?t=$id\">Comments</a>&nbsp;|&nbsp;Posted by $name</p>
</div>\n\t\t\t ";
}

Thanks advance,
Metsuo

[Fou-Lu]

This isn't enough information.
By what means are you linking your Comments to your original topic?

[marek_mar]

Try this query.

$query = query("SELECT c.*, COUNT(d.id) AS `comments` FROM `content` AS c, `chat` AS d WHERE c.id = d.source ORDER BY `id` DESC LIMIT $from, $max_results");

[shinko_metsuo]

This isn't enough information.
By what means are you linking your Comments to your original topic?

ok the post are under `content` and the comments are under `chat`

chat has a feild called `source` which has the id of the topic so when you click the permalink of the topic it shows the comments where source = post_id

[marek_mar]

I edited my previous post to match the new info. I forgot to metion to use you own field/table names :p

[shinko_metsuo]

I edited my previous post to match the new info. I forgot to metion to use you own field/table names :p
um

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

[marek_mar]

:/ Verified with phpmyadmin & MySQL5.0.15 this time.

$query = query("SELECT c.*, COUNT(d.source) AS `comments` FROM `content` AS c, `chat` AS d WHERE c.id = d.source GROUP BY c.id ORDER BY `id` DESC LIMIT $from, $max_results");

[shinko_metsuo]

Now it's only showing one post.

[marek_mar]

You mean one row or one as the value of "comments"? Ok try that again. It should count the sorce column not the id (as it surely can only have one column with the same id).

[shinko_metsuo]

You mean one row or one as the value of "comments"? Ok try that again. It should count the sorce column not the id (as it surely can only have one column with the same id).
I think you got me wrong.

Table Content
id
topicname
content
name
date
link
ip

table Chat
id
name
message
site
time
ip
source

source is the topic id. That when the permalink is click you'll get comments for only that post
ex. article.php?t=2 so all comments that have 2 in thier source will show up.

Now that that is cleared up I'm working on the index that has the list of all the post and a link to the permalink which has all the comments. Now the code I posed above is what I use to get that list of post. With that set aside the link to the permalink is comments but I want it to be comments($number_of_comments_per_post).

I hope that explains everything because that's about the best I can get it :thumbsup:

[marek_mar]

So... It worked?

[shinko_metsuo]

No it didn't work :(

[marek_mar]

Did you try that last code again? I edited it.

[shinko_metsuo]

still didn't work

[marek_mar]

Odd... I tested that with tables and it executed correctly.
If the query runs what does it output?

[shinko_metsuo]

One post that's it.

[Element]

function ChatCount($post_id) {
$query = query("SELECT * FROM `chat` WHERE source=`".$post_id."`");
$num=mysql_num_rows($query);
return $num;
}

$query= query("SELECT * FROM `content` ORDER BY `id` DESC LIMIT $from, $max_results");
$num=mysql_num_rows($query);
$i=0;
while ($i < $num) {

$id=mysql_result($query,$i,"id");
$content=mysql_result($query,$i,"content");
$topicname=mysql_result($query,$i,"topicname");
$name=mysql_result($query,$i,"name");
$time=mysql_result($query,$i,"time");

echo "
<div class=\"post\" id=\"".p$id."\">
<h2>".$topicname."</h2>
<p>".$text."</p>
<p class=\"post_foot\">".$time."<br /><a href=\"article.php?t=".$id."\">Comments (".ChatCount($id).")</a>&nbsp;|&nbsp;Posted by ".$name/"</p>
</div>\n\t\t\t ";

$i++;

}

I hope that works, bre.

[shinko_metsuo]

number of comments per the post

I'll PM you a link to it

[shinko_metsuo]

nevermind I played around with it a bit and go it to work.

Thanks for all the help!!!! :)