Re: How to find the autoincremented number?

[David Chamberlin <dwc@nautiboy.com>]

Aha.  I had seen that before, but disregarded it because I was trying to 
just use pearDB calls instead of the mysql calls.  However what I 
noticed this time when looking through there is that there's a 
LAST_INSERT_ID() that I could use in a query.

e.g.,

function addNewEntry( $stuffToPutIn ){
  $sql = "INSERT INTO myTable $stuffToPutIn";
  // I use pearDB with mysql
  $result = $this->db->query( $sql );
  checkDbResult( $result );

  $sql = "SELECT LAST_INSERT_ID()";
  $id = $this->db->getOne( $sql );
  checkDbResult( $id );

  $linkAddress = $baseEntryURL.'?entryId='.$id;

  mailOutLink( $linkAddress );
}

Thank you!

-Dave

Leif K-Brooks wrote:
> http://www.php.net/manual/en/function.mysql-insert-id.php
>
> David Chamberlin wrote:
>
> > Hey,
> >
> > Most of the tables I use identify things using an id which is 
> > auto-generated using autoincrement.  However I can't seem to figure 
> > out a good way to find what value was generated for the ID.
> >
> > For example, let's say I generate a new entry and want to e-mail out a 
> > link to it and the link identifies the entry by ID.  Currently, the 
> > only way I can figure to do this is to first do the INSERT and then do 
> > a SELECT on some unique information that I just inserted (e.g., 
> > submitter's ID and last-modified date).  So for example, I would have:
> >
> > function addNewEntry( $stuffToPutIn ){
> >   $sql = "INSERT INTO myTable $stuffToPutIn";
> >   // I use pearDB with mysql
> >   $result = $this->db->query( $sql );
> >   checkDbResult( $result );
> >
> >   $sql = "SELECT id FROM myTable WHERE ".
> >       "(last_modified_by = $userId) AND ".
> >       "(last_modified_time = $currentTime)";
> >   $result = $this->db->getAll( $sql );
> >   checkDbResult( $result );
> >
> >   $entry = $result[0];
> >
> >   $linkAddress = $baseEntryURL.'?entryId='.$entry->id;
> >
> >   mailOutLink( $linkAddress );
> > }
> >
> > What I'd really like to do is get rid of that SELECT or at least make 
> > it less "hacky" to find out what the id was that got autogenerated.
> >
> > Any ideas?
> >
> > Thanks,
> > Dave

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php