SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2002
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how do I find out a new INSERT's row ID?

    sorry, I saw a post on this last summer but "id" is too short for the search engine [img]images/smilies/frown.gif[/img]

    in a table, i have an autoincrement unique 'id' as the first column. if I do this:
    Code:
     
    "INSERT INTO table SET
    submitter_group='$groupname',
    ip='$ipaddy',
    datetime='$stamp' 
    ";
    how do I find out the id of this new row?

    I'd like to be able to UPDATE more data to this row as the user enters it...

    or should I not use the ID, but rather a new column that's a hash of the ip and date? (but same thing, how would I grab that value?)

    sorry, no 'unique' identifier per-user, this is for an anonymous survey tool. I AM using sessions (group-based authentication: u/n & p/w given to a group of people, etc) so is there a session value I should key off-of?

    thanks

    edit: sorry, i hit "preview" and got an error, so I rewrote the question and submitted. Now I know why i've got 2 sets of double posts toinight...
    Last edited by hakalugi; Mar 30, 2003 at 23:36.

  2. #2
    SitePoint Member
    Join Date
    Mar 2003
    Location
    Queanbeyan, Australia
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try 'mysql_insert_id()' to return the row auto_increment after an insert. Should you perform this function immediately after the query, then no variables are needed to be passed to the function.
    Craig
    Craig Rayner
    (We have met the enemy and he is us.)

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2002
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    :d

    Quote Originally Posted by craigray
    Try 'mysql_insert_id()' to return the row auto_increment after an insert. Should you perform this function immediately after the query, then no variables are needed to be passed to the function.
    Craig
    http://www.mysql.com/doc/en/mysql_insert_id.html thanks!

    keywords: identification return value AUTO_INCREMENT column values

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2002
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmmm....

    maybe i'm not using this correctly:

    PHP Code:
    $sql10 "INSERT INTO table SET
        submitter_dap='
    $username',
        ip='
    $ipaddy',
        datetime_st='
    $stamp'    
        "
    ;
    $session_id mysql_insert_id(sql10);
    echo(
    "$session_id  = session_id"); // for testing reasons 
    (the output in the echo is "___ = session_id"

    Originally, I tried it

    Code:
    $sql10 = "INSERT INTO table SET
    	submitter_dap='$username',
    	ip='$ipaddy',
    	datetime_st='$stamp'	
    	";
    $session_id = mysql_insert_id();
    echo("$session_id  = session_id"); // for testing reasons
    and the output of "0 = session_id" is shown. mysql.com says:

    Note that mysql_insert_id() returns 0 if the previous query does not generate an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id() immediately after the query that generates the value.
    but my table structure definitely has "auto_increment" as an "Extra" desctription, and I can see the rows auto numbreing as I add data... and as you can see above, it's definitely "immediately after"

    any ideas?

  5. #5
    SitePoint Member
    Join Date
    Mar 2003
    Location
    Queanbeyan, Australia
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, but I'm not seeing an command that will actually write the code row to the table. I assumed that you had simple left that out when you sent the code. Your code
    Code:
    $sql10 = "INSERT INTO table SET
    submitter_dap='$username',
    ip='$ipaddy',
    datetime_st='$stamp' 
    ";
    $session_id = mysql_insert_id();
    echo("$session_id = session_id" ); // for testing reasons
    I would expect to see:
    Code:
    <?php
    
    $sql10 = "INSERT INTO table SET
    submitter_dap='{$username}',
    ip='{$ipaddy}',
    datetime_st='{$stamp}' 
    ";
    $temp = mysql_query($sql10);
    $session_id = mysql_insert_id();
    echo("$session_id = session_id" ); // for testing reasons
    ?>
    You may still experience an error with this code dependant on how you have defined the fields, as SQL sometimes gets upset by variable types that are numbers being enclosed in quotes like a string. If this is the case get rid of the single quotes inside the curly braces. eg {$stamp} instead of '{$stamp}'

    eg
    Code:
    $query = "INSERT INTO teacher (tname, taddress, ttown, tstate, tpostcode, tacom_id, temail, tphone, tmobile, tlti_id) VALUES ";
    $query .= "('{$_POST['tname']}', '{$_POST['taddress']}', '{$_POST['ttown']}', '{$_POST['tstate']}', '{$_POST['tpostcode']}', '{$_POST['tacom_id']}', ";
    $query .= "'{$_POST['temail']}', '{$_POST['tphone']}', '{$_POST['tmobile']}', {$_SESSION['lti_id']})";
    lti_id is a type integer variable and is therefore not enclosed in single quotes.
    This always seem to work for me.

    Craig[img]images/smilies/rolleyes.gif[/img]
    Last edited by craigray; Mar 31, 2003 at 01:13.
    Craig Rayner
    (We have met the enemy and he is us.)

  6. #6
    SitePoint Zealot
    Join Date
    Mar 2002
    Location
    Perth, Australia
    Posts
    164
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by craigray
    Try 'mysql_insert_id()' to return the row auto_increment after an insert. Should you perform this function immediately after the query, then no variables are needed to be passed to the function.
    Craig
    You could also:
    Code:
    "SELECT id_field_name FROM table_name WHERE submitter_group='$groupname' AND ip='$ipaddy' AND datetime='$stamp'"


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
  •