Jump to content

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


Go to solution Solved by dodgeitorelse3,

Recommended Posts

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 by webdeveloper123
Link to comment
Share on other sites

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.

  • Great Answer 1
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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;
}

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.