SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Denmark
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How accurate is mysql_insert_id?

    Hi Guys,

    I'm in the process of coding a multi-instance shopping cart and have developed some doubts about the above mentioned functions infallibility:

    The case:

    People will insert the basic information on a product into a "product" table and the primary key will then be fetched with the mentioned function and used as a foreign key for product options in a "prod_options" table affiliated with the data from the product table.

    But question is: If another user - almost simultaneously - also inserts into the main "product" table. Could the wrong prod_id then end up being fetched for the first users' prod-options, so a set of product options somehow end up being affiliated with another users' product? If yes, what is the best/most efficient surefire way to prevent this from happening?

    Thx for reading.
    "There can be only one masterfarter"

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by d'biann View Post
    Could the wrong prod_id then end up being fetched ...
    no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,788
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    No. The id returned is the one relating to the last insert done from your script. It doesn't matter that there have been 50 other inserts in the half second since your insert. That's the whole purpose in using that rather than the maximum value as that maximum is not specific to your insert.

    One of the main reasons for that value's existence is to cater for the exact situation you are considering. Any other alternative would suffer from the problems you are concerned about.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Denmark
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thx for the rapid replies, much appreciated

    Quote Originally Posted by felgall View Post
    No. The id returned is the one relating to the last insert done from your script.
    Well, that's just it. All users will be inserting via the same script. One scipt, handling multiple carts. Do you know how the function operates behind the scenes to quality-ensure the result?
    "There can be only one masterfarter"

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,788
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by d'biann View Post
    Well, that's just it. All users will be inserting via the same script. One scipt, handling multiple carts. Do you know how the function operates behind the scenes to quality-ensure the result?
    No. Each visitor will be running their own iteration of the script. Each execution of the script is separate. Just because there are a hundred copies of the same script running at the same time doesn't mean that things work any differently than if they were a hundred different scripts rather than all of them being copies of the same script.

    The values associated with any particular visitor's execution of a script are entirely separate from every other visitor running the same script at the same time. Only when they access information from the database or a file are they looking at the same data.

    mysql_insert_id provides a way for each execution of the script to track the value that they inserted into the database regardless of any others that have been inserted since - it provides a field that belongs to the particular execution of the script where simply retrieving the MAX() value from the database would get the last value that was inserted regardless of who inserted it.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


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
  •