It isn’t enough to self teach
In this post I explain why we need proper lessons on technologies and development methods instead of just relying on what you have figured out yourself. I begin to understand transaction management and how to apply this to PHP/MYSQL.
I had heard before about transactions and rollbacks, and hadn’t given it much more thought. I have for some time been creating web applications which do multiple SQL statements and always live in terror of something breaking, especially because I do a mysql_query() or die(mysql_error()); yes, this tells me the error that happened, but if this was in a sequence of queries, the state of that record would be half complete. A manual fix would be required.
During one of my lectures at uni the concept of transaction management was brought up, and it started to make sense. Either the whole thing works, or nothing works. Sounds strange? But its great because you always know the state of a systems records, it’s either one way or another.
If something doesn’t work, some part of it fails, you want the record to be restored to the original state and not be half changed, this will allow you to fix the problem, then try again from the original data.
Now, I was wondering how to do this in PHP (my primary language) and during some work I was proposing for BioWare Mythic one of their team requested I use PDO (PHP Data Objects), I had never heard about this before so I quickly googled and found out about this. PDO allows you to sanitize queries and is much safer, and also it allows you to use transaction management, you sanitize the query by defining the allowed data types, and then begin the transactions within a try-catch, and on an exception you can rollback the affected rows.
I have added PDO to the proposed project for BioWare Mythic and understand the basics of this, now I just need to work on some real world examples of the transaction management.