SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating Two Tables?

    I have two tables, which look like so:
    JOB(jobid, customerid, orderid, fitting, fittingdate, fittingtime, datetaken, plywoodbase)
    ORDER(orderid, roomarea, roomperimiter, carpet, gripperstrip, antistain, underlay, discount)

    I then need to insert everything into the ORDER table, and, get the orderid returned to me, so that I can then insert it into the JOB table. How would I go about doing this? I'm using PHP / MySQL, and I only have primary key's setup.
    I did think about just returning the most recently added ID from the order table, but then realized, you could have somebody adding to that table inbetween the SQL statements taking place (it's unlikely, and impossible in my situation, but I just want to know how to do it the correct way!).

    Thanks,

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    The ideal way would be to use a transaction. If the first fails don't insert the other. If the second fails don't commit the transaction. However, depending upon your table engine types that may not be viable. The other option would be to insert the first. If that query is a success insert the next. If that query fails then use the last insert id of the previous query to delete the first insert. Again, depending on the type adaptor your using the function or method to return the last insert id will vary. That is essentially the way you would retrieve the primary key of the most recent inserted item. The exact function or method name to retrieve the last insert id varies slightly between database adaptors.

  3. #3
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, I could just run a mysql_fetch_array and get the latest ID from the ORDER table to insert it into the JOBS table?

  4. #4
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Bingo, fond out what I need: mysql_insert_id();

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    yep, immediately after you insert the item and confirm it get the last insert id.


Bookmarks

Posting Permissions

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