Integer values in dynamic PDO query

Question

I'm trying to use a dynamic PDO query (add where clause to the query if the variable is true) but i have a problem with integers values, this is my code:

$params = array();
$cond = array();

$query = "SELECT value FROM `table`";

if (!empty($firstname)) {
    $cond[] = "firstname = :fn";
    $params[':fn'] = $firstname;
}
if (!empty($lastname)) {
    $cond[] = "lastname = :ln";
    $params[':ln'] = $lastname;
}
if (count($cond)) {
    $query .= ' WHERE ' . implode(' AND ', $cond);
}

$query .= " LIMIT :min, :max";
$params[':min'] = $min; // INTEGER VALUE
$params[':max'] = $max; // INTEGER VALUE

$stmt = $db->prepare($query);
$stmt->execute($params);

The problem is that PDOStatement::execute treated all values as PDO::PARAM_STR and LIMIT need integer values.

I tried with PDOStatement::bindValue using PDO::PARAM_INT parameter but i don't know how to use it in a dynamic query.

@chris85 it does not work, see php docs: [link](http://php.net/manual/en/pdostatement.execute.php) _input_parameters_: An array of values with as many elements as there are bound parameters in the SQL statement being executed. **All values are treated as PDO::PARAM_STR.** — Manux22, Feb 29 '16 at 01:27
what exactly do you mean by *dynamic* query? Maybe I'm missing something here — Kevin Kopf, Feb 29 '16 at 01:36
Maybe bind with `is_int($param) ? PDO::PARAM_INT : PDO::PARAM_STR` with a `foreach($params as $param`.. — chris85, Feb 29 '16 at 01:38

Kevin Kopf · Accepted Answer · 2016-02-29T02:52:18.707

2

You already have an array of keys and values to bind in $params, so after you prepare the statement, loop through it and bind accordingly:

$params = array();
$cond = array();

$query = "SELECT value FROM `table`";

if (!empty($firstname)) {
    $cond[] = "firstname = :fn";
    $params[':fn'] = $firstname;
}
if (!empty($lastname)) {
    $cond[] = "lastname = :ln";
    $params[':ln'] = $lastname;
}
if (count($cond)) {
    $query .= ' WHERE ' . implode(' AND ', $cond);
}

$query .= " LIMIT :min, :max";
$params[':min'] = $min; // INTEGER VALUE
$params[':max'] = $max; // INTEGER VALUE

$stmt = $db->prepare($query);

foreach($params as $key => $value)
{
    if(is_int($value))
    {
        $stmt->bindValue($key, $value, PDO::PARAM_INT);
    }
    else
    {
        $stmt->bindValue($key, $value, PDO::PARAM_STR);
    }
}

$stmt->execute($params);

Notice, that you must use bindValue, since bindParam will not work. The PHP manual states why:

Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

And once a foreach iteration is passed, $value no longer exists and can't be used as a reference. This is precisely the reason you must use bindValue

edited Feb 29 '16 at 02:52

answered Feb 29 '16 at 01:31

Kevin Kopf

13,327
14
49
66

but if you see my code, i need add `WHERE` clause dynamically, ie add `where` clause to the query if the variable is true. – Manux22 Feb 29 '16 at 01:39
oh I got it, I answered wrongly. Let me edit my answer – Kevin Kopf Feb 29 '16 at 01:41
@Manux22 there, take a look – Kevin Kopf Feb 29 '16 at 01:46
oh! i'm st*pid xD. Its easy :P. Thanks! (PD: with `bindParam` didn't work but with `bindValue` works fine). – Manux22 Feb 29 '16 at 02:14
@Manux22 why doesn't it work with `bindParam`? You getting an error? Something eludes me here? – Kevin Kopf Feb 29 '16 at 02:38
no, with `bindParam`, `fetchAll()` return empty array, the problem is `LIMIT :min, :max` – Manux22 Feb 29 '16 at 02:46
@Manux22 Oooooohhhhh I got the reason whyyyy!!!! – Kevin Kopf Feb 29 '16 at 02:50
yes, that is the problem. A perfect answer, thanks! – Manux22 Feb 29 '16 at 03:02

Petschko · Answer 2 · 2016-02-29T11:27:03.990

2

You can bind the Value with the optional 3rd Parameter on bindParam

Like this:

$stmt->bindParam($key, $value, PDO::PARAM_INT);

If that not work try

$stmt->bindParam($key, intval($value), PDO::PARAM_INT);

This works fine for me:

foreach($params as $key => &$value)
    $stmt->bindValue($key, $value, get_type($value));

Here is my get_type() function:

    function get_type($value) {
        switch(true) {
            case is_null($value):
                return PDO::PARAM_NULL;
            case is_int($value):
                return PDO::PARAM_INT;
            case is_bool($value):
                return PDO::PARAM_BOOL;
            default:
                return PDO::PARAM_STR;
        }
   }

I'm sure there are better ways to solve this, but hey it works

(Better use PDO::bindValue() than PDO::bindParam())

edited Feb 29 '16 at 11:27

answered Feb 29 '16 at 01:35

Petschko

168
3
16

but if you see my code, i need add WHERE clause dynamically, ie add where clause to the query if the variable is true, how can i solve this with `bindParam` or `bindValue`?. – Manux22 Feb 29 '16 at 01:43
Use the bindParam() between `$stmt = $db->prepare($query);` and `$stmt->execute();` – Petschko Feb 29 '16 at 01:55

Integer values in dynamic PDO query

2 Answers2