How do I address this php 4.1.0, mysql issue with duplicate entry error

Hello, we have this is legacy web form app, written in php4.1.0. client started getting sporadic Duplicate entry for key 1 errors within the past three to four weeks. After looking into their db, found that they had a massive amount of data.  I 'assumed' that due to:


Two [or more] users grab the same # from the CV_SEQUENCE_NUMBER table, one finishes first, sending new request number to the request table; then next user finishes and gets bumpped with the Duplicate entry error

the request # will look like this '11-3197' [current year, '-', sequential number]

The big issue, in addition to the dup error of course, is that the request submission dies, per the code instructions, and the form is reset to nothing.  They have a massive form that has tons of input fields, so that is very, very painful to the end user when this occurs

Since this is a legacy app and will hopefully be phased out within the next 6 to 12 months - how can I address the dup issue or what I was thinking, how can I make the process run again, once it encounters the dup error, but before it refreshes the browser windows and removes all the form data?

Thanks in advance.
 

User fills in web form, clicks submit and the following code runs:

//Get the request number
$requestNumber=getRequestNumber();

//Upload Files
$_REQUEST['Q_34']=parseUploadedFiles($requestNumber);

//Create the Request

$sql = "INSERT INTO REQUEST (R_NUMBER, R_STATUS) VALUES ('" . $requestNumber . "','o')";
mysql_query($sql,$db) or die("ERROR >srphp-iir43: " . mysql_error()." - #".mysql_errno());
=====================================================================================================
/*
* Changed 050218 - Request number is just a seven digit number
*/

function getRequestNumber()
{
	global $db;

	$sql = "SELECT CV_SEQUENCE_NUMBER,CV_YEAR FROM CURRENT_VALUES";
	$result = mysql_query($sql,$db) or die("ERROR: " . mysql_error());
	$row = mysql_fetch_assoc($result);

	incrementRequestNumber();

	if($row['CV_YEAR']!=date('y'))
    {
    	incrementRNYear();
    	$sql = "SELECT CV_SEQUENCE_NUMBER,CV_YEAR FROM CURRENT_VALUES";
     	$result = mysql_query($sql,$db) or die("ERROR: " . mysql_error());
      	$row = mysql_fetch_assoc($result);
      	incrementRequestNumber();
      	
      	return $row['CV_YEAR']."-".sprintf("%04d",$row['CV_SEQUENCE_NUMBER']);
    }
    else
    {
        return $row['CV_YEAR']."-".sprintf("%04d",$row['CV_SEQUENCE_NUMBER']);
    }
}


/*
* Changed 050218 - Request number is just a number that is incremented
*/

function incrementRequestNumber($requestNumber="none")
{
	global $db;

	//If a requestNumber isn't specified, get the current value
	$sql = "SELECT CV_SEQUENCE_NUMBER FROM CURRENT_VALUES";
	$result = mysql_query($sql,$db) or die("ERROR: " . mysql_error());
	$row = mysql_fetch_assoc($result);

	$newNum=$row['CV_SEQUENCE_NUMBER'] + 1;

	$sql  = "UPDATE CURRENT_VALUES SET CV_SEQUENCE_NUMBER='";
	$sql .= $newNum ."'";

	mysql_query($sql,$db) or die("ERROR: " . mysql_error());

}

Select allOpen in new window