1

Let's take a simple table where one of the keys is a timestamp with default value set to CURRENT_TIMESTAMP.

Is there a way to get this generated timestamp after the insert? I need an analogical solution to mysql_insert_id which you use with auto_increment.

Is that possible at all? I know it's not the safest practice, but to be honest I'm just curious at the moment. I already do it without using CURRENT_TIMESTAMP as a key - I just generate it in code and pass as a standard parameter.

Kelu Thatsall
  • 2,494
  • 1
  • 22
  • 50

1 Answers1

0

According to the docs:

int mysql_insert_id ([ resource $link_identifier = NULL ] )

Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT).

So mysql_insert_id works only on auto increment columns and you cannot have a datetime column with AUTO_INCREMENT as attribute, thus you cannot retrieve the timestamp of the last inserted row.

Instead you can do a separate query using the mysql_insert_id to fetch the last timestamp.

AyB
  • 11,609
  • 4
  • 32
  • 47
  • I know that, I just used mysql_insert_id as an example (analogy) of what I wanted to do. And your solution wouldn't work if I had a foreign key as a primary key + date as a second primary key. – Kelu Thatsall Apr 15 '14 at 12:11
  • @KeluThatsall Then that's a bad database design. If your second primary key is date, there's still a chance for it to repeat and thus, overall failing to uniquely identify a record. – AyB Apr 15 '14 at 12:21
  • @I Can Has Cheezburger I don't agree - every time where there are events stored in database it's a good practice to put (start)date as one of the keys. It's just probably not a very good idea to use DEFAULT_TIMESTAMP for that. – Kelu Thatsall Apr 15 '14 at 12:28