use LAST_INSERT_ID() to get the last inserted id

http://www.mysql.com/doc/G/e/Getting_unique_ID.html

and

'The most recently generated ID is maintained in the server on a
per-connection basis. It will not be changed by another client. It will not
even be changed if you update another AUTO_INCREMENT column with a non-magic
value (that is, a value that is not NULL and not 0). '

Regards,
Dan


> -----Original Message-----
> From: Lee P Reilly [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, 21 February 2002 8:50 a.m.
> To: MySQL
> Subject: insert & select in ONE statement?
>
>
> Hi,
>
> I wonder if anyone can offer me some advice with this one:
>
> I have a table called 'iq_data' holding just a primary key and a field
> called 'iq_data'.
>
> +--------+------------+------+-----+---------+----------------+
> | Field  | Type       | Null | Key | Default | Extra          |
> +--------+------------+------+-----+---------+----------------+
> | iqid   | int(11)    |      | PRI | NULL    | auto_increment |
> | iqdata | mediumtext |      |     | NULL    |                |
> +--------+------------+------+-----+---------+----------------+
>
> 'iq_data' contains the contents of a plain text file in the following
> format:
>
>         .0000010     .1083649E+03     .2186916E+02
>         .0013470     .1993729E+03     .2738670E+02
>         ==> + approx. 100-1000 more lines.
>
>
> After I insert data into the table, I need to get the iqid that was
> generated. One solution is to do something like:
>
>   SELECT iqid from iq_data where iqdata="<the contents of the file>";
>   // ^ this will give me the correct answer, but it may be a
>   // little inefficient as there may be thousands of records, and the
>   // search string is very large
>
>       or
>
>   SELECT iqid from iq_id order by iqid (and get the last iqid generated)
>   // ^ querying like this immediately after inserting data to the table,
>   // but assumes that another record has not yet been added
>
> A problem will arise if  say, data is inserted by user 1, and then by
> user 2, and then the iqid for user1 is requested, but the iqid for user2
> will be returned.
>
> So...
>
> Is there any way I can e.g. insert the data into the table and return
> the value of the PK that was generated in one statement? If not, of the
> 2 solutions above what is more efficient? Is there are more elegant
> solution?
>
> Thank you very much for your time; hope someone can help ;-)
>
> - Best regards,
>
> Lee Reilly
>
> </My SQL query>
>
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to