Make two updates at once

Asked

Viewed 79 times

1

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

  • 1

    Have you ever thought of executing them within a transaction?

  • Could you give me more information about this ? I’m new in the database area :)

  • See: https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html

2 answers

0


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!

0

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

  • But in my case I was wanting to do UPDATE, I would like to do both at once so there is no chance to modify only one thing and the other go wrong!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.