SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    New records in linked tables?

    Hello,

    Very basic question here. I'm just getting started with PHP and MySQL. I've tried searching and Googling, and while I can find related stuff, I can't find anything close enough to what I need so I can fill in the blanks...because again, I'm a noob.

    Right now I am making a database app to keep track of albums and their songs. Like I said...basic stuff. I find I learn best by just messing around

    What I would like to do is create an "album" record while at the same time creating many "song" records that belong to that album. A one-to-many relationship?

    What I *THINK* should happen is that I set up the album table with an AID (album ID) which is primary key, auto-increment. The song table will get a SID (song ID) which is also primary key, auto-increment.

    To link the two tables, I should use the AID record of the album table, while also having a AID in the song table as a foreign key.


    So my question...how do I do this in PHP? I mean...when I create the "master" record, which is the album, I do not know what the AID is going to be (it is auto-increment). So how can I populate the song table with the correct AID right after creating the album?

    Thank you very much!

  2. #2
    SitePoint Zealot Cute Tink's Avatar
    Join Date
    Apr 2009
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Look at this page. It will help you:

    http://us3.php.net/manual/en/functio...-insert-id.php

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, Cute Tink. I will definitely look into that.

    It seems like kind of a clunky way to do it though? Is it standard practice to use the function for this purpose?

    Note: Because mysql_insert_id() acts on the last performed query, be sure to call mysql_insert_id() immediately after the query that generates the value.
    What if several people are creating albums at the same time? Wouldn't it be possible for MySQL to return the last ID that happened to belong to someone else?

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    no, it looks to the current connection. The only thing you can't do is insert a bunch of albums at the same time. Then there would be no "full proof" way to get all the primary keys that were last inserted. If you need the primary key for each item that is inserted then you must insert them individually and all will be well.


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
  •