[prev in list] [next in list] [prev in thread] [next in thread] 

List:       php-db
Subject:    Re: [PHP-DB] multiple tables and amounts
From:       ma <grillen () abendstille ! at>
Date:       2004-01-29 16:33:36
Message-ID: BC3EF470.5E3A%grillen () abendstille ! at
[Download RAW message or body]

thx for the query!

unfortunately it does not what i was aiming for... a result like this:

id | img     | comments | ratings
---+---------+----------+--------
1  | p.jpg   | 0        | 1
2  | a.jpg   | 3        | 4
3  | v.png   | 2        | 0

so the variable to set dynamicall should be compared to gid (id from the
gallery-table).

i found that the problem with two joins is, that the group-by-methods get
applyed to all rows, so i cannot differ the amount of comments and ratings.
the only possibility is to find it out via php, but this is not what i'm
aiming for.. 

thanks for your answer!

-mathew 

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

> Von: "Robert Sossomon" <robert@gcnorris.com>
> Organisation: Garland C Norris Company
> Antworten an: <robert@gcnorris.com>
> Datum: Thu, 29 Jan 2004 11:17:55 -0500
> An: <grillen@abendstille.at>, "'PHP-DB'" <php-db@lists.php.net>
> Betreff: RE: [PHP-DB] multiple tables and amounts
> 
> // 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@abendstille.at]
> Sent: Wednesday, January 28, 2004 10:21 PM
> To: PHP-DB
> Subject: [PHP-DB] 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
> 

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


[prev in list] [next in list] [prev in thread] [next in thread]