safe to use timstamps as id's ?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mzfp2
Forum Contributor
Posts: 137
Joined: Mon Nov 11, 2002 9:44 am
Location: UK
Contact:

safe to use timstamps as id's ?

Post by mzfp2 »

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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

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
mzfp2
Forum Contributor
Posts: 137
Joined: Mon Nov 11, 2002 9:44 am
Location: UK
Contact:

Post by mzfp2 »

i'm using MYSQL, im not sure how to use auto incrementing values in MYSQL, and it would also mean i'd have to change alot of tables, but i'll check out MYSQL documentation anyway

thanks :)
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

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.
itbegary
Forum Commoner
Posts: 34
Joined: Sun Jan 05, 2003 2:50 am

Post by itbegary »

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.
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post by m3rajk »

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
mzfp2
Forum Contributor
Posts: 137
Joined: Mon Nov 11, 2002 9:44 am
Location: UK
Contact:

Post by mzfp2 »

thanks for all the lp, that bit about timestamp rolling back to 00000000 on 2030 was particularly interesting, will slowly have to work around my system to fade them out ! thats if im still doing this in another 26 years !
Post Reply