MYSQL where $var AND IS NULL

Question

I have problem handling information from my MySQL Database, I have this code :

$sql = "SELECT * FROM obojok WHERE id_uzivatela = '".$_SESSION['uzivatel']['id']."' AND id_zvierata IS NULL";
      $res = $conn->query($sql);
      if ($res === TRUE) {
        echo "DATA Obtained";
      } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
      }

But the only thing I get is this error :

Error: SELECT * FROM obojok WHERE id_uzivatela = '******' AND id_zvierata IS NULL
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'is null AND id_uzivatela='20170'' at line 1

Answer 1

SELECT * FROM obojok WHERE id_uzivatela = '".$_SESSION['uzivatel']['id']."' OR id_zvierata IS NULL

Answer 2

The error is not in the query. At least, not in the query posted in the question.

The error is in the PHP code.

A SELECT query never returns TRUE. It returns an object when it succeeds. Either a mysqli_result or a PDOStatement, depending on what MySQL library you use.

Your query succeeds and $res is an object. It is not TRUE, the test $res === TRUE fails and the code erroneously runs the else branch. It then reports the correct query it just ran successfully and the error message of the most recent failed query. This explains why the fragment of query listed by the server in the error message does not match the query you ran.

The correct way to check the result of your query is $res != FALSE. It doesn't matter if you use == or ===, $res is either an object (not equal with FALSE no matter how it is compared) or FALSE. You can, as well, just use $res. It is the same thing:

$sql = "SELECT * FROM obojok WHERE id_uzivatela = '".$_SESSION['uzivatel']['id']."' AND id_zvierata IS NULL";
$res = $conn->query($sql);
if ($res) {
    echo "DATA Obtained";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

Depending on the MySQL library you use, read more about mysqli::query() and/or PDO::query().

Answer 3

Your query has no problem... The error you see is from another query.

From documentation, the return of $conn->query should not be strictly equal to TRUE

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object.

If you want to see if your query worked, do not test on the true value, you are better testing on not false...

  $sql = "SELECT * FROM obojok WHERE id_uzivatela = '".$_SESSION['uzivatel']['id']."' AND id_zvierata IS NULL";
  $res = $conn->query($sql);
  if ($res != FALSE) {
    echo "DATA Obtained";
  } else {
    echo "Error: " . $sql . "<br>" . $conn->error;
  }

EDIT: I have completely forgot to note that since the error you see is coming from another query, you have to repair that query too! One habit I had for a long time when writing queries like that is to write it this way:

$sql = "SELECT * FROM obojok WHERE id_uzivatela = '".$_SESSION['uzivatel']['id']."' AND id_zvierata IS NULL";
$res = $conn->query($sql) or throw new Exception("Error: " . $sql . "<br>" . $conn->error);

What this means is make $res = to the $conn->query value if it is not equal to false. If it is false, throw an exception with the relevant information.

If you do that, you won't have to test after if $res is good or not. It will also greatly help you find where errors are happening.