Jump to content


Photo

could someone walk me through the last part of this statement.


  • Please log in to reply
3 replies to this topic

#1 mojito

mojito
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 24 May 2006 - 02:36 PM

mysql_query("select a.aid as AlbumID, a.Title as Title, a.pid as PictureID, p.thumbname as Picture, count(l.pid) as Cnt from albums a, pictures p, pictures l where a.aid = p.aid and a.pid = p.pid and a.aid = l.aid group by a.aid order by a.aid", $connection);

the bit i dont get is the following bit...
count(l.pid) as Cnt from albums a, pictures p, pictures l where a.aid = p.aid and a.pid = p.pid and a.aid = l.aid group by a.aid order by a.aid

thanks for any help

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 24 May 2006 - 02:59 PM

[!--quoteo(post=376680:date=May 24 2006, 10:36 AM:name=mojito)--][div class=\'quotetop\']QUOTE(mojito @ May 24 2006, 10:36 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
the bit i dont get is the following bit...
count(l.pid) as Cnt from albums a, pictures p, pictures l where a.aid = p.aid and a.pid = p.pid and a.aid = l.aid group by a.aid order by a.aid
[/quote]

well, when you do an aggregate function such as COUNT(), SUM(), AVG() or any others, you've got to group the results based on whatever columns you're trying to pull out. so, "COUNT(l.pid) as Cnt" is simply saying, return the count of column "pid" from table "l" (pictures) and name the result column "Cnt". then, in the FROM clause, all the tables are given a single letter pseudonym for easy reference. that's where the "l." and "p." stuff comes from when referencing a column name.

finally, you have your "GROUP BY" clause which usually needs to include all columns selected besides the COUNT() itself.

hope this helps.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 May 2006 - 03:44 PM

If you were to write out this query "properly" (using uppercase and proper JOIN syntax), it might make more sense to you:

SELECT
a.aid AS AlbumID
, a.Title AS Title
, a.pid AS PictureID
, p.thumbname AS Picture
, COUNT(l.pid) AS Cnt 
FROM 
albums AS a
LEFT JOIN pictures AS p ON ( a.aid = p.aid AND a.pid = p.pid )
LEFT JOIN pictures AS l ON ( a.aid = l.aid )
GROUP BY a.aid 
ORDER BY a.aid
Of course, obsidian's explanation still applies.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 mojito

mojito
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 25 May 2006 - 01:49 PM

THANK YOU SO MUCH TO BOTH OF YOU.

Im sure I can get it now, too busy to get on this problem right now, but im sure I can understand the results from this now.

If only the original coder had commented as such or explained a little. Commenting code is good, at least for me.

thanks again

m [img src=\"style_emoticons/[#EMO_DIR#]/laugh.gif\" style=\"vertical-align:middle\" emoid=\":laugh:\" border=\"0\" alt=\"laugh.gif\" /]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users