CODEWITHSUNDEEP
phpmysqlpdo
Helder Ferreira
Helder Ferreira

Reputation: 285

Updating table using PDO : Syntax Error

Im making a function to change the password from the table user, but im getting a syntax error

Here is the function:

 public function changepassword($password, $newpassword) {
        $user_id = $_SESSION["userSession"];
        $stmt = $this->db->prepare("SELECT * FROM user WHERE user_id=:user_id");
        $stmt->execute(array(":user_id" => $user_id));
        $userRow = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($password = $userRow['password']) {
            $sql = "UPDATE user set password=:password WHERE user_id=:user_id";
            $stmt2 = $this->db->query($sql);
            $stmt->execute(array(":user_id" => $user_id, ":password" => $password ));
            $stmt2->execute();
            return true;
        } else {
            return false;
        }
    }

This is the function call

if (isset($_POST['btn-save'])) {
    $password = $_POST['password'];
    $newpassword = $_POST['newpassword'];
    $newpassword2 = $_POST['newpassword2'];

    if ($newpassword == $newpassword2) {
        if ($user->changepassword($password, $newpassword)) {
            header("Location: selfedit.php?inserted");
        } else {
            header("Location: selfedit.php?failure");
        }
    } else {
        header("Location: selfedit.php?failurematch");
    }
}

This is the error i get:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':password WHERE user_id=:user_id' at line 1' in C:\xampp\htdocs\aDatabase2\class.user.php:37 Stack trace: #0 C:\xampp\htdocs\aDatabase2\class.user.php(37): PDO->query('UPDATE user set...') #1 C:\xampp\htdocs\aDatabase2\selfedit.php(8): USER->changepassword('fereira', 'umdois') #2 {main} thrown in C:\xampp\htdocs\aDatabase2\class.user.php on line 37

EDIT 1

I changed the functions according to this question comments, getting a different error

public function changepassword($password, $newpassword) {
    $user_id = $_SESSION["userSession"];
    $stmt = $this->db->prepare("SELECT * FROM user WHERE user_id=:user_id");
    $stmt->execute(array(":user_id" => $user_id));
    $userRow = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($password == $userRow['password']) {
        $sql = "UPDATE user set password=:newpassword WHERE user_id=:user_id";
        $stmt2 = $this->db->prepare($sql);
        $stmt2->execute(array(":user_id" => $user_id, ":password" => $newpassword ));
        return true;
    } else {
        return false;
    }
}

New error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined' in C:\xampp\htdocs\aDatabase2\class.user.php:37 Stack trace: #0 C:\xampp\htdocs\aDatabase2\class.user.php(37): PDOStatement->execute(Array) #1 C:\xampp\htdocs\aDatabase2\selfedit.php(8): USER->changepassword('ferreira', 'anotherpass') #2 {main} thrown in C:\xampp\htdocs\aDatabase2\class.user.php on line 37

Upvotes: 1

Views: 704

Answers (1)

Helder Ferreira
Helder Ferreira

Reputation: 285

It works by changing, from EDIT 1, this line:

 $sql = "UPDATE user set password=:newpassword WHERE user_id=:user_id";

To this:

$sql = "UPDATE user set password=:newpassword WHERE user_id=:user_id";

Final Function:

public function changepassword($password, $newpassword) {
    $user_id = $_SESSION["userSession"];
    $stmt = $this->db->prepare("SELECT * FROM user WHERE user_id=:user_id");
    $stmt->execute(array(":user_id" => $user_id));
    $userRow = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($password == $userRow['password']) {
        $sql = "UPDATE user set password=:password WHERE user_id=:user_id";
        $stmt2 = $this->db->prepare($sql);
        $stmt2->execute(array(":user_id" => $user_id, ":password" => $newpassword ));
        return true;
    } else {
        return false;
    }
}

Upvotes: 1

Related Questions