Danter Network Danter Network - 3 months ago 7
PHP Question

How can I show rows from one table that aren't in another table?

I have two tables in a database, one of them is a list of 'buildings' you could create. The other is a list of buildings that have been built by users.

On one page, (cityproduction.php), it displays a list of 'buildings' you can build.
I want it to display the buildings that you can build, that you haven't already built.

Here is my code:

$sql = "SELECT * FROM [The list of built buildings] WHERE building_owner = '$user'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$variable = $row["building_name"];
}


(...)

$sql = "SELECT * FROM [The list of ALL buildings] WHERE name != '$variable' ORDER BY id asc";
$result = mysqli_query($database,$sql) or die(mysqli_error($database));

while($rws = mysqli_fetch_array($result)){
echo $rws["name"]; (etc.)


What this is doing is only not-showing one of the buildings that the user has built, not all of them.

Answer

Why are you using while after the first query, it suppose to be a list or just a single value? because if you use $variable in your second query it will only have the value of the last value of the list you are getting

if ($result->num_rows > 0) {

  $variable =  array();

  while($row = $result->fetch_assoc()) {

    $variable[] = $row["building_name"];    
}

Second query example:

 foreach($variable as $building) {

      $sql = "SELECT * FROM [The list of ALL buildings] WHERE name != '$building' ORDER BY id asc";
      $result = mysqli_query($database,$sql) or die(mysqli_error($database));
      $result = mysqli_fetch_assoc($result);

      echo $result["name"];
    }