Duplicate values being created
I have a column in a table that I use for display order of rows. When I add a new row to the table I run the following select to obtain the highest existing value, create a php variable from the result, add 1 to it and use that within the following INSERT statement.
$q = "SELECT display_order FROM ext_images WHERE parentId = XX ORDER BY display_order DESC LIMIT 1";
$r = @mysqli_query ($databaseConnection, $q);
if ($r) {
if (mysqli_num_rows($r) > 0) {
$row = mysqli_fetch_array($r, MYSQLI_ASSOC);
$displayOrderValue = (int)$row['display_order']+1;
... blah blah
$st = mysqli_query ($dbc, 'START TRANSACTION');
if($st) {
$q = "INSERT INTO ext_images (Bunch, of, column, names, display_order) VALUES (".$bunch.", '".$of."', '".$column."', '".$names."', ".$displayOrderValue.")";
... blah blah
It's generating duplicate values, for example I may end up with three rows with a value of 5. How, why, what can I do to resolve?
Any help appreciated, thanks in advance