[SOLVED] Date and Time Format Issue - PHP / ProFTPd / MySQL Help

[luciogodoy]

Hi all;

 

I am sorry for the long message J

 

I am developing a site, and one of the PHP web pages is a search page, which searches for particular files within a data range.

 

The files are uploaded through Proftpd with MySQL support. When the file is uploaded, proftpd inserts an entry on my MySQL database with Username, File Name, File Size and Date & Time.

 

My problem is that ProftpD is inflexible in the way it parses its date and time values to MySQL using the now() function, using the following format: 0000-00-00 00:00:00

 

I really wanted to be able to split the date and time into two separate fields. I have raised this same question over the ProftpD user group and I have been informed that this is an issue on Proftpd.

 

My question is really this:

 

Since ProftpD parses the date on this format: 0000-00-00 00:00:00

 

Is there a way that, when the data is parsed to MySQL, MySQL will only accept the 0000-00-00 date field and not the whole thing?

 

And also the opposite only accepting the time part?

 

 

I am using ProftpD version 1.30 server with MySQL version 5.0.37 support for logging purposes. The SQL query is as follow:

 

SQLLog STOR insertfileinfo

SQLNamedQuery insertfileinfo INSERT "'', '%u', '%f', %b, now(), '', '', '1'" EventMonitor

 

Where:

 

%u is the username

%f is the filename including the path

%b is the file size

now() is the date and time (format 0000-00-00 00:00:00)

 

 

The MySQL table is:

 

CREATE TABLE EventMonitor (

EventMonitor_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Username varchar(32),

FileName varchar(96),

FileSize int(10) unsigned NOT NULL default '0',

EventTime datetime NOT NULL default '0000-00-00 00:00:00',

VaultEnabled tinyint(1) unsigned NOT NULL default '0',

EventViewed tinyint(1) unsigned NOT NULL default '0',

Enabled tinyint(1)

);

 

Thanks a lot

 

Lucio Godoy

 

[luciogodoy]

I was doing a bit of research, i believe i could use the mysql date_format function on my select statement, like this:

 

select all_records from somewhere where date_1(date_retrived_1, %m %d %Y) = date_posted_1 BETWEEN date_2(date_retrived_2, %m %d %Y) = date_posted_2 order asc

 

Need some help, pls

 

Thanks

 

Lucio

 

 

[luciogodoy]

Hi Guys;

 

I managed to solve the issue by adjusting my mysql query:

 

$date_from = $date_from . " 00:00:00";

$date_to = $date_to . " 00:00:00";

 

$query_date = "SELECT * FROM EventMonitor WHERE Username = '$username' AND Enabled = 0 AND EventTime BETWEEN '$date_from' AND '$date_to' ORDER BY EventMonitor_ID ASC LIMIT $offset, $rowsPerPage ;";

 

There might be a better way, but this is working great

 

Regards

 

Lucio