Fatal error: Uncaught PDOException: using PDO to run sql update statement

[webdeveloper123]

Hi guys,

I've got this error:

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined 
in /var/www/vhosts//customer/edit.php:138 Stack trace: 
#0 /var/www/vhosts/customer/edit.php(138): PDOStatement->execute(Array) #1 {main} thrown in /var/www/vhosts/customer/edit.php on line 138

Now I researched it and apparently its because my $customers array has 11 keys and my update statement has 10 attributes (excluding the WHERE clause) which  is the customer ID record I am trying to update. Apparently if they don't match up, there will be an error. Here is my $customers array and my GET statement for the customer id

$id = $_GET['user_id'] ?? '';




$customers['customer_id'] = $id;
$customers['first_name'] = $_POST['fname'];
$customers['last_name'] = $_POST['lname'];
$customers['address'] =  $_POST['address'];
$customers['town'] =  $_POST['town'];
$customers['county'] =  $_POST['county'];
$customers['post_code'] =  $_POST['postcode'];
$customers['birthdate'] =  $_POST['birthday'];
$customers['email'] = $email = $_POST['email'];
$customers['terms'] = (isset($_POST['terms']) and $_POST['terms'] == true) ? true : false;

 $customers['fav_food']   = $_POST['fav_food'] ?? '';
    $valid   = in_array($customers['fav_food'] , $food_choice);
    $errors['fav_food'] = $valid ? '' : 'Must enter a food type';

And here is my sql/pdo:

         $sql = "UPDATE customer_details SET first_name = :first_name, last_name = :last_name, 
address = :address, town = :town, county = :county, post_code = :post_code, 
fav_food = :fav_food, birthdate = :birthdate, email = :email, terms = :terms WHERE customer_id = :id;";
          
    

            $statement = $pdo->prepare($sql);
     
            $statement->execute($customers);

This is the line I get the error on:

 $statement->execute($customers);

Many thanks

Edited July 30, 2022 by webdeveloper123

[mac_gyver]

the error is because you are using three different names for the same thing and you don't seem to be able to keep track of which name you are using. if the column in the database table is named customer_id, i recommend that you use that same name for that value throughout the code,  e.g. for the get parameter name (user_id), one unnecessarily copied  php variable name (id), another unnecessarily copied php variable name (customer_id), and the prepared query place-holder name (id). consistency and simplicity counts in programming, so you are not wasting time trying to keep track of three different names for the same thing.

[dodgeitorelse3]

you are confusing

customer_id = :id;

for

customer_id = :customer_id;

[webdeveloper123]

7 minutes ago, dodgeitorelse3 said:

customer_id = :customer_id;

ahh. Can't believe I missed that one. Thanks @dodgeitorelse3

[dodgeitorelse3]

well actually mac_gyver answered you the same thing. He was just trying to get you to see the point he was making which is the same as I pointed out. Always keep the original variable unless you have changed it in some way from it's initial creation.

 

[webdeveloper123]

thanks for the advice

[Strider64]

I find this an easy way to handle updating using an array

 

/* Note ALL index keys must match the names of the Database Table columns */

function updateData(array $data, $pdo, $table): bool
{
    /* Initialize an array */
    $attribute_pairs = [];

    /* Create the prepared statement string */
    foreach ($customers as $key => $value)
    {
        /* I personally would had just used id instead of customer_id as that is more inline with universal naming */  
        if($key === 'customer_id') { continue; } // Don't include the id:
        $attribute_pairs[] = "$key=:$key"; // Assign it to an array:
    }

    /*
     * The sql implodes the prepared statement array in the proper format
     * and updates the correct record by id.
     */
    $sql  = 'UPDATE ' . $table . ' SET ';
    $sql .= implode(", ", $attribute_pairs) . ' WHERE customer_id =:customer_id';

    /* Normally in two lines, but you can daisy-chain pdo method calls */
    $pdo->prepare($sql)->execute($customers);

    return true;
}

 

[webdeveloper123]

43 minutes ago, Strider64 said:

    /* Normally in two lines, but you can daisy-chain pdo method calls */

yes i've seen that before, method chaining

thanks strider64