动态PDO查询中的整数值[重复]

Question

This question already has an answer here:

• How to apply bindValue method in LIMIT clause? 10 answers

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.

</div>

Answer 1

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