Jump to content


Photo

Querying 2 tables where ID are Same Then Presenting The Results


  • Please log in to reply
4 replies to this topic

#1 tooNight

tooNight
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 22 March 2006 - 01:47 PM

Hi there,

Got a simple problem, i have two tables one for players and one for playerRankings and from these table i want to select the followng fields:

players: playerID, playerName

playerRankings: pRankingsID, pRplayerID, pRankingScore, pPrevRankingScore

So i devised this SQL query to pick them up


SELECT playerRankings.pRankingsID, playerRankings.pRplayerID, playerRankings.pRankingScore,     playerRankings.pPrevRankingScore, players.playerID, players.playerName 
FROM playerRankings, players 
WHERE (playerRankings.pRankingsID = players.playerID)


I think this is correct, if not could you tell me whats wrong with it.

I then tried to enumerate this in php to try and get it to display the ranking list with the points and player names beside them and I cannot do this. So I would like some help in how to fix this. My code is below it would be great if someone could tell me where I am going wrong and how to fix it.


<?php
require('db_connect.php');    // database connect script.
?>

<?php

    $query  = "SELECT playerRankings.pRankingsID, playerRankings.pRplayerID, playerRankings.pRankingScore, playerRankings.pPrevRankingScore, players.playerID, players.playerName 
                         FROM playerRankings, players 
               WHERE (playerRankings.pRankingsID = players.playerID)";
               
    $result = mysql_query($query) or die('Error : ' . mysql_error()); 
    
   
    if (mysql_num_rows($result) == 0) {
    //No rows
} else {
    //Start enumerating
    while ($row = mysql_fetch_object($result)) {
        print "<div>" .
        "<p>" . $x_pPrevRankingScore = $row["pPrevRankingScore"] . "</p>" .
        "<p>" . $x_playerID = $row["playerID"] . "</p>" .
        "<p>" . $x_playerName = $row["playerName"] . "</p>" .
        "<p>" . $x_pRankingsID = $row["pRankingsID"] . "</p>" .
        "<p>" . $x_pRankingScore = $row["pRankingScore"] . "</p>" .
        "</div>";
    }
}
    
    
    

    echo $x_pPrevRankingScore;
    echo $x_playerID;
    echo $x_playerName;
    echo $x_pRankingsID;
    echo $x_pRankingScore;
    
   
    
    
    

$db_object->disconnect();
?>




#2 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 23 March 2006 - 04:35 PM

Will this help ?

$sql=" SELECT * FROM `playerRankings` ORDER BY  `pRplayerID ` asc ";
$result = mysql_query($sql) or die mysql_error());

if $myrow = mysql_fetch_array($result)
{
do
{
$pid = $myrow["pRplayerID"];

$sql2 = " SELECT * FROM `players` WHERE playerID= '$pid' ";
$result2= mysql_query($sql2) or die mysql_error());
$myrow2 = mysql_fetch_array($result2);
$name = $myrow2["playerName"];

printf(<tr><td>
%s<td>
%s<td>
%s<td>  
</tr>",
 $myrow["pRplayerID"],
$name,
$myrow["pRankingScore"]);

 } while ($myrow = mysql_fetch_array($result));


} else {

    echo "Sorry, no records were found!";    
}

HTH.
Shishya

#3 tooNight

tooNight
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 23 March 2006 - 04:55 PM

Works a treat thatnks, been tearing my hair out for the past few days trying to do this.


Thanks alot

:)


#4 keeB

keeB
  • Staff Alumni
  • Advanced Member
  • 1,078 posts
  • LocationCalifornia

Posted 23 March 2006 - 06:22 PM

[!--quoteo(post=357666:date=Mar 23 2006, 04:55 PM:name=tooNight)--][div class=\'quotetop\']QUOTE(tooNight @ Mar 23 2006, 04:55 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Works a treat thatnks, been tearing my hair out for the past few days trying to do this.
Thanks alot

:)
[/quote]

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM playerRankings pR
INNER JOIN players p on p.playerID = pR.playerID
WHERE p.playerID = 'some playerID' [!--sql2--][/div][!--sql3--]

That's what you're looking for.. just modify the * ;)

GOOD LUCK

Come visit my site to see my latest projects
http://nick.stinemates.org/wordpress/


#5 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 23 March 2006 - 06:41 PM

You're welcome but keeB's solution is even better and probably a more efficient one.
Shishya




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users