Neverside

Counting rows on different tables, same query (MySQL)

I have a simple fetch-news query, and I need the number of comments available for each row.

This is my query:

PHP:

<?php
SELECT 
        n_news.id,
        n_news.titulo,
        n_news.fecha,
        n_news.cat_id,
        n_news.count,
        n_cats.id as cat_id,
        n_cats.nombre as cat_name,
        n_cats.shortn as cat_shortn,
    FROM 
        n_news,
        n_cats
    WHERE
        n_cats.id = n_news.cat_id
    ORDER BY 
        n_news.fecha
    DESC
    LIMIT 
        20
?>

And the table structure for the comments table (shortened)

:: n_comments
id | news_id | user_id | content

To get the number of comments I currently do this:

PHP:

<?php

$rComnum = mysql_fetch_row(mysql_query("SELECT COUNT(id) FROM n_comments WHERE news_id = '".$r['id']."'"));
$comnum = $rComnum[0];

?>

But that means 20 extra queries wasting memory, so my question is: Is there a way to count rows in another table for each row in a single query?

I have tried doing this:

PHP:

<?php
SELECT 
        n_news.id,
        n_news.titulo,
        n_news.fecha,
        n_news.cat_id,
        n_news.count,
        n_cats.id as cat_id,
        n_cats.nombre as cat_name,
        n_cats.shortn as cat_shortn,
        COUNT(n_comments.news_id) as com_num
    FROM 
        n_news,
        n_cats,
        n_comments
    WHERE
        n_cats.id = n_news.cat_id
    GROUP BY
        n_news.fecha
    ORDER BY 
        n_news.fecha
    DESC
    LIMIT 
        20
?>

But that counts all entries and returns the same number for every article, I also tried adding a WHERE n_news.id = n_comments.news_id , but that only returned news with comments and left out news with no comments.

Thanks in advance :)

PD: Also, how exactly do JOINS work?

___________________

"¿Por qué buscais la felicidad, oh, mortales, fuera de vosotros mismos?"
~Boecio

Last edited by Alekz, September 2nd, 2005 01:13 AM (Edited 3 times)

hi.

i use LEFT JOINS

PHP:

<?php
"
SELECT
    COUNT(DISTINCT `nc`.`id`) as `comments`
FROM
    `n_news`, `n_cats`

LEFT JOIN
    `n_comments` as `nc` ON `cn`.`uid`=`c`.`id`

WHERE
    n_cats.id = n_news.cat_id

GROUP BY
    `c`.`id`
    
ORDER BY  n_news.fecha DESC
LIMIT 20"
?>

i don't know if this is working but i this gives you an idea on how to do it

It worked perfect! Thanks a lot glazz :D

This is the final query I used in case anyone wants to know:

PHP:

<?php

SELECT 
        n_news.id,
        titulo,
        n_news.fecha,
        cat_id,
        count,
        n_cats.id as cat_id,
        n_cats.nombre as cat_name,
        n_cats.shortn as cat_shortn,
        COUNT(DISTINCT n_comments.id) as com_num
    FROM 
        n_news,
        n_cats
    LEFT JOIN
        n_comments ON n_news.id = n_comments.news_id
    WHERE
        n_cats.id = n_news.cat_id
    GROUP BY
        n_news.fecha
    ORDER BY 
        n_news.fecha
    DESC
    LIMIT 
        20

?>

By the way, how exactly did that JOIN work? And why did a DISTINCT had to be added? Thanks again :)

___________________

"¿Por qué buscais la felicidad, oh, mortales, fuera de vosotros mismos?"
~Boecio