Make two updates at once

Question

I have a table with some records, I want to change two rows of this table at the same time

I’m currently doing this:

include("conexao.php");

$tabela1 = "endereco";

$endereco_id= "75";
$antigo_endereco_id= "73";

$res = $pdo->prepare("UPDATE $tabela1 SET $tabela1.endereco_favorito = 0
WHERE $tabela1.endereco_id = :antigo_endereco_id");
$res->bindParam(':antigo_endereco_id',$antigo_endereco_id);
$res->execute();

$res = $pdo->prepare("UPDATE $tabela1 SET $tabela1.endereco_favorito = 1
WHERE $tabela1.endereco_id = :endereco_id");
$res->bindParam(':endereco_id',$endereco_id);
$res->execute();

But wanted some way to do both updates at the same time, do one after the other is causing problems that sometimes only one works and Leave the database

Answer 1

The problem was solved using transactions ACID database

First enable exceptions in PDO instance:

$this->pdo = new PDO($this->db_host,$this->usuario,$this->senha,array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_EMULATE_PREPARES => false)); 

These are the necessary lines to add:

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false

Then place the required requests inside this block:

try {
    $this->pdo->beginTransaction();

    $db->query('query 1'); //Exemplos
    $db->query('query 2');
    $db->query('query 3');

    $this->pdo->commit();
} catch (Exception $e) {
    $this->pdo->rollback();
}

Changes will only be saved if all operations are successful!

Answer 2

insert one at a time, use last insert id for PDO mysql_insert_id for MYSQLI