MargateSteve Posted December 27, 2010 Share Posted December 27, 2010 I currently have a query that uses six different COUNT's working at http://www.margate-fc.com/content/1st_team/stats/scorers.php. It uses the following tables (with a selection of data) -- Table structure for table `games` -- CREATE TABLE `games` ( `match_id` int(11) NOT NULL auto_increment, `date` date default NULL, `time` time default NULL, `competition` int(11) default NULL, `round` tinyint(2) default NULL, `replay` char(1) default NULL, `h_a` varchar(45) default NULL, `opponent` int(11) default NULL, `wdl` varchar(45) default NULL, `for` tinyint(4) default NULL, `against` tinyint(4) default NULL, `attendance` int(11) default NULL, `report_url` longtext, `photo_url` longtext, `stadium` int(11) default NULL, `manager` varchar(45) default NULL, `live` varchar(255) default NULL, `notes` varchar(255) default NULL, `extra_time` char(1) default NULL, PRIMARY KEY (`match_id`) ) TYPE=MyISAM AUTO_INCREMENT=312 ; INSERT INTO `games` VALUES (1, '2009-08-15', '15:00:00', 1, NULL, '', 'A', 19, 'L', 0, 4, 508, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250446274&&archive=1281131284&start_from=&ucat=10&', '', 4, '', 'N', '', ''); INSERT INTO `games` VALUES (2, '2009-08-18', '19:45:00', 1, NULL, '', 'H', 29, 'L', 0, 4, 653, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250637449&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/6.%20mfc%20v%20Dartford%20-%2018.08.2009&Qiv=thumbs&Qis=M', 7, 'TERRY YORATH', NULL, NULL, NULL); INSERT INTO `games` VALUES (3, '2009-08-22', '15:00:00', 1, NULL, '', 'H', 30, 'W', 2, 1, 345, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250965567&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/7.%20mfc%20v%20Boreham%20Wood%20-%2022.08.2009&Qiv=thumbs&Qis=M', 7, 'TERRY YORATH', NULL, NULL, NULL); INSERT INTO `games` VALUES (4, '2009-08-24', '19:45:00', 1, NULL, '', 'A', 1, 'W', 3, 0, 243, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251151671&archive=1281131284&start_from=&ucat=10&', NULL, 2, 'TERRY YORATH', NULL, NULL, NULL); INSERT INTO `games` VALUES (5, '2009-08-29', '15:00:00', 1, NULL, '', 'A', 11, 'L', 0, 3, 156, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251573000&archive=1281131284&start_from=&ucat=10&', NULL, NULL, 'TERRY YORATH', NULL, NULL, NULL); INSERT INTO `games` VALUES (6, '2009-08-31', '15:00:00', 1, NULL, '', 'H', 7, 'L', 0, 1, 423, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251746220&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/8.%20mfc%20v%20Cray%20Wanderers%20-%2031.08.2009&Qiv=thumbs&Qis=M', NULL, 'TERRY YORATH', NULL, NULL, NULL); INSERT INTO `games` VALUES (7, '2009-09-05', '15:00:00', 1, NULL, '', 'A', 31, 'D', 2, 2, 120, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1252191674&archive=1281131284&start_from=&ucat=10&', NULL, 9, 'TERRY YORATH', NULL, NULL, NULL); INSERT INTO `games` VALUES (8, '2009-09-12', '15:00:00', 2, 2, '', 'H', 19, 'D', 2, 2, 402, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1252781008&&archive=1281131284&start_from=&ucat=10&', '', NULL, 'TERRY YORATH', '', '', ''); INSERT INTO `games` VALUES (9, '2009-09-15', '19:45:00', 2, 2, 'r', 'A', 19, 'L', 2, 3, 301, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253050913&archive=1281131284&start_from=&ucat=10&', '', NULL, 'TERRY YORATH', '', '', ''); INSERT INTO `games` VALUES (10, '2009-09-19', '15:00:00', 1, NULL, '', 'H', 15, 'L', 1, 3, 356, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253386555&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/9.%20mfc%20v%20Horsham%20-%2019.09.2009&Qiv=thumbs&Qis=M', NULL, 'TERRY YORATH', NULL, NULL, NULL); -- -------------------------------------------------------- -- -- Table structure for table `goals` -- CREATE TABLE `goals` ( `goal_id` int(11) NOT NULL auto_increment, `match` int(11) default NULL, `scorer` int(11) default NULL, `goal_type` int(11) default NULL, `goal_time` int(11) default NULL, PRIMARY KEY (`goal_id`) ) TYPE=MyISAM AUTO_INCREMENT=116 ; INSERT INTO `goals` VALUES (1, 3, 48, 1, 90); INSERT INTO `goals` VALUES (2, 3, 53, 1, 49); INSERT INTO `goals` VALUES (3, 4, 6, 1, 23); INSERT INTO `goals` VALUES (4, 4, 6, 1, 33); INSERT INTO `goals` VALUES (5, 4, 38, 1, 73); INSERT INTO `goals` VALUES (6, 7, 6, 2, 34); INSERT INTO `goals` VALUES (7, 7, 68, 1, 23); INSERT INTO `goals` VALUES (8, 8, 8, 1, 41); INSERT INTO `goals` VALUES (9, 8, 33, 1, 43); INSERT INTO `goals` VALUES (10, 9, 38, 1, 43); -- -------------------------------------------------------- -- -- Table structure for table `players` -- CREATE TABLE `players` ( `player_id` int(11) NOT NULL auto_increment, `surname` varchar(255) default NULL, `firstname` varchar(255) default NULL, `date_of_birth` date default NULL, `position` int(11) default NULL, `image` varchar(255) default NULL, `date_joined` date default NULL, `date_left` date default NULL, `previous_clubs` varchar(255) default NULL, `place_of_birth` varchar(255) default NULL, `home_sponsor` varchar(255) default NULL, `away_sponsor` varchar(255) default NULL, `profile` longtext, `Triallist` varchar(10) default NULL, PRIMARY KEY (`player_id`) ) TYPE=MyISAM AUTO_INCREMENT=102 ; INSERT INTO `players` VALUES (66, 'Robinson', 'Stuart', '1901-01-01', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'defqwerqwrqwe\r\nqwerqwe\r\nrwer\r\nqwer\r\nqwer\r\nwer\r\nwqer\r\nwqe\r\nrqw\r\nrqw\r\ner\r\nqwer\r\nqwe', NULL); INSERT INTO `players` VALUES (61, 'Mullin', 'Pat', '1982-05-28', 1, NULL, '2008-06-01', '2009-10-08', NULL, NULL, NULL, NULL, 'Pat signed from Maidstone United, where he had made over 100 appearances, during the summer of 2008 for his second spell with the club. Initially took over the number one shirt from Scott Chalmers-Stevens but injury saw him sidelined until November. Upon his return he was ever-present for the remainder of the season.\r\n\r\nAs a youth Pat was at Coventry City and Millwall and has also featured for Dover Athletic, Sittingbourne and Herne Bay.\r\n\r\nStruggled to gain a first team spot this season as a plethora of goalkeepers came and went and joined Ramsgate in October.', NULL); INSERT INTO `players` VALUES (5, 'Beresford', 'Marc', '1986-10-12', 1, '', '2008-09-01', '2010-03-01', '', '', '', '', 'Stepped up from local football at the start of September last season to cover a long-term injury to Pat Mullin. Despite featuring on the bench in almost all of last season, he is still to make his first team debut.\r\n\r\nCurrently on a season long loan at Lordswood.', NULL); INSERT INTO `players` VALUES (40, 'Young', 'Dan', '1988-01-06', 2, NULL, '2007-06-01', NULL, NULL, NULL, NULL, NULL, 'Centre back born in Sidcup who started his career with Derby County. Danny captained the Rams’ youth team before progressing to the reserves, playing regularly for them during 2004/05 and 2005/06.\r\n\r\nAfter being released by Derby Danny had a short spell with Bromley at the start of the 2006/07 season before moving on to Croydon Athletic where he won most of the club’s end of season awards that year.\r\n\r\nHe signed for Margate in the summer of 2007 after turning in some impressive displays during the pre-season friendlies and took over the captains role following Louis Smiths long-term injury last season.', NULL); INSERT INTO `players` VALUES (59, 'Lewis', 'Ben', '1977-06-22', 2, NULL, '2009-06-01', '2009-11-01', NULL, NULL, NULL, NULL, 'Ben, seemingly, orignally signed for Margate in March 2009 but confusion over his release from Maidstone United prevented this from being finalised until the summer.\r\n\r\nStarted off Heybridge Swifts before joining Colchester United, where he made two youth appearances before joining Southend in August 1997. and scored the winner on his debut against Fulham.\r\n\r\nKnee problems ended his professional career after 14 appearances and 1 goal for the Roots Hall side and he dropped into non-league in 1999 again with Heybridge before being snapped up by home-town club Chelmsford City the following March.\r\n\r\nHe moved to Grays Athletic in May 2002 before moving on to Ford United that December Following this, he has played for non league teams Grays Athletic, Ford United, Chelmsford City, Heybridge Swifts, Welling United, Bishop''s Stortford and Maidstone United\r\n\r\nMoved onto Bishop''s Stortford in November 2004 joining St Albans City in 2006. He made 21 Conference South appearances that season but after just one more start the following term he joined Welling in the Sptember before moving to Maidstone the following May.', NULL); INSERT INTO `players` VALUES (33, 'Robinson', 'Curtis', '1989-04-22', 2, 'http://www.margate-fc.com/match_gallery/qdig-files/converted-images/Season%202010-11/00.%20Margate%20Squad%20Photos%202010-11/med_IMG_7923.JPG', '2008-06-01', NULL, 'Ramsgate, Dover Athletic', NULL, 'Tom McKenna', NULL, 'Signed for Margate in the summer of 2008 and gradually became a regular and reliable part of the defence with calmness that belies his young age. Curtis also possesses a impressively long throw. <br />\r\n<br />\r\nStarted off with Ramsgate in their Youth and Reserve sides before leaving for Greenwich University and featured for Dover Athletic Reserves before moving to Hartsdown Park.', NULL); INSERT INTO `players` VALUES (69, 'Wheatley', 'Luke', '1991-04-25', 2, '', '2008-10-01', '2010-01-01', '', '', '', '', 'Local youngster who was called up to the first team squad in October 2008 and scored his first goal two weeks later in the win at Boreham Wood.\r\nAnother of the local lads who have come into the first team with more confidence and strength than you would expect from a defender still in his teens.\r\n\r\nMoved to Ramsgate on an initial one month loan deal in October but returned in mid-November.', NULL); INSERT INTO `players` VALUES (15, 'Haverson', 'Jack', '1987-08-22', 2, '', '2009-03-01', NULL, '', '', '', '', 'Jack began his career at Ipswich Town, joining their academy at the age of 16. He left the Suffolk side in the summer of 2006 to join AFC Bournemouth but spent much of the following season on loan at Hayes before joining Grays at the start of this season.\r\n\r\nHe joined Bromley in February 2008. Has played also for Sutton United and Sittingbourne.', NULL); INSERT INTO `players` VALUES (28, 'Morris', 'Kieran', '1987-04-29', 3, '', '2007-08-01', NULL, '', '', '', '', 'Signed for Margate in August 2007 after solid displays in pre-season friendlies. After 22 starts and 15 substitute appearances he was one of the few players who remained with the club under new management for the 2008/09 season.\r\n\r\nKieran started the first ten games but then found himself frequently on the sub''s bench until a surprising appearance at right-back in November saw a new side to the midfielder as he slotted into the position comfortably, although injury did, eventually, restrict him to 29 appearances.\r\n\r\nMoved to Whitstable Town on loan in December.', NULL); INSERT INTO `players` VALUES (22, 'Lacy', 'Aaron', '1981-06-24', 2, 'http://www.margate-fc.com/match_gallery/qdig-files/converted-images/Season%202010-11/00.%20Margate%20Squad%20Photos%202010-11/med_IMG_7918.JPG', '2010-02-01', NULL, 'Gillingham, Lordswood, Chatham Town, Maidstone United', NULL, 'Alan Anstice', NULL, 'Signed for Margate in February 2010 after over 6 years with Maidstone United and immeidiately caught the attention of the fans with his long throws.<br />\r\n<br />\r\nA right-sided defender who started off with Gillingham and has also featured for Lordswood and Chatham Town.', NULL); this query $gls = mysql_query("SELECT *, COUNT(CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END) AS lgegls, COUNT(CASE games.competition WHEN 2 THEN goals.goal_id ELSE NULL END) AS facgls, COUNT(CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END) AS fatgls, COUNT(CASE games.competition WHEN 4 THEN goals.goal_id ELSE NULL END) AS kscgls, COUNT(CASE games.competition WHEN 5 THEN goals.goal_id ELSE NULL END) AS lgecgls, COUNT(goals.goal_id) AS tgls FROM goals, games INNER JOIN players ON goals.scorer = players.player_id WHERE goals.match = games.match_id AND games.competition <> 6 GROUP BY goals.scorer ORDER BY tgls DESC, lgegls DESC "); if (!$gls) { die("Query to show fields from table failed"); } and this html for the output <table width="577" border="0" cellpadding="0" cellspacing="0" BORDER=1 RULES=ROWS FRAME=BOX style="text-align:center"> <tr> <td width="17" align="center" valign="bottom" style="font-size: 10px; font-weight: bold;">SEE GOALS</td> <td width="100"> </td> <td width="60" align="center" valign="bottom" class="Table_Headers">RYMAN<br /> PREMIER</td> <td width="60" align="center" valign="bottom" class="Table_Headers">FA<br /> CUP</td> <td width="60" align="center" valign="bottom" class="Table_Headers">FA<br /> TROPHY</td> <td width="60" align="center" valign="bottom" class="Table_Headers">KENT SENIOR<br /> CUP</td> <td width="60" align="center" valign="bottom" class="Table_Headers">LEAGUE<br /> CUP</td> <td width="60" align="center" valign="bottom" class="Table_Headers">TOTAL</td> </tr> <?php while ($row_gls = mysql_fetch_assoc($gls)){ if ($row_gls['player_id']=="2") echo '<tr> <td></td> '; else echo '<tr> <td><a href="http://www.margate-fc.com/content/1st_team/player_goals.php?recordID='.$row_gls['player_id'].'" ><img src="/edit/news/data/upimages/more.png" width="16" border="0" alt="More Stats" title="More Stats"/></a></td> '?> <?php if ($row_gls['player_id']=="2") echo '<td style="text-align:left">'.$row_gls['firstname'].' '.$row_gls['surname'].'\'s'; else echo '<td style="text-align:left"><a href="/content/1st_team/squad_details.php?recordID='.$row_gls['player_id']. '">'. $row_gls['surname']. ', '. $row_gls['firstname']. '</a></td>'; ?> <?php echo ' <td bgcolor="#C4D8FD">'. $row_gls['lgegls']. '</td> <td>'. $row_gls['facgls']. '</td> <td bgcolor="#C4D8FD">'. $row_gls['fatgls']. '</td> <td>'. $row_gls['kscgls']. '</td> <td bgcolor="#C4D8FD">'. $row_gls['lgecgls']. '</td> <td class="Normal_Table_Column_bold">'. $row_gls['tgls']. '</td> </tr>'; } ?> </table> Everything works fine but I now want to create another page which uses this query, but with more complex COUNT's from another table as well. The extra table (and some data) is -- -- Table structure for table `appearances` -- CREATE TABLE `appearances` ( `app_id` int(11) NOT NULL auto_increment, `match` int(11) default NULL, `number` int(11) default NULL, `player` int(11) default NULL, `type` int(11) default NULL, `on` int(11) default NULL, `off` int(11) default NULL, `yellows` int(11) default NULL, `red` char(1) default NULL, `replaced` int(11) default NULL, PRIMARY KEY (`app_id`) ) TYPE=MyISAM AUTO_INCREMENT=1759 ; INSERT INTO `appearances` VALUES (1, 1, 1, 66, 1, 0, 90, 0, 'N', NULL); INSERT INTO `appearances` VALUES (2, 1, 2, 28, 1, 0, 90, 1, 'N', NULL); INSERT INTO `appearances` VALUES (3, 1, 3, 33, 1, 0, 90, 0, 'N', NULL); INSERT INTO `appearances` VALUES (4, 1, 4, 59, 1, 0, 90, 0, 'N', NULL); INSERT INTO `appearances` VALUES (5, 1, 5, 69, 1, 0, 46, 0, 'N', NULL); INSERT INTO `appearances` VALUES (6, 1, 6, 15, 1, 0, 90, 0, 'N', NULL); INSERT INTO `appearances` VALUES (7, 1, 7, 22, 1, 0, 31, 0, 'Y', NULL); INSERT INTO `appearances` VALUES (8, 1, 8, 38, 1, 0, 90, 0, 'N', NULL); INSERT INTO `appearances` VALUES (9, 1, 9, 53, 1, 0, 90, 0, 'N', NULL); INSERT INTO `appearances` VALUES (10, 1, 10, 6, 1, 0, 90, 0, 'N', NULL); I would want to still count everything by competition as I have done in the original query but as there are 3 appearances types I would need to show each for each competition, with each combination showing as a separate column in the output ie. CompetitionAppearance TypeOutput Column Name 11Comp1Type1 12Comp1Type2 13Comp1Type3 21Comp2Type1 22Comp2Type2 23Comp2Type3 Having had a good read up on the COUNT function I could not see a way to use multiple criteria so tried to count a select COUNT(SELECT * FROM goals, games, appearances INNER JOIN players ON goals.scorer = players.player_id INNER JOIN players ON appearances.player = players.player_id WHERE goals.match = games.match_id AND appearances.type = 1 AND games.competition = 1) AS comp1type1; COUNT(SELECT * FROM goals, games, appearances INNER JOIN players ON goals.scorer = players.player_id INNER JOIN players ON appearances.player = players.player_id WHERE goals.match = games.match_id AND appearances.type = 2 AND games.competition = 1) AS comp2type1; COUNT(SELECT * FROM goals, games, appearances INNER JOIN players ON goals.scorer = players.player_id INNER JOIN players ON appearances.player = players.player_id WHERE goals.match = games.match_id AND appearances.type = 1 AND games.competition = 2) AS comp1type2 but when I add that into the existing query it just brings up a page with no results. In trying to get it working I am probably further away now than I was to start with and am pretty sure that, once again, I am going the long way round this. My gut feeling is that it would work better with more grouping but having read up on that, I cannot work out how to get the results to output in the columns that I want. Any suggestions, advice or offers to rip it up and start again would be gratefully received! Thanks in advance Steve Quote Link to comment https://forums.phpfreaks.com/topic/222723-counting-from-2-tables/ Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.