Querying 2 tables where ID are Same Then Presenting The Results

[tooNight]

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:

[b]players:[/b] playerID, playerName

[b]playerRankings:[/b] pRankingsID, pRplayerID, pRankingScore, pPrevRankingScore

So i devised this SQL query to pick them up

[code]

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

[/code]

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.

[code]

<?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();
?>

[/code]

[swatisonee]

Will this help ?

[code]$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!";    
}[/code]

HTH.

[tooNight]

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


Thanks alot

:)

[keeB]

[!--quoteo(post=357666:date=Mar 23 2006, 04:55 PM:name=tooNight)--][div class=\'quotetop\']QUOTE(tooNight @ Mar 23 2006, 04:55 PM) [snapback]357666[/snapback][/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] * [color=green]FROM[/color] [color=orange]playerRankings[/color] pR
INNER [color=green]JOIN[/color] players p on p.playerID [color=orange]=[/color] pR.playerID
[color=green]WHERE[/color] p.playerID [color=orange]=[/color] [color=red]'some playerID'[/color] [!--sql2--][/div][!--sql3--]

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

GOOD LUCK

[swatisonee]

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