Condensing SQL statements

Frunkie · #1 (**permalink**) 12-25-08, 22:03

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

If found:
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:

Quote:

SELECT
t1.address_id
FROM
address AS t1
WHERE
address1 = ''

Quote:

SELECT
Max(t1.address_id) AS Max
FROM
address AS t1

Is it possible to condense these two queries into one?

Thanks,
Frank

r937 · #2 (**permalink**) 12-26-08, 03:57

yes, there is

but the whole MAX(id) concept is so fraught with danger, i hesitate to show you it

to say nothing of retrieving all the addresses which are zero-length strings

plus, the fact that you are using transactions should not prevent you from using LAST_INSERT_ID

remember, the #1 rule for when you find yourself in a hole: stop digging

Frunkie · #3 (**permalink**) 12-26-08, 04:41

Ok, point well received. Thanks.

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).

Code:

                SELECT
                  Max(t1.address_id) AS addyMax
                FROM
                  address AS t1

...increment address_id by 1....

                SELECT
                  Max(t1.rp_id) AS rpMax
                FROM
                  rp AS t1

...increment rp_id by 1....

              SELECT
                Max(t1.incident_id) AS incidentMax
              FROM
                incident AS t1
              WHERE
                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?

Thank!
Frank

Frunkie · #4 (**permalink**) 12-26-08, 05:15

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.

Thank!
Frank

r937 · #5 (**permalink**) 12-26-08, 05:26

okay, i understand your comment about LAST_INSERT_ID not working until the transaction is committed -- that makes sense even though i have no direct experience with it myself

but are you really sure it works like that?

i did a quick browse through the documentation for transactions and did not see anything suggesting that you can't use LAST_INSERT_ID inside a transaction

seems like it might have popped up somewhere if it was an issue

i mean, transactions and auto_increments are both extremely common, you'd figure someone else would've run into this before

so i'm afraid i cannot help you any further here

r937 · #6 (**permalink**) 12-26-08, 05:26

whoops, i see you replied while i was researching...

Frunkie · #7 (**permalink**) 12-26-08, 22:28

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.

Thanks for the help Rudy!

Frank

r937 · #8 (**permalink**) 12-26-08, 22:32

you're most welcome

happy holidays and best wishes for 2009

and thanks for posting back

Internet Services

Web Development

Digital Marketing

Consumer Tech