I have a form that will look up a customer and a caller. The customer will always be in the db but the caller and his info may not be.
I have an address table that holds the address of both client and caller. Its a shared table. I also have another table for the caller's name, phone number and ssn.
There can be a few different scenarios when saving this data and I am confused as to how to handle it. Maybe someone can put me back on track.
The way I thought about doing this is to query the db for the following:
1. Query the db for the caller's address
2. Query the db for the caller's name
Query for the existing PK.
If not found:
Query for the Max() PK columns respectively to do the insert.
I am using transactions on these tables and the reason why I am querying for the PKs is because there is no way to get the last insert id from the table in a transaction when there is more than 1 table being affected. I hope that makes sense.
What I have are a lot of these small queries that are making me crazy. Is there a way to condense these queries into one? Here is an example of just the address queries:
address AS t1
address1 = ''
Max(t1.address_id) AS Max
address AS t1
Is it possible to condense these two queries into one?
Here is the DDL I am working with. Rather simple but I need to get the PKs for the insert into a third table (incident).
Max(t1.address_id) AS addyMax
address AS t1
...increment address_id by 1....
Max(t1.rp_id) AS rpMax
rp AS t1
...increment rp_id by 1....
Max(t1.incident_id) AS incidentMax
incident AS t1
dealer_id = '$dealer';
...increment incident_id by 1....
By incrementing these values myself, I can achieve what I need. An identity column does not work with transactions when there is more than one table involved.
Using transactions means that the entire insert must be atomic. Using last_insert_id within a transaction fails because the only way that insert_id will give up its value is AFTER the table has been committed. In this case, the address_id needs to be inserted BEFORE any inserts occur to the rp table. address_id is a FK to the rp table. Once I have the rp_id, then I need to bring it's value to the incident table where it finally becomes a FK.
All of these inserts need to happen together and this is the only workaround I can come up with.
BTW: I posted this very question a few months back and didn't get an answer. Actually, not true... Pat recommended I use a sproc for this but I could get that to work.
I need to put away my sledgehammer because your right.. I'm digging. How should I handle this?
You know what Rudy.. I just went to mysql's site and found an example that uses LAST_INSERT_ID. I changed the PKs in those two tables to auto increment and got it to work.
Now I'm really confused because I tried like hell to get LAST_INSERT_ID to work a few months back and couldn't. It would not give up the id no matter how loud I screamed. I don't see anything that I am doing differently now than I did back then.
I am going to do some modifications to my code using that same example and see if I can get this to work. If I can't, I'll be back.
I figured I would post back for anyone that may have the same type of problem in the future.
r937 is right, LAST_INSERT_ID() does work with transactions.
I was certain that I had tried to use LAST_INSERT_ID() a few months back and wanted to research why it works now but didn't work back then.
I went back through some of my old source code and found that I was using "mysql_insert_id()" instead of LAST_INSERT_ID().
mysql_insert_id() is a php function and
LAST_INSERT_ID() is a mysql function
The names are very close and it seems that I was confusing them.
php's "mysql_insert_id()" also grabs the last inserted id from the previous insert. The only problem with php's function is that it will not work inside of a transaction but mysql's LAST_INSERT_ID() will. Go figure.