Solved

football/soccer results grid advice php/mysql

Posted on 2010-08-17
29
906 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 4

Author Comment

by:cataleptic_state
Comment Utility
is there anyway I can get this done?
0
 
LVL 10

Expert Comment

by:ollyatstithians
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ignore my last comment.

I am going crazy with this thing.
0
 
LVL 4

Author Comment

by:cataleptic_state
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi,
theres no errors
0
 
LVL 10

Expert Comment

by:ollyatstithians
Comment Utility
Please post the output code.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 4

Author Comment

by:cataleptic_state
Comment Utility
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
Comment Utility
You have put a ";" at the end of line 8 that shouldn't be there.
0
 
LVL 4

Author Comment

by:cataleptic_state
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
put "echo $html;" at the end.
0
 
LVL 4

Author Comment

by:cataleptic_state
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
No problem.
0
 
LVL 4

Author Comment

by:cataleptic_state
Comment Utility
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
Comment Utility
There is no match. That is why is shows a "-".
0
 
LVL 4

Author Comment

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

Expert Comment

by:ollyatstithians
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now