Query question for specific fields...

Mr_Mako

Hi.. I'm working on a site where I want to post the scores of specific people's games. I need to select only specific fields in certain pages and am not sure how to go about it. I've searched through the PHP manual and SQL querys with no luck.

Basically, this is what I have.

TABLE:

| ID# | plyr_name | score1 | score2 | score3 | notes |

| 001 | Billy Bobb | 000034 | 000023 | 000229 | blah |

| 002 | Tom Bobb | 000523 | 000234 | 000075 | blah |

| 003 | Mike Bobb | 000112 | 000053 | 000378 | blah |

| 004 | Billy Bobb | 000009 | 000011 | 000232 | blah |

Note that the ID# is the Primary Key and also auto increments.

What I have is a page where a player (take Billy Bobb) can see each of his individual tourneys (and only his, nobody else's), ordered by the tourney ID#. On THAT page, the problem I am having is that the "Notes" field is a link, and I need to somehow reference the link (which opens up a new page) to the Tourney ID#.

For example, if Billy wants to see his notes for Tourney #1 (in this case, Tourney ID# 001), he clicks on the "NOTES" link and his notes from ID 001 come up in that window.

What's happening to me now is that I get the notes, but it's always the last Tourney listed in the tourney list (so, with the above example, Billy clicks on "NOTES" for Tourney 001 and gets the notes for Tourney 004).

Secondly, I need to combine the scores on a second page... so that the players can see their totals for Score1, and Score2, etc., in a "Scoreboard" - like format. So, for example on this page Billy would see "Score1 = 43, Score2 = 34, and Score3 = 461". I'm having trouble figuring out how to add and combine the scores to get a total for each Score field together, and then display it.

Part of the second problem may be my table... I have the "Score" fields set as INT.

Can anyone shed some advice? Thanks in advance...

arelgee

To add the scores you would use the SUM aggregate function. I am just leaning Mysql myself but have experience with other dbs.
Hopefully mysql can sum a subquery.

If Notes is a field in one row and you are getting the field from another row, you have a problem in the SELECT clause.
I don't understand about the link, but anything called with that primary key should only return that row.
Hope that helps.
arelgee@are-l-gee.com aka The Penny Stock King

stolzyboy

mysql could probably do a sum on a subquery IF it supported subqueries, that isn't going to happen until version 4.2, which i believe is in the alpha stage of testing

arelgee

Wow. It looks like Oracle will stay in business for a few more years.
The reason I answered the original post was that I had a similar problem with my stocks db. I have 3 different sets of factors. At first, I thought of using a calculated field for the sum, but I thought it unlikely mysql had a calculated field, so I hard coded it.
To get the sum of the scores, you will have to run a SELECT query and add the variables later.
rlg

Mr_Mako

Well, here's my code for the pages in question.

Individual Tourney Page -

<?
// Describe Variables for Database //
$db_name = "DATABASE";
$table_name = "TOURNEYS";
$call = "$_POST[player]";

// Connect to Database //
$connection = mysql_connect("localhost", "USERNAME", "PASSWORD") or die(mysql_error());
$db = mysql_select_db($db_name, $connection) or die(mysql_error());

$sql = "SELECT * FROM $table_name WHERE plyr_name = '$call' ORDER BY id ASC";
$result = mysql_query($sql, $connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$player = $row['plyr_name'];
	$score1 = $row['score1'];
	$score2 = $row['score2'];
	$score3 = $row['score3'];

$display_block .= "
   <tr>
	<td width=\"9%\" align=\"left\">$player</td>
	<td width=\"9%\" align=\"center\">$score1</td>
	<td width=\"9%\" align=\"center\">$score2</td>
	<td width=\"9%\" align=\"center\">$score3</td>
	<td width=\"9%\" align=\"center\"><a href=javascript:notes.php>NOTES</a>
   </tr>";

}

$msg = "<p align=center><strong><font size=4>$call's Tourneys</font></strong></p>";

?>
<html>
<head>
<title>GAMES</title>
<script LANGUAGE="JavaScript"> 
<!-- Hide from JS-Impaired Browsers 
function report(){
window.open("notes.php","GAME_NOTES","toolbar=no,scrollbars=yes,width=600,height=400")
}
// End Hiding -->
</script>
</head>
<body>

<p align="center"><b><font size="5">
<?
// Display Message Header //
echo "$msg";
?>
</font></b></p>
<hr>
<p align="center">&nbsp;</p>
<div align="center">
  <center>
  <table border="2" cellpadding="0" cellspacing="1" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
    <tr>
      <td width="9%" align="center">Player</td>
      <td width="9%" align="center">Score for Game 1</td>
      <td width="9%" align="center">Score for Game 2</td>
      <td width="9%" align="center">Score for Game 3</td>
      <td width="9%" align="center">Click here for Game Notes</td>
    </tr>
    <tr>
      <td width="9%">&nbsp;</td>
      <td width="9%">&nbsp;</td>
      <td width="9%">&nbsp;</td>
      <td width="9%">&nbsp;</td>
      <td width="9%">&nbsp;</td>
    </tr>
    <?
    print $display_block;
    ?>
  </table>
  </center>
</div>
<p align="center">&nbsp;</p>

</body>
</html>

Notes page

<?
// Describe Variables for Database //
$db_name = "DATABASE";
$table_name = "TOURNEYS";
$call = "$_POST[plyr_name]";

// Connect to Database //
$connection = mysql_connect("localhost", "USERNAME", "PASSWORD") or die(mysql_error());
$db = mysql_select_db($db_name, $connection) or die(mysql_error());

$sql = "SELECT * FROM $table_name WHERE plyr_name = '$call' AND id = $id";
$result = mysql_query($sql, $connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$notes = $row['notes'];

$display_block = "
	<p align=left>GAME NOTES:</p>
	<p align=left>$notes</p>";

}

?>

<html>
<head>
<title>GAME NOTES</title>
</head>
<body>
<?
// Display Game Notes //
echo "$display_block";
?>
</body>
</html>

I guess I'm not sure where my problem lies in the SELECT statement to get the NOTES link to work properly.

And, to create the third page I mentioned above (the "Scoreboard") adding the total scores together isn't a difficult thing to do with variables and simple math functions, but how do I "tie" it to the Player in the code?

arelgee

As I said before, I know more about SQL in general than specifically mysql.
I cannot see the mistake in the code but the problem has to be in your database design. You say pages where I would think table fields.
I have it easier than you, since all my data revolves around a stock symbol. It is always the primary key in every table I write.
In the beginning of this thead, you drew a table that showed the ID as the primary key, I pasted part here:

| ID# | plyr_name | score1 | score2 | score3 | notes |

| 001 | Billy Bobb | 000034 | 000023 | 000229 | blah |

I know that (at least my version) of mysql does not have a foreign key constraint. But if you enforce the SAME ID on all your tables, you should not have a problem calling up a specific note.

$sql = "SELECT * FROM $table_name WHERE plyr_name = '$call' ORDER BY id ASC";

Why do you use plyr_name instead of ID? ID is the primary key and is guaranteed to be unique. If you have 2 Billy Bobbs, you are in trouble.
It would help to see a better explanation of the tables and the fields. If you are getting the last note added instead of the one you want, it has to be because you are not tying the ID to the right note. In other words, like a SELECT * where it is spitting out the last note entered.
rlg

After a little thought..........
I see now that the ID is the tourney. So you would have multilple Billy Bobbs. But to me that seems backwards. I would put the ID on the player and number them
Billy Bobb's tourney #1, #2, etc...

arelgee

<?
// Describe Variables for Database //
$db_name = "DATABASE";
$table_name = "TOURNEYS";
$call = "$_POST[plyr_name]";

$sql = "SELECT * FROM $table_name WHERE plyr_name = '$call' AND id = $id";

Where is the id coming from???
rlg

Mr_Mako

It might seem backwards... but the main thing I am tracking isn't the players themselves, it's the Tourneys played. Now, granted... perhaps I could have done the database a bit different, but since database design is a fairly new thing for me, I'm not sure how I would have done it differently.

The ID you mention was a variable that I was using to try and force the ID# of the Tourney so the right Note field would show. It wasn't working so I erased the variable definition from the code before pasting it here... I just forgot to erase that part in the SELECT statement.

I only have this one table I'm working from... there isn't any other tables in the DB.

arelgee

I wrote my first computer program in BASIC 20 years ago on an old Commodore Vic 20 with a whopping 5 Kilobytes of RAM.
I have learned one thing in that 20 years, if you have a flaw in the logic of a computer program, don't try to work around it, FIX IT!!!
Even if mysql supported the advanced features of Interbase or Oracle, what you want to do would be difficult. You are trying to link one field in one ROW of a database table to another field in the same ROW in the same table. The only way I see to do this is like this:

ID PLYNAM SCR1 SCR2 SCR3 NOTE1 NOTE2 NOTE3

The problem you have with adding the scores together is the same. Without a subquery, you will have to name the fields in the query.
arelgee