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

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>

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

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())