Page 1 of 1

Log File reading with PHP

Posted: Wed Apr 16, 2008 3:11 am
by alexking
I have very large tab-delimited text file with the following data:
timestamp [tab] url [tab] remote_ip

Example:
2008-01-01 12:30:45 [tab] http://urlhere.com [tab] 999.888.777.666

Data has been collecting in the file for the past year. I am trying to find the most popular IP address from hits that are recorder for yesterday. -- date('Y-m-d', strtotime('-1 days'))

I can't figure out how to write this code, please help???? Not sure how to do this in PHP.

Re: Log File reading with PHP

Posted: Wed Apr 16, 2008 6:18 am
by onion2k
Personally, I wouldn't use PHP. I'd use MySQL. :twisted:

Create a table with 3 columns for the date, URL and IP.

Code: Select all

CREATE TABLE `dtab` (
`ts` DATETIME NOT NULL ,
`url` VARCHAR( 250 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`ip` VARCHAR( 15 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Then load the data into the table using LOAD DATA INFILE

Code: Select all

LOAD DATA LOCAL INFILE 
'c:/data.csv' 
INTO TABLE `dtab` 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n'
(
`ts` , `url` , `ip`
)
Then select the data you need...

Code: Select all

SELECT `ip` , count( `ip` ) AS total
FROM `dtab`
WHERE 1
AND `ts` =  '2008-01-01'
GROUP BY `ip`
ORDER BY total DESC