Codeigniter/mySQL question. Checking if several inserts are possible before actual insertion.

Question

Hey SO,

So I'm trying to perform a couple inserts at once that are kind of co-dependent on each other. Let's say I'm doing a dog rating website. Anyone can add a dog to my database, but in doing so they also need to add a preliminary rating of the dog. Other people can then rate the dog afterwards. The dogs to ratings is a many to one relationship: a dog has many ratings.

This means for my preliminary add, since I have the person both rate and add the dog, I need to take the rating, and set a foreign key to the dog's primary key. As far as I know, this means I have to actually add the dog, then check what that new addition's primary key is, and then put that in my rating before insertion.

Now suppose something were to go wrong with the rating's insert, be it a string that's too long, or something that I've overlooked somehow. If the rating's failed, the dog has already been inserted, but the rating has not. In this case, I'd like the dog to not have been added in the first place.

Does this mean I have to write code that says "if the rating fails, do a remove for the dog," or is there a way to predict what the key will be for the dog should everything go as planned. Is there a way to say "hold that spot," and then if everything works, add it?

Any help would be greatly appreciated. Thanks!!

Answer 1

Welcome to the land of transactions! I'm sure you're going to enjoy your stay, because transactions were invented specifically for this problem. :)

Basically, the sequence of events will look something like this to MySQL:

START TRANSACTION;
INSERT INTO dogs ...;
INSERT INTO dog_ratings...;
COMMIT;

Now, in your code, you can do all kinds of stuff in between those queries, and end the transaction at any time using the SQL query ROLLBACK. When you do that, none of the queries as part of your transaction get stored to the database. You'll have to make it CodeIgniter specific, but basically it would look something like this:

$db->query("START TRANSACTION");
try {
    $db->query("INSERT INTO dogs...");
    //did that work?  Sweet.  Run the next one. You can fetch the insert id
    //here too, if you want to use it in the next query.
    $db->query("INSERT INTO dog_ratings...");
    $db->query("COMMIT");
} 
catch (DatabaseException $e)
{
    $db->query("ROLLBACK");
    echo "Problem!  ".$e->getMessage();
}

In the code above, assuming that $db is some kind of database wrapper object that will throw a DatabaseException when a query fails, then no records will get created if a problem is encountered with any of the queries inside the transaction.

There's lots to learn about transactions, so google around a bit. The main thing to note is that with MySQL, you need to be using InnoDB tables in order to support transactions. If you're using MyISAM, commands like START TRANSACTION and COMMIT will run, but they don't do anything.

Answer 2

CodeIgniter simplifies this process using transactions.

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

NOTE:

In MySQL, you'll need to be running InnoDB or BDB table types rather than the more common MyISAM.

$this->db->trans_start();
$this->db->query('INSERT YOUR DOG');
$this->db->query('INSERT YOUR RATING');
$this->db->trans_complete();

if ($this->db->trans_status() === FALSE)
{
    // Something went wrong, but nothing was committed to the database so you can handle the error here
}
else {
    // Everything was added fine
}