PHP/MySQL Table Question.

[Durbs]

Ok guys this is what i'm trying to do...:

I'm creating a fixtures/league table system for a football team site. I want it to be as dynamic as possible so the plan is to have a list of fixtures which are set at the start of the season. As the games are played the results are updated in the fixtures table which looks like this:

Fixture_ID | Season_ID | League_ID | Hometeam_ID | Home_Score | Awayteam_ID | Away_Gls | Date

At the moment this information only used to create a fixture/result list but I would also like to use the information stored here to calculate the league standings when called upon, any ideas? (I also have season, league, team tables)

At the moment i have a back-up plan where when the fixtures are updated the function also updates an actual league table stored on the database. I'm sure this will work fine but it seems stupid to me to store preety much the same information twice when it's possible to calculate it from one table. What do you think? I can't quite get my head around how to do it, as it's not so much of a problem getting the data from the database using 'SELECT WHERE & AS etc' to make the calculations in PHP, but then you've got to sort it into a table and you won't be able to use 'ORDER BY'

Any help would great

Durbs

PS. What happened to the SQL forum on here??

[Shmohel]

Ok, do you have any code with which you are starting from? Are you looking to create a table that can be sortable by clicking on the column headers? Are you looking to output statistics from your database? What does your database enviroment look like currently? Is it all in one table, multiple tables, etc...

[Durbs]

I haven't started coding yet, just thinking around the idea. Just to clarify what i want to do I have a table with the league fixtures in, with a typical entry having a team_id for each team in the fixture, and the goals they scored. From this you can work out whether it was a home win, draw or away win. You then need to use this information to work out the league table which will be sorted by points (3 for a win, 1 for a draw and 0 for a lose) and the Goal Difference ( scored goals - goals conceded). To work this all out surely you'd have to start with one team, process all of it's matchs and find out it's league stats, then do the same for the next and so on. Once you've done this for all teams you would then have to determine the order and create a table to that effect. This would have to be done in PHP but i'm not quite sure how, possibly count the teams in the league, create varables to hold the information (team number ones would look something like this: Pld1, W1, D1, L1, GF1, GA1, GD1, Pts1 - then process each fixture relevent to the team adding to the relevent variables. Team 2 would have Pld2, W2, D2, L2, GF2, GA2, GD2, Pts2.....and so on) then once completed for all teams determine the order in which to display them in a table...

At the moment I'm leaning towards my other idea of having a league_stats table which would hold the calculatons and is updated everytime results are entered, this way a SQL SELECT could sort and retreive the information. The only problem with this method is that you only have an up-to-date version of the league table and cannot look into the pass (i.e. specify league table up until a certain date). Do you see what i mean??

I'm just thinking out the idea at the moment before diving straight in and any thoughts would, as always, be great.

Durbs

[Shmohel]

You could keep your stats table as a history if you wanted. Just add a field that holds the date. And, then when you do you calculations for league_stats only recall the most recent historical item. This way you do not lose any historical information if you wanted it.

[Welshsteve]

Hi everyone. I need some help with something like this as well.

I run a snooker league website which has some MySQL backend for league tables, player stats etc.

At the moment, I manually update the league tables but would like to do this automatically using an online form.

I currently have a "Submit Result" section on the website which emails me the match details. What I would like is as well as emailing me the details, I'd like it to update the league table as well.

If I pass a php script the following four variables from the online form, I then need to use them in a SQL statement

$teamhome
$teamhomescore
$teamaway
$teamawayscore

The LeagueTable database table has the fields:

Team = team name
Pld = games played
W = games won
L = games lost
Pts = points

One SQL statement would need to be run to update games played. Something like this:

UPDATE LeagueTable SET Pld = Pld + 1 WHERE Team IN ('$teamhome', '$teamaway')

Not sure about the syntax of that.

I'm then on to the games won.

Depending on which team wins. Some code is needed to determine the winning team.

if $teamhomescore > $teamawayscore then 
$sql = "UPDATE LeagueTable SET W = W + 1 WHERE Team IN ('$teamhome')";
else
$sql = "UPDATE LeagueTable SET W = W + 1 WHERE Team IN ('$teamaway')";
endif 

again, I'm unsure on the syntax of that.

The L field is just Pld - W, so not sure if I can just set the field in MySQL to be a calculated field.

Finally there's the points. Teams get point for the number of frames won, so the pts update would need to be SQLs like below.

UPDATE LeagueTable SET Pts = Pts + $teamhomescore WHERE Team = $teamhome

UPDATE LeagueTable SET Pts = Pts + $teamawayscore WHERE Team = $teamaway


If anybody is able to point me in the right direction here I'd greatly appreciate it. I'm still fairly new to working with php so need all the help and advice I can get.

[Welshsteve]

I have solved my issue now. If anyone wants to see the code, I can post it here. Just let me know.

[Welshsteve]

Had a request to post the answer here, so here it is. This is a stripped down version of my solution, and uses the theory of 1 league point for each point scored in a match. e.g. if the result is 4-3, the home team gets 4 points onto their "For" total.

inputresult.php

<h1>Insert result to update table</h1>
<form method="post" action="update.php">
<table style="border:0;">
<tr><th>Home Team</th><th>Score</th><th>&nbsp;</th><th>Score</th><th>Away Team</th></tr>
<tr>
	<td>
	<select name="team1" id="team1">
		<option value="Team01">Team 1</option>
		<option value="Team02">Team 2</option>
		<option value="Team03">Team 3</option>
		<option value="Team04">Team 4</option>
	</select>
	</td>
	<td><input type="text" class="fieldtxtsml" name="team1score" id="team1score" /></td>
	<td>-</td>
	<td><input type="text" class="fieldtxtsml" name="team2score" id="team2score" /></td>
	<td>Pick away team: <select name="team2" id="team2">
		<option value="Team01">Team 1</option>
		<option value="Team02">Team 2</option>
		<option value="Team03">Team 3</option>
		<option value="Team04">Team 4</option>
	</select>
	</td>
</tr>
</table>
<input type="hidden" id="div" name="div" value="Div1" />
<input type="submit" name="btnSubmit1" id="btnSubmit1" class="btn" value="Update Table" />
</form>

update.php

<?php
$team1 = $_POST['team1'];
$team2 = $_POST['team2'];
$team1score = (int) $_POST['team1score'];
$team2score = (int) $_POST['team2score'];
$div = $_POST['div'];

// Replace values in capitals with relevant information
$con = mysql_connect("SERVER ADDRESS","USERNAME","PASSWORD");
if (!$con)
  {
  die('Could not connect: ' . mysql_error() . 'database connection problem');
  }
mysql_select_db("DATABASE NAME", $con);

//UPDATE TABLE
if($team1score > $team2score)  
{ 
  $sqlteam1 = "UPDATE ".$div." SET Pld=Pld+1,W=W+1,F=F+".$team1score." WHERE Team='".$team1."'"; 
  $resteam1 = mysql_query($sqlteam1)
     or die( "$sqlteam1: " .mysql_error( ) ); 
  $sqlteam2 = "UPDATE ".$div." SET Pld=Pld+1,F=F+".$team2score." WHERE Team='".$team2."'"; 
  $resteam2 = mysql_query($sqlteam2)
     or die( "$sqlteam2: " .mysql_error( ) ); 
} 
elseif($team1score < $team2score) 
{ 
  $sqlteam1 = "UPDATE ".$div." SET Pld=Pld+1,W=W+1,F=F+".$team2score." WHERE Team='".$team2."'"; 
  $resteam1 = mysql_query($sqlteam1)
     or die( "$sqlteam1: " .mysql_error( ) ); 
  $sqlteam2 = "UPDATE ".$div." SET Pld=Pld+1,F=F+".$team1score." WHERE Team='".$team1."'"; 
  $resteam2 = mysql_query($sqlteam2)
     or die( "$sqlteam2: " .mysql_error( ) ); 
}  
echo "Table updated";
?>

This is not an ideal solution, but it works.