PHP MYSQL: instant select + insert

Question

I need to pull from MYSQL a value , than change forward the value and insert in to the mysql.

for example :

$str = mysql_fetch_assoc(mysql_query('SELECT `str_id` FROM `ids` ORDER BY `num_id` DESC LIMIT 1 ')); // = aaa
$str = $str++; // aaa -> aab
// than check if "aab" key already exist
// if not , insert as key as "aab"

as for this algorithm there is a risk that this script runs from the two sides of the globe and two unique keys will be sign as "aab"..

how can i do this without risk of sign this unique key two times?

Answer 1

When using MySQL-InnoDB you should use Transactions (Provided by MySQLi and PDO) for that cause. MyISAM does not support it.

Here is the manual about transactions in PDO: http://php.net/manual/en/pdo.transactions.php

Answer 2

If you set up your database so that the column is unique, the MySQL table will not allow you to add the same value twice. You can then check the result of the transaction to ensure that it succeeded.

From your example it looks like you just want an incremented unique key. You should use an integer for this, which can automatically increment when you insert a new row. If you want it to be in string form just perform some basic math on it. i.e.

1 = a
2 = b
...
27 = aa
28 = ab

This is assuming you don't already have a primary key on your table, in which case I don't understand why you need the string in that form. Hopefully this helps, and I apologize if I misunderstood the question.