Codeigniter / mySQL. Checking for multiple inserts before actual insertion
So, I'm trying to do a couple of inserts at once that are co-dependent on each other. Let's say I'm building a dog rating site. Anyone can add a dog to my database, but they also need to add a provisional dog rating. Then other people can then rate the dog. Rating dogs are a many-one relationship: a dog has many ratings.
This means that for my pre-add, since I have a bid and add a dog, I need to take the rating and set a foreign key for the dog's primary key. As far as I know, this means that I have to actually add the dog and then check what this new primary key is and then add that to my rank before inserting.
Now, suppose something went wrong with the rating insertion, be it a string that was too long or something that I somehow forgot. If the rating fails, the dog is already inserted, but has no rating. In this case, I would like the dog not to be added in the first place.
Does this mean that I need to write code that says "If the rating fails, do a deletion for the dog" or is there a way to predict what the key will be for the dog, everything should go as planned. Is there a way to say "hold this place" and then if everything works, add it?
Any help would be greatly appreciated. Thanks!!
a source to share
CodeIgniter simplifies this process by using transactions.
http://codeigniter.com/user_guide/database/transactions.html
NOTE:
In MySQL, you will need to work with InnoDB or BDB Table Types, not 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
}
a source to share
Welcome to the land of transactions ! I'm sure you will enjoy your stay because the deals were invented specifically for this problem. :)
Basically, the sequence of events would look something like this MySQL:
START TRANSACTION;
INSERT INTO dogs ...;
INSERT INTO dog_ratings...;
COMMIT;
Now, in your code, you can do all sorts of things in between these requests and complete the transaction at any time with an SQL query ROLLBACK
. When you do this, none of the queries will be saved to the database as part of your transaction. You'll have to make it CodeIgniter specific, but it would basically 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 above code, assuming that $db
is some kind of database wrapper object that will generate DatabaseException
when the request fails, then no records are created if the problem occurs with any of the requests within the deal.
You can find out about transactions there, so google a little. The main thing to note is that with MySQL you need to use InnoDB tables to support transactions. If you are using MyISAM, commands like START TRANSACTION
and will run COMMIT
, but they do nothing.
a source to share