StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAUTO_INCREMENT implementation for string column containing a special number format
text
Body
copied!<h1>Context and goal</h1> <p>In table <code>clients</code> I have a column <code>clientNum CHAR(11) NOT NULL</code> with <code>UNIQUE KEY</code> constraint. It contains client number in the format <code>xxx-xxx-xxx</code> where <code>x</code> is a decimal digit. For more details on the format see below.</p> <p>I want to implement something like <code>AUTO_INCREMENT</code> for this column so that each client gets their number calculated automatically. From <a href="http://dev.mysql.com/doc/refman/5.0/en/create-table.html" rel="nofollow">MySQL <code>CREATE TABLE</code> docs</a>:</p> <blockquote> <p>An integer or floating-point column can have the additional attribute <code>AUTO_INCREMENT</code>. When you insert a value of <code>NULL</code> (recommended) or <code>0</code> into an indexed <code>AUTO_INCREMENT</code> column, the column is set to the next sequence value. Typically this is <code>value+1</code>, where <code>value</code> is the largest value for the column currently in the table. <code>AUTO_INCREMENT</code> sequences begin with <code>1</code>.</p> </blockquote> <p>So I want to find the next number available and use it as <code>clientNum</code> value for newly inserted client row. Next number available is current maximum of <code>clientNum</code> incremented.</p> <p>I’m coding in PHP using <a href="http://www.php.net/manual/en/book.pdo.php" rel="nofollow">PDO</a> to access the MySQL database (see <a href="http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers" rel="nofollow">PDO Tutorial for MySQL Developers</a>).</p> <h1>Client number format</h1> <p>As stated above, the client number is in format <code>xxx-xxx-xxx</code> where <code>x</code> is a decimal digit. The range of each segment is <code>000</code> to <code>999</code>. It is basically a 9-digit integer with leading zeroes and dash as thousands separator. It cannot get above <code>999-999-999</code>.</p> <p>Currently we want it be even more restricted, specifically in format <code>000-1xx-xxx</code> (between <code>000-100-000</code> and <code>000-199-999</code>). But there are already some numbers in the database that can start anywhere from <code>000-000-001</code> to <code>500-000-000</code>.</p> <p>Unfortunately it has to be stored in this format, I cannot change it.</p> <h1>Finding maximum</h1> <p>I need to get the max number in range <code>000-100-000</code> to <code>000-199-999</code>, values outside this range must be ignored. This is where my problem comes in because as said before some numbers already exist above this.</p> <p>Maximum is never <code>000-199-999</code>. Otherwise in would result in adding <code>000-200-000</code> and the next time called maximum will be <code>000-199-999</code> again, resulting in attempt to insert <code>000-200-000</code> again.</p> <h1>How incrementation works</h1> <p>In PHP in can be done like this:</p> <pre><code>$clientNum = "000-100-000"; $clientNum = str_replace("-", "", $clientNum); $clientNum++; $clientNum = implode("-", str_split(str_pad($clientNum, 9, "0", STR_PAD_LEFT), 3)); </code></pre> <p>Final <code>$clientNum</code> value is <code>000-100-001</code>.</p> <p>When the initial number is <code>000-120-015</code> then the code above produces <code>000-120-016</code>. Overflow propagates to the next segment, i.e. <code>000-100-999</code> becomes <code>000-101-000</code>. <code>999-999-999</code> cannot be incremented.</p> <h1>Idea to start with</h1> <p>In a loop I want to get the next number available, check if that number exists in the database, and if so, redo that loop until it finds an unused number. I know how to check if it’s in the database the first time, but I’m not sure how to do the loop.</p> <p>Does anyone know a way to do this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload