CODEWITHSUNDEEP
phpmysqlpdo
methuselah
methuselah

Reputation: 13216

Correct way to write PDO update statements

Is this the correct way to write a PDO update statement? I based it off a tutorial I saw online but it doesn't seem to be doing anything at all when fired by AJAX (even though the AJAX request is being logged as successful in the console, no update is made to the database):

$duedate = $_POST["duedate"];
$status = $_POST["status"];
$id = $_POST["id"];

$sql = "UPDATE pm_schedule SET duedate=?, status=? WHERE id=?";
$q = $pdo->prepare($sql);
$q->execute(array($duedate,$status,$id));

Upvotes: 2

Views: 266

Answers (3)

Nono
Nono

Reputation: 7302

Here is very good tutorial to understand PDO

And correct way is:

// Named Placeholders
$sSql  = "UPDATE mytable SET FName = :FName, LName = :LName, Age = :Age, Gender = :Gender WHERE ID = :ID;";

// PDO prepared statement
$oSth = $oDbh->prepare($sSql);

// Bind Named Placeholders
$oSth->bindParam(":FName", $FName, PDO::PARAM_STR);
$oSth->bindParam(":LName", $LName, PDO::PARAM_STR);
$oSth->bindParam(":Age", $Age, PDO::PARAM_INT);
$oSth->bindParam(":Gender", $Gender, PDO::PARAM_STR);
$oSth->bindParam(":ID", $ID, PDO::PARAM_INT);

// Execute PDO with condition
if ($oSth->execute()) {
    // Get Affected row
    $iAffectedRows = $oSth->rowCount();
    $oSth->closeCursor();
    return $iAffectedRows;
} else {
    // PDO error, could also be caught in a try/catch statement
    $errorMsg = $oSth->errorInfo();
    error_log(json_encode($errorMsg));
}

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157880

Yes, it's correct, but you need to tell PDO to raise an error on error.
So, make your connection code like this:

$dsn = "mysql:host=$host;dbname=$db;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn, $user, $pass, $opt);

And make sure you can see PHP errors. So, you'll kn ow what's going wrong.

Upvotes: 5

user2047438
user2047438

Reputation: 

$duedate = $_POST["duedate"];
$status = $_POST["status"];
$id = $_POST["id"];

try {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = $pdo->prepare('UPDATE `pm_schedule` SET `duedate` = ?, `status` = ? WHERE `id` = ?');
    $sql->execute(array($duedate, $status, $id));
} catch(PDOException $e) {
    echo $e->getMessage();
}

PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION This helps you find the error

With the try and catch you can get an echo an error message of where and why it fails.

Upvotes: 1

Related Questions