Updating user info by using an user id with PDO

Question

I'm trying to update existing user information in my database but no matter what I try it won't execute.

Code :

public function updateUser()
{
    $this->userId = $_SESSION['user_id'];
    var_dump($this->userId);
    include "DBConnect.php";
    $updateUser = $conn->prepare("UPDATE `users` SET 
    `user_username`=:user_name,`user_password`=:user_password,
    `user_firstname`=:user_firstname,`user_middlename`=:user_middlename,
    `user_lastname`=:user_lastname,`user_country`=user_country,
    `user_city`=:user_city,`user_street`=:user_street,
    `user_housenumber`=:user_housenumber,`user_postalcode`=:user_postalcode
    ,`user_email`=:user_email,`user_phone`=:user_phone 
    WHERE `user_id` =:user_id");
    $updateUser->bindParam(':user_id', $_SESSION['user_id']);
    $updateUser->bindParam(':user_name', $this->userName);
    $updateUser->bindParam(':user_password', $this->userPassword);
    $updateUser->bindParam(':user_firstname', $this->userFirstName);
    $updateUser->bindParam(':user_middlename', $this->userMiddleName);
    $updateUser->bindParam(':user_lastname', $this->userLastName);
    $updateUser->bindParam(':user_country', $this->userCountry);
    $updateUser->bindParam(':user_city', $this->userCity);
    $updateUser->bindParam(':user_street', $this->userStreet);
    $updateUser->bindParam(':user_housenumber', $this->userHouseNumber);
    $updateUser->bindParam(':user_postalcode', $this->userPostalCode);
    $updateUser->bindParam(':user_email', $this->userEmail);
    $updateUser->bindParam(':user_phone', $this->userPhone);

    var_dump($this->userId);
    var_dump($this->userName);
    var_dump($this->userPassword);
    var_dump($this->userFirstName);
    var_dump($this->userMiddleName);
    var_dump($this->userLastName);
    var_dump($this->userCountry);
    var_dump($this->userCity);
    var_dump($this->userStreet);
    var_dump($this->userHouseNumber);
    var_dump($this->userPostalCode);
    var_dump($this->userEmail);
    var_dump($this->userPhone);

    $updateUser->execute();
}

full error :

string(1) "9" string(1) "9" string(4) "test" string(4) "test" string(4) "test" string(4) "test" string(4) "test" string(4) "test" string(4) "test" string(4) "test" string(2) "21" string(4) "test" string(4) "test" string(8) "12345678" 
Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /Applications/MAMP/htdocs/webshop/classes/User.php:279 Stack trace: #0 /Applications/MAMP/htdocs/webshop/classes/User.php(279): PDOStatement->execute() #1 /Applications/MAMP/htdocs/webshop/adminpanel/userdashboard.php(23): User->updateUser() #2 {main} thrown in /Applications/MAMP/htdocs/webshop/classes/User.php on line 279

The error is pretty clear.. the number of PDO name variables in the query is not the same as you binding with `bindParam()` — Raymond Nijland, Mar 08 '19 at 10:24
indeed @Pupil this edit will directly show the topicstarter which one is missing.. Topicstarter SQL is meant to be readed, writing SQL as a "oneliner" without good formatting is 9 out of the 10 times trouble. — Raymond Nijland, Mar 08 '19 at 10:27

score 1 · Answer 1 · answered Mar 08 '19 at 10:28

I found it, you just missed a colon to add to user_country,Try this

{
    $this->userId = $_SESSION['user_id'];
    var_dump($this->userId);
    include "DBConnect.php";
    $updateUser = $conn->prepare("UPDATE `users` SET 
    `user_username`=:user_name,`user_password`=:user_password,
    `user_firstname`=:user_firstname,`user_middlename`=:user_middlename,
    `user_lastname`=:user_lastname,`user_country`=:user_country,
    `user_city`=:user_city,`user_street`=:user_street,
    `user_housenumber`=:user_housenumber,`user_postalcode`=:user_postalcode
    ,`user_email`=:user_email,`user_phone`=:user_phone 
    WHERE `user_id` =:user_id");
    $updateUser->bindParam(':user_id', $_SESSION['user_id']);
    $updateUser->bindParam(':user_name', $this->userName);
    $updateUser->bindParam(':user_password', $this->userPassword);
    $updateUser->bindParam(':user_firstname', $this->userFirstName);
    $updateUser->bindParam(':user_middlename', $this->userMiddleName);
    $updateUser->bindParam(':user_lastname', $this->userLastName);
    $updateUser->bindParam(':user_country', $this->userCountry);
    $updateUser->bindParam(':user_city', $this->userCity);
    $updateUser->bindParam(':user_street', $this->userStreet);
    $updateUser->bindParam(':user_housenumber', $this->userHouseNumber);
    $updateUser->bindParam(':user_postalcode', $this->userPostalCode);
    $updateUser->bindParam(':user_email', $this->userEmail);
    $updateUser->bindParam(':user_phone', $this->userPhone);

    var_dump($this->userId);
    var_dump($this->userName);
    var_dump($this->userPassword);
    var_dump($this->userFirstName);
    var_dump($this->userMiddleName);
    var_dump($this->userLastName);
    var_dump($this->userCountry);
    var_dump($this->userCity);
    var_dump($this->userStreet);
    var_dump($this->userHouseNumber);
    var_dump($this->userPostalCode);
    var_dump($this->userEmail);
    var_dump($this->userPhone);

    $updateUser->execute();
}

score 1 · Answer 2 · answered Mar 08 '19 at 10:28

1

Find line:

user_lastname`=:user_lastname,`user_country`=user_country,

Replace by:

user_lastname`=:user_lastname,`user_country`=:user_country,

The missing semi colon before user_country is not binding it the SQL query.

answered Mar 08 '19 at 10:28

Pupil

23,834
6
44
66

score 0 · Answer 3 · answered Mar 08 '19 at 11:12

Your issue is a typo error for user_name and user_country and session may not have been initialized. Finally you should check all variable for spellings, typo, colon, comma errors etc.

Below is just my findings from your code

First You have

`user_username`=:user_name,

instead of

`user_name`=:user_name,

secondly

you have(missing colon here)

 `user_country`=user_country,

instead of

 `user_country`=:user_country,

thirdly user_id is session based and I did not see where you initialized session in your code

session_start();

Try this code

public function updateUser()
{
session_start();
    $this->userId = $_SESSION['user_id'];
    var_dump($this->userId);
    include "DBConnect.php";

    $updateUser = $conn->prepare("UPDATE `users` SET 
    `user_name`=:user_name,
    `user_password`=:user_password,
    `user_firstname`=:user_firstname,
    `user_middlename`=:user_middlename,
    `user_lastname`=:user_lastname,
    `user_country`=:user_country,
    `user_city`=:user_city,
    `user_street`=:user_street,
    `user_housenumber`=:user_housenumber,
    `user_postalcode`=:user_postalcode
    ,`user_email`=:user_email,
    `user_phone`=:user_phone WHERE `user_id` =:user_id");

    $updateUser->bindParam(':user_id', $_SESSION['user_id']);
    $updateUser->bindParam(':user_name', $this->userName);
    $updateUser->bindParam(':user_password', $this->userPassword);
    $updateUser->bindParam(':user_firstname', $this->userFirstName);
    $updateUser->bindParam(':user_middlename', $this->userMiddleName);
    $updateUser->bindParam(':user_lastname', $this->userLastName);
    $updateUser->bindParam(':user_country', $this->userCountry);
    $updateUser->bindParam(':user_city', $this->userCity);
    $updateUser->bindParam(':user_street', $this->userStreet);
    $updateUser->bindParam(':user_housenumber', $this->userHouseNumber);
    $updateUser->bindParam(':user_postalcode', $this->userPostalCode);
    $updateUser->bindParam(':user_email', $this->userEmail);
    $updateUser->bindParam(':user_phone', $this->userPhone);

    var_dump($this->userId);
    var_dump($this->userName);
    var_dump($this->userPassword);
    var_dump($this->userFirstName);
    var_dump($this->userMiddleName);
    var_dump($this->userLastName);
    var_dump($this->userCountry);
    var_dump($this->userCity);
    var_dump($this->userStreet);
    var_dump($this->userHouseNumber);
    var_dump($this->userPostalCode);
    var_dump($this->userEmail);
    var_dump($this->userPhone);

    $updateUser->execute();
}

Alternatively.

Instead of using all this bind bind bind....... you can just use array method. Let me know if there are more issues

public function updateUser()
{

//initialized session if you have not done so
session_start();
    $this->userId = $_SESSION['user_id'];
    var_dump($this->userId);
    include "DBConnect.php";


$updateUser = $conn->prepare("UPDATE `users` SET 
    `user_name`=:user_name,
    `user_password`=:user_password,
    `user_firstname`=:user_firstname,
    `user_middlename`=:user_middlename,
    `user_lastname`=:user_lastname,
    `user_country`=:user_country,
    `user_city`=:user_city,
    `user_street`=:user_street,
    `user_housenumber`=:user_housenumber,
    `user_postalcode`=:user_postalcode
    ,`user_email`=:user_email,
    `user_phone`=:user_phone WHERE `user_id` =:user_id");




$updateUser->execute(array(
 ':user_id' =>  $_SESSION['user_id'],
 ':user_name' => $this->userName,
 ':user_password'=> $this->userPassword,
 ':user_firstname'=> $this->userFirstName, 
 ':user_middlename'=> $this->userMiddleName, 
 ':user_lastname'=> $this->userLastName, 
 ':user_country'=> $this->userCountry, 
 ':user_city'=> $this->userCity, 
 ':user_street'=> $this->userStreet, 
 ':user_housenumber'=> $this->userHouseNumber, 
 ':user_postalcode'=> $this->userPostalCode,  
 ':user_email'=> $this->userEmail, 
 ':user_phone'=> $this->userPhone,

));


}

One **big** downside is that `execute()` can only bind as `PDO::PARAM_STR` so it's not ideal trusting on the database to correctly auto casting the data.. — Raymond Nijland, Mar 08 '19 at 11:16

Updating user info by using an user id with PDO

3 Answers3