?
Solved

football/soccer website need help and advice php/mysql

Posted on 2010-08-16
18
Medium Priority
?
438 Views
Last Modified: 2013-12-13
Hi,
I have started making a website for a football league.
The spec has changed since starting it and now I am stuck as it requires a lot more functionality then originally set out.

What I need to do is create a system where a season is defined, and matches are "setup" as fixtures.

The customer wants the fixtures to be automatically generated so each team will play another team twice. The dates and times and goals will be entered later.

Once the game as been played, the number of goals are entered into the site and the player who scored the goal will be entered too.

The player will have his stats updated to show how many goals he has scored, how many yellow cards, and red cards and "man of the match" he has in the current season.

I then need a results table of all the teams and all the points each team has gained in the season from the games played and the goals scored.

I have the most problems with the results table as I do not know how I will get this to calculate the score for each team.

1 goal scored = 1 point
Winning team = 3 points
if its a draw = 1 points
if its 0 - 0 = 0 points

I have attached a screen print of another site that has this working. results table
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
  • 10
  • 8
18 Comments
 
LVL 8

Expert Comment

by:McNetic
ID: 33444970
The whole list could be generated using a single sql statement, given that your database design is clear and normalized. That said, to help with the answer, we need the exact table structure you are using for your teams and matches, and all other data used for the columns in your example.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33445513
Hi,
I have added a dump of my mysql dbase. I think I have done it correctly, but as I said before the spec changed really heavily, so I have had to chop and change as I go along.
dbase-dump.txt
0
 
LVL 8

Accepted Solution

by:
McNetic earned 2000 total points
ID: 33452189
I'm afraid it is not possible using a single statement with your current db design, as the matches table is not fully normalized. To get a list of all matches of one team using JOIN, you have to do two queries, as the team_id appears two times in the matches table.
You could implement a junction table (for example matchscores) to hold match_id, team and score, so two entries (one for each team) in this table give you the result of one match.
Then, the following query can be used (this is tested). I think you can figure the rest:

SELECT 
  team.team_name, 
  COUNT(IF(ownscores.score > foreignscores.score, 1, NULL)) AS wins, 
  COUNT(IF(ownscores.score = foreignscores.score, 1, NULL)) AS draws, 
  COUNT(IF(ownscores.score < foreignscores.score, 1, NULL)) AS losses, 
  SUM(ownscores.score) + SUM(IF(ownscores.score > foreignscores.score, 3, IF(ownscores.score < foreignscores.score, 0, IF(ownscores.score = 0, 0, 1)))) AS points
FROM team 
  INNER JOIN (matches 
    INNER JOIN (matchscores AS ownscores 
      INNER JOIN (matchscores AS foreignscores 
        INNER JOIN team AS foreignteam ON foreignscores.team = foreignteam.team_id) 
      ON (ownscores.team <> foreignscores.team) AND (ownscores.match_id = foreignscores.match_id)) 
    ON matches.match_id = ownscores.match_id) 
  ON team.team_id = ownscores.team 
GROUP BY team.team_name 
ORDER BY points DESC;

Open in new window

0
Industry Leaders: 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!

 
LVL 4

Author Comment

by:cataleptic_state
ID: 33452528
Hi, that is way too advanced for me, how else can I create the 'matches' table so that I can do it in 1 query.

How else can I get this done?
0
 
LVL 8

Assisted Solution

by:McNetic
McNetic earned 2000 total points
ID: 33452605
There is no totally simple way to get this done with a single statement. The easiest solution if you are not very into sql is to do the computing in php.

But I think it's not really that complicated. I'm not quite sure what parts are too advanced for you. I'll try to explain better:

First, you need to have an additional table 'matchscores' like below. This table gets two entries for each match, one for each team with it's corresponding score. You then have to drop the columns team1, team2, score1, score2 from the matches table, as they are contained in the new table. If you need to know which team is the home team, you'll have to add a hometeam (team_id) column to the matches table.

If you did this, and added a few matches and matchscores to your tables, just execute the above SELECT statement to see the resulting table.

If you need more explanation about the SELECT query, and how it calculates the relevent values, just ask again.

CREATE TABLE matchscores (
  matchscores_id INT(8) NOT NULL AUTO_INCREMENT,
  match_id int(8),
  team int(8),
  score int(8)
  PRIMARY KEY (matchscores_id),
  KEY match_id (match_id),
  KEY team (team)
);

Open in new window

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33452993
So how would I insert the data in both in the same table for both teams?

As the client wants one page where all the fixtures are available and they input which team plays which team
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33453015
I have added the above and added the query as below, but in Dreamweaver the fields are not coming up, will this stil display the information?


mysql_select_db($database_db, $db);
$query_league_tables = "SELECT 
  team.team_name, 
  COUNT(IF(ownscores.score > foreignscores.score, 1, NULL)) AS wins, 
  COUNT(IF(ownscores.score = foreignscores.score, 1, NULL)) AS draws, 
  COUNT(IF(ownscores.score < foreignscores.score, 1, NULL)) AS losses, 
  SUM(ownscores.score) + SUM(IF(ownscores.score > foreignscores.score, 3, IF(ownscores.score < foreignscores.score, 0, IF(ownscores.score = 0, 0, 1)))) AS points
FROM team 
  INNER JOIN (matches 
    INNER JOIN (matchscores AS ownscores 
      INNER JOIN (matchscores AS foreignscores 
        INNER JOIN team AS foreignteam ON foreignscores.team = foreignteam.team_id) 
      ON (ownscores.team <> foreignscores.team) AND (ownscores.match_id = foreignscores.match_id)) 
    ON matches.match_id = ownscores.match_id) 
  ON team.team_id = ownscores.team 
GROUP BY team.team_name 
ORDER BY points DESC";
$league_tables = mysql_query($query_league_tables, $db) or die(mysql_error());
$row_league_tables = mysql_fetch_assoc($league_tables);
$totalRows_league_tables = mysql_num_rows($league_tables);

Open in new window

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33453031
amazing that works!
The wins, draws and losses works :)

How do I get a total count of games played using this sql statement and would I beable to calculate the points(pts) with it too?
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33453039
Been looking at your statement, and trying to understand it, can you explain what its doing exactly?
0
 
LVL 8

Expert Comment

by:McNetic
ID: 33453116
The statement already calculates the points correctly, if I'm not mistaken.
To explain: after the SELECT before FROM is one line for every value selected/calculated. The first is the simple team_name, then comes wins, draws, losses, finally points. You can of course add more as you like.

The calculation is done by so called aggregate functions, like SUM(), COUNT() etc. COUNT() for example counts all rows where the given value is not NULL. For the wins, I choose the value IF(ownscores.score > foreinscores.score, 1, NULL). This means, the value will be 1 if the own score is higher than the foreign score, or NULL otherwise, which will bei counted by the surrounding COUNT() function to the correct number of wins. Likewise for draws and losses.

The SUM() function, on the other hand, sums up all values from the selected rows. These functions can be combined (added, subtracted, etc.) , and with the above explained conditional construct, the points are calculated.

You can now use various calculations to get additional information, like total scores, count of matches, etc. The count of matches will for example be the number of different match_ids in the result set, so the row would bei COUNT(matches.match_id) AS number_matches.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33453213
Hi,
I understand now. Thank you, before I give you the points can you tell me how I can give no points to losses.

I think I discribed the points system wrong. I believe it to be

3 points for a win
1 point for a draw.
I am not sure about the 1 point per goal
0
 
LVL 8

Expert Comment

by:McNetic
ID: 33453339
Ok, I'll explain the points formula in detail so you can adapt for yourself. I've added it again and formatted a bit to be better understandable (hopefully).

The first line would be one point per goal (simply sum up all goals). Leave it out if unnecessary.
The second line sums up the win/draw/loss points in the following lines. The 3rd line says, if own score is higher than foreign score (->win), use 3 points from line 4, otherwise go on with line 5.
The 5th line says if own score lower than foreign score (->loss), use 0 points from line 6, otherwise go on with line 7 (it can only be a draw then).
The 7th line says: if the own score is 0 (remember, it was a draw, so it's a 0-0), use 0 points from line 8, otherwise, 1 point from line 9.

If it should be just the usual win 3, draw 1, loss 0 points rule of most major football leagues, it could be simplified to the formula in lines 14 to 21. Of course, all line breaks are optional.


SUM(ownscores.score) + 
SUM( 
  IF(ownscores.score > foreignscores.score, 
    3, 
    IF(ownscores.score < foreignscores.score, 
      0, 
      IF(ownscores.score = 0, 
        0, 
        1)
      )
    )
  ) AS points

Open in new window

0
 
LVL 8

Expert Comment

by:McNetic
ID: 33453347
Sorry, I forget to add the simplified formula, here it is:
  
SUM(
  IF(ownscores.score > foreignscores.score, 
    3,
    IF(ownscores.score = foreignscores.score, 
      1, 
      0
    )
  ) AS points

Open in new window

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33453488
Great Thanks
So I can do the same for the player stats too?
0
 
LVL 4

Author Closing Comment

by:cataleptic_state
ID: 33453501
Brilliant works very well
0
 
LVL 8

Expert Comment

by:McNetic
ID: 33453578
In principle, yes. It will work similar for the player stats, although I did not look at the table structures for these. You'll first have to JOIN the relevant tables like in the league table, then construct the formulas for the values you want to list.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33796860
Hi,
I still need your help, the problem is that I cannot seem to display which team is which

if team 1 vs team 2
and there is another instance of team 1 vs team 2

it will display it as team 1 vs team 2

not team 2 vs team 1

and the points dont seem to be working properly.
0
 
LVL 8

Expert Comment

by:McNetic
ID: 33805439
Hi, unfortunately, I don't have time to comment in detail at the moment. I mentioned above, that you'll need an additional hometeam column for this purpose, but if you need more help, I suggest opening an new question.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

743 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