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.
elepilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I cannot seen anything off, so I must start guessing.
is there anything "NULL" in the values passed?
0
Ray PaseurCommented:
This appears to be a data-dependent problem, so we would need to see the contents of the $u object.  If you can print that out with var_dump() and post it here, that might be helpful.

Just a thought... I find it is much easier to keep my thinking straight when I use named placeholders -- less counting and therefore less cognitive load.

I have working examples in this article.  They use bindParam() instead of bindValue().  The article also shows dependable ways of getting error messages when things go wrong.
http://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
elepilAuthor Commented:
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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

elepilAuthor Commented:
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.
0
elepilAuthor Commented:
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.
0
Slick812Commented:
greetings elepil, ,  I looked at your $sql string, , And the number of bindValue( ) , they both are 19, so the numbers match, ,
I did see a  "+" in your SQL, which is INCORRECT, as -
$sql = " UPDATE users " + // this + is for javascript, PHP should be a period
               "     SET username = ?, " .

you might try -
$sql = " UPDATE users " .
               "     SET username = ?, " .
               "       password = ?, " .

But I see NO REASON for you to use the PDO  bindValue( )   , I feel that for a proper prepare-execute, the PDO bindParam()  is much more appropriate, and corresponds more to the mysqli DB methods.

Not sure if this is a concern for you, but the mysqli and the PDO, are intended to be used for very different reasons, the PDO was created for php scripts that are built to be distributed to many users, for use in varied environments, where you need code that works in a sever with only MySQL, another server with only MSSQL, and another server that has no separate database engine, and has to use SQLite . The PDO is suppose to have common code for ALL the different DB, that can be invoked in the initialization, in the first parameter -
$db = new PDO('sqlite:dogs.sqlite');
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
elepilAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.