Lenar Lõhmus wrote:
Anyway, instead of writing this:
SELECT pics.*, pic_comments.*, count(*) AS num_comments FROM pics, pic_comments WHERE pics.category = 1 AND pics.id = pic_comments.pic_id GROUP BY pic_comments.pic_id;
Write this:
SELECT pics.*, pic_comments.*, count(*) AS num_comments FROM pics LEFT JOIN pic_comments ON (pic_comments.pic_id = pics.id) WHERE pics.category = 1 GROUP BY pics.pic_id;
Lenar
Though, (regardless of SQL database platform) in a case like this, it MAY be both more straightforward and efficient to use two separate queries for the pictures and comments, given the one-to-many cardinality between them. The above statement causes redundant data to be returned - it duplicates the pics record for each new comment. If there are many large columns in the pics table and many comments to each record there's a lot of redundant data in a query like that.
Depending on the nature of your data, this may or may not outweigh the added cost of one more query to the database. The choice will obviously affect the way you have to write your PHP code (hey, now I'm on-topic).
Splitting it up, it could look like this:
SELECT * FROM pics;
SELECT *, count(*) AS num_comments FROM pic_comments GROUP BY pic_id
You may want to add an ORDER BY clause to both of these. People often say it's 'good form' to enumerate your fields in the SELECT clause but benefits of that may vary between database platforms.
Mattias Thorslund
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
