Solved

football/soccer website need help and advice php/mysql

Posted on 2010-08-16
18
425 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
  • 10
  • 8
18 Comments
 
LVL 8

Expert Comment

by:McNetic
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
 
LVL 4

Author Comment

by:cataleptic_state
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Been looking at your statement, and trying to understand it, can you explain what its doing exactly?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 8

Expert Comment

by:McNetic
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Great Thanks
So I can do the same for the player stats too?
0
 
LVL 4

Author Closing Comment

by:cataleptic_state
Comment Utility
Brilliant works very well
0
 
LVL 8

Expert Comment

by:McNetic
Comment Utility
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
Comment Utility
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
Comment Utility
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

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

763 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

12 Experts available now in Live!

Get 1:1 Help Now