Page 1 of 1

Getting data from 2 tables depending on user?!?!

Posted: Wed Nov 28, 2007 3:01 pm
by jmansa
I'm trying to make an leaderboard for my golfclub, and each member is signing up with alot of data wich is going in several different tables. I have now made a script where I get the avarage point from each player and then get a list/leaderboard with the players name and his avarage points like this:

Code: Select all

echo '<table cellpadding="0" cellspacing="0">';
 $result = mysql_query('SELECT AVG(oak_listen.Point) AS Sum, Navn FROM oak_listen GROUP BY SpillerID ORDER BY Sum DESC');
 
 $i = 1;
 while ($row = mysql_fetch_assoc($result)) {  
 
 echo '<tr>';
 echo '<td width="5%" align="left" valign="middle">' . $i . '</td>';
 echo '<td align="left" valign="middle">' . $row['Navn'] . '</td>';
 echo '<td align="left" valign="middle">' . number_format($row['Sum'], 3) . '</td>';
 echo '</tr>';
 
 $i++;
 }
 
 echo '</table>';
Now I want to add some date (region) into the leaderboard for each player but the data comes from another table (oak_region). The "SpillerID" is unique for each player, and are offcourse in both tables... Can anyone help...

Posted: Wed Nov 28, 2007 3:45 pm
by califdon
It's hard to say for sure without knowing exactly what your whole database is about, but my immediate reaction is that your data should not be separated into different tables by location. Unless there are some other reasons that aren't apparent to me, that's simply a bad table design. A database schema should be based on defining the entities about which you are going to store data. A database is a model of some portion of the real world. Decisions as to how to combine or separate data into tables are much more than a casual "looks right to me" opinion. They should be made according to rather strict rules laid out in Relational Database theory that has been followed since about 1960.

My first impression is that your scores at different golf courses are all the same entity, namely, a SCORE. Each score was made on a certain date and on a certain course, both of which are properties of a score, just as the name of the player. If that is correct, then you should add a Course field to each table, populate it with the name of that course, then append all the records into one table.

With your schema correct, you should have no problem with your queries.

Posted: Wed Nov 28, 2007 3:56 pm
by jmansa
I see what you are saying, but let me give you an example.

When a player creates his profile his is doing it with this data:

Table: profile
playerid = autoincrement
name = entered by player
Adress = entered by player
city = entered by player
Now when a player is updating his scores he is doing so by entering a form where he can find his name with a dropdownlist and the name has the playerid attached as data. He then inserts his score in another table called "scores":
playerid = inserted automatic by the form depending on player name
points = entered by player
Now, what I want now is to make a script wich shows all the players avarage score and sorts them with the best avarage at top and worst at the bottom. But to be able to both show the score and the name I have to get data from 2 different tables depending on the playerid...

I hope this claryfies some of it!