On Thu, 2009-02-26 at 11:27 -0500, PJ wrote:
> 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.
>
Here's how I mostly do it (albeit simplified):
$query = "INSERT INTO `sometable`(`title`,`content`)
VALUES('$title','$content')";
$result = mysql_query($query);
$autoId = mysql_insert_id($result);
$query = "INSERT INTO `another_table`(`link_id`,`value`)
VALUES($autoId,'$value');
$result = mysql_query($query);
No need to call another query to retrieve the last inserted id, as it is
tied to the last query executed within this session.
Ash
www.ashleysheridan.co.uk
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
