Update Query with Pdo and MySQL

MySQL update using PDO with variable

The syntax for UPDATE with PDO is SET col=:col

Using the column name, followed by the equal sign and the named placeholder.

$PDO = $PDO->prepare( 'UPDATE users SET name = :name, email = :email, 
                       telefone = :telefone, endereco = :endereco, 
                       numero = :numero, bairro = :bairro, cidade = :cidade 
                       WHERE email = :email' ); 

PDO error handling would have clearly shown you the errors:

• https://secure.php.net/manual/en/pdo.error-handling.php

Use error reporting also.

• https://php.net/manual/en/function.error-reporting.php

This assuming that the column names are of the same names that I used here.

However and as stated, why use the $_REQUEST's? Just use the variables that you assigned in the POST arrays, and assuming that your form is using a post method.

$PDO->bindValue(':name', $name);
$PDO->bindValue(':email', $email);
$PDO->bindValue(':telefone', $telefoneHash);
$PDO->bindValue(':endereco', $telefone);
$PDO->bindValue(':numero', $numero);
$PDO->bindValue(':bairro', $bairro);
$PDO->bindValue(':cidade', $cidade);
$PDO->execute();

Update query with PDO and MySQL

1. Your UPDATE syntax is wrong
2. You probably meant to update a row not all of them so you have to use WHERE clause to target your specific row

Change

UPDATE `access_users`   
      (`contact_first_name`,`contact_surname`,`contact_email`,`telephone`) 
      VALUES (:firstname, :surname, :telephone, :email)

to

UPDATE `access_users`   
   SET `contact_first_name` = :firstname,
       `contact_surname` = :surname,
       `contact_email` = :email,
       `telephone` = :telephone 
 WHERE `user_id` = :user_id -- you probably have some sort of id 

case when in update query MySQL PDO

dont try to assign the value to your column inside the CASE WHEN statements since you are already doing that.

the CASE WHEN will evaluate to the value that satisfies the condition.

try this code

UPDATE payments SET 
 total = :total,
 paid = (CASE WHEN paid > :new THEN :new ELSE paid END),
 due = (CASE WHEN paid < :new THEN (:new - paid) ELSE due END)
 WHERE id = :id 

I removed the assignments to paid and due columns inside the case statement.

php mysql PDO update

You have written wrong query.
Below is the corrected query:

$q_ins="UPDATE myguests SET firstname=?, lastname=?,email=? WHERE id=?"; 
$ins=$dbh->prepare($q_ins);

Read php PDO manual.

PHP PDO Prepared Update Statement where =

1.You need to execute first ($stmt->execute()) and then fetch count ($stmt->rowCount()).

2.UPDATE query don't return records after successful execution, it just return number of affected rows. So use rowCount() to get number of affected rows.

Check below correct code:-

$stmt = $conn->prepare("UPDATE site_users SET users_email_verified = :users_email_verified WHERE users_email = :users_email AND users_password = :users_password and users_email_verified = :users_email_not_verified ");

$users_email_verified = 1;
$users_email_not_verified = 0;

$stmt->bindParam(':users_email_not_verified', $users_email_not_verified,PDO::PARAM_STR);
$stmt->bindParam(':users_email_verified', $users_email_verified,PDO::PARAM_STR);
$stmt->bindParam(':users_email',$_GET["email"],PDO::PARAM_STR);
$stmt->bindParam(':users_password',$_GET["token"],PDO::PARAM_STR);

// The next 2 lines are supposed to count total number of rows effected

$stmt->execute();
$result = $stmt->rowCount();
echo $result;

PHP, MySQL, PDO - Get result from UPDATE query?

You need to do the SELECT @update_id as a separate query -- you can't put multiple queries in a single statement. So do:

$sql = "SET @update_id := '';
        UPDATE testing SET status='1', id=(SELECT @update_id:=id)
        WHERE status='0' LIMIT 1";
try{
    $db->beginTransaction();
    $db->query($sql); // no need for prepare/execute since there are no parameters
    $stmt = $db->query("SELECT @update_id");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $id = $row['@update_id'];
    $db->commit();
} catch (Exception $e) {
    echo $e->getMessage();
    $db->rollBack();
    exit();
}

SQL query to update item quantity using PHP PDO

To add to @TangentiallyPerpendicular's comment, why are you binding to :orderedQuantity? This variable is not being used in your SQL statement, even though you have told the SQL engine to expect the variable. The column doesn't need to be a variable in order pass a variable to it.

PHP PDO MySQL Correct way to check if an update query succeeded when no rows are affected

The execute() method will either throw an exception or return FALSE (depending on the error mode you have set for the database connection) when the execution of a SQL statement fails.

If we set the error mode to throw an exception, prior to executing a statement, (usually immediately after establishing a database connection), like this

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Then we can use a try/catch block to handle an exception thrown by a SQL statement. Something like this:

try {
  $query->execute(...);
  echo 'Update succeeded';

} catch(PDOException $e) {
  echo 'Update failed!';
  echo 'Error: ' . $e->getMessage();
}

Information about error mode settings and handling is available in the documentation here: http://php.net/manual/en/pdo.error-handling.php

---

Or, if PDO isn't set to throw an exception, we can use a simple if test. (The execute() method will return FALSE if the the statement fails.)

if ($query->execute(...)) {
   echo 'Update succeeded';

} else {
   echo 'Update failed!';

}

For more precise control with the different types of failure, we can use the errorCode() method to retrieve the SQLSTATE associated with the last operation on the statement handle, and we can perform conditional tests on the returned value. http://php.net/manual/en/pdostatement.errorcode.php

---

Related Topics