How to get the next auto-increment id?

[marcus123]

I have a table called ads that has a column called ID which is auto-increment how to get the next ID before updating the table?

 I tried all PHP methods but they don't work with MYBB?

[Euan T]

What have you tried? Give us an example of the approaches you've attempted. You'll never learn if we just keep giving you code...

[marcus123]

$q = $db->query("
        SHOW TABLE STATUS LIKE 'ads'
    ");

$row = $db->fetch_array($q);
$next_increment = $row['Auto_increment'];
echo "next increment number: [$next_increment]";

I wanna get the value from the table row or column (ID) as what if the table contains more than 1 row with Auto_increment.


Thanks buddy!


Pretty much tried all from here yet no result: http://stackoverflow.com/questions/13720...-increment

P.S. It's like I wanna know what the new thread TID is gonna be on new thread creation! Can you tech me how to get the next TID before updating the thread table tid?

[Euan T]

You cannot know for sure due to race conditions, but the best guess you could make would be:

$nextTid = $db->fetch_feld($db->simple_select('threads', '(MAX(`tid`) + 1) AS next_tid'), 'next_tid');

However, this will not always be correct as between making this query and performing some manipulation on it, a new thread could be inserted.

TL;DR: Let the database handle auto-increment IDs for you. Don't try to do it yourself.

[marcus123]

Thanks big time! The reason I need it is case: I convert thread title on the URL that looks like this: this-is-a-title on the new thread creation I check if this matches any value already in the database if so then I add at the end of the URL thread tid some like SEO Plugin.

this-is-a-title-2456

Can I update the table then through another query which will fetch the latest tid and then update the table second time. Sound really.


This will create an ID

$data = array(
        "title" => $title,
        "description" => $description,
        "message" => $message
    );
    $db->update_query("ads", $data);

Now fetch latest ID

$q = $db->query("
        SELECT max(id)
        FROM ".TABLE_PREFIX."ads a
    ");

$row = $db->fetch_array($q);
$id = $row['max(id)'];

then update the query second time:

$data = array(
        "url" => $string,
    );
    $db->update_query("ads", $data, "id='{$id}'");

Finally redirect

redirect("ads.php", $lang->success);

[Omar G.]

You don't store the _changing title_ thread column into the ADS row. You save the thread _unique (auto increment) ID_ into it and query for this row based off that _unique ID_ once you know the _thread ID_ that is being requested, not before.

[Omar G.]

I would highly suggest you to post more (probably not the same amount you consider to be _more_ but even __more__) details about what you are trying to achieve, code snippets, and attempts you have considered and the ones you have actually tried, even if those had failed, to get to your desired result next time.

Currently most of the posts you open requesting _advice_ lacks information on to inspire people to help you.

[dragonexpert]

I have a table called ads that has a column called ID which is auto-increment how to get the next ID before updating the table?

 I tried all PHP methods but they don't work with MYBB?

Technically you could use SHOW CREATE TABLE my_table to get the query that creates the table. You then can use regex to get it. Something like this perhaps:

$query = $db->query("SHOW CREATE TABLE my_table");
$string = $db->fetch_field($query, "Create Table");
$ai = preg_replace("/\A(.*?)Auto.Increment=([0-9]{1,})(.*?)\Z/is", "$2", $string);
echo $ai;

The best way though is to use $id = then your insert query. This assigns the auto incremented value to your variable. You can then do whatever with it, including deleting it by using it in the where clause of a delete query.

[Omar G.]

Note that incrementing the value of $db->inset_id() is not reliable method because of race conditions as Euantor mentioned.

[marcus123]

Guys you are awesome thanks!

I decided to use

max(id)

Even if the next ID will not be same cause I have deleted the previous one like it will jump from 11 to 13 case I have deleted the ID 12 this will still work as it will never case issues with adding same ID at the end of URL string, I don't care if it's wrong the only thing I care is to avoid creating same URL!