Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: Condensing SQL statements

    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:

    SELECT
    t1.address_id
    FROM
    address AS t1
    WHERE
    address1 = ''
    SELECT
    Max(t1.address_id) AS Max
    FROM
    address AS t1
    Is it possible to condense these two queries into one?


    Thanks,
    Frank

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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


  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whoops, i see you replied while i was researching...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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


  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're most welcome

    happy holidays and best wishes for 2009

    and thanks for posting back

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •