TalkPHP - Last Inserted ID?

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - Last Inserted ID? (http://www.talkphp.com/general/1565-last-inserted-id.html)

WinSrev

12-01-2007 12:52 AM

Last Inserted ID?

Hey,

I've been trying for a while to get the last inserted id of a table to use in my next query, the problem is mysql_insert_id seens to produce an empty return, any ideas?

Thanks :-)

Wildhoney

12-01-2007 01:25 AM

Does the table you are inserting into contain a primary, auto increment index?

WinSrev

12-01-2007 01:47 AM

Indeed it does :-) although not in correlation to the other table.

Wildhoney

12-01-2007 02:47 AM

You'll have to paste me your INSERT statement so that I can see.

WinSrev

12-01-2007 02:53 AM

Theres 4, but, i'm trying to do it with PHP if that's possible? It's just a standard insert query (forums based).

Wildhoney

12-01-2007 02:58 AM

Well, it's going to get the ID from the very last query, regardless of whether or not it's an INSERT or not. Do you happen to have another query in between the INSERT you want the ID from, and the mysql_insert_id call?

DragonBe

12-01-2007 01:31 PM

Hey Winsrev,

A simple example might show you where you might have done things wrong.

PHP Code:


		
			
if ($conn = mysql_connect('hostname:port', 'username', 'password')) {
  if ($db = mysql_select_db('database', $conn)) {
    $query = mysql_query(sprintf("INSERT INTO `table` SET `field_a`='%s', `field_b`='%s'", $clean_field_a, $clean_field_b), $conn);
    $last_insert_id = mysql_insert_id($conn); // returns last auto_incremented id
    $affected_rows = mysql_affected_rows($conn); // returns how many rows were affected
  } else {
    $error_db = mysql_errno($conn) . ": " . mysql_error($conn);
  }
} else {
  $error_db_conn = mysql_errno($conn) . ": " . mysql_error($conn);
}

Hope this cleared some of your questions.

Good luck,

DragonBe

WinSrev

12-01-2007 05:57 PM

It's order is:
First Query
mysql_insert_id();
Second Query
Third Query
mysql_insert_id();
Fourth Query

Problem is the second mysql_insert_id produces nothing although the first one works.

Wildhoney

12-01-2007 06:06 PM

Is the third query not an INSERT? 'Cause if you want the ID from the second query then you should put it after the second query, not after the third.

WinSrev

12-01-2007 06:08 PM

They're all inserts? There's no selects, updates, etc... Just inserts.

WinSrev

12-01-2007 08:10 PM

Actually, i just took a look at my query's and realised the problem is the first bit seen as php doesn't like giving line numbers of mysql errors. It's all fixed now, thanks for your help :D

SOCK	12-01-2007 09:02 PM

Incidentally, you can often solve an odd problem like this by using straight SQL, e.g.

(using SQL to retrieve the ID value and store as a variable)

Code:

SELECT @last_id:= LAST_INSERT_ID();

Now whenever you need to retrieve that value or use it within an SQL statement (all within the same login session, e.g. same script execution of course), just refer to it using the MySQL variable @last_id. Several calls to mysql_query() later it will still be the same value.

WinSrev

12-01-2007 09:22 PM

Problem is, by the time that's called another record might have alread been inserted causing you to get an unexpected output.

bdm	12-01-2007 09:58 PM

Isn't mysql_insert_id() a little dangerous if multiple people are using the system?

WinSrev

12-01-2007 10:07 PM

Not really, it gets the id of exactly the last query, if you run it straight after the query that is. If you select last_inserted_id then chances are it'd be dangerous.

aristoworks

12-02-2007 02:03 PM

Taken From: http://us3.php.net/mysql_insert_id

mysql_insert_id() converts the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). If your AUTO_INCREMENT column has a column type of BIGINT, the value returned by mysql_insert_id() will be incorrect. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query.

Haris

12-03-2007 01:54 AM

Quote:

Originally Posted by aristoworks (Post 5040)

^^ You deserve rep for it.

Now, I need to update my scripts. :-)

Wildhoney

12-03-2007 02:30 AM

Repped and thanked :-) I must admit that I didn't even know that, and so it's a good piece of information to have!

aristoworks

12-03-2007 03:53 PM

Thanks!

Thanks guys. Glad to help out. I too have some "updating" to do.

Jon

All times are GMT. The time now is 05:17 PM.