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

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}

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.