Jump to content

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


PeppaPigKilla

Recommended Posts

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 

16f6b997409d2d9e541767ef0be7093c.png

 

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. 

 

9c853b4311eb6106739f310b3f60bd25.png

 

 

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. 

Link to comment
Share on other sites

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. 

 

1c076cca9b5889f50290af5742c454ed.png

 

So where it says Soldier 1 it will say Soldier SerratedRabbit19

 

Hop i explained that better. 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.  :happy-04:

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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.