mysql_insert_id()

[Lu Jia]

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

 

[Lawrence]

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.

 

[Ryan Kent]

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.

 

[Lu Jia]

$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?

 

[Ryan Kent]

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.

 

[Mike]

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

$db->lastInsertId();

 

[Lu Jia]

Thanks Mike!

 

[Lu Jia]

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

My code:

            $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

 

[James Freeman]

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

My code:

            $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

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.

$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);

 

[Danny.VBT]

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

My code:

            $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

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 ?

 

[Lu Jia]

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

 

[Despair]

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

$db->lastInsertId();

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.

 

[Despair]

Anyone else have the above problem?

 

[compwhizii]

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).

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