vendredi 31 juillet 2015

MySQL Where clause values 0 and 1 not working correctly

The situation

In table I have columns configurated as ENUM('0','1'). I have select query build with PDO like this example

$value = isset($_POST['value']) ? $_POST['value'] : (isset($_GET["value"]) ? $_GET["value"] : null);

$sql = $pdo->prepare("SELECT * FROM tablename WHERE column = :value");
$sql->bindValue(':value', $_POST['value']); // post contains 0 or 1
$sql->execute();


The problem

When printing the results, value 1 is working normally. But when using value 0, all rows are showing including rows with value 1.

Following query is working normally when trying it in HeidiSQL, but it's not with PHP. What's wrong?

SELECT * FROM tablename WHERE column = '0'

I noticed that PHP thinks $_POST['value'] is unset when its value is zero. I'm using isset()


Trying to solve the problem

  • No effect either if using $_GET['value'] and url like index.php?value=0

  • Tried following, not working

    $sql->bindValue(':value', '0'); // post contains 0 or 1
    
  • I changed column type to TINYINT(1) - no effect. When looking for zero, all are showing.

  • Set PDO bindValue() $data_type to PDO::PARAM_BOOL, not working

Aucun commentaire:

Enregistrer un commentaire