Kamata Kamata - 20 days ago 7
MySQL Question

PHP mySQL table compare two columns

I need to compare data between two tables.

t1 = playerachievements

| ID | Username | Achievement ID |

t2 = achivements

| ID | Achievement Name | Image Location |

Now I search for items in t1 which match the username of the logged in user

$sql = "SELECT * FROM playerachievement WHERE username = '$user'";


Now what I need to do is grab the Image Location for each achievement the user has based on the Achievement ID in t1 and the ID in t2

What would be the best way to go about this?

EDIT:



I think I almost have it, however this only brings back one result:

$sql = "SELECT * FROM playerachievement WHERE username = '$user'";

$result = $db_conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$achid = $row['achievement'];
}

$sql = "SELECT * FROM achievements WHERE id = '$achid'";
$result = $db_conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "<div class='custom-container'>
<img src='".$row["image"]."'</img></div>";
}
}
}

Answer

for checking for each value you should give the condition inside while loop

$sql1 = "SELECT * FROM playerachievement WHERE username = '$user'";
$result1 = $db_conn->query($sql1);
if ($result1->num_rows > 0) {
// output data of each row
 while($row1 = $result1->fetch_assoc()) {
    $achid = $row1['achievement'];

  $sql2 = "SELECT * FROM achievements WHERE id = '$achid'";
  $result2 = $db_conn->query($sql2);
  if ($result2->num_rows > 0) {
          while($row2 = $result2->fetch_assoc()) {
              echo "<div class='custom-container'>
                      <img src='".$row2["image"]."'</img></div>";
          }
  }
 }
}