Can someone tell me the error in PDO update query

Question

I have an update query, it says success, even redirect page as it has to. But, table row remain unchanged.

my code is as :

The below code for display records as per user_id , it works well:

<?php config.php ?>
<?php $stmt = $conn->prepare("SELECT * FROM user where user_id = :user_id");
$stmt->bindValue('user_id', $_GET['id']);
$stmt->execute();
while($user = $stmt->fetch(PDO::FETCH_OBJ)){
echo "form stuff to echo the fields value" ;
} ?>

Below codes for update values as: which works without the errors but, do not update the records.

if (isset($_POST['save'])) {
error_reporting(E_ERROR | E_PARSE | E_CORE_ERROR);
$sql = $conn->prepare("UPDATE user SET username = :username, password = :password, firstname = :firstname, lastname =:lastname where user_id = :user_id");
$sql->bindParam(":user_id",$_POST["user_id"],PDO::PARAM_INT);
$sql->bindParam(":username",$_POST["username"],PDO::PARAM_STR);
$sql->bindParam(":password",$_POST["password"],PDO::PARAM_STR);
$sql->bindParam(":firstname",$_POST["firstname"],PDO::PARAM_STR);
$sql->bindParam(":lastname",$_POST["lastname"],PDO::PARAM_STR);
if($sql->execute()){
echo "Successfully updated ";
}
else {
echo "Not updated";
} 
} ?>

Please do advice me, what i have made mistakes on my code?

Try to get the error message: http://stackoverflow.com/questions/3999850/pdo-error-message — Michael, Jan 23 '16 at 05:26

score 1 · Answer 1 · answered Jan 23 '16 at 05:48

As for your solution, Please follow two steps:

1. Check actually effected records
You can check records is actually effected by this way using PDOStatement::rowCount:

$count = $sql->rowCount(); 
print($count); 
exit;

at after success message.

2. Check MySql query error
I check that there is no code for check your query has error OR not. You can check this by this way using PDOStatement::errorInfo()

echo "\nPDOStatement::errorInfo():\n";
$arr = $sql->errorInfo();
print_r($arr);

Hope this help you well.

it says: PDOStatement::errorInfo(): Array ( [0] => [1] => [2] => ) As well as, it says 0 Rows affected — Abiral D, Jan 23 '16 at 07:27

score 0 · Answer 2 · answered Jan 23 '16 at 05:30

Try removing those PDO::PARAM_INT , PDO::PARAM_STR stuff . Maybe your input is causing all the damage

if (isset($_POST['save'])) {
error_reporting(E_ERROR | E_PARSE | E_CORE_ERROR);
$sql = $conn->prepare("UPDATE user SET username = :username, password = :password, firstname = :firstname, lastname =:lastname where user_id = :user_id");

$sql->bindParam(":user_id",$_POST["user_id"]);
$sql->bindParam(":username",$_POST["username"]);
$sql->bindParam(":password",$_POST["password"]);
$sql->bindParam(":firstname",$_POST["firstname"]);
$sql->bindParam(":lastname",$_POST["lastname"]);
if($sql->execute()){
echo "Successfully updated ";
}
else {
echo "Not updated";
} 
} ?>

It says successfully updated, but value remain unchanged as previous one. — Abiral D, Jan 23 '16 at 05:33
print_r($this->pdo->errorInfo()); getting any error from here? — Aniruddha Chakraborty, Jan 23 '16 at 05:34

score 0 · Accepted Answer · answered Jan 23 '16 at 08:31

I made change on my code, now it's working perfectly.

Thank you all for your contribution.

working code is :

<?php

                        if (isset($_POST['save'])) {

                            $sql = "UPDATE user SET username = :username, 
                                        password = :password, 
                                        firstname = :firstname,  
                                        lastname = :lastname 
                                        WHERE user_id = :user_id";
                            $stmt = $conn->prepare($sql);                                  
                            $stmt->bindParam(':username', $_POST['username'], PDO::PARAM_STR);       
                            $stmt->bindParam(':password', $_POST['password'], PDO::PARAM_STR);    
                            $stmt->bindParam(':firstname', $_POST['firstname'], PDO::PARAM_STR);
                            // use PARAM_STR although a number  
                            $stmt->bindParam(':lastname', $_POST['lastname'], PDO::PARAM_STR); 
                            $stmt->bindParam(':user_id', $_GET['id'], PDO::PARAM_INT);   
                            if($stmt->execute()){
                                     header ("Location: user.php");

                            }                           
                        }
                        ?>

Can someone tell me the error in PDO update query

3 Answers3