1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

mysql_insert_id()

Discussion in 'General PHP and MySQL Discussions' started by Lu Jia, May 31, 2011.

  1. Lu Jia

    Lu Jia Active Member

    Is it possible to get the most recently generated AUTO_INCREMENT value?
     
  2. Lawrence

    Lawrence Well-Known Member

    As far as I know there is no reliable way of doing this. The most reliable method is to insert a new row and and immediately run the mysql -> SELECT_LAST_INSERT_ID().

    FYI, if SELECT_LAST_INSERT_ID() was executed after a multiple Insert of, for example 5 rows, it will return the LAST_INSERT_ID value for the first Inserted record and not the value of the last row inserted as you would expect.
     
  3. Ryan Kent

    Ryan Kent Well-Known Member

    SELECT Auto_increment FROM information_schema.tables WHERE table_name='xf_user';

    That will provide the NEXT value, so -1 if you want the most recently generated value.
     
  4. Lu Jia

    Lu Jia Active Member

    PHP:
    $next_id $db->query("SELECT Auto_increment FROM information_schema.tables WHERE table_name='xf_user';");

    $last_id $next_id--;
    something like this one will return the last id?
     
  5. Ryan Kent

    Ryan Kent Well-Known Member

    I am very familiar with SQL, but not PHP. I can answer that the SQL portion will show the NEXT id value for the xf_user table.
     
  6. Mike

    Mike XenForo Developer Staff Member

    In the context of our DB object (well, ZF's), you can run the insert and then do:

    $db->lastInsertId();
     
    Lu Jia likes this.
  7. Lu Jia

    Lu Jia Active Member

    Thanks Mike!
     
  8. Lu Jia

    Lu Jia Active Member

    I doesn't work on my site >.>
    I hoped it will work :(

    My code:

    PHP:
                $data = array(
                    
    'name' => $group_name,
                    
    'tag' => $group_tag,
                    
    'fansub' => $group_fansub,
                    
    'scanteam' => $group_scanteam,
                    
    'alias' => $group_alias,
                    
    'published' => $published,
                );

                
    $db->insert('group'$data);

                
    $id_group $db->lastInsertId();
    I followed the ZF doc too.
    Any suggestion? Maybe I did some mistakes
     
  9. James Freeman

    James Freeman Member

    I'm not familiar with the Zend framework, Nor have I done any research from this, but I would assume (call it an educated guess).

    the ->insert(); would have another optional parameter for "where", try this.

    PHP:
    $data = array(
                    
    'name' => $group_name,
                    
    'tag' => $group_tag,
                    
    'fansub' => $group_fansub,
                    
    'scanteam' => $group_scanteam,
                    
    'alias' => $group_alias,
                    
    'published' => $published,
                );

    $where = array(
                 
    'id' => $db->lastInsertId()
     );

                
    $db->insert('group'$data$where);

     
  10. Danny.VBT

    Danny.VBT Active Member


    What exactly do you mean by it isn't working? Is the data being inserted into the database? What is the value of $id_group ?
     
  11. Lu Jia

    Lu Jia Active Member

    The problem i got was $id_group return a null value :)
    I fixed it :) the problem was dreamweaver :D he doesn't upload my file when I save it and upload. Now it works fine. Thanks for the time mates
     
  12. Despair

    Despair Active Member

    On my live vs. development site I'm getting different values. On my live site, the value I'm getting is the last inserted id plus one. Does anyone know if a different mysql version could produce different results? I'm running 5.0.92 and 5.1.53 respectively. If not, what else could be the problem?

    Also not sure if it's related, but php versions 5.2.3 vs. 5.3.4. Noticed that XenForo requires 5.2.4, but when I ran the compatibility script way back, everything was green.
     
  13. Despair

    Despair Active Member

    Anyone else have the above problem?
     
  14. compwhizii

    compwhizii Active Member

    In what universe would an INSERT statement ever have a WHERE clause?
     

Share This Page