CODEWITHSUNDEEP
phpmysql
diekunstderfuge
diekunstderfuge

Reputation: 541

PDO Insert writes value of 1 into all fields with bound parameters

I have a table of contacts with the following fields/data types: contactid (PK, auto-increment, not null), fname varchar(50), lname varchar(50), email varchar(50), is_member tinyint(1) (i.e., boolean), and createdate date.

The data is submitted to the same PHP page that the form is on, and the values of the $_POST array are packaged into a Contact object and passed to the following function:

<?php
    public static function insertContact(Model $model, Contact $contact) {
        $database = new Database();
        $sql = 'INSERT INTO contact (fname, lname, email, is_member, createdate) VALUES (:fname, :lname, :email, :is_member, CURDATE())';
        $params = array(
            ':fname' => $contact->getFirstname(),
            ':lname' => $contact->getLastname(),
            ':email' => $contact->getEmail(),
            ':is_member' => intval($contact->isMember())
        );
        $result = $database->query($sql, $params, 'LASTID'); // Will return last inserted ID.
        $model->notify('Added contact ID ' . strval($result) . ' to the database.');
    }
?>

This function is called if another function that checks to see if the contact already exists returns false. I'm using PDO prepared statements, and everything's working fine for SELECT statements, but when I try to execute this INSERT statement, I'm running into problems. The $params array is correct, and the number of params matches the number of placeholders, but the is_member field is causing problems. If the value is 1, the INSERT completes, but the resulting row looks something like this:

contactid  fname  lname  email  is_member  createdate
14         1      1      1      1          2014-05-31

Has anybody seen this behavior before? Moreover, if the value of is_member is 0, then the query fails entirely with the PDOStatement warning [HY093]: Invalid parameter number: number of bound variables does not match number of tokens. What I don't get is that $params has the correct number of values to bind to the placeholder tokens.

[UPDATE:] The $database->query() method is as follows:

<?php
    // Return type is the format in which to return the result of the query.
    public function query($sql, $params, $returnType) {
        if($stmt = $this->connection->prepare($sql)) {
            foreach($params as $key => $value) {
                if($value != 'noParams') {
                    $stmt->bindParam($key, $value);
                }
            }
            if($stmt->execute()) {    // execute() returns TRUE on success
                 switch($returnType) {
                     case 'ASSOC':
                         $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
                     break;
                     case 'LASTID':
                         $result = $this->connection->lastInsertId();
                     break;
                     // ... more cases, etc.
                 }
             } else {
                 $result = null;
                 die('Error: ' . $this->connection->error);
             }

             return $result;
         } else {
             die('Prepare failed: (' . $this->connection->error . ')');
         }
     }
?>

As mentioned, this query() method works for prepared statements for SELECT operations, however it is exhibiting the aforementioned behavior when trying to do a simple INSERT. Any help is appreciated!! Thanks!

Upvotes: 0

Views: 754

Answers (2)

Mark Baker
Mark Baker

Reputation: 212452

The first problem:

if($value != 'noParams') {

If value is 0, then this will be a false match because it's a loose-typed comparison; so "falsey" type values (null, 0.0, false, etc) will give you problems, because it won't bind any falsey values from your array... which explains your Invalid parameter number: number of bound variables does not match number of tokens error....

make it a strict comparison: 

if($value !== 'noParams') {

Your second problem:

Quoting from the PHP Docs

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

At the point where you actually execute the statement, $value only has the value from the last iteration of your binding loop, which is the value from the is_member array element, which is 1. That's why you're getting nothing but 1 values for all your bind variables

Change your foreach loop to 

foreach($params as $key => &$value) {

So that $value is "by reference", and it should then be the correct reference that is bound to each bind var

Alternatively, use bindValue() rather than bindParam()

Holistic:

So for the complete fix

foreach($params as $key => &$value) {
    if($value !== 'noParams') {
        $stmt->bindParam($key, $value);
    }
}

Upvotes: 2

Rob
Rob

Reputation: 224

have you tried using exec versus query?

in the docs: https://www.php.net/manual/en/pdo.exec.php

versus https://www.php.net/pdo.query

bottom line: PDO::query — Executes an SQL statement, returning a result set as a PDOStatement object while PDO::exec — Execute an SQL statement and return the number of affected rows

Upvotes: 0

Related Questions