CODEWITHSUNDEEP
phpmysqlpdo
blackandorangecat
blackandorangecat

Reputation: 1344

PHP MySQL Update returns success, but no rows are changed

I have been trying to update my database through PHP but it's not working. Here is the function I am using. This is all PHP

function update($query, $values = "") {

        $success = false;
        $statement = $this->db->prepare($query);

        if (is_array($values)) {
            $success = $statement->execute($values);
            print("\nIt is an array");
        } else {
            $success = $statement->execute();
        }

        $statement->closeCursor();
        return $success;
}

And here is how I am calling it.

$data=array();
    $data[] = $FirstName;
    $data[] = $LastName;
    $data[] = $Email;
    $data[] = $AccessLevel;
    $data[] = $UserID;

try{
    $thisDatabase->db->beginTransaction();
    $query ="UPDATE Users SET FirstName=?, LastName=?, ContactEmail=?, AccessLevel=? WHERE UserID=?";
    $userData = $thisDatabase->update($query,$data);
}catch (PDOExecption $e){
    $thisDatabase->db->rollback();
    print "There was a problem with the query";
}

This function returns 1 (when I print out $userData).

Here is the $data array that I am passing it.

Array(
    [0] => Jon
    [1] => Smith
    [2] => [email protected]
    [3] => 1
    [4] => 2
)

If I run the query in PHPmyadmin it works. Note: I do have to add quotes to the columns stored as strings. UPDATE Users SET FirstName='Jon', LastName='Smith', ContactEmail='[email protected]', AccessLevel=1 WHERE UserID=2

So, does anyone have any ideas where my issue is or how I can go about finding it?

For reference, I use a similar function for select statements, and it works perfectly.

function select($query, $values = "") {

        $statement = $this->db->prepare($query);

        if (is_array($values)) {
            $statement->execute($values);
        } else {
            $statement->execute();
        }
        $recordSet = $statement->fetchAll(PDO::FETCH_NUM);

        $statement->closeCursor();
        return $recordSet;
}

Upvotes: 1

Views: 264

Answers (1)

Bartosz Zasada
Bartosz Zasada

Reputation: 3900

You're not committing your transaction. Therefore, changes that you make with your UPDATE are not being actually saved in the database.

Add $thisDatabase->db->commit() after the try...catch block. This, of course, shouldn't be called if the transaction needs to be rolled back, so return from the function in the catch block, or add a flag.

Upvotes: 2

Related Questions