CODEWITHSUNDEEP
phpmysqlpdo
Lucas Raines
Lucas Raines

Reputation: 1315

MySQL PDO won't work with integers

I Have a basic function that looks like this:

public function query($query, $params = []) {
        $statement = $this->db->prepare($query);

        // Bind parameters based on value's type
        foreach ($params as $key => $value) {
            if(is_int($value)) {
                $statement->bindParam($key + 1, $value, PDO::PARAM_INT);
            } else {
                $statement->bindParam($key + 1, $value, PDO::PARAM_STR);
            }
        }

        $statement->execute();

        return $statement;
}

For whatever reason, when I run something like this:

public static function photosByTag($tag, $user = null) {
    $db = new DBConnection();

    $query = "SELECT * FROM photos JOIN tags ON tags.photo = photos.pid WHERE tag LIKE ? AND owner = ?";
    $params = [$tag, $user];

    $result = $db->query($query, $params);
    return $result->fetchAll();
}

photosByTag('city', 1)

It doesn't work. If I replace the AND owner = ? with AND owner = 1 it works fine. Something is wrong when binding integers as params, but I don't know what or why.

Upvotes: 1

Views: 336

Answers (1)

Devon Bessemer
Devon Bessemer

Reputation: 35337

The problem isn't the bind, it is the loop. If you look at the manual, the second parameter for bindParam (&$variable) requires a reference. Your loop destroys that reference once it reassigns $value. The solution would be to use $params[$key] instead of $value in the bindParam()

Seems kind of redundant to do it this way when you can just use the execute() statement to bind the parameters.

$statement->execute($params);

Just let PDO handle how it assigns the variables. All your doing is checking what is submitted and then choosing the type, you're not enforcing a type, so it is probably similar to what PDO::execute does as is.

Upvotes: 2

Related Questions