how to get primary key value after just creating it... 1

[spewn]

i'm using this to create a new row in my database:

$sth2 = $dbh->prepare("INSERT INTO emailgroup1 VALUES(0,'$userFirstName','$userLastName','$userEmail','','','','','','')");
$sth2->execute;

it automatically increments the primary key, the first value, and i need to find out the value, (number) of that key...how is this possible?

- g

 

[sleipnir214]

There are a couple of ways.

One is to use MySQL's LAST_INSERT_ID() function. See

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

Another is to use functions made available by your programming language. PHP, for example, has a mysql_insert_id() See:

http://www.php.net/manual/en/function.mysql-insert-id.php

Want the best answers? Ask the best questions! TANSTAAFL!

 

[spewn]

i'm actually using perl...

i tried this:

$id = $query->insertid;

but i'm getting this error:

Can't call method "insertid" on an undefined value at send-email.cgi line 39.

no ideas?

- g

 

[audiopro]

You could select the latest table entry, like this.
The auto increment field is called NUM.

my $make_blank=$dbh->prepare("INSERT INTO TABLE VALUES ()");
$make_blank->execute();

my $get_number=$dbh->prepare("SELECT NUM FROM TABLE ORDER BY NUM DESC");
$get_number->execute();
@FILENUM=$get_number->fetchrow_array();

Keith

http://www.studiosoft.co.uk

 

[r937]

keith, don't do that, you will end up in a race condition

another way to approach the problem is to recognize that the auto_increment is a surrogate key and that the table should have another candidate key which uniquely identifies each row

thus, you can simply query the row back using the value of this other key

but LAST_INSERT_ID() is the best way to go here

r937.com | rudy.ca

 

[audiopro]

Not sure I understand how this sets up a race condition.
A new record is created then the value is read. I have used this method without problems, have I been lucky?

Keith

http://www.studiosoft.co.uk

 

[r937]

yes, lucky

user 1 inserts into the table, getting auto_increment value 42

user 2 inserts into the table, getting auto_increment value 43

user 1 runs the select for the highest num and gets 43

user 2 runs the select for the highest num and gets 43

did you read that article? ;-)


r937.com | rudy.ca

 

[audiopro]

Thanks for the explanation.
I hadn't considered the multiple thread possibility. I had to do a bit of reading elsewhere to get a grip of it but what you said now makes sense. I guess I am too used to a low traffic environment and the possibility of two users adding files together has not yet happened. I can see the headaches that could cause where some data just disappears. Hava a star for saving me future problems.
What would LAST_INSERT_ID() return if the table did not have auto increment set?

Keith

http://www.studiosoft.co.uk

 

[r937]

What would LAST_INSERT_ID() return if the table did not have auto increment set?

no idea, sorry


time for a test?



r937.com | rudy.ca