SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Getting the index to use it later

    Good day,

    Let's I have a parent and a child tables.
    I load some data into the parent table, so a new record is generated with its primary key.
    As the private key is auto increment, I do not know the value of this index.

    Now, I need to load some data into the child table, where there is a foreing key linked to the parent table primary key.

    How can I get the value of the parent table primary key, in order to use it to load fata into the child table?

    Parent table: "Customers"
    CustomerID <-PK
    Name
    Address
    Phone
    E-mail

    Child table: "Orders"
    OrderID <-PK
    Status
    Total
    CustomerID <-FK


    Summarizing, I need to load data into both tables, my problem is because there is a "CustomerID" field I need to know from the parent table to be able to load the child table, and I do not know how to get it.

    If there is another easier way to do it, much better!!

    Thanks a lot!!!

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    use LAST_INSERT_ID to get the value of the auto increment value from the insert into the previous table.

  3. #3
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot.

    I applied your indication.
    Could you please check what is wrong in these instructions:

    $sql_id = "SELECT LAST_INSERT_ID() FROM Purchases";
    $PurchaseID = mysql_query($sql_id);
    echo $PurchaseID;

    What I got was the string: "Resource id #6", instead of the number 11, the ID of the table last inserted row.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you should always test your query outside of php first

    the "resource id" error message means your query didn't even run, and your query didn't run because it has a syntax error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You don't even need to select it separately to use it. You can just do your first insert:
    Code:
    INSERT INTO customers
    (
    CustomerID,
    Name,
    Address,
    Phone,
    E-mail)
    VALUES
    ('stooges','larry','curly','moe','shemp');
    and then the second like this:
    Code:
    INSERT INTO orders
    (
    OrderID,
    Status,
    Total,
    CustomerID)
    VALUES
    ('foo','bar','qux',LAST_INSERT_ID());

  6. #6
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    You don't even need to select it separately to use it. You can just do your first insert...
    ... and probably fail to record it properly.

    You should have means to get the CustomerID for new orders with new customers like you do for new orders with old customers.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by itmitică View Post
    ... like you do for new orders with old customers.
    retrieving old customers is done by customerid

    by definition, a new customer doesn't have a customerid yet, and post#1 is talking about a new customer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    by definition, a new customer doesn't have a customerid yet, and post#1 is talking about a new customer
    By definition, a new customer always has an ID, otherwise it won't be called a customer in the first place, since it's not registered yet. New means recently registered, not unregistered, in database language. In PR language it may mean something else.


    Quote Originally Posted by Sir_Arcturua View Post
    I have a parent and a child tables.
    I load some data into the parent table [...]
    [Then] I need to load some data into the child table [...]

    How can I get the value of the parent table primary key, in order to use it to load data into the child table?
    It's seems that I know what I'm talking about: first insert a new customer. By doing that, it becomes an old customer. Then employ the mechanisms normally used to insert orders for a customer.

    The OP is trying to insert a new customer and its orders in one go. He can do that if he retrieves and saves the customer id in a variable.

    But the idea of last inserted id has nothing to do with relational data and its integrity. It has everything to do with guessing.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by itmitică View Post
    So it's seems that I know what I'm talking about:
    that remains to be seen, in this particular instance

    Quote Originally Posted by itmitică View Post
    first insert a new customer. By doing that, it becomes an old customer. Then employ the mechanism normally used to insert orders for a customer.
    if you insert a new customer, how will you know what its customerid is?

    the suggestion is to use LAST_INSERT_ID()

    what specificially is wrong with that approach, please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if you insert a new customer, how will you know what its customerid is?
    Here's a wild proposition: make a query and store the result in a variable? Like you probably do for older, already registered customers, at the moment of a new order.

    Quote Originally Posted by r937 View Post
    the suggestion is to use LAST_INSERT_ID()

    what specificially is wrong with that approach, please?
    Everything. It's guessing, not proper retrieving of relational data. It's sidestepping on what falsely seem like a particular case. The proper mechanisms are already in place, no need to build redundant ones.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by itmitică View Post
    Everything. It's guessing, not proper retrieving of relational data.
    that, sir, is an opinion -- perhaps a well-informed opinion, with some merit -- but it certainly does not give you the right to denigrate a proven technique and make it sound like people are wrong to use LAST_INSERT_ID()

    perhaps you should register your opinion with mysql.com

    meanwhile, we will continue to recommend a proven technique to mysql developers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that, sir, is an opinion -- perhaps a well-informed opinion, with some merit -- but it certainly does not give you the right to denigrate a proven technique and make it sound like people are wrong to use LAST_INSERT_ID()
    Issues with:
    - concurrent inserts
    - multiple inserts in one hit

    No need to inform MySQL.com sir, they already know: http://bugs.mysql.com/bug.php?id=34319.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by itmitică View Post
    Issues with:
    - concurrent inserts
    guaranteed not possible, as stated in da manual

    if you would kindly post a test case that proves it doesn't work, i'd be inclined to take you more seriously

    Quote Originally Posted by itmitică View Post
    - multiple inserts in one hit
    irrelevant, we are not adding more than one customer at a time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    guaranteed not possible, as stated in da manual

    if you would kindly post a test case that proves it doesn't work, i'd be inclined to take you more seriously
    Oh, "da' manual". Right. Sure. "da' manual" is never wrong. Anyway, what do I know, I'm a Oracle and PostgreSQL guy myself, but still, I can read about bugs with MySQL MyISAM or InnoDB server settings. It seems LAST_INSERT_ID() has failed on some fellows, but if da' manual states that it's not possible...


    <hr>


    Quote Originally Posted by r937 View Post
    irrelevant, we are not adding more than one customer at a time
    Really?! Or should I say:

    Quote Originally Posted by r937 View Post
    that remains to be seen, in this particular instance

  15. #15
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Sir_Arcturua View Post
    Thanks a lot.

    I applied your indication.
    Could you please check what is wrong in these instructions:

    $sql_id = "SELECT LAST_INSERT_ID() FROM Purchases";
    $PurchaseID = mysql_query($sql_id);
    echo $PurchaseID;

    What I got was the string: "Resource id #6", instead of the number 11, the ID of the table last inserted row.
    Quote Originally Posted by r937 View Post
    you should always test your query outside of php first

    the "resource id" error message means your query didn't even run, and your query didn't run because it has a syntax error
    The returning of a resource id means that the query run fine, if the query had failed then false would have been returned.

    To get the last inserted ID if you're using the mysql_ extension in php you need to use the mysql_insert_id() function to get the ID of the last INSERT query (use it before you fire off another INSERT query).

    You might want to consider migrating from using the mysql_ extenstion to either the mysqli_ extenstion or PDO which:
    1. You can use prepared statements to prevent sql injection (http://php.net/manual/en/security.da...-injection.php)
    2. You can use transactions to prevent issues with data integrity if something goes wrong when processing an order (http://dev.mysql.com/doc/refman/5.0/en/commit.html)
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  16. #16
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,060
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Sir_Arcturua View Post
    $sql_id = "SELECT LAST_INSERT_ID() FROM Purchases";
    $PurchaseID = mysql_query($sql_id);
    echo $PurchaseID;
    The problem with this is that mysql_query only returns a query resource, not the result of the query.

    Instead you should do,

    PHP Code:
    $sql_id "SELECT LAST_INSERT_ID() FROM Purchases";
    $res mysql_query($sql_id);
    $row mysql_fetch_array($res);
    $purchaseID $row[0];
    echo 
    $PurchaseID
    or, as @SpacePhoenix ; says, use mysql_insert_id

    Also note that it says in the manual

    The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.
    -- http://dev.mysql.com/doc/refman/5.0/...insert-id.html
    So concurrent inserts is not really an issue here; unless -maybe- you're using multiple threads that use the same connection, but then you'd have bigger fish to fry than the mysql_insert_id() function anyway

    As for the multiple inserts, that's also a problem when you're not using last_insert_id(), so it's not an argument against using last_insert_id(). At most it's an argument not to use multiple inserts on a table with an AUTO_INCREMENT field if you need the generated ID values afterward because the behavior is undefined!

    Also,

    Quote Originally Posted by itmitică View Post
    No need to inform MySQL.com sir, they already know: http://bugs.mysql.com/bug.php?id=34319.
    That's not even a bug. As it says further down:

    Thank you for the bug report. Currently that is the expected behavior:

    http://dev.mysql.com/doc/refman/5.0/...increment.html

    "Note

    For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This allows multiple-row inserts to be reproduced correctly on other servers in a replication setup."
    The "bug" reporter then goes on about the name of the function which incorrect, as opposed to the behavior of the function. He has a point, but your argument that last_insert_id() is flawed in MySQL is invalid; at least when based on this "bug" report.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Code:
    $sql_id = "SELECT LAST_INSERT_ID() FROM Purchases";
    i believe FROM Purchases is incorrect here

    earlier in this thread, i thought it was invalid, and that the syntax error was the cause of the resource id, but i don't do php, so i got that wrong

    it actually does work -- it returns the same value once for every row in the table!!

    not exactly what is desired here, is it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Sir_Arcturua View Post
    Summarizing, I need to load data into both tables, my problem is because there is a "CustomerID" field I need to know from the parent table to be able to load the child table, and I do not know how to get it.
    If I read this correctly, you don't have yet code to cover new orders from old customers? You're starting up with the "new customer - new order" scenario?

    If so, I believe you should cover the "old customer - new order" scenario first. Then, in case of new customers, you only have to register the new customer data first, then call the mechanism in place for "old customer - new order" scenario.

    What I'm trying to say, in case of a new customer,

    don't do this:
    - call the procedure for new customer registration and then the procedure for "new customer - new order" registration (which is redundant to a "old customer - new order" procedure)

    but this:
    - call the procedure for new customer registration and then the unique procedure for "old customer - new order" registration


    One could argue that using two redundant old/new procedures and a LAST_INSERT_ID call, it may save something for you. It's not true. You'd have to always do and remember the old/new customer check. And you'd have... two redundant procedures.


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
  •