Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

football/soccer match report problem php/mysql

match report
Hi,
The image above shows a football match report card, what it needs to do is say who played who (which is done)

But I need to know which player scored how many goals, for their team.

I tried creating a loop within a loop but dreamweaver does not allow that, is there anyway I can do it?

Also iit will list which player got a red card/yellow card and who was the man of the match for each team

The SQL statements for the page:

 
$matchID = mysql_real_escape_string($_REQUEST['id']);
mysql_select_db($database_db, $db);
$query_history = "SELECT *, date_format(matches.date, '%W %D %M %Y') as mDate FROM matches LEFT JOIN matchscores ON matches.match_id = matchscores.match_id LEFT JOIN team ON team.team_id = matchscores.team LEFT JOIN referee ON referee.ref_id = matches.referee_id LEFT JOIN venue ON venue.venue_id = matches.venue_id WHERE matches.match_id = $matchID";
$history = mysql_query($query_history, $db) or die(mysql_error());
$row_history = mysql_fetch_assoc($history);
$totalRows_history = mysql_num_rows($history);

mysql_select_db($database_db, $db);
$query_enable_login = "SELECT disable_login FROM season";
$enable_login = mysql_query($query_enable_login, $db) or die(mysql_error());
$row_enable_login = mysql_fetch_assoc($enable_login);
$totalRows_enable_login = mysql_num_rows($enable_login);

mysql_select_db($database_db, $db);
$query_match_fixtures = "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 AND m.match_id = $matchID
group by match_id 
order by m.match_id";
$match_fixtures = mysql_query($query_match_fixtures, $db) or die(mysql_error());
$row_match_fixtures = mysql_fetch_assoc($match_fixtures);
$totalRows_match_fixtures = mysql_num_rows($match_fixtures);

mysql_select_db($database_db, $db);
$query_match_player = "SELECT * FROM match_player LEFT JOIN player on player.player_id = match_player.player_id LEFT JOIN team ON player.team_id = team.team_id WHERE match_id = $matchID AND team.team_name = '$row_match_fixtures[team1_name]'";
$match_player = mysql_query($query_match_player, $db) or die(mysql_error());
$row_match_player = mysql_fetch_assoc($match_player);
$totalRows_match_player = mysql_num_rows($match_player);

Open in new window

0
cataleptic_state
Asked:
cataleptic_state
  • 16
  • 10
1 Solution
 
Chris HarteThaumaturgeCommented:
Remove your existing mysql_fetch_assoc statements, they do need to be looped.
This will populate your existing variable as an array of the returned results


    
    for ($i = 0; $i < $totalRows_history; $i++)
    {
        $row_history[$i] = mysql_fetch_assoc($history);
    }

    for ($i = 0; $i < $totalRows_enable_login; $i++) 
    {
    	$row_enable_login[$i] = mysql_fetch_array($enable_login);
    }
    
    for ($i = 0; $i < $totalRows_match_fixtures; $i++) 
    {
    	$row_match_fixtures[$i] = mysql_fetch_array($match_fixtures);
    }
    
    for ($i = 0; $i < $totalRows_match_player; $i++) 
    {
    	$row_match_player[$i] = mysql_fetch_array($match_player);
    }

Open in new window

0
 
cataleptic_stateAuthor Commented:
where would I put this?
0
 
Chris HarteThaumaturgeCommented:
Anywhere after the code that you posted
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
cataleptic_stateAuthor Commented:
And that will loop the info I want to loop or all of the information?

At the moment its not doing anything.
0
 
cataleptic_stateAuthor Commented:
"Remove your existing mysql_fetch_assoc statements" which ones are these?

$row_history = mysql_fetch_assoc($history);??
0
 
Chris HarteThaumaturgeCommented:
In the code you provided remove these lines.

$row_history = mysql_fetch_assoc($history);
$row_enable_login = mysql_fetch_assoc($enable_login);
$row_match_fixtures = mysql_fetch_assoc($match_fixtures);
$row_match_player = mysql_fetch_assoc($match_player);

What is it you are expecting it to do?
The code you provided would just get the last data fetched from the database and put it in a variable. All the information is now stored in the same variable as an array. To access the data you have to address it as an array. eg


echo "the first entry in the array is $row_history[0]";


I need to know what you are trying to do with the data before I can help you further.


0
 
cataleptic_stateAuthor Commented:
Hi,
If you look at the picture attached in the first post you will see a "Goalscorer" row with the player who scored goals in this match and the number of goals he's scored in brackets.

I want to be able to show more than one player who has scored goals for his team, had a yellow card, or red card.

Under the "home" column the Team name is displayed for team1.
Away column shows "away" for team2

If player 1 is from team 1 he should be put under teams1's column and the same for team 2 and its players

The following can have more than one player per match per team:
Goalscorer
Yellow Card
Red Card


I hope that helps?
0
 
Chris HarteThaumaturgeCommented:
Not really, I need your source code to see how you are handling the variables. The complex bit is getting the data from the database which you appear have achieved. Now it just has to be looped through and output to your html.
0
 
cataleptic_stateAuthor Commented:
Hi,
I have included the page and tables
match-report.php
dbase-dump.txt
0
 
Chris HarteThaumaturgeCommented:
Not as straight forward as you would have me believe.
When getting data from the database, write it to an array using the for loop.
To display the data, use a foreach loop. In this case one for the matches and a nested loop for the players.

I have partially solved your problem in the attached source code. In the table header (I have highlighted it with a comment) you are trying to mix the output from two arrays, I can not think of an obvious way round this other than to have a second sub heading with the $row_history. Or getting the data into $row_match_fixtures with a join.
match-report-2.php
0
 
cataleptic_stateAuthor Commented:
hi i will have a look at this, I do not need the headings repeating just the info in the white area
0
 
cataleptic_stateAuthor Commented:
Warning: Invalid argument supplied for foreach()

The line that is causing this is:
foreach ($row_match_player as $player_display)
0
 
Chris HarteThaumaturgeCommented:
The query

$query_match_player = "SELECT * FROM match_player LEFT JOIN player on player.player_id = match_player.player_id LEFT JOIN team ON player.team_id = team.team_id WHERE match_id = $matchID AND team.team_name = '$row_match_fixtures[team1_name]'";

does not work because you are trying to use the variable array $row_match in it. I will have a think about this but you may have to redo your queries.
0
 
cataleptic_stateAuthor Commented:
Thank you :)
0
 
Chris HarteThaumaturgeCommented:
Try this. If it does not work let me know. I have decided that I will not let this problem beat me (bearing in mind I normally charge shit loads of money for this sort of effort).
match-report-3.php
0
 
cataleptic_stateAuthor Commented:
Warning: Invalid argument supplied for foreach()

If you can get it working I can paypal you some money or cash?
0
 
Chris HarteThaumaturgeCommented:
no money exchanges on this site, I will get it right just because I can.

Just in case you are not aware, the sql queries you are using do not match the sql dump you sent me. eg there are no tables for venue or referee. I am working around this for now but I am getting there.
0
 
cataleptic_stateAuthor Commented:
Hi,
here are the updated tables

Thanks again, I really appreciate your help
CREATE TABLE IF NOT EXISTS `matches` (
  `match_id` int(8) NOT NULL auto_increment,
  `date` date default NULL,
  `time` varchar(5) default NULL,
  `report` longtext,
  `referee_id` int(8) NOT NULL,
  `season_id` int(8) NOT NULL,
  `venue_id` int(8) NOT NULL,
  PRIMARY KEY  (`match_id`),
  KEY `referee_id` (`referee_id`),
  KEY `venue_id` (`venue_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;


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=39 ;


CREATE TABLE IF NOT EXISTS `match_player` (
  `match_player` int(8) NOT NULL auto_increment,
  `player_id` int(8) NOT NULL,
  `Goals` int(8) default '0',
  `YC` int(8) default '0',
  `RC` int(8) default '0',
  `MOM` mediumint(8) default '0',
  `match_id` int(8) NOT NULL,
  PRIMARY KEY  (`match_player`),
  KEY `player_id` (`player_id`,`match_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

CREATE TABLE IF NOT EXISTS `player` (
  `player_id` int(8) NOT NULL auto_increment,
  `fname` varchar(100) NOT NULL,
  `sname` varchar(100) NOT NULL,
  `gender` varchar(10) default 'Male',
  `nationality` varchar(50) default NULL,
  `email` varchar(255) default NULL,
  `dob` date default NULL,
  `place_birth` varchar(255) default NULL,
  `height` varchar(20) default NULL,
  `weight` varchar(10) default NULL,
  `photo` varchar(255) default NULL,
  `comments` text,
  `position` varchar(255) default NULL,
  `team_id` int(8) NOT NULL,
  PRIMARY KEY  (`player_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

CREATE TABLE IF NOT EXISTS `team` (
  `team_id` int(8) NOT NULL auto_increment,
  `team_name` varchar(255) NOT NULL,
  `team_address` text,
  `team_phone` varchar(25) default NULL,
  `team_email` varchar(255) default NULL,
  `team_web` varchar(255) default NULL,
  `colours_home` varchar(50) default NULL,
  `colours_away` varchar(50) default NULL,
  `manager` varchar(255) default NULL,
  `assistant` varchar(255) default NULL,
  `club_rep` varchar(255) default NULL,
  `division` varchar(255) default NULL,
  `team_pic` varchar(255) default NULL,
  `team_logo` varchar(255) default NULL,
  `profile` mediumtext,
  `honours` mediumtext,
  `password` varchar(10) NOT NULL,
  PRIMARY KEY  (`team_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

Open in new window

0
 
Chris HarteThaumaturgeCommented:
The sql for match report now works.
The tables you just provided did not contain the other sql, eg venues, news and referees. So I have left them out, leaving you with empty columns.


match-report-4.php
0
 
cataleptic_stateAuthor Commented:
Hi,
Omg that is amazing you have done it :)
I will have to rearrange and add other fields as the left side headings are coming up twice.

So do I do this again for the next team? For the "Away" column?
Heres the output


Untitled-1.jpg
0
 
Chris HarteThaumaturgeCommented:
You will need another query to get the data, which means another question. I have answered this one.


Also I am not getting the results twice, so check the data on your database.
0
 
cataleptic_stateAuthor Commented:
I'll check it. If you want another question check this
http://www.experts-exchange.com/Web_Development/Software/Macromedia_Dreamweaver/Q_26446816.html

I will accept your solution for this one as it works.
0
 
cataleptic_stateAuthor Commented:
the page says no database selected when I go to it, why is that?
0
 
cataleptic_stateAuthor Commented:
any help?
0
 
cataleptic_stateAuthor Commented:
Munterman I am getting this error:

        <?php foreach ($row_match_player as $player_array)
        {
            foreach ($player_array as $player_display)
            {
        ?>      
0
 
cataleptic_stateAuthor Commented:
Your code does not work
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 16
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now