RE: Re[2]: [PHP-WIN] SQL SELECT while INSERTING

>> Nope. You have to insert the record first (otherwise how the heck >> will MySQL know what the next key is?). After the record is >> inserted, you can access the key with: mysql_insert_id ([int >> link_identifier]) This is not entirely true. There exists a way to determine the auto-inc key before inserting (this is academically though). First one have to know the incremental value of the key, and then query the database for maximal value of the present auto-inc-key. Then it is simply a matter if a computation to determine the next key value. Why some one though would like to do this is another question. This is though a risky operation to do, since if between the first select and the last insert some one else is doing an insert in the database, this value will be inconsistent. I once had to deal with the problem above. When I did insert new records in the database, I had to reread the record to determine the key value for the record. It all ended up that I had to write a voting mechanism to determine weather I had the right key value or not - nothing I would like to recomed inexperinced programmers to do. >Hmmm... ok for MySQL, but I think access is a little different. At >least when you manually add a record, the key is assigned immediately. >I'll do some experimenting and call back if I get something. > >Gonzalo. > >>> -----Original Message----- >>> From: Asendorf, John [mailto:JAsendorf@lcounty.com] >>> Sent: Thursday, 7 December 2000 06:42 >>> To: Php-Windows (E-mail) >>> Subject: [PHP-WIN] SQL SELECT while INSERTING >>> >>> >>> Hello all, >>> >>> Have a quick question. Can I select an auto-incrementing key >>> at the same time I am 'inserting into' a new record... No, you can't select it at_the_same_time, since there is no such operation supplied with in SQL (standard SQL does not in no way return this value). On the other hand once could imagine support in the API (php in this case) where the API rereads the insert to determine which key-value is present in the row, and then a return value (or rather a structure) from the function would supplies you with this value - but I can imagine only a lunatic programmer would be willing to put in this kind of support in the API... >>> using Access ODBC >>> >>> like this >>> >>> $sql = "INSERT INTO blocked (page,name,email,attempt) VALUES >>> ('$page','$name','$email','$attempt') >>> >>> AND GET THE KEY FROM THIS ENTRY HERE >>> >>> ;"; >>> >>> Thanks all, >>> >>> John