LAST_INSERT_ID is returning same value everytime

I'm working on a little script... it was working fine until i started messing around with randomizing rows within the table.

My problem is that in one of my PHP files the query SELECT LAST_INSERT_ID() is now returning the value 2 every time even though the AUTO_INCREMENT number is now up to 9.

I know its not a problem with the code of LAST_INSERT_ID or the code around because I have not touched it.

What I am worried about is that I was messing around with pulling out random rows from the table in another PHP file and i'm not sure how everything I did works.

I used offset and limit in when I was first trying to randomize. And currently my code is:

function ShowRandomPic(){
	mysql_connect("localhost", "username", "password") or die ('Error: ' . mysql_error());
	mysql_select_db ("database");
	$select = mysql_query("SELECT * FROM `table` ORDER BY RAND() LIMIT 0,1;");
	$row = mysql_fetch_array($select, MYSQL_ASSOC);
        $ID = "{$row['ID']}";
	$fileName = "{$row['FileName']}";
	print $fileName;

	echo "<img src='images/" . $fileName. "'>";

}

Code (markup):

I'm just worried that i did something to my DB... any ideas?

 

can you give the code regardiing the LAST_INSERT_ID problem?

 

Code for LAST_INSERT_ID:

	
mysql_connect("localhost", "user", "password") or die ('Error: ' . mysql_error());
mysql_select_db ("database");
$lastID =  mysql_query("SELECT LAST_INSERT_ID()");
$newID = $lastID + 1;
return $newID;

Code (markup):

(Prints "Resource id #2" every time no matter if i put new entry in the table or not)

Code to upload file information to the DB:

$validEXT = False;
$ext = findEXT ($_FILES['uploadedfile']['name']);
$filename = getNextID(). "." .$ext;

$targetpath = "pictures/";
$targetpath = $targetpath . $filename;


if (copy($_FILES['uploadedfile']['tmp_name'], $targetpath)){
	echo "The file ".basename($_FILES['uploadedfile']['name']). " was successfully uploaded.";
	$validEXT = True;
}else{
	echo "There was an error uploading the file";
}

if($validEXT == TRUE){	
	SubmitToDB($filename);
}else{
	echo "The file ".basename($_FILES['uploadedfile']['name']). " was successfully uploaded.";
}

Code (markup):

And finally, function SubmitToDB:

function SubmitToDB($FN){
	mysql_connect("localhost", "username", "password") or die ('Error: ' . mysql_error());
	mysql_select_db ("database");
	$query = "INSERT INTO table (ID, FileName, AveRating, TimesRated)VALUES ('NULL','".$FN."', 'NULL', 'NULL')";
	mysql_query($query) or die ('Error updating database');
}

Code (markup):

What is supposed to happen is the image that is uploaded is supposed to be renamed the next AUTO_INCREMENT. The files are uploading, the ID is auto incrementing, but now the file name for every picture uploaded is "3.(file ext)".

 

try to change your insert to something like this:

"INSERT INTO table (FileName, AveRating, TimesRated)VALUES ('".$FN."', 'NULL', 'NULL')";

and see if it works?

 

Hi,

^^Now you've to mysql_fetch_array to get the number, or better use mysql_insert_id directly. To get right value, last id must be retrieved after insert, without dropping connection and reconnect:

function SubmitToDB($FN){
	mysql_connect("localhost", "username", "password") or die ('Error: ' . mysql_error());
	mysql_select_db ("database");
	$query = "INSERT INTO table (ID, FileName, AveRating, TimesRated)VALUES ('NULL','".$FN."', 'NULL', 'NULL')";
	mysql_query($query) or die ('Error updating database');
return mysql_insert_id();
}

PHP:

Regards

 

last_insert_id return the value only associated with the current mysql resource. If you start a new connection and do not make an insert, you cannot get the last id inserted using this method. You would need to query the table for the last row instead. "SELECT id FROM my_table ORDER BY id DESC LIMIT 1;"

The reason it doesn't work the way you are expecting, is that you would end up with colissions. If someone inserted a row after your insert, but before your last_insert_id query. it would return the wrong result. This would be completely unreliable, so it is available only to the current resource.

 

Alright, thanks for the replies guys. I'll be trying some of this stuff later today.

I don't get why it would return 2 over and over again though... it doesn't make sense to me. Any ideas?

 

$lastID =  mysql_query("SELECT LAST_INSERT_ID()");
$newID = $lastID + 1;

PHP:

$lastID above is mysql resource result. To get the value itself, you must use mysql_fetch_array() or similar functions as mysql_fetch_assoc() , mysql_fetch_row()...

In this case it contains size of mysql resource result array (which contains last id, so array size is always 1).
return 1+1

 

I'm sorry... I wasn't clear enough, my fault. It is actually returning three in that instance and i was just subtracting 1 for simplicity. I don't understand why it doesn't work now, it just did two days ago.

 

anyone have any ideas?