Populating a MySQL table from a text file

[GothyStitch]

I have three text files that I need to pull information from to populate three separate tables within the same database. Each text file is for a separate table.

Database: angelsfu_tribalwars
Tables:

players
villages
tribes

Text Files (in respective order):

http://en2l1.ds.innogames.net/map/tribe.txt
http://en2l1.ds.innogames.net/map/village.txt
http://en2l1.ds.innogames.net/map/ally.txt


The files are hosted on a different server than mine, and I would be updating the tables once an hour with updated information. The URLs for the files will not change, so that is not a concern.

The database and tables are already created. I just have no clue how to pull the information from the text files and fill the appropriate fields in each table.

For example, let's take the table "players" and it's file "http://en2l1.ds.innogames.net/map/tribe.txt".

The table contains the following fields:



id
name
ally
villages
points
rank


Let's say the file contains the following:


1,exception,0,1,116,24391
10713,mr_stabby,17150,4,39837,3092
6902,Guffman,5775,19,183537,1695
398,Boopzilla+Rex,8348,21,255884,1423
28627,Black_adder,21767,205,2268396,98
27281,Cringe,389,131,1420390,293
30679,Kenoside,8348,159,1682900,213
27712,barman+v,3473,118,766530,640
32695,dreid,19,121,1073991,456
12142,Seth+Brake,3533,5,39908,3087
19086,Sufex,8348,152,1808011,191
11564,silverstride,218,64,657995,737
11674,OldTart,218,65,526711,904
34676,lonmyster,60,196,1824192,187
8804,vaia,8348,90,898329,544
31652,Wexler,91,64,699264,689
37420,bohica,389,3,26256,3584
17449,wizard,12574,72,780760,623
11110,stevna_666,13834,17,209992,1588

From that file, the fields go in the order listed:



id name ally villages points rank
1, exception, 0, 1, 116, 24391


How would I write the PHP code to grab the information from the URL, put it into an array, and then populate the table accordingly?

[jonnyynnoj]

<?php

$filename = "http://en2l1.ds.innogames.net/map/tribe.txt";
$file = file($filename);
$lines = count($file);

for ($line=0; $line<=$lines; $line++){

$final_line = explode(",", $file[$line]);

mysql_query("INSERT INTO table (id, name, ally, villages, points, rank) VALUES ('$final_line[0]', '$final_line[1]', '$final_line[2]', '$final_line[3]', '$final_line[4]', '$final_line[5]')");

echo ( $line==$lines ) ? "Done" : "";

}

?>

However due to the size of the file there's probably a good chance that it'll timeout about halfway.

[GothyStitch]

Ah, ok... that's where I was going wrong... I forgot about explode!

Any way to work around the file size?

[jonnyynnoj]

Ah, ok... that's where I was going wrong... I forgot about explode!

Any way to work around the file size?
Best idea i came up with was to make it refresh itself every so often:

<?php

//////////////////////////////// EDIT ////////////////////////////////////

$redirect_num = 5000; // Select how many rows to insert each time before refresh.
// More rows = faster insertion. However cannot be too high otherwise it will timeout.

$filename = "http://en2l1.ds.innogames.net/map/tribe.txt"; // The file we are going to get the data from...

$table = "table";

////////////////////////////// END EDIT //////////////////////////////////

$file = file($filename);
$lines = count($file);

// Have we just redirected?
$nextline = $_GET['nextline'];
if (!isset($nextline)){
$nextline = 0;
}

for ($line=$nextline; $line<=$lines; $line++){

$final_line = explode(",", $file[$line]);

if ($line!=$lines){
mysql_query("INSERT INTO {$table} (id, name, ally, villages, points, rank) VALUES ('$final_line[0]', '$final_line[1]', '$final_line[2]', '$final_line[3]', '$final_line[4]', '$final_line[5]')");
}

if ($line % $redirect_num){
}
else {
$nextline = $line+1;
exit ('<meta http-equiv="refresh" content="0;url=test.php?nextline='.$nextline.'" />');
}

echo ( $line==$lines ) ? "Done" : "";

}
?>