1

I have been struggling with this one for a couple days. I am trying to build a Joomla component that will display team standings based on record and then points for and points against. Currently I have the winning team of a game pushed into an array, but now trying to find a way to count the amount of times each specific name is in the array and display each teams record. For some reason this is really kicking my butt, any suggestions on what I am doing wrong or how to make it work is greatly appreciated.

$wins = array(); $loss = array(); $ties = array(); $db = Factory::getDBO(); $query = $db->getQuery( true ); $query = "SELECT s.id as id,s.hometeamid,s.awayteamid,b.finv,b.finh,ht.id as hometeamid,ht.name as hometeamname,at.id as awayteamid,at.name as awayteamname FROM #__football_schedule s INNER JOIN #__football_boxscore b ON b.gameid=s.id LEFT OUTER JOIN #__football_team ht ON ht.id = s.hometeamid LEFT OUTER JOIN #__football_team at ON at.id = s.awayteamid"; $db->setQuery( $query ); $schedules = $db->loadObjectList(); foreach ($schedules as $schedule) { if ($schedule->finh > $schedule->finv) { array_push($wins, $schedule->hometeamid, $schedule->finh); } if ($schedule->finv > $schedule->finh) { array_push($wins, $schedule->awayteamid); } if ($schedule->finh < $schedule->finv) { array_push($loss, $schedule->hometeamid, $schedule->finh); } if ($schedule->finv < $schedule->finh) { array_push($loss, $schedule->awayteamid); } if ($schedule->finv == $schedule->finh) { array_push($ties, $schedule->hometeamid and $schedule->awayteamid); } }

Here is a SQL Fiddle with the above tables and some sample data.

Improve this question
5
  • Welcome Brandon, please take our tour. I don't have time to read your question thoroughly, but I'd like to see better logical grouping in your battery of if conditions. Some subsequent if can be ignored if an earlier if is satisfied -- in these cases, we should use elseif and else conditions. Have a rethink about your pushing. Also, I am concerned about that last array_push() containing and -- I don't think you want this. Personally, I always use square brace syntax to push unless pushing multiple elements at one time. Commented Nov 2, 2021 at 6:29
  • Honestly, if you want to see a pure sql solution (I'd probably try to do it all with sql) which counts each outcome for each team, I'll ask you to provide a db-fiddle demo with enough sample data to allow volunteers to instantly test their solutions with 100% certainty. Commented Nov 2, 2021 at 6:31
  • Thanks MickMack. I agree with the too many ifs, and utilizing elseifs, and pushing to arrays, but I am seriously stumped on this one and have just been trying to find a way. Sql: sqlfiddle.com/#!9/af734f/1 Commented Nov 2, 2021 at 14:01
  • I modified your sample data to include a tie. Is this what you require? db-fiddle.com/f/qb7v887RGECyFxLdnR7AnR/1 Commented Nov 3, 2021 at 12:40
  • 1
    Hey MickMack, as per usual you are a genius and have taken minutes to resolve something I have spent hours on! I really do appreciate it, I never thought to use just SQL, and was doing everything in the PHP. Thank you so much. Commented Nov 3, 2021 at 16:19

1 Answer 1

Reset to default
1

Logically speaking, you only need the #__football_boxscore data. The convolution comes with the fact that you need to relate the final scores to individual teams -- made more difficult because the team id might be in the home id or away id column.

To relate the team table rows to the boxscore table rows, you'll need to leverage the schedule table. Since you'll never need to account for "unrelated" ids between these tables, using standard JOINs is appropriate. This would be true, if for some reason, you wanted to relate scheduled games to games that have not been created in the boxscore table.

After joining the tables, you will need to condense all rows down so that a team only exists once in the result set -- use GROUP BY.

Now that the team-specific data has been "crowded together", you'll need to use conditions and aggregate functions in the SELECT clause to prepare and prepare the game outcome tallies. If, within the group of boxscore data, you have a row where both scores are the same, then you don't need to check which team had which score -- it's a tie. If not a tie, you'll need to see if the current row's team id is in the home or away column and determine if the team won or lost.

The SQL that I've craft looks pretty elegant to me, but arriving at this script is not very easy for developers who are new to aggregate functions. Setting up the SQL in this way should allow you to extend the functionality if/when needed -- for instance, if you only wanted the win/loss/draw records for a single team, you could just write the SQL with a WHERE clause between the last JOIN and the GROUP BY then you're done!

Raw SQL (DB Fiddle)

SELECT tm.id, tm.name, SUM(bs.finh = bs.finv) ties, SUM((tm.id = sc.hometeamid AND bs.finh > bs.finv) OR (tm.id = sc.awayteamid AND bs.finh < bs.finv)) wins, SUM((tm.id = sc.hometeamid AND bs.finh < bs.finv) OR (tm.id = sc.awayteamid AND bs.finh > bs.finv)) losses FROM #__football_team tm JOIN #__football_schedule sc ON tm.id IN (sc.hometeamid, sc.awayteamid) JOIN #__football_boxscore bs ON sc.id = bs.gameid GROUP BY tm.id, tm.name
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.