Increment ID by one on duplicate with PHP

Question

Currently I'm creating a commentary system and sometimes I must verify if the ID that will be inserted in the database exists or not. If not exists, just return, else, generate again.

Here is my function:

function checkId($n) {
    global $mysqli;
    $query = mysqli_query($mysqli, "SELECT id FROM comments WHERE id = ".$n.""); //line error
    if (!$query) {
        die('Error: ' . mysqli_error($mysqli));
    }
    if(mysqli_num_rows($query) > 0){
        checkId($n++);
        return;
    } else {
        return $n;
    }
}

Probably you gonna tell me to use AUTO_INCREMENT, but in this situation I'm not able to do. The error is: Fatal error: Maximum function nesting level of '100' reached, aborting!

I have tried fix it myself but I'm unable, can you help me? Thank you.

Yes, you are correct. You should fix the `AUTO_INCREMENT` issue. Don't take this approach. Please add the code and schema when you get that error. — user3783243, Dec 13 '18 at 04:51
Because I need each ID separated by a range of 100 numbers, and If I use AUTO_INCREMENT I can't do this. (1, 101, 201, 301, etc) — inukix, Dec 13 '18 at 04:54
@gview because I'll index all replies from this comment system inside this 100 numbers, and the limit reply limit will be 100 of course. — inukix, Dec 13 '18 at 04:57
@inukix No offense meant here, but what that isn't relational and doesn't make much sense from a utility or end user standpoint. Why aren't you just using a foreign key to the post or whatever it is that these comments point to? — gview, Dec 13 '18 at 04:58
A uuid or a guid would be a key that you could generate when you don't want to use an integer. Often for scalability where you don't want a centralized limiter, people will use these alternative id's for primary keys. — gview, Dec 13 '18 at 04:59
You can auto increment and use a primary key as suggested by others and use `COUNT()` to keep track of the comment limit. When the count reaches 100, then you can forbid more comments. — Rasclatt, Dec 13 '18 at 05:06
try adding one more querry to get current last_id(eg:101) before calling this function..and in the if condition of your function return (100 + $last_id).. — josephthomaa, Dec 13 '18 at 05:14

EthanB · Accepted Answer · 2018-12-13T05:07:29.227

Try this simple exercise: add an echo/print statement just inside the checkId function that prints the value of $n... You should notice that the value of $n is going to be the same 100 times in a row right up until the Fatal error occurs.

Cause: In the expression checkId($n++), you're using the ++ as a "postfix increment" operator. Which means, the $n variable is only going to be incremented after the nested checkId(...) function is called. Basically this:

checkId($n++)

... is just short-hand for this:

checkId($n)
$n = $n + 1

The second call to checkId (with the same value of $n) is going to end up calling checkId($n++), which is going to trigger a third call with the exact same value of $n as well, and on and on until PHP says "this is too much" and blows up with the "Fatal error"... which is a "Stack Overflow" error, by the way :P

Instead, use a prefix increment expression checkId(++$n), or something trivially simple like checkId($n + 1). Or, better yet, figure out how to get the job done in exactly ONE call to mysqli_query (which is a separate question, by the way). Thinking you can just query a bunch of times until you get something right means your application won't be as scalable if/when you get a bunch of people using it. Just let the database do the heavy lifting. :)

I agree that this would fix the problem with his blowing the recursion stack, however, given the whole reason stated as why this code exists, this code shouldn't be used as it has many other problems that haven't been touched upon, like lack of locking. — gview, Dec 13 '18 at 05:04
First step: help OP understand the difference between pre/postfix increment (this question). Second step: _just let the database do the heavy lifting_ (probably a separate question). — EthanB, Dec 13 '18 at 05:06

Increment ID by one on duplicate with PHP

1 Answers1