transfer Apache logfile to mysql

[Anon]

Hi,

I want to transfer the apache access log into a mysql database.

My problem is the format of the data! the mysql datatype TIMESTAMP supports YYYY-MM-DD but the format in the apache logfile is [20/Jan/2001:16:47:57 -0800] , so DD-MM-YYYY. What is the easiest way to switch this.

thx

christian

[majik-sheff]

Couple of different ways to do this:

1. Use the LogFormat directive in your httpd.conf like this:
   LogFormat "%h %l %u [%{%Y-%m-%d %H:%M:%S %z}t] \"%r\" %>s %b"
   That will (should?) produce a log file date that looks like this: [2001-01-22 16:47:57 -0800]
   be aware that this approach will break any other log analyzers you might use.

2. a purely PHP solution:

sscanf($logLine, "%s %s %s [%s] \"%s\" %s %s", &$hostName, &$userName, &$userAuth, &$timeStamp, &$requestLine, &$httpStatus, &$fileSize);
$timeStamp = date("Y-m-d", strtotime(str_replace("/", " ", substr($timeStamp, 0, 11))));

This takes the line from the log file and parses it out into named variables. I'm assuming that you are using common log format, but you get the idea. There are probably cleaner ways to code this, but I like a 2-line solution every once-in-a-while. I actually tested this code on my own log file and it works fine.
good luck
-Sheff