Re: [PHP-DB] multiple tables and amounts
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" <[EMAIL PROTECTED]> > Organisation: Garland C Norris Company > Antworten an: <[EMAIL PROTECTED]> > Datum: Thu, 29 Jan 2004 11:17:55 -0500 > An: <[EMAIL PROTECTED]>, "'PHP-DB'" <[EMAIL PROTECTED]> > 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:[EMAIL PROTECTED] > 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
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:[EMAIL PROTECTED] 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
Re: [PHP-DB] multiple tables and amounts
hi! yes - it seems as if there was no way out. i'll have to render it without this php-class. i didn't thought that it's not possible to do such queries in sql - well at least i don't know how to do them?? :/ thx for your answers! -mathew # life would be easier if i knew the source code... > Von: "Robert Sossomon" <[EMAIL PROTECTED]> > Organisation: Garland C Norris Company > Antworten an: <[EMAIL PROTECTED]> > Datum: Thu, 29 Jan 2004 11:10:04 -0500 > An: <[EMAIL PROTECTED]> > Betreff: RE: [PHP-DB] multiple tables and amounts > > I may be able to hack the XML code to do it, but with wanting a COUNT of > each based on the ID, it is going to be difficult to do it in 1 query > only, in my opinion at least... Is there a way to out-think the XML > generation to grab the items you need? > > > > -Original Message- > From: ma [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 29, 2004 10:40 AM > To: PHP-DB > Subject: Re: [PHP-DB] multiple tables and amounts > > > hi! > > thanks a lot for your answer. i also thought of dividing the problem > into several selects, but the application i'm working with (it's > designed by a friend of mine, which lets you easily generate XML out of > an sql) only accepts one single query.. > > do you know about any problems using CREATE TEMPORARY TABLE - eg. when > there are a lot of users accessing the page? > > thx for your help again! > > -mathew > > > # die welt ist schnell geworden... > >> Von: "Hutchins, Richard" <[EMAIL PROTECTED]> Antworten >> an: [EMAIL PROTECTED] >> Datum: Thu, 29 Jan 2004 08:25:03 -0500 >> An: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> >> Betreff: RE: [PHP-DB] multiple tables and amounts >> >> Matt, >> >> What about this (assuming MySQL): >> >> //$photoid is whatever photo you want the details for. >> $sql_a = "SELECT id FROM comments WHERE phid = '".$photoid."'"; $sql_b > >> = "SELECT id FROM ratings WHERE phid = '".$photoid."'"; >> >> $result_a = mysql_query($sql_a) or >> die(mysql_error()); >> $num_comments = mysql_num_rows($result_a); >> >> $result_b = mysql_query($sql_b) or >> die(mysql_error()); >> $num_ratings = mysql_num_rows($result_b); >> >> echo "There were ".$num_comments." comments and ".$num_ratings." for >> that photo."; >> >> Since you're not doing anything really difficult with the queries, you > >> probably won't see much of a speed hit by doing back-to-back queries >> like this. Besides, I think doing two straight queries like this is >> going to be faster than creating and deleting temporary tables. Just a > >> guess. > > -- > 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
Re: [PHP-DB] multiple tables and amounts
hi! thanks a lot for your answer. i also thought of dividing the problem into several selects, but the application i'm working with (it's designed by a friend of mine, which lets you easily generate XML out of an sql) only accepts one single query.. do you know about any problems using CREATE TEMPORARY TABLE - eg. when there are a lot of users accessing the page? thx for your help again! -mathew # die welt ist schnell geworden... > Von: "Hutchins, Richard" <[EMAIL PROTECTED]> > Antworten an: [EMAIL PROTECTED] > Datum: Thu, 29 Jan 2004 08:25:03 -0500 > An: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > Betreff: RE: [PHP-DB] multiple tables and amounts > > Matt, > > What about this (assuming MySQL): > > //$photoid is whatever photo you want the details for. > $sql_a = "SELECT id FROM comments WHERE phid = '".$photoid."'"; > $sql_b = "SELECT id FROM ratings WHERE phid = '".$photoid."'"; > > $result_a = mysql_query($sql_a) or > die(mysql_error()); > $num_comments = mysql_num_rows($result_a); > > $result_b = mysql_query($sql_b) or > die(mysql_error()); > $num_ratings = mysql_num_rows($result_b); > > echo "There were ".$num_comments." comments and ".$num_ratings." for that > photo."; > > Since you're not doing anything really difficult with the queries, you > probably won't see much of a speed hit by doing back-to-back queries like > this. Besides, I think doing two straight queries like this is going to be > faster than creating and deleting temporary tables. Just a guess. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
