Solved

last_insert_id always returns the same non-zero value

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

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with mod_substitute 18 80
Securing source code... theoretical question 10 49
emailing registered email addresses in phpmyadmin 5 71
Best hashing method? 1 23
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…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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 …

752 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