No access to other table data SQL

Question

I have two tables, in relationship (as you can see in the screenshot):

Now I select from table zerts and want to get all zerts in a list. And now I want to get the username, by the userid. How to access this data which is connected?

$sql = "SELECT * FROM zerts ORDER BY created";
    $result = mysqli_query($conn, $sql);
    while($row = mysqli_fetch_assoc($result)) {
      echo "<tr><td>" . $row["id"] . "</td><td>" . $row["userid"] . "</td><td>" . $row["username"] . "</td></tr>";
    }

Why is this not possible to get the username by the userid? I get a blank output at the username field.

Have a look at how to do joins - https://stackoverflow.com/questions/3536283/how-to-join-two-tables-mysql may help. — Nigel Ren, Jul 07 '18 at 09:03

score 0 · Answer 1 · answered Jul 07 '18 at 09:08

You need to create join for this to access username of different table.

$sql = "SELECT zerts.userid,users.username,users.id FROM zerts JOIN users ON users.id = zerts.userid ORDER BY created";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)) {
  echo "<tr><td>" . $row["id"] . "</td><td>" . $row["userid"] . "</td><td>" . 
  $row["username"] . "</td></tr>";
}

score 0 · Accepted Answer · answered Jul 07 '18 at 09:09

0

Please update your query rest of the code will same.

$sql = "SELECT zerts.*,users.username FROM zerts 
LEFT JOIN users ON (zerts.userid = users.id)  ORDER BY zerts.created";

Hope this help.

answered Jul 07 '18 at 09:09

Mohsin Marui

459
2
8

No access to other table data SQL

2 Answers2