Solved

football/soccer results grid advice php/mysql

Posted on 2010-08-17
29
955 Views
Last Modified: 2013-12-13
example from another site
I need to create the above from my database, and I am not sure how to go about it.
Basically this calculates all the goals scored against another team. If team 1 (column) is the same as team 1 (row) an "x" is put in place as team 1 cannot play itself

I have added my database structure

dbase-dump.txt
0
Comment
Question by:cataleptic_state
  • 16
  • 11
  • +1
29 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 33455365
Build the header first and then the rows.  If you're teams are pulled from a database, sort by team number ascending.  Team 1 is always in column 1, team 2 is always in column 2...etc...

As you're building the rows and going across the columns, note what column you're in.  If Row team and column team equal, put in your "X".  Otherwise, get the score.

0
 
LVL 9

Expert Comment

by:Snarfles
ID: 33455763
1. do a query to select all teams into an array first.
2. print out the table start and build the header in the first row based on this array using a for loop
3. Use another for loop with the above array this time with the aim to print out one row per array record
4. In this for loop get it to select all scores from the matches table where the team1 is the current team. Make sure you sort this by team2 to get it in the right order.
5. Build your cells from this new array.
6. Close off the table.

Your matches table couldn't have a team in both team1 and team2 so you will need to just add a variable that you increment by 1 each time you go through your row for loop. When this variable is equal to current cell rather than printing the score print an x.

Hope that helps.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33455905
I re-did my matches table with thanks to another EE member.

I need to echo the scores out when the match_id is the same for each team.


CREATE TABLE IF NOT EXISTS `matchscores` (
  `matchscores_id` int(8) NOT NULL auto_increment,
  `match_id` int(8) NOT NULL,
  `team` int(8) NOT NULL,
  `score` int(8) default NULL,
  PRIMARY KEY  (`matchscores_id`),
  KEY `match_id` (`match_id`,`team`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Author Comment

by:cataleptic_state
ID: 33472600
is there anyway I can get this done?
0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33500333
So is each cell a single match or the total number of goals against that team? Currently you have 2 cells per each pairing.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33501006
Each sell has the teams scores with the apposing team this is the total goals played in each encounter and they are only allowed two encounters.

So each team places the other twice
0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33502117
So we have our output from this query (from the other question: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/Q_26417023.html?cid=748#a33501495)...

How about we populate an array based on the team ids, check if there is already a score for that cell, and if there is we swap the teams round...

(Sorry, i haven't manage to test this code, but the concept should be good).


<?php
$result = mysql_query('select m.match_id, m.date, m.time, m.report, t1.team_name as team1_name, s1.team_id as team1_id, s1.score as score1, t2.team_name as team2_name, s2.team_id as team2_id, s2.score as score2 from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id) where s1.team <> s2.team group by match_id order by m.match_id');

while($record = mysql_fetch_assoc($result))
{
  // Check if we have already got an entry for this pair of teams, and if so, try them the other way round.
  if(!isset($resultsgrid[$record['team1_id']][$record['team2_id']]))
  {
    $resultsgrid[$record['team1_id']][$record['team2_id']] = $record['score1'].'-'.$record['score2'];
  }
  elseif(!isset($resultsgrid[$record['team2_id']][$record['team1_id']]))
  {
    $resultsgrid[$record['team2_id']][$record['team1_id']] = $record['score2'].'-'.$record['score1'];
  }
  else
  {
    // This would be the 3rd result for this pair so make an error or something.
  }
}
// Get extents of table
$maxrow = $maxcol = 0;
$minrow = key($resultsgrid);
$mincol = key($resultsgrid[$minrow]);
foreach($resultsgrid as $rownum => $row)
{
	if($rownum>$maxrow)$maxrow=$rownum;
	if($rownum<$minrow)$minrow=$rownum;
	foreach($row as $colnum => $cell)
		if($colnum>$maxcol)$maxcol=$colnum;
		if($colnum>$mincol)$mincol=$colnum;
}
// Now output the grid as a table
$html = '
	<table>';
for($row=$minrow;$row<$maxrow;$row++)
{
	$html .= '
		<tr>';
	for($col=$mincol;$col<$maxcol;$col++)
	{
		$html .= '
			<td>'.(isset($resultsgrid[$row][$col])?$resultsgrid[$row][$col]:'-').'</td>'; // If there is no result for this position, make a '-' instead.
	}
	$html .= '
		</tr>';
}
$html .= '
	</table>';

Open in new window

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33502454
Ok,
I will check that, i just realised how will I make the page to input that information?

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33502519
ignore my last comment.

I am going crazy with this thing.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33509050
Hi,
I am getting these errors:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\football\results-grid.php on line 248

Warning: key() [function.key]: Passed variable is not an array or object in C:\xampp\htdocs\football\results-grid.php on line 266

Warning: key() [function.key]: Passed variable is not an array or object in C:\xampp\htdocs\football\results-grid.php on line 267

Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\football\results-grid.php on line 272

Open in new window

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33509103
Hi
I have been playing with the code and I have copied the sql code from the fixtures and it has made the errors disappear.

But it does not output anything.
$result = mysql_query("select m.match_id, date_format(m.date, '%d/%m/%Y') as mDate, m.time, t1.division, m.report, t1.team_name as team1_name, s1.score as score1, t2.team_name as team2_name, s2.score as score2 
from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id) 
where s1.team <> s2.team 
group by match_id 
order by m.match_id");

while($record = mysql_fetch_assoc($result));

Open in new window

0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33509147
to see if there is an error in the sql, add "or die(mysql_error())" to the end of the mysql_query() line.
$result = mysql_query('select m.match_id, m.date, m.time, m.report, t1.team_name as team1_name, s1.team_id as team1_id, s1.score as score1, t2.team_name as team2_name, s2.team_id as team2_id, s2.score as score2 from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id) where s1.team <> s2.team group by match_id order by m.match_id') or die(mysql_error());

Open in new window

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33509256
Hi,
theres no errors
0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33509709
Please post the output code.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33509924
Hi,
Here is the code I have on the page, it outputs nothing.
<?php
$result = mysql_query("select m.match_id, date_format(m.date, '%d/%m/%Y') as mDate, m.time, t1.division, m.report, t1.team_name as team1_name, s1.score as score1, t2.team_name as team2_name, s2.score as score2 
from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id) 
where s1.team <> s2.team 
group by match_id 
order by m.match_id") or die(mysql_error());

while($record = mysql_fetch_assoc($result));
{
  // Check if we have already got an entry for this pair of teams, and if so, try them the other way round.
  if(!isset($resultsgrid[$record['team1_id']][$record['team2_id']]))
  {
    $resultsgrid[$record['team1_id']][$record['team2_id']] = $record['score1'].'-'.$record['score2'];
  }
  elseif(!isset($resultsgrid[$record['team2_id']][$record['team1_id']]))
  {
    $resultsgrid[$record['team2_id']][$record['team1_id']] = $record['score2'].'-'.$record['score1'];
  }
  else
  {
    // This would be the 3rd result for this pair so make an error or something.
  }
}
// Get extents of table
$maxrow = $maxcol = 0;
$minrow = key($resultsgrid);
$mincol = key($resultsgrid[$minrow]);
foreach($resultsgrid as $rownum => $row)
{
	if($rownum>$maxrow)$maxrow=$rownum;
	if($rownum<$minrow)$minrow=$rownum;
	foreach($row as $colnum => $cell)
		if($colnum>$maxcol)$maxcol=$colnum;
		if($colnum>$mincol)$mincol=$colnum;
}
// Now output the grid as a table
$html = '
	<table>';
for($row=$minrow;$row<$maxrow;$row++)
{
	$html .= '
		<tr>';
	for($col=$mincol;$col<$maxcol;$col++)
	{
		$html .= '
			<td>'.(isset($resultsgrid[$row][$col])?$resultsgrid[$row][$col]:'-').'</td>'; // If there is no result for this position, make a '-' instead.
	}
	$html .= '
		</tr>';
}
$html .= '
	</table>';
?>

Open in new window

0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33510077
You have put a ";" at the end of line 8 that shouldn't be there.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33510893
yes but If I dont put it in, errors come up regarding the passing of values and foreach loop see the previous post from earlier today : 33509050
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33510939
Hi,
OK I have removed the ";" as I realised I had changed the SQL too, and now it does not error, and also the page output is blank, the rest of the page is displayed. Just not the table in the code.
0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33511070
Ah! Sorry, I missed curly brackets out enclosing lines 33 & 34. Also an error in 39 and 43.
<?php
$result = mysql_query("select m.match_id, date_format(m.date, '%d/%m/%Y') as mDate, m.time, t1.division, m.report, t1.team_name as team1_name, s1.score as score1, t2.team_name as team2_name, s2.score as score2 
from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id) 
where s1.team <> s2.team 
group by match_id 
order by m.match_id") or die(mysql_error());

while($record = mysql_fetch_assoc($result));
{
  // Check if we have already got an entry for this pair of teams, and if so, try them the other way round.
  if(!isset($resultsgrid[$record['team1_id']][$record['team2_id']]))
  {
    $resultsgrid[$record['team1_id']][$record['team2_id']] = $record['score1'].'-'.$record['score2'];
  }
  elseif(!isset($resultsgrid[$record['team2_id']][$record['team1_id']]))
  {
    $resultsgrid[$record['team2_id']][$record['team1_id']] = $record['score2'].'-'.$record['score1'];
  }
  else
  {
    // This would be the 3rd result for this pair so make an error or something.
  }
}
// Get extents of table
$maxrow = $maxcol = 0;
$minrow = key($resultsgrid);
$mincol = key($resultsgrid[$minrow]);
foreach($resultsgrid as $rownum => $row)
{
	if($rownum>$maxrow)$maxrow=$rownum;
	if($rownum<$minrow)$minrow=$rownum;
	foreach($row as $colnum => $cell)
	{
		if($colnum>$maxcol)$maxcol=$colnum;
		if($colnum>$mincol)$mincol=$colnum;
	{
}
// Now output the grid as a table
$html = '
	<table>';
for($row=$minrow;$row<=$maxrow;$row++)
{
	$html .= '
		<tr>';
	for($col=$mincol;$col<=$maxcol;$col++)
	{
		$html .= '
			<td>'.(isset($resultsgrid[$row][$col])?$resultsgrid[$row][$col]:'-').'</td>'; // If there is no result for this position, make a '-' instead.
	}
	$html .= '
		</tr>';
}
$html .= '
	</table>';
?>

Open in new window

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33511428
Hi still blank,
I had to edit a line as it was sayin $send error
<?php
$result = mysql_query("select m.match_id, date_format(m.date, '%d/%m/%Y') as mDate, m.time, t1.division, m.report, t1.team_name as team1_name, s1.score as score1, t2.team_name as team2_name, s2.score as score2 
from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id) 
where s1.team <> s2.team 
group by match_id 
order by m.match_id") or die(mysql_error());

while($record = mysql_fetch_assoc($result));
{
  // Check if we have already got an entry for this pair of teams, and if so, try them the other way round.
  if(!isset($resultsgrid[$record['team1_id']][$record['team2_id']]))
  {
    $resultsgrid[$record['team1_id']][$record['team2_id']] = $record['score1'].'-'.$record['score2'];
  }
  elseif(!isset($resultsgrid[$record['team2_id']][$record['team1_id']]))
  {
    $resultsgrid[$record['team2_id']][$record['team1_id']] = $record['score2'].'-'.$record['score1'];
  }
  else
  {
    // This would be the 3rd result for this pair so make an error or something.
  }
}
// Get extents of table
$maxrow = $maxcol = 0;
$minrow = key($resultsgrid);
$mincol = key($resultsgrid[$minrow]);
foreach($resultsgrid as $rownum => $row)
{
	if($rownum>$maxrow)$maxrow=$rownum;
	if($rownum<$minrow)$minrow=$rownum;
	foreach($row as $colnum => $cell)
	{
		if($colnum>$maxcol)$maxcol=$colnum;
		if($colnum>$mincol)$mincol=$colnum;
	}
}
// Now output the grid as a table
$html = '
	<table>';
for($row=$minrow;$row<=$maxrow;$row++)
{
	$html .= '
		<tr>';
	for($col=$mincol;$col<=$maxcol;$col++)
	{
		$html .= '
			<td>'.(isset($resultsgrid[$row][$col])?$resultsgrid[$row][$col]:'-').'</td>'; // If there is no result for this position, make a '-' instead.
	}
	$html .= '
		</tr>';
}
$html .= '
	</table>';

Open in new window

0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33511572
put "echo $html;" at the end.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33511768
Hi
So using echo $html; I can output anything I input into the $html variable?

Ok so I have added that and this comes up "-"

I assume from this line: <td>'.(isset($resultsgrid[$row][$col])?$resultsgrid[$row][$col]:'-').'</td>'; // If there is no result for this position, make a '-' instead.

So can I now put the team names in somewhere?
0
 
LVL 10

Accepted Solution

by:
ollyatstithians earned 500 total points
ID: 33518976
Tested code below:
$result = mysql_query("
select 
m.match_id, m.date, m.time, m.report, 
t1.team_name as team1_name, t1.team_id as team1_id, s1.score as score1, 
t2.team_name as team2_name, t2.team_id as team2_id, s2.score as score2 
from matches m 
left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) 
left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id) 
where s1.team <> s2.team group by match_id order by m.match_id") or die(mysql_error());

while($record = mysql_fetch_assoc($result))
{
  // Check if we have already got an entry for this pair of teams, and if so, try them the other way round.
  if(!isset($resultsgrid[$record['team1_id']][$record['team2_id']]))
  {
    $resultsgrid[$record['team1_id']][$record['team2_id']] = $record['score1'].'-'.$record['score2'];
  }
  elseif(!isset($resultsgrid[$record['team2_id']][$record['team1_id']]))
  {
    $resultsgrid[$record['team2_id']][$record['team1_id']] = $record['score2'].'-'.$record['score1'];
  }
  else
  {
    // This would be the 3rd result for this pair so make an error or something.
  }
  // Also we want a list of teams for our headers
  $teams[$record['team1_id']] = $record['team1_name'];
  $teams[$record['team2_id']] = $record['team2_name'];
}

// Now output the grid as a table
$html = '
	<table border="1">
		<tr><th>&nbsp;</th>';
foreach($teams as $col=>$teamcolname)
{
	$html .= "<th>$teamcolname</th>";
}
$html .= '</tr>';
foreach($teams as $row=>$teamrowname)
{
	$html .= "
		<tr><th>$teamrowname</th>";
	foreach($teams as $col=>$teamcolname)
	{
		if($col == $row)
		{
			$html .= '
			<td style="width: 4em; text-align: center;">X</td>';
		}
		else
		{
			$html .= '
			<td style="width: 4em; text-align: center;">'.(isset($resultsgrid[$row][$col])?$resultsgrid[$row][$col]:'-').'</td>'; // If there is no result for this position, make a '-' instead.
		}
	}
	$html .= '
		</tr>';
}
$html .= '
	</table>';
echo $html;

Open in new window

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33529232
Hi,
Just gave your code a try. And I would just like to say thats amazing it works.
I have been stuck on doing this for ages.

Thank you so much. I am not really good at programming I am just a designer, I hate it when I get jobs that involve programming. I try to muddle along, a lot of times with not much success.

Thank you for all your help.
0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33529327
No problem.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33584821
Hi,
how can I have the date of that match in the position where '-' is

$html .= '
                  <td style="width: 4em; text-align: center; background-color:#124f16; color:#ffffff; font-weight:bold; height: 30px; padding: 5px;">'.(isset($resultsgrid[$row][$col])?$resultsgrid[$row][$col]:'-').'</td>'; // If there is no result for this position, make a '-' instead.
0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33584950
There is no match. That is why is shows a "-".
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33584990
Could I replace it with the date?
As the date will still be inputted in (future date)
0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33587604
There is no match record to fill the cell that has a "-", therefore you cannot put a date in it.

The problem is that you have designed your database to fulfill one task earlier on, and now it makes it hard to complete the overall picture.
I personally like to start a database project by drawing a big picture of all the data I want to store, divide it into tables, and then draw links between the tables. This way you can spot design difficulties at the start.
Don't panic though, I am not suggesting you start again, but just revisit the table design with the benefit of hindsight.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question