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.
Log File reading with PHP
Moderator: General Moderators
Re: Log File reading with PHP
Personally, I wouldn't use PHP. I'd use MySQL.
Create a table with 3 columns for the date, URL and IP.
Then load the data into the table using LOAD DATA INFILE
Then select the data you need...
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;Code: Select all
LOAD DATA LOCAL INFILE
'c:/data.csv'
INTO TABLE `dtab`
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(
`ts` , `url` , `ip`
)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