How to use PHP Objects and Transaction Control in MySQL?

1

I have a database with some tables, and I've created a class for handling each table in the bank, one example is one class for requests and another for Orders . Each class handles your table, and when a class needs to write data to another table, I make an instance of it to be able to change the data, which I have been able to do without any problems.

My problem started when I was separating some things that were being held together, for example the requests class was writing the items directly into the orderedItems table. And from this point on, I do not know how to proceed. The transaction control I perform works perfectly when I create all statements within the same object:

private function inserir()
{
    if( !$this->verificaDados() ):
        return false;
    endif;

    $this->getCon();
    try{
        $this->con->beginTransaction();
        $s = $this->con->prepare( "INSERT INTO pedidos(cliente, total, situacao, data) VALUES(:cliente, :total, :situacao, now())" );
        $s->bindValue( ':cliente', $this->cliente->getId() );
        $s->bindValue( ':total', $this->total );
        $s->bindValue( ':situacao', $this->situacao );
        $s->execute();

        $pedido = $this->con->lastInsertId();

        $st = $this->con->prepare( "INSERT INTO itenspedido(pedido, produto, quantidade, preco, subtotal) VALUES(:pedido, :produto, :quantidade, :preco, :subtotal)" );

        foreach( $this->itens as $item ):
            $st->bindValue( ':pedido', $pedido );
            $st->bindValue( ':produto', $item->getProduto()->getId() );
            $st->bindValue( ':quantidade', $item->getQuantidade() );
            $st->bindValue( ':preco', str_replace( ',', '.', $item->getPreco() ) );
            $st->bindValue( ':subtotal', str_replace( ',', '.', $item->getTotal() ) );
            $st->execute();
        endforeach;

        $this->con->commit();

        return true;
    }catch( PDOException $e ){
        echo 'Erro: ' . $e;
        $this->con->rollBack();

        return false;
    }
}

But the point is that I want each class to manipulate its own table, but I do not know how to put the transaction control so that it works together with other objects:

try{
    $this->con->beginTransaction();
    $s = $this->con->prepare( "INSERT INTO pedidos(cliente, total, situacao, data) VALUES(:cliente, :total, :situacao, now())" );
    $s->bindValue( ':cliente', $this->cliente->getId() );
    $s->bindValue( ':total', $this->total );
    $s->bindValue( ':situacao', $this->situacao );
    $s->execute();

    $pedido = $this->con->lastInsertId();

        foreach( $this->itens as $item ):
            $item->setPedido($pedido);
            $item->salvar();
        endforeach;

        $this->con->commit();

        return true;
    }catch( PDOException $e ){
        echo 'Erro: ' . $e;
        $this->con->rollBack();

        return false;
    }

Within each save method, I also have beginTransaction (), commit (), and rollBack () statements. The request should not be saved if insertion of one of the order items failed.

Could anyone help solve this problem?

How would I implement transaction control in MySQL having influence on the inserts of other classes within the transaction.

Thanks for any help you can give me.

    
asked by anonymous 04.10.2016 / 16:31

0 answers