PDO prepared statement on UPDATE is not working

Question

I am trying to UPDATE table with a prepared statement, for some reason I have a hard time doing it as I keep getting an error with the statement. I am using prepared statements often and not sure what I am missing.

That is what I got (part of a function):

       $result = array();

    try {

        $sql  = "UPDATE UsersData SET firstName = :firstName, lastName = :lastName, birthDay = :birthDay, aboutMe = :aboutMe, facebookU = :facebookU, twitterU = :twitter, githubU = :github WHERE uId =: id";
        $stmt = $this->connect()->prepare($sql);
        $stmt->execute(array(
            ':firstName' => $userInformation['firstName'],
            ':lastName'  => $userInformation['lastName'],
            ':birthDay'  => $userInformation['birthDay'],
            ':aboutMe'   => $userInformation['aboutMe'],
            ':facebookU' => $userInformation['facebook'],
            ':twitterU'  => $userInformation['twitter'],
            ':github'    => $userInformation['github'],
            ':id'        => $userInformation['id']
        ));
        $result = true;

    } catch (\PDOException $e) {
        $result = "Error: " . $e->getMessage();
    }
    return $result;

I get

Fatal error: Uncaught Error: Call to a member function execute() on bool in

On line

$stmt->execute(array(

I tried as well:

   $result = array();

    try {

        $sql = "UPDATE UsersData AS UD, Users AS US
        SET
        UD.firstName=:firstName,
        UD.lastName=:lastName,
        UD.birthDay=:birthDay,
        UD.aboutMe=:aboutMe,
        UD.facebookU=:facebookU,
        UD.twitterU=:twitter,
        UD.githubU=:github
        US.uEmail=:email
        WHERE UD.uId=:id AND US.id=:id";
        $stmt = $this->connect()->prepare($sql);
        $stmt->execute(array(
            ':firstName' => $userInformation['firstName'],
            ':lastName'  => $userInformation['lastName'],
            ':birthDay'  => $userInformation['birthDay'],
            ':aboutMe'   => $userInformation['aboutMe'],
            ':facebookU' => $userInformation['facebook'],
            ':twitterU'  => $userInformation['twitter'],
            ':github'    => $userInformation['github'],
            ':id'        => $userInformation['id']
        ));
        $result = true;

    } catch (\PDOException $e) {
        $result = "Error: " . $e->getMessage();
    }
    return $result;

Got the same error.

I found a solution but I am not sure why it would work like that and not the other ways:

$sql  = "UPDATE UsersData SET firstName =?, lastName =?, birthDay =?, aboutMe =?, facebookU =?, twitterU =?, githubU =? WHERE uId = ?";
        $stmt = $this->connect()->prepare($sql);
        $stmt->execute([
            $userInformation['firstName'],
            $userInformation['lastName'],
            $userInformation['birthDay'],
            $userInformation['aboutMe'],
            $userInformation['facebook'],
            $userInformation['twitter'],
            $userInformation['github'],
            $userInformation['id']
        ]);

Thank in advance!

Your `prepare` failed (`: id` should be `:id`). You need to look at `$this->connect()->errorInfo`. — Nick, Apr 19 '20 at 07:36
Thanks, but still getting this error: Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in *path* on line 560 — , Apr 19 '20 at 07:55
You have a parameter called `:twitter` but you are setting `:twitterU` in the inputs. You need to look for *all* of these issues... — Nick, Apr 19 '20 at 07:57

Repox · Accepted Answer · 2020-04-19T08:03:51.357

0

Your prepare statement fails and results in a boolean value instead of a resource.

You're missing a comma in you query, try the following:

UPDATE
  UsersData AS UD,
  Users AS US
SET
  UD.firstName = :firstName,
  UD.lastName = :lastName,
  UD.birthDay = :birthDay,
  UD.aboutMe = :aboutMe,
  UD.facebookU = :facebookU,
  UD.twitterU = :twitter,
  UD.githubU = :github,
  US.uEmail = :email
WHERE
  UD.uId = :id
  AND US.id = :id

edited Apr 19 '20 at 08:03

answered Apr 19 '20 at 07:38

Repox

15,015
8
54
79

Are you sure about that? – Nick Apr 19 '20 at 07:42

PDO prepared statement on UPDATE is not working

1 Answers1