// This is just 1 query, I have not tested it, but it may work.

$get_Quote = "select ph.id, ph.img, cm.id, cm.phid, cm.txt, rt.id,
rt.phid, rt.text from photos as ph left join comments as cm left join
ratings as rt on ph.id = cm.phid = rt.phid where id = '$photo_id' order
by ph.id";

//runs the query
$get_Quote_res = mysql_query($get_Quote) or die(mysql_error());

-----Original Message-----
From: ma [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 28, 2004 10:21 PM
Subject: [PHP-DB] multiple tables and amounts


i have a weired problem:

there are 3 tables:
photos (id, img)
comments (id, phid, text)
ratings (id, phid, text)

now i tried to create a query returning the amount of ratings and the
amount of comments for all images in the photo-table.

i tried it with multiple joins, but without success...
but i ended up using some CREATE TEMPORARY TABLE syntax - is there a way
to do it one-query?

SELECT comments.id, comments.phid,
COUNT(comments.id) AS ratings
FROM comments
LEFT JOIN ratings ON ratings.phid=comments.phid
WHERE comments.phid=1
GROUP BY comments.phid,

SELECT photos.*,
COUNT(photos.id) AS comments,
FROM photos
LEFT JOIN comments_temp ON comments_temp.phid = photos.id
WHERE gid=1
GROUP BY photos.id;

DROP TABLE comments_temp;

as said i would like to do it one-query, because it does not seem to me
being a simple and beautiful solution??

help would be warmly appretiated... thx alot
- mathew

# life would be easier if i knew the source code...

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to