Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
|
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 1st, 2003, 12:53 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default 2 Queries in one script

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
 
Old December 1st, 2003, 01:00 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
 
Old December 1st, 2003, 01:24 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old December 1st, 2003, 02:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old December 1st, 2003, 03:03 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 |
================================================== =====
 
Old December 1st, 2003, 06:47 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
 
Old December 1st, 2003, 10:38 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

While on that note...how do I?
 
Old December 2nd, 2003, 12:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
 
Old December 2nd, 2003, 01:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Parameter Queries designdawg Access 13 March 5th, 2008 01:39 PM
Combining two Queries arholly Access 1 January 16th, 2007 06:40 PM
Combining Queries or results from 2 queries Ford SQL Server 2000 24 November 7th, 2005 08:54 PM
Queries xzvi0r Access 5 September 8th, 2003 10:03 AM
Call and run CGI script from a PHP script ... how? dbruins BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 1 June 10th, 2003 03:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.