Help for Football Fixtures

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!

Thanks Dave, glad to hear that it is not impossibile :-)

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

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

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?

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!

Thanks for all Dave! It works whit left join :-)!!