football/soccer website need help and advice php/mysql

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.

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

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?

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

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);

Select allOpen in new window

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?

Been looking at your statement, and trying to understand it, can you explain what its doing exactly?

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.

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

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

Select allOpen in new window

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

Select allOpen in new window

Great Thanks
So I can do the same for the player stats too?

Brilliant works very well

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.

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.

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.