0

My Database DDL is below

CREATE TABLE `user` (
  `token` text NOT NULL,
  `country` text NOT NULL,
  `timezone` text NOT NULL,
  `date_registered` date NOT NULL,
  `time_registered` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

How can I make it so that whenever a new record is created that the date and time will automatically be generated and inserted without relying on PHP to add an extra variable for me to process?

http://example.com/webapps/api/add_token.php?token=%@&country=%@&timezone=%@

The @% are variables generated at run time from my iOS application.

Before a row was generating when I ran my application. But I added two new fields (date_registered and time_registered) and now it is not adding data to the table

software is fun
  • 7,286
  • 18
  • 71
  • 129
  • Hi, can you clarify what you mean by this... 'But I added two new fields (date_registered and time_registered) and now it is not adding data to the table' Are you saying data insertion isn't working at all now that you added those fields? If so, that makes sense since date_registered is not null and if you want the database to do something, you need to add a DEFAULT action of CURRENT_TIMESTAMP to that line. – hughjidette Nov 06 '15 at 20:06
  • now i get no data being inserted – software is fun Nov 06 '15 at 20:20
  • 1
    Try adding "default CURRENT_TIMESTAMP" on hour date_registered column. Also make sure that you have a value for 'token'. – john elemans Nov 06 '15 at 20:25
  • As I and @johnelemans have stated, try adding default CURRENT_TIMESTAMP to the date_registered column. The only catch is mysql has to be version 5.6.5 or higher or it's not supported. See this post: http://stackoverflow.com/questions/267658/having-both-a-created-and-last-updated-timestamp-columns-in-mysql-4-0 – hughjidette Nov 06 '15 at 20:29
  • how can i determine the version of MySQL? – software is fun Nov 06 '15 at 20:42
  • @johnelemans if I use TIMESTAMP as my data type for the DATE field. Won't that just give me the time?I was hoping to keep the DATE and TIME separated – software is fun Nov 06 '15 at 20:44
  • Timestamp has both date and time. You can use a DATE type and CURRENT_DATE as well. Do some tests on the command line and see the results quickly. – john elemans Nov 06 '15 at 20:48
  • thank you i will try this out. – software is fun Nov 06 '15 at 21:03
  • but is there a way to automatically generate the date field (using TIMESTAMP) automatically when a new record is inserted? – software is fun Nov 08 '15 at 13:58

0 Answers0