Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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.</p> <p>In MySQL, you can use this SQL reimplementation of your PHP code:</p> <pre class="lang-sql prettyprint-override"><code>INSERT(INSERT(LPAD(CAST(CAST(REPLACE(clientNum, '-', '') as UNSIGNED) + 1 as CHAR), 9, '0'), 7, 0, '-'), 4, 0, '-') </code></pre> <p><em>This increments <code>000-000-999</code> to <code>000-001-000</code> and <code>999-999-999</code> to <code>100-000-000</code> (truncated from <code>100-000-0000</code> by <code>LPAD()</code>). I warned you.</em></p> <p>E.g. to just preview what the next value is, use</p> <pre class="lang-sql prettyprint-override"><code>SELECT INSERT(INSERT(LPAD(CAST(CAST(REPLACE(clientNum, '-', '') as UNSIGNED) + 1 as CHAR), 9, '0'), 7, 0, '-'), 4, 0, '-') FROM clients </code></pre> <p>If you want to use this when inserting a new row, it is used like this:</p> <pre class="lang-sql prettyprint-override"><code>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 </code></pre> <p>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 <code>clients</code> is a temporary table, which I expect it not to be. More on that below.</p> <p>See also <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html" rel="nofollow noreferrer">string functions</a>, <a href="http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast" rel="nofollow noreferrer">CAST()</a>, <a href="http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce" rel="nofollow noreferrer">COALESCE()</a> and <a href="http://dev.mysql.com/doc/refman/5.0/en/insert-select.html" rel="nofollow noreferrer">INSERT … SELECT</a> in MySQL manual.</p> <hr> <p>Later you added that the permitted values are from range <code>000-100-000</code> to <code>000-199-999</code>. Other values shall be ignored for the purpose of finding maximum. A <code>WHERE</code> clause must be added to the <code>SELECT</code> part of <code>INSERT</code> written above.</p> <pre class="lang-sql prettyprint-override"><code>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' </code></pre> <hr> <p>Then you stated that my solution does not work for you and <a href="https://stackoverflow.com/review/suggested-edits/3605509">proposed a supposed fix</a>:</p> <pre class="lang-sql prettyprint-override"><code>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' </code></pre> <p>This uses a <a href="http://dev.mysql.com/doc/refman/5.0/en/subqueries.html" rel="nofollow noreferrer">subquery</a> instead of the <code>INSERT … SELECT</code> syntax.</p> <p>In MySQL, table cannot be modified (by <code>INSERT</code> in this case) and read by a subquery at the same time. Quoting the subquery manual:</p> <blockquote> <p>In MySQL, you cannot modify a table and select from the same table in a subquery. This applies to statements such as <a href="http://dev.mysql.com/doc/refman/5.0/en/delete.html" rel="nofollow noreferrer"><code>DELETE</code></a>, <a href="http://dev.mysql.com/doc/refman/5.0/en/insert.html" rel="nofollow noreferrer"><code>INSERT</code></a>, <a href="http://dev.mysql.com/doc/refman/5.0/en/replace.html" rel="nofollow noreferrer"><code>REPLACE</code></a>, <a href="http://dev.mysql.com/doc/refman/5.0/en/update.html" rel="nofollow noreferrer"><code>UPDATE</code></a>, and (because subqueries can be used in the <code>SET</code> clause) <a href="http://dev.mysql.com/doc/refman/5.0/en/load-data.html" rel="nofollow noreferrer"><code>LOAD DATA INFILE</code></a>.</p> </blockquote> <p>However, you found a <a href="http://verysimple.com/2011/03/30/mysql-cant-specify-target-table-for-update-in-from-clause/" rel="nofollow noreferrer">workaround using a temporary table</a>. A temporary table is used when an alias (in this case <code>clients AS tmptable</code>) 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.</p> <p>At this point I want to point out that my solution should work (and <a href="http://pastebin.com/X9hsez9Y" rel="nofollow noreferrer">works for me</a>!) too except for the improbable case when <code>clients</code> is a temporary table. I think I can expect it not to be one. Quoting the <code>INSERT … SELECT</code> manual page:</p> <blockquote> <p>When selecting from and inserting into a table at the same time, MySQL creates a temporary table to hold the rows from the <a href="http://dev.mysql.com/doc/refman/5.0/en/select.html" rel="nofollow noreferrer"><code>SELECT</code></a> and then inserts those rows into the target table. However, it remains true that you cannot use <code>INSERT INTO t ... SELECT ... FROM t</code> when <code>t</code> is a <code>TEMPORARY</code> table, because <code>TEMPORARY</code> tables cannot be referred to twice in the same statement (see <a href="http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html" rel="nofollow noreferrer">Section C.5.7.2, “<code>TEMPORARY</code> Table Problems”</a>).</p> </blockquote> <p>As for me this is explicitly saying that my original approach using <code>INSERT … SELECT</code> should work.</p> <hr> <p>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.</p> <p>Your <code>clientNum</code> column must be a unique key. You need to repeat the following steps until successful update:</p> <ol> <li>Get the current maximum of <code>clientNum</code>.</li> <li>Increment the obtained value.</li> <li>Try to insert the row.</li> <li>If successful, finish, otherwise throw the <code>clientNum</code> max value away and loop.</li> </ol> <p>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 <em>1.</em> and <em>3.</em>.</p> <p>You should prepare the statement outside the loop using <a href="http://php.net/manual/en/pdo.prepare.php" rel="nofollow noreferrer"><code>PDO::prepare()</code></a> and then <a href="http://php.net/manual/en/pdostatement.execute.php" rel="nofollow noreferrer">execute</a> it in the loop. The return value of <code>execute</code> method indicates success (<code>true</code>) or failure (<code>false</code>).</p> <p>This is enough info to implement step <em>3.</em>. Steps <em>1.</em> and <em>2.</em> consist of fetching the result of</p> <pre class="lang-sql prettyprint-override"><code>SELECT MAX(clientNum) FROM clients </code></pre> <p>and running it through the code provided by <a href="https://stackoverflow.com/a/20603419/2157640">Stephanus Yanaputra</a>. Step <em>4.</em> is a simple loop condition using the return value from execution of <code>INSERT</code> query in step <em>3.</em>.</p>
    singulars
    1. This table or related slice is empty.
    1. POAUTO_INCREMENT implementation for string column containing a special number format
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USPalec
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POAUTO_INCREMENT implementation for string column containing a special number format
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COthanks for this, I just realised I forgot a crucial bit of information. Due to numbers already being in the system I need to check the MAX() between the values of `000-100-000` to `000-199-999`. Is this possible? Most of these are new to me so not sure what to alter. :)
      singulars
      1. PO
      1. USdpDesignz
    2. COAlso the `REPLACE(clientNum, '_', '')`, shouldn't that underline be a dash?
      singulars
      1. PO
      1. USdpDesignz
    3. COThis looks much better! :). Thanks so much! My only query now is for an insert I don't understand how this works? Shouldn't it be `INSERT INTO clients (clientNum, name) VALUES ((//SELECT INSERT HERE),'John Doe')`
      singulars
      1. PO
      1. USdpDesignz
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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