CODEWITHSUNDEEP
phpcodeigniter
eli
eli

Reputation: 313

CodeIgniter Transaction not working properly

I have this function in my model that is supposed to run two queries in a transaction, but the update query is not working.

public function delete($id = null) 
{
    if($id) {
        $delete = "DELETE FROM borrowed_books 
        WHERE id = '$id'; ";

        $mod="UPDATE `books` b
              INNER JOIN `borrowed_books` a
              SET b.nr_copies=b.nr_copies+1
              WHERE b.id_book=a.id_book AND a.id = '$id'; ";

        $this->db->trans_start();
        $this->db->query($delete);
        $this->db->query($mod);
        $this->db->trans_complete();
        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }   

}

Upvotes: 0

Views: 669

Answers (3)

Kevin Yan
Kevin Yan

Reputation: 1236

INNER JOIN can't find a data out that you already deleted in first step of your transaction, you need query the specific borrowed_books data and save it in a variable before you delete it. By the way I never saw people use UPDATE like this UPDATE .... INNER JOINin sql, and it make your code so confused, I change your code a little, I think it is more understandable.

public function delete($id = null)
{
    if ($id) {
        $borrowedBook = $this->db->query('SELECT * FROM borrowed_books WHERE id=' . $id);

        $delete = "DELETE FROM borrowed_books 
        WHERE id = '$id'; ";


        $mod = "UPDATE books SET nr_copies = nr_copies + 1 WHERE id_book = " . $borrowedBook->id_book;

        $this->db->trans_start();
        // execute the update
        $this->db->query($mod);
        // then delete
        $this->db->query($delete);
        $this->db->trans_complete();
        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }   
}

Upvotes: 0

ckkaqa
ckkaqa

Reputation: 181

A quickfix might be executing the update before deleting;

public function delete($id = null)
{
    if ($id) {
        $delete = "DELETE FROM borrowed_books 
        WHERE id = '$id'; ";

        $mod="UPDATE `books` b
              INNER JOIN `borrowed_books` a
              SET b.nr_copies=b.nr_copies+1
              WHERE b.id_book=a.id_book AND a.id = '$id'; ";

        $this->db->trans_start();
        // execute the update
        $this->db->query($mod);
        // then delete
        $this->db->query($delete);
        $this->db->trans_complete();
        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }   
}

The update query condition; it is looking for data which is not existent because you deleted it. So if you do the update (then your query finds result assuming that the condition is correct), it will update..

Upvotes: 0

Brian Gottier
Brian Gottier

Reputation: 4582

It would be impossible to update something that isn't there. In the first query, your delete query, you delete from the borrowed_books WHERE id = '$id', but then right after that you are trying to update the very borrowed_books record(s) with the same ID, and a.id = '$id'. It won't update because there is nothing to update.

To make clear, in the second query, a.id is referring to the id of the borrowed_books table, yet in the delete query (the first query), you just deleted that very same borrowed_books record, so there is nothing to join.

Upvotes: 2

Related Questions