Robin de Cock Robin de Cock - 4 months ago 13
MySQL Question

Use a query in a query, but query became null

So I want to select data from a table in my DB and want to use that data in a other select query, I try but my last query became a null, So what did I wrong ?

$query = "SELECT * FROM `DBovervieuw` WHERE owner =" . $owner;
$result_set = mysqli_query($connection, $query);
while($result = mysqli_fetch_array($result_set)) {
$stmt = "SELECT * FROM `card` WHERE DBid=" . $result['ID'];
$Finale_set = mysqli_query($connection, $Finale_set);
while($row = mysqli_fetch_ASSOC($Finale_set)) {
$data[] = $row;
}

}
echo json_encode($data);


I will do my BIND variable later

Answer

Hi the main thing here you are missing is in the second WHERE ( Update .. actually both queries )

 $query = "SELECT * FROM `DBovervieuw` WHERE " . $owner;

 $stmt = "SELECT * FROM `card` WHERE " . $result['ID'];

You have no column name. So where what?

  $query = "SELECT * FROM `DBovervieuw` WHERE {insert column name here } = " . $owner;

Think if $owner is an id with a value of 1 then you have this query..

  $query = "SELECT * FROM `DBovervieuw` WHERE 1"

Which will just return all the records in the table.

That said a simple Join may be preferable here, then 2 queries

 $query = "SELECT
               d.*, c.*
           FROM
               `DBovervieuw` AS d
           JOIN
               card AS c
           ON
               d.ID = c.DBid
           WHERE d.owner = " . $owner;

Without knowing those column names that's the best I can do... Essentially instead of the second where you use that relationship as the ON in the join. Then you can save yourself a bunch of query calls to the DB, and it makes the code much shorter and easier because you have all the data on hand, and can probably avoid a second ( nested ) while loop.

UPDATE This is also an issue.

  $stmt = "SELECT * FROM `card` WHERE DBid=" . $result['ID'];
  $Finale_set = mysqli_query($connection, $Finale_set);

I think you want this instead:,

  $stmt = "SELECT * FROM `card` WHERE DBid=" . $result['ID'];
  $Finale_set = mysqli_query($connection, $stmt);  //<-----

By the way that is responsible for the empty query error, because you used $Finale_set before defining it. Which would be NULL as your SQL, thereby giving you the empty query error. It's the wrong variable though, so we'll just use the correct one instead of defining it.

Cheers.

As a side note the $stmt variable is typically used when using PDO it stands for PDOstatement http://php.net/manual/en/class.pdostatement.php So convention wise I would change that simply to $sql. But that is just a best practice sort of thing.