You may want to solve this in SQL, because otherwise you need two transactions (one for reading, one for writing) and meanwhile the number could be used by a concurrent access.

In MySQL, you can use this SQL reimplementation of your PHP code:

INSERT(INSERT(LPAD(CAST(CAST(REPLACE(clientNum, '-', '') as UNSIGNED) + 1 as CHAR), 9, '0'), 7, 0, '-'), 4, 0, '-')

This increments 000-000-999 to 000-001-000 and 999-999-999 to 100-000-000 (truncated from 100-000-0000 by LPAD()). I warned you.

E.g. to just preview what the next value is, use

SELECT INSERT(INSERT(LPAD(CAST(CAST(REPLACE(clientNum, '-', '') as UNSIGNED) + 1 as CHAR), 9, '0'), 7, 0, '-'), 4, 0, '-') FROM clients

If you want to use this when inserting a new row, it is used like this:

INSERT
INTO clients(clientNum, name)
SELECT
    INSERT(INSERT(LPAD(CAST(
        COALESCE(MAX(CAST(REPLACE(clientNum, '-', '') AS UNSIGNED)), 0) + 1
    AS CHAR), 9, '0'), 7, 0, '-'), 4, 0, '-'),
    'John Doe'
FROM clients

This works regardless of what API you use to access the database, as long as it is MySQL database. The database does the computation. However, it does not work if clients is a temporary table, which I expect it not to be. More on that below.

See also string functions, CAST(), COALESCE() and INSERT … SELECT in MySQL manual.

Later you added that the permitted values are from range 000-100-000 to 000-199-999. Other values shall be ignored for the purpose of finding maximum. A WHERE clause must be added to the SELECT part of INSERT written above.

INSERT
INTO clients(clientNum, name)
SELECT
    INSERT(INSERT(LPAD(CAST(
        COALESCE(MAX(CAST(REPLACE(clientNum, '-', '') AS UNSIGNED)), 0) + 1
    AS CHAR), 9, '0'), 7, 0, '-'), 4, 0, '-'),
    'John Doe'
FROM clients
WHERE clientNum BETWEEN '000-100-000' AND '000-199-999'

Then you stated that my solution does not work for you and proposed a supposed fix:

INSERT
INTO clients(clientNum, name)
VALUES
    (SELECT
        INSERT(INSERT(LPAD(CAST(
            COALESCE(MAX(CAST(REPLACE(clientNum, '-', '') AS UNSIGNED)), 0) + 1
        AS CHAR), 9, '0'), 7, 0, '-'), 4, 0, '-')
        FROM clients AS tmptable
        WHERE clientNum BETWEEN '000-100-000' AND '000-199-999'),
    'John Doe'

This uses a subquery instead of the INSERT … SELECT syntax.

In MySQL, table cannot be modified (by INSERT in this case) and read by a subquery at the same time. Quoting the subquery manual:

In MySQL, you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, UPDATE, and (because subqueries can be used in the SET clause) LOAD DATA INFILE.

However, you found a workaround using a temporary table. A temporary table is used when an alias (in this case clients AS tmptable) is defined, which evades reading from and writing to the same table at the same time. You used temporary table to store the original table, the article describing the workaround uses it to store the result of the subquery (which is more efficient, I guess). Both approaches work.

At this point I want to point out that my solution should work (and works for me!) too except for the improbable case when clients is a temporary table. I think I can expect it not to be one. Quoting the INSERT … SELECT manual page:

When selecting from and inserting into a table at the same time, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows into the target table. However, it remains true that you cannot use INSERT INTO t ... SELECT ... FROM t when t is a TEMPORARY table, because TEMPORARY tables cannot be referred to twice in the same statement (see Section C.5.7.2, “TEMPORARY Table Problems”).

As for me this is explicitly saying that my original approach using INSERT … SELECT should work.

Just to provide a complete answer, I’ll address your original request for PHP solution using database polling. Once more I must add that this is certainly not a good solution.

Your clientNum column must be a unique key. You need to repeat the following steps until successful update:

1. Get the current maximum of clientNum.
2. Increment the obtained value.
3. Try to insert the row.
4. If successful, finish, otherwise throw the clientNum max value away and loop.

The insertion will fail due to violation of the aforementioned unique key constraint. This happens when another connection to the database successfully performs an insert in the meantime between steps 1. and 3..

You should prepare the statement outside the loop using PDO::prepare() and then execute it in the loop. The return value of execute method indicates success (true) or failure (false).

This is enough info to implement step 3.. Steps 1. and 2. consist of fetching the result of

SELECT MAX(clientNum) FROM clients

and running it through the code provided by Stephanus Yanaputra. Step 4. is a simple loop condition using the return value from execution of INSERT query in step 3..

An easy solution based on function that you provided. Change the function name rawr() to any naming that you like. (I couldn't find the best name and ended up using some gibberish name lol).

function rawr($in)
{
    $num = str_replace("-", "", $in);
    $num++;

    // Convert back
    $str = (string) $num;

    // Add Leading 0
    while(strlen($str) < 9)
    {
        $str = "0" . $str;
    }
    echo $str . "<br />";
    $final = substr($str,0,3) . "-" . substr($str,3,3) . "-" . substr($str,6,3);

    return $final;
}

To test it, try this code:

echo rawr(0);
echo "<br />";
echo rawr("000-000-000");
echo "<br />";
echo rawr("012-345-678");
echo "<br />";
echo rawr("123-456-789");
echo "<br />";

This will give an output that you desire. However you will have to code it yourself to test the database. In my opinion, this is not the best way to solve your problem, but it should work :)

<?php
    mysql_connect(....);
    mysql_select_db($db_name);
    $res=mysql_query("select ClientNum from ClientTable");
    $name_arr=array();
    while($row=mysql_fetch_array($res))
    {
        foreach($row as $name)
            $name_arr[]=$name;
    }

    $clientNum="000-000-000";
    while(true){
        $clientNum = str_replace("-", "", $clientNum);
        $clientNum++;
        if($clientNum>999999999)
        {
                echo("No mismatch found");
                break;
        }
        $clientNum = implode("-", str_split($clientNum, 3));
        if(!in_array($clientNum, $name_arr))
        {
                echo "The first unmatched clientNum is:".$clientNum;//This is what you want.
                break;
        }
    }
?>

Comments

1. The query execution only once i.e. outside the while loop makes it less time complex. The time complexity is reduced due to use of array instead of execution of the query itself multiple times because searching in an array is comparatively very less time complex than searching in the database.