PHP, MySql and concurrency

The scenario is this, i have one table with a filed of type varchar(n) that cannot contain duplicated values except the sting '00000' (then i cannot use one unique index for the field validation).
One example of a valid scenario is:

id field
-------------
1 11201
2 31242
3 00000
4 14649
...
124 00000
125 23234

now, if i want to insert a tuple (126, 31242) i will not able to do.
The first thing that comes to me mind was checking that the field value not exist and then do the insert:

<?php
    $id = mysql_pconnect(..., ..., ...);
    if ($id) {
        if (mysql_select_db(..., $id)) {
            $valid_value = false;
            if ($_POST['value'] != '00000') {
                $valid_value = true;
            } else {
                $sql = "SELECT COUNT(*) AS total\n";
                $sql.= "FROM   table\n";
                $sql.= "WHERE  (field <> '00000') AND (field = '" . $_POST['value'] . "')";
                $dataset = mysql_query($sql, $id);
                if ($dataset) {
                    if ($registro = mysql_fetch_assoc($dataset)) {
                        if ($registro['total'] == 0) {
                            $sql = "INSERT INTO tabla(field)\n";
                            $sql.= "VALUES ('" . $_POST['value']. "')";
                            $result = mysql_query($sql, $id);
                            if (!$result) {
                                // Error message
                            } else {
                                // Ok
                            }
                        } else {
			    // Error message
                        }
                    } else {
                        // Error message
                    }
                } else {
                    // Error message
                }
            } else {
               // Error message
            }
        } else {
            // Error message
        }
    }
?> 

Code (markup):

but if i don't miss anything, with concurrency of users for insertions that code fail. (The code is only for example, not debugged, not enchanced)

• User 1 has executing the first SQL sentence and don't finde the value for insert and go on
• User 2 executes the first SQL sentence and then don't find the value for insert and go on (meanwhile the User 1)
• User 2 insert the value
• User 1 insert the value

¿Which is the better (or a good one) approximation to do insertions without duplicate values of the field? ¿I must use transactions?
BEGIN TRANSACTION
Execute SQL sentence 1
Execute SQL sentence 2
COMMIT or ROLLBAK transaction

Thanks

 

I think you should use some other mechanism to enable field validation so the `field` column can be defined unique. It seems to me as if this column has two uses now: contain a value and indicate some kind of (lack of) validation. If this is the case use it just for the value part and create an extra column for the validation. Then you can add a unique index on `field` and MySQL will do the job for you.

 

Make the field 'field' unique.

Also create a random number for that field.

Peace,