SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    ********* Ornithologist AtomicPenguin's Avatar
    Join Date
    May 2002
    Location
    Vancouver, BC
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Simple MySQL Question

    Hey y'all.

    [My MySQL skills are only one or two notches above those of a squirrel, so if the following is really really easy, feel free to mock me to your hearts desire.]

    Okay. When I input a new record into my database, I want ONE table (the Master Table) to have a unique, autoincremented id column, to keep track of each record. No problem. BUT, I then want the id of that record to be used in a second table. Again, no problem.

    Now the way I've done this was to separate it into two stages. Stage one inputs the information into Table One. Then, stage two queries the database to find out the id number of the last inputed record. This is retrieved, and that number is used in table two.

    Is this sound? Here's my worry: isn't it possible that two people using the same code could upload at the same time, and then confuse the data by mistakenly assuming the last inputed record to be THEIRS, whereas it was, in fact, the other persons?

    This problem is probably really simple, but I thought I'd better cover my butt by asking you guys.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what you describe is called multi-threading, and yes, it's a potential problem, and yes, SELECT MAX(ID) is not the way to do it

    retrieving the autonumber of an inserted row so that you can use it in other tables as a foreign key is a very common requirement

    in mysql you use the mysql_insert_id function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •