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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jmansa
Forum Commoner
Posts: 81
Joined: Wed Aug 23, 2006 4:00 am

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

Post 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...
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
jmansa
Forum Commoner
Posts: 81
Joined: Wed Aug 23, 2006 4:00 am

Post 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!
Post Reply