relatively complex php/mysql queries

[tryan]

I need to write to 5 related mySQL tables from one form, using php.

First, I need to write some of the data to one of the tables so that I can extract (SELECT) the auto_incrementing primary key to use as a foreign key in the next two INSERTS.

Then I have to extract (SELECT) the auto_incrementing primary key from one of those tables to use as a foreign key in the last tables.

But if I get a failure part way through this operation, I could have a partial write and my data integrity is not just compromised... it's just plain bunged up!

What are some strategies that can be used to prevevnt this from happening?

Tom

[henry0]

Could you check at each step?
if...ok then carry on etc...

else problem message

But your problem is dealing with partial data
So instead of focusing on what's happen after submission
Why do not check the form for correctness before submitting

[tryan]

Part B:

I extract the primary key that will be used as a foreign key like this:

SELECT MAX(column_name) as for_key FROM table1

Then INSERT for_key into the second table.

But if two folks out there on the internet 'submit' almost simultaneously, the second submitter may end up with the first submitter's MAX(column_name) and my data will be further bunged-up.

Same question: how, in general, does one avoid this sort of unlikely, but possible, situation? Or does mySQL prevent this by only allowing one connection at a time, or something else like that?

Make sense?

Tom

(maybe this should be a separate topic...)

[tryan]

But your problem is dealing with partial data
So instead of focusing on what's happen after submission
Why do not check the form for correctness before submitting

Exactly. But it's not just data verification that could cause a problem. I've taken care of that. I'm thinking, if a network or server goes down during execution.

Or some problem I didn't find while testing. It wouldn't be the first time that's happened.

Thanks for responding.

Tom

[httpwebwitch]

Yes, you will need to make sure your SQL queries are squeaky-clean and will work in every possible situation. Test for data containing \ and " and ' and , and even try putting some japanese characters in there

‰Ž¶┐¿

check for the proper data type (no strings going into your integer fields), string length, valid date syntax, NULLness, etc.

if you're still worried about your database messing up halfway through a process, then check it all when it's done.
You'll still have all the incrementing ID numbers handy, right? So do a few SELECTs to make sure it all arrived safely, and if you find any of the records are missing, DELETE them all or deal with them in some other way.

good luck

[coopster]

Welcome to WebmasterWorld, tryan!

If I understand you correctly, you are describing transactional processing (START TRANSACTION, COMMIT or ROLLBACK). Basically what happens is you START a TRANSACTION, run as many SQL statements as necessary, and if you reach the end successfully, COMMIT the entire bunch. If you don't COMMIT, you can explicitly set a ROLLBACK which puts everything back to where it was before you started and you would send the message stating so.

[httpwebwitch]

Don't grab your ID fields using MAX on the column... You pretty much described the problem yourself, it's a bad, bad habit. use mysql_insert_id() instead.

If your server is hit by a tornado half way through a process, it's not even going to be alive to do a final check. That's when a recent backup of your database will be handy.

Why not store a random checksum along with each step? the process could write an MD5 string like "d2jrl63k72dg3ds72ll6s3" at each step along with the data. Then when you reboot and clean up the debris, you can check if any of the records "don't match up"

and... install a UPS and some lightning rods. :)

[coopster]

Although PHP supplies us with the mysql_insert_id() [php.net] function, we may be better of not using it. There is a warning in the manual regarding certain column types and a known issue, therefore I use the internal MySQL SQL function LAST_INSERT_ID() [dev.mysql.com] in an SQL query instead. Just a heads up -- coopster

[httpwebwitch]

Good point from coopster, as usual. A transactional database like Oracle is essential for financial applications.

[coopster]

You don't necessarily need Oracle, MySQL supports transactions as well.

Transactions and Atomic Operations [dev.mysql.com]
START TRANSACTION, COMMIT, and ROLLBACK Syntax [dev.mysql.com]

[httpwebwitch]

really? I had no idea. I have some reading to do... (httpwebwitch goes in search of a MySQL manual)

[coopster]

LOL. Yeah, it's a common misconception. MySQL started out as a lightweight database, but the more companies started embracing the rather inexpensive (and fast) server, they wanted more and more out of it. MySQL obliged. Although the developers don't want to trade speed for features, they do want to keep folks using their database!

As the link above states, MySQL Server (version 3.23-max and all versions 4.0 and above) supports transactions with the InnoDB and BDB transactional storage engines. InnoDB provides full ACID compliance. However, you can simulate transactions with non-transactional (ISAM, MyISAM) tables. Keep these functions in an abstraction layer and when it's time for you to move to transactional tables, your coding will be a breeze.

[henry0]

Coopster
do you ref to
[us4.php.net...] for exmample

trying to grasp the abstraction layer concept

thanks

Henry

[coopster]

Yes, somewhat. Same concept anyway. Let's say you want to use transaction processing but you aren't on a database server that supports transactions. You are going to have to roll your own code then, right? But you are thinking at some point it sure would be nice if I could move to that someday. So, create your own script that performs the "transaction". This separates that particular layer of database-interaction code from being embedded all over the place in your application -- a layer if you will.

A small example, (Disclaimer: nowhere near complete), but should give you the concept...

To simulate transactions, you might use table-locking and your own error-handling, so you create a script that will use dynamic variables to accomplish the task at hand, keep the script/function separate so that you can use it from all over the place in different scripts as required.

$sql = "LOCK TABLES $table1 $action1, $table2 $action2"; 
// of course, this next line would be another asbtraction layer, 
// like the dba functions you mentioned 
mysql_query($sql); 
$sql = "INSERT INTO $table1 ($columns1) VALUES ($values1)"; 
mysql_query($sql); 
$sql = "INSERT INTO $table2 ($columns2) VALUES (LAST_INSERT_ID(), $values2)"; 
mysql_query($sql); 
$sql = "UNLOCK TABLES"; 
mysql_query($sql);

As I stated, this is nowhere near complete, but hopefully will give you an idea of where you could take things...

[henry0]

Thank you
a good example that I can understand :) and learn more about
Henry

[tryan]

I, too, have reading to do. It seems mySQL has evolved a lot since I last used it. I'll have to see what version the host is using... it's not my server. It's client work.

I need to get my head around how (SQLServer) Stored Procedures relate to transactional or atomic operations. Perhaps they are not really related, perhaps they're synonymous. Perhaps it's irrelevant.

But the COMMITTING and ROLLING BACK may be what I'm needing. It seems there's a lot more meat to mySQL than I thought. That's good.

Thanks again. And thanks for the welcome coopster. It's always nice to find a group who's on the ball and willing to share knowledge.

Tom