last_insert_id always returns the same non-zero value

I am using last_insert_id to try to retrieve the id of the last record added to a table, just after the insert was done.  The key field is auto_increment.

However, I ALWAYS get "Resource id #26", regardless of how many records there are in the table.

Even when the "next autoindex" figure (according to phpMyAdmin) is up around 31, I still get 26.  I even tried completely clearing the table, but, there it was again - 26.

This is still a system in testing, and I am wondering if I have stuffed about with the table so much it needs rebuilding.

I am not sure what code or other details might be relevant.

All help appreciated!  

Thanks,
Christine.
geckogullyAsked:
Who is Participating?
 
hieloConnect With a Mentor Commented:
>> Resource id #26
That is NOT an auto number. That is a resource. Think of it as  result identifier.

Right after you insert the record, call mysql_insert_id() and it should give you the id of the last inserted record:

mysql_query("INSERT INTO mytable (product) values ('Shoes')");
printf("Last inserted record has id %d\n", mysql_insert_id());
0
 
geckogullyAuthor Commented:
I have changed the last_insert_id to mysql_insert_id, and now I always get a value of spaces (null?) returned.

I am calling the mySQL utilities via functions (i am using php).  I moved the mysql_insert_id one into the main part of the program, rather than via a function, but it still always returns zero.

Should I move the insert into the main program too, so that it is the very last statement executed before the mysql_insert_id?

Or does that not matter, as long as it was the last sql action called?

I have attached code from the main program, followed by the insert function code.

Christine.
		$insert = $db->insert($glob['dbprefix']."CubeCart_inventory", $record_prod);
 
		$result = mysql_query(" select mysql_insert_id() from ".$glob['dbprefix']."CubeCart_inventory");
		echo ("1: result = ");  echo ($result);
 
And the insert function:
 
	function insert ($tablename, $record)
	{
		if(!is_array($record)) die ($this->debug("array", "Insert", $tablename));
		
		$count = 0;
		foreach ($record as $key => $val)
		{
			if ($count==0) {$fields = "`".$key."`"; $values = $val;}
			else {$fields .= ", "."`".$key."`"; $values .= ", ".$val;}
			$count++;
		}	
		
		$query = "INSERT INTO ".$tablename." (".$fields.") VALUES (".$values.")";
		
		$this->query = $query;
		mysql_query($query, $this->db);
		
		if ($this->error()) die ($this->debug());
		
		if ($this->affected() > 0) return true; else return false;
		
	} // end insert

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you misread the suggestion:
$result = mysql_query(" select mysql_insert_id() from ".$glob['dbprefix']."CubeCart_inventory");
            echo ("1: result = ");  echo ($result);

should be:
$result = mysql_insert_id();
echo ("1: result = ");  echo ($result);

or:
$result = mysql_result(mysql_query(" select last_insert_id() from ".$glob['dbprefix']."CubeCart_inventory"), 0,0);
echo ("1: result = ");  echo ($result);

the "problem" was that mysql_query returns a recordset with 1 row / 1 column (which is the resource# you get), so you have to fetch that result (mysql_result is only 1 of the possibilites, but the shortest one)

or simply use the mysql_insert_id() php function without running a query.
0
 
geckogullyAuthor Commented:
Thanks to both of you!  To one for solving it, and to the other for explaining it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.