• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

football/soccer website need help and advice php/mysql

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
cataleptic_state
Asked:
cataleptic_state
  • 10
  • 8
2 Solutions
 
McNeticCommented:
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
 
cataleptic_stateAuthor Commented:
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
 
McNeticCommented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
cataleptic_stateAuthor Commented:
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
 
McNeticCommented:
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
 
cataleptic_stateAuthor Commented:
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
 
cataleptic_stateAuthor Commented:
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
 
cataleptic_stateAuthor Commented:
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
 
cataleptic_stateAuthor Commented:
Been looking at your statement, and trying to understand it, can you explain what its doing exactly?
0
 
McNeticCommented:
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
 
cataleptic_stateAuthor Commented:
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
 
McNeticCommented:
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
 
McNeticCommented:
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
 
cataleptic_stateAuthor Commented:
Great Thanks
So I can do the same for the player stats too?
0
 
cataleptic_stateAuthor Commented:
Brilliant works very well
0
 
McNeticCommented:
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
 
cataleptic_stateAuthor Commented:
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
 
McNeticCommented:
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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