Invalid parameter number: number of bound variables does not match number of tokens using pdo

Question

This problem has got me absolutely stumped, I have looked through other answered questions on stack overflow and none of them answer my question.

**

Code:

**

    function inputVCA(){
    global $dbh;
    $count = 0;
    if(isset($_POST['vca'])){
        $pcid = mysql_real_escape_string($_POST['ID']);
        $vca = $_POST['vca'];
        $tok = strtok($vca, ";");
        while($tok !== false){      
            $countNew = countDataToHour($count);
            $stmt = $dbh->prepare("UPDATE $pcid SET $countNew = :token WHERE ID='1'");
            $stmt -> bindParam(':token', $tok);
            echo "<br>".$pcid."<br>";
            echo countDataToHour($count)."<br>";
            echo $tok."<br>";
            if($stmt->execute()){
                // do nothing
            }else{
                die("failed to execute query");
            }
            $count++;
            $tok = strtok(";");
        }
    }
 }

**

Output (including the error message):

**

888 00:00 40

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\xampp\htdocs\practice\src\dataChecker.php on line 170 failed to execute query

**

What do I already know about the issue?

**

The reason this problem has me so stumped is because when I test variables etc. everything seems to output correctly. Anyway:

• I've tested the sql query without the :variables. That works
• The variables outputted are the ones I entered into the form so I assumed it cannot be a problem there

**

Extra info

**

The one bit of my program that I am unsure about is the:

WHERE ID='1'

in my sql query. The reason I do this is because I only will ever need one line in the sql table for the moment.

---

Any help appreciated, thank you :). Also as a sidenote, whoever voted me down should not have the privileges to vote anyone down. I presented my problem very clearly. My php code is a little bit sketchy but if you are voting me down for this reason this is counter productive and may discourage new users to posting questions on this site. If new users don't come then you will lose out to other competitors. Thank you to all those who gave helpful contributions.

Answer 1

Firstly, PDO and mysql_, those two APIs do not mix together.

Remove mysql_real_escape_string.

Remove the quotes around this $count = ':token' to read as $count = :token (placeholders cannot have quotes around them).

You will have to put backticks around your $pcid in the UPDATE, combined with my other suggestion(s) in my comments and my answer here.

$pcid = $_POST['ID'];
...
$stmt = $dbh->prepare("UPDATE `$pcid` SET `$countNew` = :token WHERE ID='1'");
...

---

This is error message as per one of your comments:

MySQL server version for the right syntax to use near '888 SET 00:00 = ':token' WHERE ID='1'' at line 1'

Look at what the error is telling you, near '888 and those errors won't stop there.

Table names cannot start with a number or be inclusively composed of numbers.

As per documentation:

• http://dev.mysql.com/doc/refman/5.1/en/identifiers.html

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

Answer 2

Placeholders can only represent VALUES in a query. Never a field/table name, or other SQL keyword.

UPDATE :pcid SET :count = ':token' WHERE ID='1'
       ^^^^^--field         ^^^^^^--value
                  ^^^^^--field

If you want to dynamically insert table/field names, you'll have to use good old sql-injection-vulnerable techniques like

$table = $source_of_pcid
$field = $source_of_count
$sql = "UPDATE $table SET $field = :token";