Gather data from two tables where one column is the value for another column in another database

[PeppaPigKilla]

Hello 

 

Let me lay out what im trying to achieve, then hopefully it will become more understandable. Few years ago, someone made me a script that dispalyed the top x amount of users in my database, what determined them being the top was their score. 

 

I get my Score from a table called tbl_playerstats that info is presented like so 

 

You can also see in this image the column StatsID , on this next screenshot you will see my other table which is called tbl_playerdata in this table there is a value called PlayerID , PlayerID is the value of StatsID. 

 

 

 

My code which ive had to make some changes to does work and currently gives me the top 10 guys, with top being the one with the highest score, however, it shows their StatsID and I would like it to show their PlayerID instead. 

 

This is my code so far 

<?php
$servername = "localhost";
$username = "*********";
$password = "*****";
$dbname = "******";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT StatsID, Score, Kills, Deaths FROM tbl_playerstats";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<br> Soldier: ". $row["StatsID"]. "<p> Score: ". $row["Score"]. "<p> Kills: ". $row["Kills"]. " <p>Deaths: " . $row["Deaths"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
$i=1;
$toolinfo = array();
while ($row = mysqli_fetch_assoc($result)) {
    $aditional_class = "";
    if($i%2==0)
      $aditional_class = "even";
    array_push($toolinfo, $row);
    echo "<tr class=\"".$aditional_class."\">";
    echo "<td style='font-weight:bold;'>".$i."</td>";
    echo "<td><a id='soldier".$i."' href='#'>".$row['SoldierName']."</a></td>";
    echo "<td>".$row['Kills']."</td>";
    echo "<td><span class=\"flag ".$row['CountryCode']."\"></span></td>";
    echo "</tr>";
    $i++;
}
?>

My apologies if this is really ugly to look at, but im new to this and working off an exmaple i never originally wrote. 

 

Any help or guidance would be appreciated, thank you. 

[PeppaPigKilla]

Cannot edit my first post, I made a slight error in describing whats im trying to do 

 

where i have put "My code which ive had to make some changes to does work and currently gives me the top 10 guys, with top being the one with the highest score, however, it shows their StatsID and I would like it to show their PlayerID instead."

 

I need to use the SoldierName to display for the PlayerID of the StatsID

 

So 

StatsID 1

PlayerID 1

SoldierName SerratedRabbit19     <--- only top display this. 

 

 

So where it says Soldier 1 it will say Soldier SerratedRabbit19

 

Hop i explained that better. 

[PeppaPigKilla]

Ok Found out how to do this 

 

I have to replace 

$sql = "SELECT StatsID, Score, Kills, Deaths FROM tbl_playerstats";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<br> Soldier: ". $row["StatsID"]. "<p> Score: ". $row["Score"]. "<p> Kills: ". $row["Kills"]. " <p>Deaths: " . $row["Deaths"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();

With 

$sql = "SELECT tps.*, tpd.* FROM tbl_playerstats tps, tbl_playerdata tpd WHERE tps.StatsID = tpd.PlayerID Limit 10";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    echo "<br> Soldier: ". $row["SoldierName"]. "<p> Score: ". $row["Score"]. "<p> Kills: ". $row["Kills"]. " <p>Deaths: " . $row["Deaths"] . $row["Rounds"] . "<br>";
  }
} else {
  echo "0 results";
}

$conn->close();

Now i need to find out how to make it so that the order of the results is determined by the highest 

Score 

value

[Jacques1]

Learn SQL. I understand that copypasting random code from the Internet is the new programming, but when you've reached the point where you cannot even copypaste, then it's time to actually learn the language.

[cyberRobot]

With 

$sql = "SELECT tps.*, tpd.* FROM tbl_playerstats tps, tbl_playerdata tpd WHERE tps.StatsID = tpd.PlayerID Limit 10";

 

 

While selecting all columns can work, it can also make the debugging process more complicated. For example, if both tables have columns that have the same name, there will be data loss.

 

 

 

Now i need to find out how to make it so that the order of the results is determined by the highest

 

MySQL has a feature for sorting. 

https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html