Danter Network Danter Network - 3 months ago 9
SQL Question

Little help, please? Confused at showing results from SQL

(I'm sorry, this isn't a great question - but I could do with some help)

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.

(It sounds simple but for some reason this isn't working - it's probably me, but help?)

What I have tried:

$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.

I would appreciate any help at all - even if that means I get a lot of down arrows. Thank you,

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"];
    }
Comments