Solved

last_insert_id always returns the same non-zero value

Posted on 2008-06-21
4
546 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
change password links 7 74
problems with mysql ODBC Connector and vbscript 4 28
updating the date data 12 25
datetime in sql 6 31
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

895 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

12 Experts available now in Live!

Get 1:1 Help Now