MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:PJ Date:February 26 2009 4:27pm
Subject:Re: [PHP] RE: non-auto increment question
View as plain text  
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.:)

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.

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"
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@stripped
http://www.ptahhotep.com
http://www.chiccantine.com

Thread
Re: [PHP] RE: non-auto increment questionPJ26 Feb
  • RE: [PHP] RE: non-auto increment questionJerry Schwartz26 Feb
  • RE: [PHP] RE: non-auto increment questionJerry Schwartz26 Feb
    • Re: [PHP] RE: non-auto increment questionPJ26 Feb
      • RE: [PHP] RE: non-auto increment questionJerry Schwartz26 Feb
  • RE: [PHP] RE: non-auto increment questionGary W. Smith27 Feb
RE: [PHP] RE: non-auto increment questionJerry Schwartz26 Feb
Re: [PHP] RE: non-auto increment questionPJ26 Feb
RE: [PHP] RE: non-auto increment questionJerry Schwartz26 Feb
Re: [PHP] RE: non-auto increment questionPJ11 Mar