RE: multiple tables and amounts

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



// 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:grillen@xxxxxxxxxxxxxx] 
Sent: Wednesday, January 28, 2004 10:21 PM
To: PHP-DB
Subject:  multiple tables and amounts


hi!

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?

CREATE TEMPORARY TABLE comments_temp
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,
comments.id;

SELECT photos.*,
COUNT(photos.id) AS comments,
comments_temp.ratings
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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux