SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lookup Table Coding Help

    I have a table with a filename referrenced to an ID number. That all is working fine.

    What I want to do is create a form that referrences an ID number to a number corresponding a table. I have it all worked out in my mind how i want to do it. But Im having problems with the code for the insert.

    I cannot input it in one table and get that ID number back to insert it into a category(i.e. referrence it to another number).

    Can anyone help. I will post the code if that helps.
    -Ben

  2. #2
    purple monkey dishwasher scoates's Avatar
    Join Date
    Nov 2001
    Location
    Montreal
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if MySQL supported triggers, you could write a trigger that would automatically select the ID on insert.. what I usually do is this:

    PHP Code:
    <?
    $sql 
    "INSERT INTO table ...";
    $DB->query($sql);
    $sql "SELECT MAX(id) "
          
    ."FROM   table ";
    $maxQ $DB->query($sql);
    $newID $maxQ->result(0,0);

    ?>
    make sense? the DB module is custom, but (hopefully) easy to understand..

    S

  3. #3
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Lookup Table Coding Help

    Originally posted by B1011011
    I cannot input it in one table and get that ID number back to insert it into a category(i.e. referrence it to another number).
    Sure you can: mysql_insert_id

    --Viral
    A computer without Windows is like a chocolate cake without mustard.

  4. #4
    purple monkey dishwasher scoates's Avatar
    Join Date
    Nov 2001
    Location
    Montreal
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting.. I'd never heard of that function (-: must've overlooked it..

    That's a good idea, and the proper way to do it (right Freddy? <wink> ) (-:

    S

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by scoates
    That's a good idea, and the proper way to do it (right Freddy? <wink> ) (-:
    yes! you should definitely use mysql_insert_id(). your way could easily get the wrong number if 2 clients INSERTed a row at the same time. mysql_insert_id() is client specific. therefore, you know you'll always get the ID that YOUR OWN query generated.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR


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
  •