Solved

football/soccer match report problem php/mysql

Posted on 2010-09-01
26
487 Views
Last Modified: 2013-12-13
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
Comment
Question by:cataleptic_state
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 10
26 Comments
 
LVL 17

Expert Comment

by:Chris Harte
ID: 33575442
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33577525
where would I put this?
0
 
LVL 17

Expert Comment

by:Chris Harte
ID: 33578915
Anywhere after the code that you posted
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 4

Author Comment

by:cataleptic_state
ID: 33584692
And that will loop the info I want to loop or all of the information?

At the moment its not doing anything.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33584702
"Remove your existing mysql_fetch_assoc statements" which ones are these?

$row_history = mysql_fetch_assoc($history);??
0
 
LVL 17

Expert Comment

by:Chris Harte
ID: 33585436
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33585849
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
 
LVL 17

Expert Comment

by:Chris Harte
ID: 33588047
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33588255
Hi,
I have included the page and tables
match-report.php
dbase-dump.txt
0
 
LVL 17

Expert Comment

by:Chris Harte
ID: 33596144
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33609858
hi i will have a look at this, I do not need the headings repeating just the info in the white area
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33610014
Warning: Invalid argument supplied for foreach()

The line that is causing this is:
foreach ($row_match_player as $player_display)
0
 
LVL 17

Expert Comment

by:Chris Harte
ID: 33611897
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33612291
Thank you :)
0
 
LVL 17

Expert Comment

by:Chris Harte
ID: 33617830
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33618547
Warning: Invalid argument supplied for foreach()

If you can get it working I can paypal you some money or cash?
0
 
LVL 17

Expert Comment

by:Chris Harte
ID: 33618733
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33619209
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
 
LVL 17

Expert Comment

by:Chris Harte
ID: 33626093
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33634630
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
 
LVL 17

Accepted Solution

by:
Chris Harte earned 500 total points
ID: 33635638
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33635688
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33636873
the page says no database selected when I go to it, why is that?
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33716363
any help?
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33797024
Munterman I am getting this error:

        <?php foreach ($row_match_player as $player_array)
        {
            foreach ($player_array as $player_display)
            {
        ?>      
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33882904
Your code does not work
0

Featured Post

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.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

622 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