I need to have the data from two queries available in one line of HTML.

I have the table: PH_SCORES that has the matching PLAYERID from PH_PLAYER in one of two places: either FIRST_ASSIST or SECOND_ASSIST for every game they have played.

Since I have not been successfull in generating one query to show the total of the first as well as the total of the second assists for each player, I am now trying to do it in two queries.

But, now my questions is how will I get and show the result from the FIRST_ASSIST query and the SECOND_ASSIST query for each player in PH_PLAYER?

__________________
Mitch

What happens when you try this query?

SELECT P.playerid, s.first_assist, s.second_assist
  FROM PH_PLAYER p, PH_SCORES s
 WHERE P.player_id = s.first_assist OR p.player_id = s.second_assist
 ORDER BY p.player_id


Take care,

Nik
http://www.bigaction.org/

nikolai, when I run your code (after changing it) it gives me a list of all the individual records but does not give TOTALS for the 1stAssist as well as the 2ndAssist.

Also, I modified your code with the correct table and field names (my origninal post was from memory) and if I left the "p" or "s" in, then I got an error about those fields not found, so I used the entire reference.

SELECT PH_PLAYERS.PLAYERID, PH_SCORE_DETAIL.1stAssist, PH_SCORE_DETAIL.2ndAssist
  FROM PH_PLAYERS, PH_SCORE_DETAIL
 WHERE PH_PLAYERS.PLAYERID = PH_SCORE_DETAIL.1stAssist OR PH_PLAYERS.PLAYERID = PH_SCORE_DETAIL.2ndAssist
 ORDER BY PH_PLAYERS.PLAYERID


So how do I total each? If I can do that then I won't need the two queries.

I did get this to partially work:

SELECT PH_PLAYERS.PLAYERID, Count(PH_SCORE_DETAIL.1stAssist), Count(PH_SCORE_DETAIL.2ndAssist)
  FROM PH_PLAYERS, PH_SCORE_DETAIL
 WHERE PH_PLAYERS.PLAYERID = PH_SCORE_DETAIL.1stAssist OR PH_PLAYERS.PLAYERID = PH_SCORE_DETAIL.2ndAssist
 GROUP BY PH_PLAYERS.PLAYERID


Except that the count for each column is exactly the same, which is wrong.

It looks like the one query is totaling both counts together.
Here is what the two seperate query shows:

SELECT PH_PLAYERS.PLAYERID, Count(PH_SCORE_DETAIL.1stAssist), Count(PH_SCORE_DETAIL.2ndAssist)
  FROM PH_PLAYERS, PH_SCORE_DETAIL
 WHERE PH_PLAYERS.PLAYERID = PH_SCORE_DETAIL.1stAssist OR PH_PLAYERS.PLAYERID = PH_SCORE_DETAIL.2ndAssist
 GROUP BY PH_PLAYERS.PLAYERID

+----------+----------------------------------+----------------------------------+
| PLAYERID | Count(PH_SCORE_DETAIL.1stAssist) | Count(PH_SCORE_DETAIL.2ndAssist) |
+----------+----------------------------------+----------------------------------+
| 1 | 5 | 5 |
| 2 | 5 | 5 |
| 3 | 15 | 15 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
| 6 | 13 | 13 |
| 7 | 9 | 8 |
| 8 | 9 | 9 |
| 9 | 6 | 6 |
| 10 | 8 | 8 |
| 11 | 9 | 9 |
| 12 | 10 | 10 |
| 13 | 13 | 13 |
| 14 | 20 | 20 |
| 15 | 3 | 3 |
| 16 | 8 | 8 |

=================

SELECT PH_PLAYERS.PLAYERID, Count(PH_SCORE_DETAIL.2ndAssist)
  FROM PH_PLAYERS, PH_SCORE_DETAIL
 WHERE PH_PLAYERS.PLAYERID = PH_SCORE_DETAIL.2ndAssist
 GROUP BY PH_PLAYERS.PLAYERID

+----------+----------------------------------+
| PLAYERID | Count(PH_SCORE_DETAIL.2ndAssist) |
+----------+----------------------------------+
| 1 | 3 |
| 3 | 7 |
| 4 | 1 |
| 5 | 1 |
| 6 | 3 |
| 8 | 3 |
| 10 | 1 |
| 11 | 4 |
| 12 | 3 |
| 13 | 4 |
| 14 | 3 |
| 15 | 1 |
| 16 | 4 |
| 39 | 1 |
| 42 | 1 |

================================================== =====
SELECT PH_PLAYERS.PLAYERID, Count(PH_SCORE_DETAIL.1stAssist)
  FROM PH_PLAYERS, PH_SCORE_DETAIL
 WHERE PH_PLAYERS.PLAYERID = PH_SCORE_DETAIL.1stAssist
 GROUP BY PH_PLAYERS.PLAYERID

+----------+----------------------------------+
| PLAYERID | Count(PH_SCORE_DETAIL.1stAssist) |
+----------+----------------------------------+
| 1 | 2 |
| 2 | 5 |
| 3 | 8 |
| 4 | 3 |
| 5 | 4 |
| 6 | 10 |
| 7 | 9 |
| 8 | 6 |
| 9 | 6 |
| 10 | 7 |
| 11 | 5 |
| 12 | 7 |
| 13 | 9 |
| 14 | 17 |
| 15 | 2 |
| 16 | 4 |
================================================== =====

Huh... Off the top of my head, I can't think of a way to total the two columns separately using one query. The reason your single query comes up with the same totals is because you're counting the number of rows where either the player is has a first or second assist.

Take this sample scores data for example:

   1st assist 2nd assist
       1 2
       1 3
       2 1

For player 1, you'll return all three rows with the "id = 1st OR id = 2nd" where clause. The count(col) counts the number of rows in which that column has a value, which is basically one per row. Therefore, in the above example your query would return:

   player id count(1st) count(2nd)
     1 3 3
     2 2 2
     3 1 1


For now, I'd recommend just getting things to work with two queries. It shouldn't be THAT much less efficient, especially if you've set up your tables correctly: that is, the PLAYERID column of your PH_PLAYERS table is a primary key, and the assist columns of the PH_SCORE_DETAIL table are indexed integer foriegn key columns.

This might also be a question to ask in the DB forums, as they tend to deal with more complicated SQL and might know a few tricks.


Take care,

Nik
http://www.bigaction.org/

Ya know,...that is why my original question was how to do it with two queries. ;)

While on that note...how do I?

Oh, okay. I got lost somewhere in the middle. I thought your question was originally "how do I do it with two queries", but then I saw you trying to do it with one. When the results of the single query were incorrect, you said "but I can get the correct results with two queries", so I thought you wanted to switch it to one.

I see now (do I?) that your question isn't about the queries at all: it's how to get the results of two different queries organized so that it's easy for PHP to generate one line of code.

Easy: arrays.

$result = mysql_query($first_assist_query);
if ($result !== FALSE)
{
    while ($row = mysql_fetch_array($result))
    {
        $result_array[$row['playerID']]['1st assist'] = $row['first_assist'];
    }
}

$result = mysql_query($second_assist_query);
if ($result !== FALSE)
{
    while ($row = mysql_fetch_array($result))
    {
        $result_array[$row['playerID']]['2nd assist'] = $row['second_assist'];
    }
}


Now, $result_array will be a two-level array. The first level index is the player ID. The second level indexes are "1st assist" and "2nd assist".

In my toy example above, that would result in an array structured like this:

$result_array = Array
(
    [1] => Array
    (
        [1st assist] => 2
        [2nd assist] => 1
    )
    [2] => Array
    (
        [1st assist] => 1
        [2nd assist] => 1
    )
    [3] => Array
    (
        [2nd assist] => 1
    )
)


Then, when you're creating each line of HTML, you can use that single combined-results array:

echo "<table>\n";
echo " <tr><th>Player ID</th><th>1st Assists</th><th>2nd Assists</th></tr>\n";
foreach($results_array as $player_id => $assist_array)
{
   echo " <tr>\n";
   echo " <td>" . (isset($assist_array['1st assist'])? $assist_array['1st assist'] : 0) . "</td>\n";
   echo " <td>" . (isset($assist_array['2nd assist'])? $assist_array['2nd assist'] : 0) . "</td>\n";
   echo " </tr>\n";
}
echo "</table>\n";



This scheme has only one problem: your query isn't guaranteed to return all the players. That's why we need the isset() when outputting the results -- because a player that has one kind of assist might not have another. Other players might not have any.

You should be able to rewrite your queries to return 0 counts for players that exist in the PH_PLAYER table but do not have any scoring assists.

Did I answer your question finally? =)


Take care,

Nik
http://www.bigaction.org/

Ya hoo!! :D

Yep that is exactly what I needed help with!

I will try that and see if it works. Now my players can quit hounding me about seeing their stats.