Invalid parameter number: number of bound variables does not match number of token

Question

So many question with this title but none of them able to solve my problem and I dont know WTH? This is a simple code php adding data to a table

  global $connPDO;
    ini_set('date.timezone', 'Asia/Karachi');
    $date = date('Y-m-d H:i:s');

    $sql = "INSERT INTO `pqa` VALUES (null, :ProId, :ProQuestion, null, '$date', null)";
    $queryInsert = $connPDO->prepare($sql);

    try {
        //Post contain $_POST["ProId"], $_POST["ProQuestion"];
        $querySuccess = $queryInsert->execute($_POST);
        echo $querySuccess;
    }
    catch(Exception $e) {
        echo '<h1>An error has ocurred.</h1><pre>', $e->getMessage() ,'</pre>';
    }

Catch block is not executing but giving error.

var data = {
    "action" : "SaveProjectNewQuestion",
    "ProId" : 1,
    "ProQuestion" : $jqueryLib("#NewQuestion").val()
};
$jqueryLib.ajax({
    url : "ESP.php",
    type : "POST",
    data : data,
    success : function(data, textStatus, XMLHttpRequest) {
        console.log(data);
    },
    error: function(jqXHR, textStatus, errorThrown) {
        console.log(textStatus, errorThrown);
    }
});

Amazingly success log showing this warning and no data has inserted also.

( ! ) Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\wamp\www\ESP\ESP.php on line 5621............................

What is in `$_POST`? Do a `var_dump();` on it. – Qirel Nov 22 '16 at 06:55 — Qirel, Nov 22 '16 at 06:55
ProjectId, and ProjectQuestion data – Muhammad Faizan Khan Nov 22 '16 at 06:56 — Muhammad Faizan Khan, Nov 22 '16 at 06:56

Qirel · Accepted Answer · 2016-11-22T07:11:47.613

2

When you're doing

$queryInsert->execute($_POST);

you're trying to bind everything in the global post-array with their respective indexes in the array as placeholder. When you submit your ajax, you're sending the following data

var data = {
    "action" : "SaveProjectNewQuestion",
    "ProId" : 1,
    "ProQuestion" : $jqueryLib("#NewQuestion").val()
};

Here there are three items, but you're only trying to bind two of them, causing there to be a missmatch in the number of binds and number of values, even if the two indexes you're trying to bind match the name of the placeholders - because your action isn't a placeholder in the query.

Generally I'd avoid doing like that, using the entire POST-array. It's only two variables to bind, I'd rather just bind them directly, like this

$queryInsert->execute(array("ProId" => $_POST['ProId'], 
                            "ProQuestion" => $_POST["ProQuestion"]));

edited Nov 22 '16 at 07:11

answered Nov 22 '16 at 06:59

Qirel

25,449
7
45
62

yes i were posted edited code and forget to change vars names. so you means binding must required? – Muhammad Faizan Khan Nov 22 '16 at 07:03
Bind the values in a one-to-one relationship, that way you're sure that you always bind the values you expect to. This means using the example I posted in my answer (you may have to alter the POST-indexes according to your actual code, you changed them so I'm not sure which you're actually using). – Qirel Nov 22 '16 at 07:05
Thanks Qirel for your answer and time. Generally speaking i expecting that $Post should need to be worked as paramters names are same. we should not need to be explicitly defined it – Muhammad Faizan Khan Nov 22 '16 at 07:16
1

If you only send `ProId` and `ProQuestion` in the `data` jQuery-variable, then yes - you could do `$queryInsert->execute($_POST);` (as then there would be 2 values and 2 placeholders == match), but you're sending 3 values, so you need to explicitly (as is good practice) state which to use. – Qirel Nov 22 '16 at 07:20
thanks again! but action is also necessary to send, it is method name. – Muhammad Faizan Khan Nov 22 '16 at 07:34

score 1 · Answer 2 · answered Nov 22 '16 at 06:58

1

You need to fix how you're executing your statement. Since you're defining (:ProId, :ProQuestion) your binds/parameters, you need to pass a constructed array to ->execute(...) that looks like this:

$binds = array(
    ':ProId' => $_POST['ProjectId'],
    ':ProQuestion' => $_POST['ProjectQuestion']
);
$querySuccess = $queryInsert->execute($binds);

answered Nov 22 '16 at 06:58

Darren

13,050
4
41
79

@MohammadFaizanKhan if this is external user input, then you should [**always bind**](http://stackoverflow.com/a/24989031/2518525) as you can never trust any user input. – Darren Nov 22 '16 at 07:03
Thanks for your time. i wish that i could accept more than one answer. But i still unable to understand binding purpose as the parameters names are same then you should not need to bind it – Muhammad Faizan Khan Nov 22 '16 at 07:18

Invalid parameter number: number of bound variables does not match number of token

2 Answers2