List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:February 26 2009 6:35pm
Subject:RE: [PHP] RE: non-auto increment question
View as plain text  

>-----Original Message-----
>From: PJ [mailto:af.gourmet@stripped]
>Sent: Thursday, February 26, 2009 11:27 AM
>To: Jerry Schwartz
>Cc: ash@stripped; 'Gary W. Smith'; 'MySql'; php-
>general@stripped
>Subject: Re: [PHP] 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@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