>-----Original Message----- >From: PJ [mailto:af.gourmet@xxxxxxxxxxxx] >Sent: Thursday, February 26, 2009 11:27 AM >To: Jerry Schwartz >Cc: ash@xxxxxxxxxxxxxxxxxxxx; 'Gary W. Smith'; 'MySql'; php- >general@xxxxxxxxxxxxx >Subject: Re: RE: non-auto increment question > >Jerry Schwartz wrote: > >>> Being rather new to all this, I understood from the MySql manual that >>> the auto_increment is to b e used immediately after an insertion not >>> intermittently. My application is for administrators (the site owner >& >>> designates) to update the database from and administration directory, >>> accessed by user/password login... so there's really very little >>> possibility of 2 people accessing at the same time. >>> By using MAX + 1 I keep the id number in the $idIn and can reuse it >in >>> other INSERTS >>> >> [JS] Are you looking for something like LAST_INSERT_ID()? If you >INSERT a >> record that has an auto-increment field, you can retrieve the value >> that got >> inserted with "SELECT LAST_INSERT_ID()". It is connection-specific, so >> you'll always have your "own" value. You can then save it to reuse, >either >> as a session variable or (more easily) as a hidden field on your form. >> >Thanks, Jerry, > > >You hit the nail on the head.:) > [JS] I'm glad to hear it. >To refine my problem (and reduce my ignorance),here's what is happening >on the form page: > >There is a series of INSERTs. The first inserts all the columns of >"book" table except for the id, which I do not specify as it if auto- >insert. > >In subsequent tables I have to reference the book.id (for transitional >tables like "book_author"(refers authors to book) etc. > [JS] Okay. >If I understand it correctly, I must retrieve ("SELECT >LAST_INSERT_ID()") after the first INSERT and before the following >insert; and save the id as a string ($id)...e.g. $sql = "SELECT >LAST_INSERT_ID() AS $id" [JS] You are confusing database column names with PHP variable names. You don't need an alias at all, unless you feel like it for reasons of convenience or style. Assume that $title is your book title, and that the first column is an auto-increment field. The first two queries should look like $query_insert = "INSERT INTO book VALUES (NULL, '$title', ...)"; and $query_select_id = "SELECT LAST_INSERT_ID()"; Of course, you need to actually execute the two queries. The first one doesn't return anything (check for errors, of course). The second one retrieves the ID of the record you just inserted. Now retrieve the value returned by the SELECT statement and put it into a variable. You'll use something like $row_selected = mysql_query($query_select_id) or die("$query_select_id failed"); $last_id = mysql_fetch_array($row_selected) or die("Unable to fetch last inserted ID"); and you have what you want. You can now use $last_id anywhere you want, until your script ends. This is all very simplified, but I think you can get my drift. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com >I need clarification on the "AS $id" - should this be simply id(does >this have to be turned into a value into $id or does $id contain the >value? And how do I retrieve it to use the returned value for the next >$sql = "INSERT ... - in other words, is the id or $id available for the >next directive or do I have to do something like $id = id? >I'm trying to figure this out with some trials but my insert does not >work from a php file - but it works from command-line... that's another >post. > >-- > >Phil Jourdan --- pj@xxxxxxxxxxxxx >http://www.ptahhotep.com >http://www.chiccantine.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
