By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,865 Members | 1,453 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,865 IT Pros & Developers. It's quick & easy.

Next Autoindex

P: n/a
Hi guys, which query should I do to get it?
thx to all, chr
Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Christian Giordano wrote:
Hi guys, which query should I do to get it?


There's a SQL function LAST_INSERT_ID().
http://dev.mysql.com/doc/mysql/en/in...functions.html

There's also a MySQL API call, mysql_insert_id().
http://dev.mysql.com/doc/mysql/en/mysql-insert-id.html

Regards,
Bill K.
Jul 23 '05 #2

P: n/a
> There's a SQL function LAST_INSERT_ID().
http://dev.mysql.com/doc/mysql/en/in...functions.html


I've already tried with this, but it returns 0 and I don't know how to
tell it in which table to get the number, so I used "SHOW TABLE STATUS"
and then I look for my data :S

thx, chr
Jul 23 '05 #3

P: n/a
Christian Giordano wrote:
There's a SQL function LAST_INSERT_ID().
http://dev.mysql.com/doc/mysql/en/in...functions.html


I've already tried with this, but it returns 0 and I don't know how to
tell it in which table to get the number, so I used "SHOW TABLE STATUS"
and then I look for my data :S


"LAST_INSERT_ID() ...
The last ID that was generated is maintained in the server on a
per-connection basis. This means the value the function returns to a
given client is the most recent AUTO_INCREMENT value generated by that
client."

If you connect to the database and query for LAST_INSERT_ID before you
do any inserts or updates that would cause an id to be generated, then
yes, it would be zero. The function only returns the last id generated
_during your current client session_.

I suspect that if you're trying to get the last insert id without having
inserted anything, you're not using it for its intended purpose. The
intended purpose is to make it easier to insert rows in dependent tables
that reference a row you inserted into your primary table:

INSERT INTO myMasterTable (val1, val2) VALUES (123, 456); /* autoinc
field is not named, thus gets a new incremented value */
INSERT INTO myDependentTable (fKey, val3, val4) VALUES
(LAST_INSERT_ID(), 789, 'abc');

This works because the last insert id is isolated to your current client
connection. Even if someone else is working on the database
concurrently and does their own insert to myMasterTable between the two
inserts you do, you still get the correct results.

But the purpose of LAST_INSERT_ID() is not for querying the greatest
value in an autoincrementing column.

Regards,
Bill K.
Jul 23 '05 #4

P: n/a
On Thu, 3 Mar 2005 09:48:30 +0000 (UTC), in mailing.database.mysql
Christian Giordano <ch*******@urmomlikesspam.com> wrote:
| > There's a SQL function LAST_INSERT_ID().
| > http://dev.mysql.com/doc/mysql/en/in...functions.html
|
| I've already tried with this, but it returns 0 and I don't know how to
| tell it in which table to get the number, so I used "SHOW TABLE STATUS"
| and then I look for my data :S
|
| thx, chr


Don't close the connection to the table before calling this function
i.e.

(JScript)
db = Server.CreateObject('ADODB.Command');
db.ActiveConnection = MM_CONNECTION_STRING;
db.CommandText = "INSERT INTO myTable ........";
db.Execute();

//--- get id of last value inserted into database
db.CommandText = "SELECT LAST_INSERT_ID()";
evdID = db.Execute().Fields(0).Value;

db.ActiveConnection.Close();

---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.