CODEWITHSUNDEEP
phppdo
rudicangiotti
rudicangiotti

Reputation: 566

Why does the AND SQL operator make the query fail?

I am learning the basics of PHP building a little web application.
In order to let it interface with the database, I am using a PDO instance.
All seems to work fine when I make queries with a single filtering condition, as:

SELECT * FROM mytable WHERE x = 1;

| id | x | y | z |
+----+---+---+---+
|  1 | 1 | a | 9 |

But when I add a second condition with the AND operator (for instance, AND y = 'a') something seems to not work properly, since no rows are returned.

Here is the PHP code with which I make the queries:

[...]

private $_PDO_TYPES = [
  'integer' => PDO::PARAM_INT,
  'string' => PDO::PARAM_STR,
];

private function _exec_query($query, $args) {
  $query_obj = $this->db_obj->prepare($query);
  foreach ($args as $key => $value) {
    $value_type = gettype($value);
    $pdo_type = $this->_PDO_TYPES[$value_type];
    $query_obj->bindParam($key, $value, $pdo_type);
  }
  $query_obj->execute();
  return $query_obj;
}

public function read_query($query, $args) {
  $query_obj = $this->_exec_query($query, $args);
  $rows = $query_obj->fetchAll(PDO::FETCH_ASSOC);
  return $rows;
}

[...]

$query = "SELECT * FROM mytable WHERE x = :x AND y = :y";
$rows = $dbi->read_query($query, [':x' => 1, ':y' => 'a']);
// $rows is an empty array...

Can you please explain me what I am missing to do the query in the right way? From command line, using mysql the query returns the correct table row.

Upvotes: 0

Views: 50

Answers (1)

Dharman
Dharman

Reputation: 33316

It is usually a very bad idea to bind PDO parameters based on the type of the variable in PHP. The binding type should be dictated by column definition or by SQL. It's much simpler to bind everything as strings and not worry about the correct cast type in bindParam().

Your actual problem comes from the fact that bindParam() binds by reference. Each time you iterate you overwrite the variables which means that you are creating a reference multiple times to the same value. What you want instead is binding by value.

There are two ways to achieve what you need.

  1. Bind in execute.

    private function _exec_query($query, $args)
{
    $query_obj = $this->db_obj->prepare($query);
    $query_obj->execute($args); // Pass arguments in execute
    return $query_obj;
}

  2. Use bindValue().

    private function _exec_query($query, $args)
{
    $query_obj = $this->db_obj->prepare($query);
    foreach ($args as $key => $value) {
        $query_obj->bindValue($key, $value);
    }
    $query_obj->execute();
    return $query_obj;
}

Upvotes: 1

Related Questions