[development] How to get the auto-increment value

[Larry Garfield]

Yep, you're doing it backwards, actually. :-)

In D6:

db_query("INSERT INTO {foo} (a, b) VALUES (%d, '%s')", 1, 'hi');
$cid = db_last_insert_id(); // The DB auto-generated this value.

In D7:

$cid = db_insert()
  ->fields(array(
    'a' => 1, 
    'b' => 'hi'
  ))
  ->execute();
// db_insert()->execute() returns the new auto-inc value automatically.

Note: In D7, you can do a multi-insert statement where several inserts run as 
a set.  That is faster than issuing separate queries, but the return value is 
then undefined and you do not have access to the last-insert-id.  Decide if 
you *actually* need to auto-generated ID right then and then pick multi-insert 
or not as appropriate.

As to your other question, the max number of rows, bear in mind that the 
number of rows may not correspond go the largest auto-inc value.  If you ever 
delete a row then the two will no longer match up.  Depending on which you 
care about, either of the following are valid (this is D7 syntax):

$num_records = db_query("SELECT COUNT(*) FROM {foo}")->fetchField();

$max_cid = db_query("SELECT MAX(cid) FROM {foo}")->fetchField();

Cheers.

--Larry Garfield

On Monday, March 28, 2011 5:59:14 am Austin Einter wrote:
> Thanks Nancy.
> Do you mean that in my code I should not put the value for "cid". That
> means $lastid (in red) should be removed.
> 
> 
>  *$lastid = 0;
>   for($delta = 0; $delta < $all_companies; $delta++)
>   {
>  $lastid = db_last_insert_id(rs_companies, cid) + 1;
>       db_query(
>     'INSERT INTO {rs_companies} ( cid, uid, prevcompany, joindate,
> releasedate) '
>       ."VALUES (%d, '%d', '%s', '%d', '%d')",
>  $lastid,  *
> *$user->uid*
> 
> 
> Regards
> Austin
> 
> On Mon, Mar 28, 2011 at 4:20 PM, nan wich <nan_wich at bellsouth.net> wrote:
> >   You don't need to provide the value for an auto increment field.
> > 
> > "Db_last_insert_id()" is how you get the value *after* the insert, if you
> > need it at all.
> > 
> > 
> > *Nancy*
> > 
> > 
> > 
> > Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L.
> > King, Jr.
> > 
> >  ------------------------------
> >  *From:* Austin Einter
> >  
> >  I have a table, where 'cid' field is of type "serial".
> >  
> >  *$lastid = 0;
> >  
> >   for($delta = 0; $delta < $all_companies; $delta++)
> >   {
> >  
> >  $lastid = db_last_insert_id(rs_companies, cid) + 1;
> >  
> >       db_query(
> >     
> >     'INSERT INTO {rs_companies} ( cid, uid, prevcompany, joindate,
> > 
> > releasedate) '
> > 
> >       ."VALUES (%d, '%d', '%s', '%d', '%d')",
> >  
> >  $lastid,  *