CodeIgniter - Multiple table insert using transactions

Question

Im currently following a tutorial on creating a forum in PHP / MySQL ... and im trying to implement it into my CodeIgniter project.

I have hit a snag that i have never dealt with before, transactions ... I have read the documentation on codeigniters transactions, but im not really understanding it considering the code i need to convert.

I was wondering if someone could take the code below and turn it into codeigniters transaction code for me, i have tried doing it myself but its using multiple tables and i just get completely confused.

Any help would be great, code is below:

$query = "BEGIN WORK;";
$result = mysql_query($query)

if(!$result) {
    echo 'An error has occured';
} else {
    $sql = "INSERT INTO topics(t_subeject,date,cat) VALUES ($_POST['subject'],NOW(),$_POST['cat'])";
    $result = mysql_query($sql);

    if(!$result) {
        echo 'An error has occured';
        $sql = "ROLLBACK;";
        $result = mysql_query($query)
    } else {
        $topid = mysql_insery_id();
        $sql = "INSERT INTO posts(content, date) VALUES ($_POST['content'],NOW())";
        $result = mysql_query($sql);

        if(!$result) {
            echo 'An error has occured';
            $sql = "ROLLBACK;";
            $result = mysql_query($sql);
        } else {
            $sql = "COMMIT;";
            $result = mysql_query($sql);
            echo 'Insert successful!';
        }
    }
}

Kumar V · Accepted Answer · 2014-04-12 09:41:47Z

2

If an INSERT failed, it will do a ROLLBACK automatically. That's why you're using transactions in the first place. No need to explicitly check for it. So in the end, this should be all there is to it:

$this->db->trans_start();
$this->db->query("INSERT INTO topics(t_subeject,date,cat) VALUES ($_POST['subject'],NOW(),$_POST['cat'])");
$topid=$this->db->insert_id();
$this->db->query("INSERT INTO posts(content, date) VALUES ($_POST['content'],NOW())");
$this->db->trans_complete();

...from within your model class. So if the first INSERT already fails, it won't execute the second.

http://ellislab.com/codeigniter/user_guide/database/transactions.html

edited Apr 12, 2014 at 9:41

Kumar V

8,8289 gold badges44 silver badges60 bronze badges

answered Nov 17, 2012 at 12:26

DanMan

11.6k4 gold badges43 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BigJobbies Over a year ago

Hey, thanks for that .. that makes things a little easier, after the first query, how would i go about getting the mysql_insert_id, also if the first query fails will it still run the second one?

DanMan Over a year ago

You're welcome. And make sure your table engine supports transactions. MyISAM does not, InnoDB does.

BigJobbies Over a year ago

haha yeh i found that out the hard way, took me a few hours of staring at the screen hehe

Collectives™ on Stack Overflow

CodeIgniter - Multiple table insert using transactions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related