asked on

Getting MySQL error: 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

It's complaining that my number of anonymous parameters does not match the number of Bind() statements that I have. I am in the process of migrating a past application over to PHP, but it also had MySQL as the backend, and it works there.

Here is my SQL statement:

$sql = " UPDATE users " +
               "     SET username = ?, " .
               "	 password = ?, " .
               "         firstname = ?, " .
               "         middlename = ?, " .
               "         lastname = ?, " .
               "         address1 = ?, " .
               "         address2 = ?, " .
               "         city = ?, " .
               "         state = ?, " .
               "         zipcode = ?, " .
               "         phone1 = ?, " .
               "         phone2 = ?, " .
               "         email = ?, " .
               "         canHaveAppts = ?, " .
               "         eFiler = ?, " .
               "         apptColor = ?, " .
               "         notes = ?, " .
               "	 editdate = now(), " .
               "         editedbyid = ? " .
               " WHERE userid = ? ";

Open in new window

And here are my binding statements:

$stmt = $db->prepare($sql);
            $stmt->bindValue(1, $u->username, PDO::PARAM_STR);
            $stmt->bindValue(2, $u->password, PDO::PARAM_STR);
            $stmt->bindValue(3, $u->firstname, PDO::PARAM_STR);
            $stmt->bindValue(4, $u->middlename, PDO::PARAM_STR);
            $stmt->bindValue(5, $u->lastname, PDO::PARAM_STR);
            $stmt->bindValue(6, $u->address1, PDO::PARAM_STR);
            $stmt->bindValue(7, $u->address2, PDO::PARAM_STR);
            $stmt->bindValue(8, $u->city, PDO::PARAM_STR);
            $stmt->bindValue(9, $u->state, PDO::PARAM_STR);
            $stmt->bindValue(10, $u->zipcode, PDO::PARAM_STR);
            $stmt->bindValue(11, $u->phone1, PDO::PARAM_STR);
            $stmt->bindValue(12, $u->phone2, PDO::PARAM_STR);
            $stmt->bindValue(13, $u->email, PDO::PARAM_STR);
            $stmt->bindValue(14, $u->canHaveAppts, PDO::PARAM_STR);
            $stmt->bindValue(15, $u->eFiler, PDO::PARAM_STR);
            $stmt->bindValue(16, $u->apptColor, PDO::PARAM_INT);
            $stmt->bindValue(17, $u->notes, PDO::PARAM_STR);
            $stmt->bindValue(18, $u->userid, PDO::PARAM_INT);
            $stmt->bindValue(19, $u->userid, PDO::PARAM_INT);

            $retValue = $stmt->execute();

Open in new window

I've been counting over and over, and I can't see where I'm off. I would greatly appreciate any help on this. Thanks in advance.

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

elepil

ASKER

Guy Hengel,

It's hard for me to tell if there are any null values because I'm using the magic functions in PHP. Here is my User class:

class User {
    protected $data = array();
    
    public function __construct() {
    }
    
    public function __get($name) {
        if (isset($this->data[$name])) {
            return $this->data[$name];
        } else {
            return false;
        }
    }
    
    public function __set($name, $value) {
        $this->data[$name] = $value;
    }
    
    public function __toString() {
        $output = '[userid=' . $this->userid . ']' .
                  '[username=' . $this->username . ']' .
                  '[password=' . $this->password . ']' .
                  '[firstname=' . $this->firstname . ']' .
                  '[middlename=' . $this->middlename . ']' .
                  '[lastname=' . $this->lastname . ']' .
                  '[address1=' . $this->address1 . ']' .
                  '[address2=' . $this->address2 . ']' .
                  '[city=' . $this->city . ']' .
                  '[state=' . $this->state . ']' .
                  '[zipcode=' . $this->zipcode . ']' .
                  '[phone1=' . $this->phone1 . ']' .
                  '[phone2=' . $this->phone2 . ']' .
                  '[email=' . $this->email . ']' .
                  '[canHaveAppts=' . $this->canHaveAppts . ']' .
                  '[eFiler=' . $this->eFiler . ']' .
                  '[apptColor=' . $this->apptColor . ']' .
                  '[deletable=' . $this->deletable . ']' .
                  '[notes=' . $this->notes . ']' .
                  '[editdate=' . $this->editdate . ']' .
                  '[editedbyid=' . $this->editedbyid . ']' .
                  '[deleted=' . $this->deleted . ']' . '<br/>';
        
        return $output;
    }
}

Open in new window

Only PHP has something like this, along with a mechanism for PDO to automatically populate the object upon reading the database. In past programming languages I've used, I had to define a getter/setter set of functions for each property, and I would always initialize every single one of them. But I can't do that here.

When I invoke the __toString() method of the class, it doesn't output 'null' anywhere, but PHP is a different animal that prints 1 for TRUE and nothing at all for FALSE, and it's probably doing the same thing about NULL. But thanks for leading me to that path, I'll look into it and get back.

elepil

ASKER

Ray,

The reason why I don't use named parameters is because it is unique only to PDO. As a PHP developer, it's important to know both PDO and mysqli, and the latter supports only anonymous parameters; I'm trying to make my PDO and mysqli code as similar as possible so that future copy/pastes will entail less modifications. Besides, I doubt named parameters would've helped because the problem is not that my SQL statement is off, there's something else going on here.

elepil

ASKER

Guy Hengel,

Most of the properties of my User object had string values to start with, and the ones that didn't are the ones I tested. None of them are nulls.

ASKER CERTIFIED SOLUTION

Member_2_248744

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

elepil

ASKER

Slick812,

YOU NAILED IT! That SQL statement came from the Java platform, and I had been looking at it over and over and over, ad infinitum, and I did already take care of the rest of the + signs but missed that one! In fact, just right before I got the email notification of your response, I was just testing the update with only ONE parameter (the username) and I was still getting the error and was frustrating me to no end! So your response was very timely. Thank you!