Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

last_insert_id always returns the same non-zero value

Posted on 2008-06-21
4
Medium Priority
?
563 Views
Last Modified: 2013-12-12
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.
0
Comment
Question by:geckogully
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
ID: 21839762
>> 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
 

Author Comment

by:geckogully
ID: 21839851
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21840037
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
 

Author Closing Comment

by:geckogully
ID: 31469500
Thanks to both of you!  To one for solving it, and to the other for explaining it.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question