Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 1st, 2003, 12:53 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
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
Reply With Quote
  #2 (permalink)  
Old December 1st, 2003, 01:00 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
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/
Reply With Quote
  #3 (permalink)  
Old December 1st, 2003, 01:24 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
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.
Reply With Quote
  #4 (permalink)  
Old December 1st, 2003, 02:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
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.
Reply With Quote
  #5 (permalink)  
Old December 1st, 2003, 03:03 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
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 |
================================================== =====
Reply With Quote
  #6 (permalink)  
Old December 1st, 2003, 06:47 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
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/
Reply With Quote
  #7 (permalink)  
Old December 1st, 2003, 10:38 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
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?
Reply With Quote
  #8 (permalink)  
Old December 2nd, 2003, 12:18 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
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/
Reply With Quote
  #9 (permalink)  
Old December 2nd, 2003, 01:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:43 AM.


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