MyBB Community Forums
How to get the next auto-increment id? - Printable Version

+- MyBB Community Forums (https://community.mybb.com)
+-- Forum: Extensions (https://community.mybb.com/forum-201.html)
+--- Forum: Plugins (https://community.mybb.com/forum-73.html)
+---- Forum: Plugin Development (https://community.mybb.com/forum-68.html)
+---- Thread: How to get the next auto-increment id? (/thread-172151.html)

Pages: 1 2


How to get the next auto-increment id? - marcus123 - 2015-06-20

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?


RE: How to get the next auto-increment id? - Euan T - 2015-06-20

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


RE: How to get the next auto-increment id? - marcus123 - 2015-06-20

$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/1372077/get-next-auto-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?


RE: How to get the next auto-increment id? - Euan T - 2015-06-20

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.


RE: How to get the next auto-increment id? - marcus123 - 2015-06-20

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



RE: How to get the next auto-increment id? - Omar G. - 2015-06-21

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.


RE: How to get the next auto-increment id? - Omar G. - 2015-06-21

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.


RE: How to get the next auto-increment id? - dragonexpert - 2015-06-21

(2015-06-20, 11:59 AM)marcus123 Wrote: 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.


RE: How to get the next auto-increment id? - Omar G. - 2015-06-21

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


RE: How to get the next auto-increment id? - marcus123 - 2015-06-22

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!