I wrote a web application that uses a PHP script to create a more-or-less empty record in a database, then a Perl script to update that record, filling in all of the fields. What I see happening occasionally is that an initialized "empty" record will be re-used by another user. I though that wasn't supposed to happen with the mysql_insert_id() function. Although I may be laying blame in the wrong direction. Here is the PHP code that creates a new record:

// create a new record now, so there are no conflicting App_ID's

$query = "insert into $appTable (EmpID, FIBDE) values ('$EmpID', '$FIBDE')";

$result = mysql_query($query) or die("Cannot execute $query: " . mysql_errno().": ".mysql_error());

// revised Aug 9 2002 to hopefully solve missing app mystery
if (mysql_affected_rows()>0) 
	{
	$App_ID=mysql_insert_id();
	error_log("New Application: EmpID $EmpID - App_ID $App_ID - FIBDE $FIBDE");
	}
else
	{
	error_log("WARNING: New application not initialized properly");
	die("Application unable to initialize. Contact admin.");
	}

$App_ID is an autoincrement column. $EmpID is grabbed from a cookie and associates each employee with an application.
Then $App_ID gets passed in a hidden form field which gets submitted to a Perl CGI. That CGI includes a statement to update the database where App_ID= the hidden form field value of $App_ID.

The problem, or rather the symptom I am seeing, is that sometimes an employee fills out an application, it goes into the database, but when viewing a report of applications, their application is associated with the wrong employee. I hope this makes sense, and any help would be appreciated.

    I assume what you are saying is that what you see in the errorlog is one employeeID, but then when the Application is posted there is a different employeeID than you expected.

    This is a wild guess, but are you sure the cookie contains the right employeeID? Do they enter an employeeID when they fill out the application? Are they sharing a computer?

      Thanks for your respone. I am quite sure that the cookies are not the problem, because the users are at different sites throughout the state. The cookie gets set two pages before the application page.

      Now that I have been examining my logs a little longer, I have some more questions that might help me with my detective work (more strictly mysql-related):

      1) What is the value of mysql_affected_rows() if an insert or update statement fails?

      2) If an update statement tries to update a row that doesn't exist (for example, update where app_id=400 and there is no such row), what kind of result gets returned?

      Another thing I realized after browsing this board a bit is that I am using an older version of mySQL where the autoincrements may be reused if records are deleted. However I see no evidence of records being deleted at any point, just of them being initialized and then not updated.

      Since I'm using Perl & DBI in my other script, this may get beyond the scope of this board but... if anyone has answers to 1 & 2 let me know. Thank you.

        Ad question 1: The value of mysql_affected_rows() is 0, if an insert or update statement fails

        Ad question 2: In this case the value of mysql_num_rows() is 0

          Write a Reply...