Hi
I get very tempted when conctrsucting queries in PHP, to use timestamps as ID values for database rows, as this means i dont have to create two queries when inserting a row, one to get MAX id to add one too, in order to obtain the new id, and one query to insert the data using this id.
Anyway basically, is it safe to use timestamps as identification values, would there ever be a situation where two timestamps would be identical?
Thanks
http://alljammin.com
safe to use timstamps as id's ?
Moderator: General Moderators
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
Which database do you use? Can't you use a autoincrementing ID value?
Timestamps (assuming you mean UNIX ones) could be identical if two records are submitted within the same second. Personally I would use an autoincrementing ID value, thus removing the need for two queries and ensuring that the same ID cannot be given twice.
Mac
Timestamps (assuming you mean UNIX ones) could be identical if two records are submitted within the same second. Personally I would use an autoincrementing ID value, thus removing the need for two queries and ensuring that the same ID cannot be given twice.
Mac
-
microthick
- Forum Regular
- Posts: 543
- Joined: Wed Sep 24, 2003 2:15 pm
- Location: Vancouver, BC
Setting up auto-increment id fields in MySQL is a cinch, especially if you use MySQL Control Center (http://www.mysql.com/products/mysqlcc/index.html).
In MySQL CC it's just a matter of making your id field your primary key, then selecting the AUTO INCREMENT checkbox.
In MySQL CC it's just a matter of making your id field your primary key, then selecting the AUTO INCREMENT checkbox.
And after you have inserted the value run this statement "SELECT last_insert_id() AS lastid" and then read the resultset. This isn't that expensive of an operation as this value is present on the open connection to MySQL.
According to something I read somewhere this value is good for each conenction, meaning that if you have to apps, pages, whatever, inserting at the same time each will get the proper value because it's part of the original connection.
As for the timestamps I would say away from them. It is possible to get a duplicate and on 2030 you'll be screwed (unless you have upgraded since then) because that's when the timestamp field rolls over to 00000000000. This is not true on the date or date/time fields though.
According to something I read somewhere this value is good for each conenction, meaning that if you have to apps, pages, whatever, inserting at the same time each will get the proper value because it's part of the original connection.
As for the timestamps I would say away from them. It is possible to get a duplicate and on 2030 you'll be screwed (unless you have upgraded since then) because that's when the timestamp field rolls over to 00000000000. This is not true on the date or date/time fields though.
assuming you didn't make the table:
create table blah(
id_field bigint NOT NULL auto_increment PRIMARY KEY,
some_field int,
another_field text
) TYPE=MyISAM;
anything italicised is a name you have ot make up.
that creates a simple table with three fields, one is a primary key (optional) and the auto-incremented field
if the table is created
alter table balh2 modify id_field bigint NOT NULL auto_increment PRIMARY KEY
i added the primary key again becuase i use id feilds as auto incremented primary keys
create table blah(
id_field bigint NOT NULL auto_increment PRIMARY KEY,
some_field int,
another_field text
) TYPE=MyISAM;
anything italicised is a name you have ot make up.
that creates a simple table with three fields, one is a primary key (optional) and the auto-incremented field
if the table is created
alter table balh2 modify id_field bigint NOT NULL auto_increment PRIMARY KEY
i added the primary key again becuase i use id feilds as auto incremented primary keys