Can I determine the index (value) of an auto-increment field

Hello MYSql Lovers,

Is there a handy way (using PHP) to determine the value of an auto-
increment field when I am going to
insert a record into a table?

by this I mean, without counting the records in the table already and
adding 1? or using MAX(id) + 1 ,etc?

A more challenging question (for me at least), suppose I have an empty
table with an auto-increment field (id),
and I am going to insert all the files in a given directory in the the
database table, at insert time, is there a way I can predict (or know)
the value of "id"??

I would like to create a value in another field based on the value (a
number) of the id field.
Like col1 value = id + (MOD %30 of id).

I know that i can load the table first, then do an update on col1
using the formula based on the value of "id".

I was wondering if there was a better way, etc.

thanks
eholz1

eholz1 wrote:
> Hello MYSql Lovers,
>
> Is there a handy way (using PHP) to determine the value of an auto-
> increment field when I am going to
> insert a record into a table?
>
> by this I mean, without counting the records in the table already and
> adding 1? or using MAX(id) + 1 ,etc?
>
> A more challenging question (for me at least), suppose I have an empty
> table with an auto-increment field (id),
> and I am going to insert all the files in a given directory in the the
> database table, at insert time, is there a way I can predict (or know)
> the value of "id"??
>
> I would like to create a value in another field based on the value (a
> number) of the id field.
> Like col1 value = id + (MOD %30 of id).
>
> I know that i can load the table first, then do an update on col1
> using the formula based on the value of "id".
>
> I was wondering if there was a better way, etc.
>
> thanks
> eholz1
>

Sorry, you can't reliably predict the value of the last insertid before
the row is inserted. For instance - what if you had two connections
inserting concurrently? Both could "predict" the same id, but only one
would get it.

Of course you can use mysql_insert_id() to get the last id inserted.
However, it might be easier to fetch the first id you insert, then run
one SQL statement to update all rows after that one, i.e.

(insert row)
$start = mysql_insert_id();
(insert more rows)
$result = mysql_query("UPDATE mytable " .
"SET col1=MOD(id, 30)" .
"WHERE id >= " . id);

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

> (insert row)
> $start = mysql_insert_id();
> (insert more rows)
> $result = mysql_query("UPDATE mytable " .
> "SET col1=MOD(id, 30)" .
> "WHERE id >= " . id);
>

Or, in one go, on the database server:
INSERT INTO mytable(...) VALUES(...);
SET @Id=LAST_INSERT_ID();
UPDATE mytable SET col1=MOD(@Id, 30) WHERE id=@Id;
-- More rows:
INSERT INTO mytable(...) VALUES(@Id, ...);


This should be done in one connection session, otherwise the variable
@Id is cleared.

Best regards
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Willem Bogaerts wrote:
>> (insert row)
>> $start = mysql_insert_id();
>> (insert more rows)
>> $result = mysql_query("UPDATE mytable " .
>> "SET col1=MOD(id, 30)" .
>> "WHERE id >= " . id);
>>
>
> Or, in one go, on the database server:
> INSERT INTO mytable(...) VALUES(...);
> SET @Id=LAST_INSERT_ID();
> UPDATE mytable SET col1=MOD(@Id, 30) WHERE id=@Id;
> -- More rows:
> INSERT INTO mytable(...) VALUES(@Id, ...);
>
>
> This should be done in one connection session, otherwise the variable
> @Id is cleared.
>
> Best regards

Yes, but it's less efficient than when updating multiple rows. Each
entry has an INSERT, a SET and an UPDATE. This requires three
statements to be parsed and executed for each insert - much slower,
unless you have a very slow database connection.

My way has one INSERT per row, on get last insert id and a single
UPDATE. Parsing/execution will be much faster.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

>> INSERT INTO mytable(...) VALUES(...);
>> SET @Id=LAST_INSERT_ID();
>> UPDATE mytable SET col1=MOD(@Id, 30) WHERE id=@Id;
>> -- More rows:
>> INSERT INTO mytable(...) VALUES(@Id, ...);
>>
>> This should be done in one connection session, otherwise the variable
>> @Id is cleared.
>
> Yes, but it's less efficient than when updating multiple rows. Each
> entry has an INSERT, a SET and an UPDATE. This requires three
> statements to be parsed and executed for each insert - much slower,
> unless you have a very slow database connection.
>
> My way has one INSERT per row, on get last insert id and a single
> UPDATE. Parsing/execution will be much faster.

The last INSERT can have more than one row and could even be delayed, I
think. If the data to insert is not too complicated, you could even
write a stored procedure for the above queries. I think the above
queries are not more load than the "external" ID handling, but it can
take some load of the webserver or the data traffic.

Best regards
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Willem Bogaerts wrote:
>>> INSERT INTO mytable(...) VALUES(...);
>>> SET @Id=LAST_INSERT_ID();
>>> UPDATE mytable SET col1=MOD(@Id, 30) WHERE id=@Id;
>>> -- More rows:
>>> INSERT INTO mytable(...) VALUES(@Id, ...);
>>>
>>> This should be done in one connection session, otherwise the variable
>>> @Id is cleared.
>> Yes, but it's less efficient than when updating multiple rows. Each
>> entry has an INSERT, a SET and an UPDATE. This requires three
>> statements to be parsed and executed for each insert - much slower,
>> unless you have a very slow database connection.
>>
>> My way has one INSERT per row, on get last insert id and a single
>> UPDATE. Parsing/execution will be much faster.
>
> The last INSERT can have more than one row and could even be delayed, I
> think. If the data to insert is not too complicated, you could even
> write a stored procedure for the above queries. I think the above
> queries are not more load than the "external" ID handling, but it can
> take some load of the webserver or the data traffic.
>
> Best regards

William,

I suggest you try it with a few thousand rows. I think you'll find
there is a significant difference in the processing time required.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Feb 6, 2:57 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> eholz1 wrote:
> > Hello MYSql Lovers,
>
> > Is there a handy way (using PHP) to determine the value of an auto-
> > increment field when I am going to
> > insert a record into a table?
>
> > by this I mean, without counting the records in the table already and
> > adding 1? or using MAX(id) + 1 ,etc?
>
> > A more challenging question (for me at least), suppose I have an empty
> > table with an auto-increment field (id),
> > and I am going to insert all the files in a given directory in the the
> > database table, at insert time, is there a way I can predict (or know)
> > the value of "id"??
>
> > I would like to create a value in another field based on the value (a
> > number) of the id field.
> > Like col1 value = id + (MOD %30 of id).
>
> > I know that i can load the table first, then do an update on col1
> > using the formula based on the value of "id".
>
> > I was wondering if there was a better way, etc.
>
> > thanks
> > eholz1
>
> Sorry, you can't reliably predict the value of the last insertid before
> the row is inserted. For instance - what if you had two connections
> inserting concurrently? Both could "predict" the same id, but only one
> would get it.
>
> Of course you can use mysql_insert_id() to get the last id inserted.
> However, it might be easier to fetch the first id you insert, then run
> one SQL statement to update all rows after that one, i.e.
>
> (insert row)
> $start = mysql_insert_id();
> (insert more rows)
> $result = mysql_query("UPDATE mytable " .
> "SET col1=MOD(id, 30)" .
> "WHERE id >= " . id);
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

Hello,

Thanks for for reply, Always good info. Seems like jerry always
wins!!!!
Thanks all for the tip, I will put it into action!!

ewholz