Help for Football Fixtures
8 posts by 2 authors in: Forums > CMS Builder
Last Post: November 1, 2013 (RSS)
By mbareara - October 26, 2013
Thank you in advance for your help
i have a football database on CMS and i would put a fixtures in my website.
I have 3 tables
Team (Num, Name) - Season (num, year) - Matches(num, team_home, team_away, goal_home, goal_away, season)
Of course each win give 3 points, 1 for draw, 0 for lose.
I determine number of wins, draws and lose matches for each team in tis way
<?php $homeTeamMatches = mysql_count("matches", "team_home = 1 " ) ;?><?php echo $homeTeamMatches;?>
<?php $homeTeamWins = mysql_count("matches", "goal_home > goal_away AND team_home = 1" ) ;?>
<?php $homeTeamDraw = mysql_count("matches", "goal_home = goal_away AND team_home = 1" ) ;?>
<?php $homeTeamLose = mysql_count("matches", "goal_home < goal_away AND team_home =1 " ) ;?>
<?php $awayTeamMatches = mysql_count("matches", "team_away = 1 " ) ;?><?php echo $awayTeamMatches;?>
<?php $awayTeamWins = mysql_count("matches", "goal_home < goal_away AND team_away = 1" ) ;?>
<?php $awayTeamDraw = mysql_count("matches", "goal_home = goal_away AND team_away = 1" ) ;?>
<?php $awayTeamLose = mysql_count("matches", "goal_home > goal_away AND team_away =1 " ) ;?>
and finally points gained by the team
<?php echo number_format(($homeTeamWins * 3) + ($homeTeamDraw * 1) + ($awayTeamWins * 3) + ($awayTeamDraw * 1)) ?>
This is good for stats in team 1 page, but i would have one page with all this stats GROUPED BY team and ORDER BY points DESC
so
TEAM | GAME | POINTS
is it possibile?
Thank you
By Dave - October 30, 2013
Hi mbareara,
Yes, it's possible. It will just some time figuring out the right MySQL query. Even writing code as much as we do, sometimes figuring out the right query for a report can take a fair bit of time.
Here's some PHP code to do a mySQL query that loads all the records from the accounts table.
// mysql query
global $TABLE_PREFIX;
$query = "SELECT * FROM {$TABLE_PREFIX}accounts";
$result = @mysql_query($query) or die("MySQL Error: ". htmlspecialchars(mysql_error()) . "\n");
$rows = array();
while ($row = mysql_fetch_assoc($result)) { $rows[] = $row; }
if (is_resource($result)) { mysql_free_result($result); }
showme($rows); // show returned rows
So what you can do is actually have MySQL do some calculations all in one query and assign them fake fieldnames. Here's an example with accounts that adds fields that indicate whether a users number is over or under 150 and then sorts by that.
global $TABLE_PREFIX;
$query = "SELECT *, (num > 150) as over150s, (num < 150) as under150s FROM {$TABLE_PREFIX}accounts ORDER BY under150s DESC";
$result = @mysql_query($query) or die("MySQL Error: ". htmlspecialchars(mysql_error()) . "\n");
$rows = array();
while ($row = mysql_fetch_assoc($result)) { $rows[] = $row; }
if (is_resource($result)) { mysql_free_result($result); }
So that's not exactly what you need, but that's where you'd want to put your COUNT, GROUP BY and ORDER BY code. So the next step after that would just be to google for the right MySQL to get the output you want and experiment until you get it right.
Hope that helps!
interactivetools.com
By mbareara - October 30, 2013
select team,
count(team) as matches,
sum(if(points=3,1,0)) as wins,
sum(if(points=1,1,0)) as draws,
sum(if(points=0,1,0)) as lose,
sum(points) as points,
sum(goalf) as goalf,
sum(goals) as goals,
sum(goalf)-sum(goals) as diff_goal,
sum(if(points=3 and dove = "H",1,0)) as home_win,
sum(if(points=1 and dove = "H",1,0)) as home_draw,
sum(if(points=0 and dove = "H",1,0)) as home_lose,
sum(if(points=3 and dove = "A",1,0)) as away_win,
sum(if(points=1 and dove = "A",1,0)) as away_draw,
sum(if(points=0 and dove = "A",1,0)) as away_lose
from
(
select team_home as team,goal_home as goalf,goal_away as goals,"H" as dove, case when goal_home > goal_away then 3 when goal_home = goal_away then 1 else 0 end as points from cms_matches
union all select team_away as team,goal_away as goals,goal_home as goals,"A", case when goal_away > goal_home then 3 when goal_away = goal_home then 1 else 0 end as points from cms_matches
) as tab group by team order by points desc
Here it is :-) I'm really happy because it works in mysql plugin, now i'm trying to show this in a html page :D
By mbareara - October 30, 2013
And this is the php code...
// mysql query
global $TABLE_PREFIX;
$query = "select team,
count(team) as matches,
sum(if(points=3,1,0)) as wins,
sum(if(points=1,1,0)) as draws,
sum(if(points=0,1,0)) as lose,
sum(points) as points,
sum(goalf) as goalf,
sum(goals) as goals,
sum(goalf)-sum(goals) as diff_goal,
sum(if(points=3 and dove = 'H',1,0)) as home_win,
sum(if(points=1 and dove = 'H',1,0)) as home_draw,
sum(if(points=0 and dove = 'H',1,0)) as home_lose,
sum(if(points=3 and dove = 'A',1,0)) as away_win,
sum(if(points=1 and dove = 'A',1,0)) as away_draw,
sum(if(points=0 and dove = 'A',1,0)) as away_lose
from
(
select team_home as team,goal_home as goalf,goal_away as goals,'H' as dove, case when goal_home > goal_away then 3 when goal_home = goal_away then 1 else 0 end as points from cms_matches
union all select team_away as team,goal_away as goals,goal_home as goals,'A', case when goal_away > goal_home then 3 when goal_away = goal_home then 1 else 0 end as points from cms_matches
) as tab group by team order by points desc";
$result = @mysql_query($query) or die("MySQL Error: ". htmlspecialchars(mysql_error()) . "\n");
$rows = array();
while ($row = mysql_fetch_assoc($result)) { $rows[] = $row; }
if (is_resource($result)) { mysql_free_result($result); }
So how can i put this result in a html table? Thank you for your help
Orazio
By mbareara - October 30, 2013
Ok now the code is complete...
<?php
// mysql query
global $TABLE_PREFIX;
$query = "select team,
count(team) as matches,
sum(if(points=3,1,0)) as wins,
sum(if(points=1,1,0)) as draws,
sum(if(points=0,1,0)) as lose,
sum(points) as points,
sum(goalf) as goalf,
sum(goals) as goals,
sum(goalf)-sum(goals) as diff_goal,
sum(if(points=3 and dove = 'H',1,0)) as home_win,
sum(if(points=1 and dove = 'H',1,0)) as home_draw,
sum(if(points=0 and dove = 'H',1,0)) as home_lose,
sum(if(points=3 and dove = 'A',1,0)) as away_win,
sum(if(points=1 and dove = 'A',1,0)) as away_draw,
sum(if(points=0 and dove = 'A',1,0)) as away_lose
from
(
select team_home as team,goal_home as goalf,goal_away as goals,'H' as dove, case when goal_home > goal_away then 3 when goal_home = goal_away then 1 else 0 end as points from cms_matches
union all select team_away as team,goal_away as goals,goal_home as goals,'A', case when goal_away > goal_home then 3 when goal_away = goal_home then 1 else 0 end as points from cms_matches
) as tab group by team order by points desc";
$result = @mysql_query($query) or die("MySQL Error: ". htmlspecialchars(mysql_error()) . "\n");
$rows = array();
while ($row = mysql_fetch_assoc($result)) :
?>
<tr>
<td><?php echo $row['team'] ?></td>
<td><?php echo $row['points'] ?></td>
</tr>
<! -- etc etc -->
<?php endwhile ?>
Last question (i swear), how can i have team:label?
By Dave - October 31, 2013
Hi Orazio,
Nice work!
Last question (i swear), how can i have team:label?
You seem pretty good at MySQL, so the "proper" way is to do a LEFT JOIN on the team table.
However, if you don't have too many team records, an easier way might be to just create an array of team numbers to names and just reference that like this: echo $teamNumToName[ $teamNum ]
Here's some sample code to create a num to name lookup array:
// create lookup array of user nums to names
$records = mysql_select('accounts');
$numsToNames = array_combine(array_pluck($records, 'num'), array_pluck($records, 'fullname'));
// example code:
print "User 140's fullname is: " . @$numsToNames['140']. "<br/>\n";
showme($numsToNames);
Hope that helps!
interactivetools.com