2 tables, 3 columns need 1 value

Question

im new today for mysql, so please come with me.

I am trying to get 5 bits of data, of which 5 of tbl_playerstats these columns

StatsID 

Score

Kills

Deaths

Rank

Connected with it, that StatsIDis related to another table tbl_playerdata in this table PlayerIDcoincide with StatsIDtheir values are numeric, and in this tbl_playerdatathere is SoldierNamewhat I'm trying to put in place StatsID, the ultimate goal is that only these shows:

SoldierName

Score

Kills

Deaths

Rank

My code still looks like this (minus the details of connecting to the database

// 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, Rounds FROM tbl_playerstats Limit 10";
$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"] . $row["Rounds"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();

I have googled, and it seems that use JOINis the way to go, the examples I found did not work for me, it may not be entirely clear what this is called. I'm trying to do.

Answer 1

, StatsID PlayerID

<?php
$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";
}?>

Answer 2

, tbl_playerstats tps.StatsID = tpd.StatsID, ,

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

$sql = "SELECT tpd.SoldierName,tps.Score,tps.Kills,tps.Deaths,tps.Rank FROM tbl_playerstats tps LEFT JOIN tbl_playerdata tpd on tps.StatsID  = tpd.StatsID Limit 10";
$result = $conn->query($sql);

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

$conn->close();

, innner join

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

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

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

$conn->close();

- , .

Answer 3

, , , ?
, , .

, table-1 5

stats

score

kill

death

rank

and another table 2 is playerData containing these columns

playerId

playerName 

playerEmail

and so on...!!

and you need the whole data of table 1 along with the playerEmail file, and statId and Playerid are shared. then use this query

select p.stats, p.score, p.kill, p.death, p.rank, d.playerEmail
from Player p 
inner join playerData d
on p.statid = d.playerid.