MySQL database problem (Players & Goalscorers)

[mhmhc]

Hi,

I currently have a mysql database consisting of three tables:

fixtures (
fixtureID, fixtureDate, ourTeam, opposition, home/away, resultUs, resultThem, points, league/cup/ matchReport
}

players {
playerID, playerNumber, playerName, gamesPlayed, goalsScored
}

playerFixtures {
fixtureID, playerID, goalsScored
}

I think the fixtures and players tables are self-explanitory, but what I would like to do is have a way of entering multiple players into the 'playerFixtures' table for each fixture. This, coupled with the amount of goals each player scored, should update the statistics in the 'players' table. I'm not sure if this is the right way to go about this.

Any help would be really appreciated.

[chazzy]

sounds more like you need to have multiple playerFixtures for each fixtures entry.

does that sound reasonable?

essentially, you would have a one to many relationship between fixtures and playerFixtures (and alternatively, also between players and playerFixtures)

[mhmhc]

thanks for helping.

I think I know what you mean. I realise that I need multiple entries in the playerFixtures table for each fixture, as obviously there will be multiple players for each fixture. Do you mean having one-to-many relationships between playerFixtures and players tables for the fields playerID and goalsScored?

I'm also having trouble creating one-to-many relationships in phpmyadmin. I can create one-to-one relationships in the designer view but not one-to-many

[chazzy]

i'm not sure what php my admin designer is, but the definition of a one to many relationship is essentially the same as a one to one. unless there's some code being generated for you, i think you're safe with however it made it, unless there's a PK being defined somewhere on this table (it should be a composite PK w/ both FK columns being included, as the pairing should be unique)

[mhmhc]

I'm thinking it might be more appropriate to have multiple fields for players and how many goals they scored in the playerFixtures table. Say player_1, goals_1, player_2, goals_2 etc. Do you think this would make queries for the database difficult?
I'm still lost on how to do it in one field. I thought it might be by storing the information in an array or something, but I think it is above my abilities.

when i was taking about the phpmyadmin designer I was referring to the 'drag-and-drop' application in phpmyadmin for creating relationships.

I'm not sure what you mean by FK? I gather PK is primary key. sorry im not used to the terminology

[chazzy]

FK = foreign key, it defines a relationship between 2 tables.

just as a general note, i'm not sure if you're considering being a developer fulltime or not, but it's hard to learn relational databases, and sql, using design tools.

as for your approach, no, I don't think that would work.

i'm not sure you're getting it - but your original design's not off from what i'm suggesting, you just need to work on your coding side (PHP?) in order to make it real.

[mhmhc]

ok thanks for the help.
I'll look into making the original design work

[mhmhc]

ok I've managed to enter multiple players for each fixture now, including how many goals each player scored. I realise that this was by making both fixtureID and playerID as 'dual PKs' in the playerFixture table. I'm now trying to make the gamesPlayed field increment in the players table every time a player is selected in playerFixtures, and goalsScored increases depending on the goalsScored in the playerFixtures table.
Any help is greatly appreciated on this part. You've been very helpful so far

[mhmhc]

I've managed to get the PHP on my webpage to count the games played and the goals scored for each player. The code selects a random player and then displays his/her stats.
<?php
// Connects to Database
mysql_connect("server", "user", "password") or die(mysql_error());
mysql_select_db("mhmhc") or die(mysql_error());

// Generates Random Number
$random = (rand()%80)+1;

$details = mysql_query("SELECT * FROM `players` WHERE PlayerID = '$random'")
or die(mysql_error());

$data = mysql_query("SELECT COUNT(PlayerID) FROM `playerfixtures` WHERE PlayerID = '$random'")
or die(mysql_error());

$goals = mysql_query("SELECT SUM(GoalsScored) FROM `playerfixtures` WHERE PlayerID = '$random'")
or die(mysql_error());

while($info = mysql_fetch_array( $details ))
{
Print "<tr>";
Print "<td class=\"details\">Name : ".$info['PlayerName'] . "</td></tr>";
Print "<tr>";
Print "<td class=\"details\">Number : ".$info['PlayerNumber'] . "</td></tr>";
}

while($info = mysql_fetch_array( $data ))
{
Print "<tr>";
Print "<td class=\"details\">Games Played : ".$info['COUNTPlayerID)'] . "</td></tr>";
}

while($info = mysql_fetch_array( $goals ))
{
Print "<tr>";
Print "<td class=\"details\">Goals Scored : ".$info['SUM(GoalsScored)'] . "</td></tr>";
}
?>

This appears to be working just fine in my webpage, however, I would really like to get the stats to update in the players table and pull from there, rather than carrying out queries on the playerFixtures table. I gather in the long run this would be beneficial because it would load easier? I'm just speculating really but I don't think I have achieved the best solution here

[chazzy]

well so far you haven't displayed any code that does the insertion into these other tables, so i can't really describe how to handle stats.

in general though, since stats are aggregated from the other tables, i'd say leave them as sql unless your tables aren't indexed properly.