[SOLVED] subquery problem

[acidglitter]

This is what I have..

$sql=mysql_query("SELECT *,
(SELECT COUNT(*) FROM news_comments WHERE news_id=id) AS 'numcomments'
FROM news
ORDER BY id DESC") or die(mysql_error());

 

 

And for comments, if there aren't any it says 0, and if there's one or more it always says 1. Like its just showing true or false.. Does anyone see whats wrong with my code? Because I want it to show the *actual* number of comments

[Fyorl]

I'm not 100% sure here as I'm not really familiar with what you're doing in that query but will the following not achieve the comment count:

$sql = "
SELECT COUNT(*)
FROM `news_comments`
WHERE `news_id`='$id'
";
$data = mysql_query($sql);
$numcomments = mysql_result($data, 0);

[GingerRobot]

The problem is that you havn't specified which table the field id should be in, inside your subquery.  Try:

 

$sql=mysql_query("SELECT *,
(SELECT COUNT(*) FROM news_comments WHERE news_id=news.id) AS 'numcomments'
FROM news
ORDER BY id DESC") or die(mysql_error());

[acidglitter]

that fixed it. thanks!