Solved

football/soccer match report problem php/mysql

Posted on 2010-09-01
26
460 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
  • 16
  • 10
26 Comments
 
LVL 16

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 16

Expert Comment

by:Chris Harte
ID: 33578915
Anywhere after the code that you posted
0
 
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 16

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 16

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 16

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 16

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
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: 33612291
Thank you :)
0
 
LVL 16

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 16

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 16

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 16

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Creating and Managing Databases with phpMyAdmin in cPanel.
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 count occurrences of each item in an array.

863 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

19 Experts available now in Live!

Get 1:1 Help Now