"Number of bound variables does not match number of tokens", but I have a parameter for every "?"

Question

I'm trying to insert the results of a POST submission into a MySQL database.

<?php
    if(isset($_POST['activity'])&&isset($_POST['timeLength'])&&isset($_POST['datevalue']))
    {
        ini_set('display_errors', 1);
        $name=$_POST['activity'];
        $timeLength=$_POST['timeLength'];
        $datevalue=$_POST['datevalue'];
        $datevalue=date("m-d-Y",strtotime($datevalue));
        echo $name." ".$timeLength." ".$datevalue;
        if(strlen(trim($timeLength))>0)
        {
            $dsn="mysql:host=database.net;dbname=databasename;charset=utf8";
            $db=new PDO($dsn,'databasename','password');
            $insertSQL="insert into preasc_activityLog (name,timeLength,datevalue) values (?,?,?)";
            $stmt=$db->prepare($insertSQL);
            $stmt->bindParam("sis",$name,$timeLength,$datevalue);
            $stmt->execute();
        }
    }
    else
    {
        echo "FAIL!";
    }
?>

With my test data, the echo() call returns:

program 2 11-22-2016

Fields in table to be inserted into have the respective data types char(40),mediumint, and datetime, so the data seems like it should be good for the INSERT statement.

But I get the following error result:

<b>Notice</b>:  A non well formed numeric value encountered in 
<b>/home/server/module/submit.php</b> on line <b>16</b><br />
<br />
<b>Warning</b>:  PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter
number: number of bound variables does not match number of tokens in
<b>/home/server/module/submit.php</b> on line <b>17</b><br />

The date seems like it should be acceptable, but it's causing the "non well formed" error (because this error disappears if I only use the first two parameters).

And there are 3 tokens in $insertSQL, which match the 3 parameters in the bindParam() call. So where's the inconsistency between tokens and bound variables?

FLX · Accepted Answer · 2016-11-30T08:27:54.243

1

You can't use bindParam like that. You must call it once for each parameter, or use execute. Also it's spelled wrong.

$sth = $dbh->prepare('SELECT nom, couleur, calories
    FROM fruit
    WHERE calories < ? AND couleur = ?');
$sth->bindParam(1, $calories, PDO::PARAM_INT);
$sth->bindParam(2, $couleur, PDO::PARAM_STR, 12);

edited Nov 30 '16 at 08:27

answered Nov 30 '16 at 06:58

FLX

2,626
4
26
57

The example [here](http://www.w3schools.com/php/php_mysql_prepared_statements.asp) indicates that you can use it for multiple parameters: `$stmt->bind_param("sss", $firstname, $lastname, $email);` Is that tutorial wrong? – sigil Nov 30 '16 at 07:19
2

Wrong, wrong and outdated. W3School is the worst site to learn. Look how they write bind_param instead of bindParam... – FLX Nov 30 '16 at 08:27
Do you have an idea of why I'm getting the "non well formed" error here? When I use `bindParam()` as you suggested, the datetime parameter gets left blank. – sigil Nov 30 '16 at 08:38
Because (IMO) the date format is wrong. Try "Y-m-d" instead of "m-d-Y" – FLX Nov 30 '16 at 08:43
@sigil you better realize that mysqli and PDO are two different APIs – Your Common Sense Nov 30 '16 at 08:51

"Number of bound variables does not match number of tokens", but I have a parameter for every "?"

1 Answers1