Help for Football Fixtures

8 posts by 2 authors in: Forums > CMS Builder
Last Post: November 1, 2013   (RSS)

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!

Dave Edis - Senior Developer

interactivetools.com

By mbareara - October 30, 2013

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

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!

Dave Edis - Senior Developer

interactivetools.com

By mbareara - November 1, 2013

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