-4

I have a bunch of log plaintext files that I wish to convert with php to put them into a database instead of using plaintext files, the largest log file I have is 14MB and theres 26 log files.

How would I go about reading these text files with PHP to convert them into SQL?

they all have the same sort of format.

user2279125
  • 9
  • 3

2 Answers2

2

Most of the time MySQL will be smart enough to read the file format and insert it "as if", you can use LOAD DATA INFILE for this. You can also add "fields terminated by" and "lines terminated by", both will use the correct vale between single quotes.

LOAD DATA INFILE '/mylog_file.txt' INTO TABLE `my_db`.`my_table` FIELDS TERMINATED BY 'xxx' LINES TERMINATED BY 'xxx' ( first_field, seccond_field );
Solrac
  • 924
  • 8
  • 23
0

Get the log file text using file_get_contents() — Reads entire file into a string

$file = file_get_contents('log.txt', true);

If you are using MySQL , store $file variable content in field of MEDIUMTEXT datatype.

Following is the size limit

TINYTEXT    256 bytes    
TEXT        65,535 bytes    ~64kb
MEDIUMTEXT  16,777,215 bytes    ~16MB
LONGTEXT    4,294,967,295 bytes ~4GB
Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133
  • The log file is in a format like "[2013/1/1 - 0:8:45] [BANK] First Name (IP:ipv4ip) has transferred $9999 to Last Name (IP:ipv4ip)". How can I get it to just echo the data like the timestamp names and IP's rather than the 'has transferred'? – user2279125 Apr 27 '13 at 04:08
  • It will read as text and store in a column of table of datatype MEDIUMTEXT. – Mudassir Hasan Apr 27 '13 at 04:10
  • 1
    I would not recommend reading the entire log file into a single string. I would loop through the file, line by line, splitting each entry into each of your different columns or fields, then insert each of those lines as a row in your database. This way, you can actually do something with your data. If you're storing the entire log into one cell...what's the point? You can't do anything with it. – Sajan Parikh Apr 27 '13 at 04:13
  • Thats what I plan on doing Sajan, i just need an angle to work from to go from there. – user2279125 Apr 27 '13 at 04:20