Solved

football/soccer match report problem php/mysql

Posted on 2010-09-01
26
458 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

707 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

14 Experts available now in Live!

Get 1:1 Help Now