Last Inserted ID?

WinSrev · 12-01-2007, 12:52 AM

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

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.

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 guys. Glad to help out. I too have some "updating" to do.

Jon