Solved

last_insert_id always returns the same non-zero value

Posted on 2008-06-21
4
545 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
  • 2
4 Comments
 
LVL 82

Accepted Solution

by:
hielo earned 125 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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now