CodeIgniter Forums

Full Version: [SOLVED]How do you get_dir_file_info date into mysql?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]zimco[/eluser]
get_dir_file_info('path/to/directory/') returns an array with the following file info for each file in a directory:
Quote:name
server_path
size
date
relative_path

The files date info seems to be returned in unix time. So how do you store a unix time in a mysql database? I've tried creating a table like the following:

Code:
DROP TABLE IF EXISTS `mydir_fileslist`;
CREATE TABLE IF NOT EXISTS `html_racefiles` (
  `file_id` int(11) NOT NULL auto_increment,
  `file_name` varchar(255),
  `server_path` varchar(255),
  `size` int(11),
  `date` timestamp DEFAULT CURRENT_TIMESTAMP,
  `relative_path` varchar(255),
  PRIMARY KEY  (`file_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
but when i go to insert the values from get_dir_file_info into my database the date field always gets set to 0000-00-00 00:00:00. I'm sure this is probably due to the fact that it expecting a mysql timestamp and i'm trying to give it a unix timestamp so i can i get mysql to store a unix timestamp?

El Forum

[eluser]Yorick Peterse[/eluser]
Rather than using a "timestamp", use "Unix_Timestamp" and set the default value to nothing.

El Forum

[eluser]zimco[/eluser]
Tried your suggestion like this:

Code:
DROP TABLE IF EXISTS `html_racefiles`;
CREATE TABLE IF NOT EXISTS `html_racefiles` (
  `file_id` int(11) NOT NULL auto_increment,
  `file_name` varchar(255),
  `server_path` varchar(255),
  `size` int(11),
  `date` unix_timestamp,
  `relative_path` varchar(255),
  PRIMARY KEY  (`file_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

and it errors out with:
Quote:#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'unix_timestamp,
`relative_path` varchar(255),
PRIMARY KEY (`file_id`)
) ENG' at line 6

Any other suggestions?

El Forum

[eluser]zimco[/eluser]
Wink DOH! Apparently you just use an unsigned int to represent unix time:

Code:
`date` int unsigned,