Need Auto - increment function for the ID field

Hi,

I have an requirement in my project which is as follows,

we used one field called listingID,it is unique field ,used
as primary
key..

In my project,i have to insert the listingID along with
other values
every time while creating the record using web page..

For this, i have taken the max(listingID) from table and
incremant the
ID + 1 and written the Query to insert the values along
with
listingID.

Now we want the requirement as follows,

 we need to have function for the auto increment for the
listingID,
means every time while creating or saving the
record,listingID should
be automatically generated instead of every time taking
maxid and
increment it and inserting to table.

If anybody know the solution to auto increment for the ID
,plz help
me..


Thank u,
Dhana.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "PHP & MySQL" group.
To post to this group, send email to phpmysqlgooglegroups.com
To unsubscribe from this group, send email to
phpmysql-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/phpmysql?hl=en
-~----------~----~----~----~------~----~------~--~---

Dhana,

Why not just change your database field
"listingID" to an AutoIncrement
field? This way, instead of running something like:
INSERT INTO YourTableName (insertID, NameField) VALUES (1,
"Dhana") you'd
just do this:
INSERT INTO YourTableName (NameField) VALUES
("Dhana")

Sarel

-----Original Message-----
From: phpmysqlgooglegroups.com [mailto:phpmysqlgooglegroups.com] On Behalf
Of Dhana
Sent: 22 October 2007 02:46 PM
To: PHP & MySQL
Subject: Need Auto - increment function for the ID field


Hi,

I have an requirement in my project which is as follows,

we used one field called listingID,it is unique field ,used
as primary
key..

In my project,i have to insert the listingID along with
other values
every time while creating the record using web page..

For this, i have taken the max(listingID) from table and
incremant the
ID + 1 and written the Query to insert the values along
with
listingID.

Now we want the requirement as follows,

 we need to have function for the auto increment for the
listingID,
means every time while creating or saving the
record,listingID should
be automatically generated instead of every time taking
maxid and
increment it and inserting to table.

If anybody know the solution to auto increment for the ID
,plz help
me..


Thank u,
Dhana.




--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "PHP & MySQL" group.
To post to this group, send email to phpmysqlgooglegroups.com
To unsubscribe from this group, send email to
phpmysql-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/phpmysql?hl=en
-~----------~----~----~----~------~----~------~--~---

Hi,

   Thanks for the reply.

   We have made the field as "auto increment". We
have also used two
functionalities - mysql_insert_id() and last_insert_id() to
retrieve
the last created id.

   But can you tell me whether the above two syntax will
have any
issue in a web project? Meaning we are going to use it in
multi user
environment - so more than one user can create record.

   So the last_insert_id or mysql_insert_id() will give the
id that
was created lastly from my system alone or the lastly
created id from
any system in that table?

  For your reference - i got the info from the help file as

+++++++++++

"LAST_INSERT_ID(), LAST_INSERT_ID(expr)

Returns the first automatically generated value that was set
for an
AUTO_INCREMENT column by the most recent INSERT or UPDATE
statement to
affect such a column.

mysql> SELECT LAST_INSERT_ID();
        -> 195

The ID that was generated is maintained in the server on a
per-
connection basis. This means that the value returned by the
function
to a given client is the first AUTO_INCREMENT value
generated for most
recent statement affecting an AUTO_INCREMENT column by that
client.
This value cannot be affected by other clients, even if they
generate
AUTO_INCREMENT values of their own. This behavior ensures
that each
client can retrieve its own ID without concern for the
activity of
other clients, and without the need for locks or
transactions."

+++++++++++++

Any help would be helpful...

Regards,
Guru

On Oct 22, 6:23 pm, "Sarel van der Walt"
<sare...gmail.com> wrote:
> Dhana,
>
> Why not just change your database field
"listingID" to an AutoIncrement
> field? This way, instead of running something like:
> INSERT INTO YourTableName (insertID, NameField) VALUES
(1, "Dhana") you'd
> just do this:
> INSERT INTO YourTableName (NameField) VALUES
("Dhana")
>
> Sarel
>
>
>
> -----Original Message-----
> From: phpmysqlgooglegroups.com [mailto:phpmysqlgooglegroups.com] On Behalf
>
> Of Dhana
> Sent: 22 October 2007 02:46 PM
> To: PHP & MySQL
> Subject: Need Auto - increment function for the ID
field
>
> Hi,
>
> I have an requirement in my project which is as
follows,
>
> we used one field called listingID,it is unique field
,used as primary
> key..
>
> In my project,i have to insert the listingID along with
other values
> every time while creating the record using web page..
>
> For this, i have taken the max(listingID) from table
and incremant the
> ID + 1 and written the Query to insert the values along
with
> listingID.
>
> Now we want the requirement as follows,
>
>  we need to have function for the auto increment for
the listingID,
> means every time while creating or saving the
record,listingID should
> be automatically generated instead of every time taking
maxid and
> increment it and inserting to table.
>
> If anybody know the solution to auto increment for the
ID ,plz help
> me..
>
> Thank u,
> Dhana.- Hide quoted text -
>
> - Show quoted text -


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "PHP & MySQL" group.
To post to this group, send email to phpmysqlgooglegroups.com
To unsubscribe from this group, send email to
phpmysql-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/phpmysql?hl=en
-~----------~----~----~----~------~----~------~--~---

Hey,

To be honest, the chances of somebody doing two inserts at
precicely the
same millisecond are really slim. So your chances of having
say:
User 1 inserting
User 2 inserting
Retrieve last_id for User 1 and getting User 2's last_id
... is very very slim.

But, if you are paranoid like I am ... (and
you are using MySQL 5 or
better) then you can do the following:
1) Begin SQL Transaction - I think it's SQL: "BEGIN
TRANSACTION" or
something like that
2) do your insert query
3) get the last_id
4) End/Commit Transaction

Although, there's an alternative way of doing this as well -
and this is to
use something like ADOdb or Propel. These DB Management
Classes calculate
the last-id's a lot better than I've ever done.  They also
give you a lot
more flexibility.  For instance, with propel I did this:
$DBConn->begin(); // starts SQL transaction
$DBConn->executeQuery("INSERT blah blah blah");
// executes the insert query
inside the transaction
$primaryKeyValue = $DBConn->getIdGenerator()->getId();
// get the last-id
from the object in PHP - much better way
$DBConn->commit(); // ends/commits SQL transaction

Sarel

-----Original Message-----
From: phpmysqlgooglegroups.com [mailto:phpmysqlgooglegroups.com] On Behalf
Of Guru
Sent: 23 October 2007 09:54 AM
To: PHP & MySQL
Subject: Re: Need Auto - increment function for the ID
field


Hi,

   Thanks for the reply.

   We have made the field as "auto increment". We
have also used two
functionalities - mysql_insert_id() and last_insert_id() to
retrieve
the last created id.

   But can you tell me whether the above two syntax will
have any
issue in a web project? Meaning we are going to use it in
multi user
environment - so more than one user can create record.

   So the last_insert_id or mysql_insert_id() will give the
id that
was created lastly from my system alone or the lastly
created id from
any system in that table?

  For your reference - i got the info from the help file as

+++++++++++

"LAST_INSERT_ID(), LAST_INSERT_ID(expr)

Returns the first automatically generated value that was set
for an
AUTO_INCREMENT column by the most recent INSERT or UPDATE
statement to
affect such a column.

mysql> SELECT LAST_INSERT_ID();
        -> 195

The ID that was generated is maintained in the server on a
per-
connection basis. This means that the value returned by the
function
to a given client is the first AUTO_INCREMENT value
generated for most
recent statement affecting an AUTO_INCREMENT column by that
client.
This value cannot be affected by other clients, even if they
generate
AUTO_INCREMENT values of their own. This behavior ensures
that each
client can retrieve its own ID without concern for the
activity of
other clients, and without the need for locks or
transactions."

+++++++++++++

Any help would be helpful...

Regards,
Guru

On Oct 22, 6:23 pm, "Sarel van der Walt"
<sare...gmail.com> wrote:
> Dhana,
>
> Why not just change your database field
"listingID" to an AutoIncrement
> field? This way, instead of running something like:
> INSERT INTO YourTableName (insertID, NameField) VALUES
(1, "Dhana") you'd
> just do this:
> INSERT INTO YourTableName (NameField) VALUES
("Dhana")
>
> Sarel
>
>
>
> -----Original Message-----
> From: phpmysqlgooglegroups.com [mailto:phpmysqlgooglegroups.com] On
Behalf
>
> Of Dhana
> Sent: 22 October 2007 02:46 PM
> To: PHP & MySQL
> Subject: Need Auto - increment function for the ID
field
>
> Hi,
>
> I have an requirement in my project which is as
follows,
>
> we used one field called listingID,it is unique field
,used as primary
> key..
>
> In my project,i have to insert the listingID along with
other values
> every time while creating the record using web page..
>
> For this, i have taken the max(listingID) from table
and incremant the
> ID + 1 and written the Query to insert the values along
with
> listingID.
>
> Now we want the requirement as follows,
>
>  we need to have function for the auto increment for
the listingID,
> means every time while creating or saving the
record,listingID should
> be automatically generated instead of every time taking
maxid and
> increment it and inserting to table.
>
> If anybody know the solution to auto increment for the
ID ,plz help
> me..
>
> Thank u,
> Dhana.- Hide quoted text -
>
> - Show quoted text -




--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "PHP & MySQL" group.
To post to this group, send email to phpmysqlgooglegroups.com
To unsubscribe from this group, send email to
phpmysql-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/phpmysql?hl=en
-~----------~----~----~----~------~----~------~--~---