Error creating a query invalid parameter number

Question

The following script throws me this error:

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Error comes from these lines:

$query .= "WHERE username1=:un1";
$binValues["un1"] = $_POST['username1'];

Is there any problem with php syntax?

My full script:

<?php

    require_once "config.inc.php";  

    $query = "UPDATE customer SET ";
    $binValues = [];


    if(!empty($_POST['eidosmetaf1'])) {
        $query .= "eidosmetaf1 = :e1";
        $binValues["e1"] = $_POST['eidosmetaf1'];
    }

    if(!empty($_POST['weight1'])) {  
        $query .= ",weight1 = :w1";
        $binValues["w1"] = $_POST['weight1'];
    }

    if(!empty($_POST['startNomos1'])){ 
        $query .= ",startNomos1 = :sn1";
        $binValues["sn1"] = $_POST['startNomos1'];
    }

    if(!empty($_POST['startPoli1'])) { 
        $query .= ",startPoli1 = :sc1";
        $binValues["sc1"] = $_POST['startPoli1'];
    }

    if(!empty($_POST['start_lat'])) { 
        $query .= ",start_lat = :slat1";
        $binValues["slat1"] = $_POST['start_lat'];
    }

    if(!empty($_POST['start_lng'])) { 
        $query .= ",start_lng = :slng1";
        $binValues["slng1"] = $_POST['start_lng'];
    }

    if(!empty($_POST['finalNomos1'])) { 
        $query .= ",finalNomos1 = :fn1";
        $binValues["fn1"] = $_POST['finalNomos1'];
    }

    if(!empty($_POST['finalPoli1'])) {  
        $query .= ",finalPoli1 = :fc1";
        $binValues["fc1"] = $_POST['finalPoli1'];
    }

    if(!empty($_POST['final_lat'])) {  
        $query .= ",final_lat = :flat1";
        $binValues["flat1"] = $_POST['final_lat'];
    }

    if(!empty($_POST['final_lng'])) {  
        $query .= ",final_lng = :flng1";
        $binValues["flng1"] = $_POST['final_lng'];
    }

    if(!empty($_POST['depDate1'])) {  
        $query .= ",depDate1 = :dD1";
        $binValues["dD1"] = $_POST['depDate1'];
    }

    if(!empty($_POST['depTime1'])) { 
        $query .= ",depTime1 = :dT1";
        $binValues["dT1"] = $_POST['depTime1'];
    }

    if(!empty($_POST['specialservices1'])) {  
        $query .= ",specialservices1 = :ex1";
        $binValues["ex1"] = $_POST['specialservices1'];
    }

    if(!empty($_POST['comments1'])) {  
        $query .= ",comments1 = :c1";
        $binValues["c1"] = $_POST['comments1'];
        }   

        //error here
        $query .= "WHERE username1=:un1";
        $binValues["un1"] = $_POST['username1'];    


        $query .= "and comments1=:c1_old";
        $binValues["c1_old"] = $_POST['comments2_old'];


    try {
        $stmt = $db->prepare($query);
        $stmt->execute($binValues);

    } catch (PDOException $ex) {
        $response["success"] = 0;
        $response["message"] = "Database Error2. Please Try Again!";
        echo $ex->getMessage();
        die(json_encode($response));
    }    

    $response["success"] = 1;
    $response["message"] = "..............!";
    echo json_encode($response); 


?>

similar question: http://stackoverflow.com/q/29354264/3933332 don't you think?! — Rizier123, Mar 31 '15 at 12:24
*Hm...* you might need a space before the `where` --- `$query .= " WHERE username1=:un1";` — Funk Forty Niner, Mar 31 '15 at 12:26
Hello again Rizier, are you always online; hahaha. But i insist: what is now the problem at the specific lines; — johnnal, Mar 31 '15 at 12:27
Plus, make sure your `$_POST['username1']` is set and named in your form. — Funk Forty Niner, Mar 31 '15 at 12:28
Did you see comment # deux? `$query .= " WHERE username1=:un1";` — Funk Forty Niner, Mar 31 '15 at 12:31
You also need a space here `$query .= " and comments1=:c1_old";` — Funk Forty Niner, Mar 31 '15 at 12:44

score 1 · Accepted Answer · edited May 23 '17 at 12:06

As I fixed already your previous code I would recommend you to use the code from my answer: https://stackoverflow.com/a/29354781/3933332 because it's a lot simpler to read and understand.

And if you use my code you can add your WHERE clause like this:

<?php

    //...

    foreach($checkedValues as $k => $v) {
        $query .= "$v = :$k,"; 
        $bindValues[$k] = $_POST[$v];
    }

    $query = rtrim($query, ",");

    //fixed code here
    if(isset($_POST['username1'])) {
        $query .= " WHERE username1=:un1";
        $bindValues["un1"] = $_POST["username1"];
    }

    if(isset($_POST['comments1'])) {
        $query .= " AND comments1=:c1_old";
        $bindValues["c1_old"] = $_POST["comments1"];
    }


    try {           
        $stmt = $db->prepare($query);
        $stmt->execute($binValues);         
    } catch (PDOException $ex) {
        $response["success"] = 0;
        $response["message"] = "Database Error2. Please Try Again!";
        echo $ex->getMessage();
        die(json_encode($response));
    }    

    //...

?>

@johnnal Then go back to my last answer and see my updated code :D And then you also can combine it with the new code here. — Rizier123, Mar 31 '15 at 12:53
ok i will. But question remains: why is the specific code i provided wrong; — johnnal, Mar 31 '15 at 12:56
@johnnal 2 things. 1. You need to check if the variable is set 2. And the main problem you need to add spaces before you concatenate both strings to the query otherwise you will get something like this: `:c1WHERE username1=:un1AND...` And it won't see where the WHERE and AND clause are — Rizier123, Mar 31 '15 at 12:57

score -1 · Answer 2 · answered Mar 31 '15 at 12:31

-1

$binValues[":un1"] = $_POST['username1']

answered Mar 31 '15 at 12:31

Professor Abronsius

33,063
5
32
46

Adding : doesn't work. Note that statements for example "$binValues["dD1"] = $_POST['depDate1'];" are working perfect – johnnal Mar 31 '15 at 12:39

Error creating a query invalid parameter number

2 Answers2