football/soccer results grid advice php/mysql

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.

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.

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 ;

Select allOpen in new window

is there anyway I can get this done?

So is each cell a single match or the total number of goals against that team? Currently you have 2 cells per each pairing.

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

So we have our output from this query (from the other question: https://www.experts-exchange.com/questions/26417023/auto-generate-team-matches.html?cid=748&anchorAnswerId=33501495#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>';

Select allOpen in new window

Ok,
I will check that, i just realised how will I make the page to input that information?

ignore my last comment.

I am going crazy with this thing.

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

Select allOpen in new window

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));

Select allOpen in new window

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());

Select allOpen in new window

Hi,
theres no errors

Please post the output code.

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>';
?>

Select allOpen in new window

You have put a ";" at the end of line 8 that shouldn't be there.

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

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.

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>';
?>

Select allOpen in new window

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>';

Select allOpen in new window

put "echo $html;" at the end.

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?

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.

No problem.

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.

There is no match. That is why is shows a "-".

Could I replace it with the date?
As the date will still be inputted in (future date)

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.