Importing text file into MySQL php program

Question

I have a code that I took from https://www.howtoforge.com/community/threads/importing-text-file-into-mysql.7925/ and changed to what I needed it for but when I run the program it doesn't import the data into the database

Text file

GeoID|X|Y|Wood|Clay|Iron|Stone|Food|TerrainSpecificTypeID|TerrainCombatTypeID|RegionID
7025277|279|-1321|0|0|0|0|0|62|14|31
7025278|279|-1320|0|0|0|0|0|62|14|31
7025279|279|-1319|0|0|0|0|0|62|14|31
7025280|279|-1318|0|0|0|0|0|62|14|31
7025281|279|-1317|0|0|0|0|0|62|14|31
7025282|279|-1316|0|0|0|0|0|62|14|31
7025283|279|-1315|0|0|0|0|0|62|14|31
7025284|279|-1314|0|0|0|0|0|62|14|31
7025285|279|-1313|0|0|0|0|0|62|14|31
7025286|279|-1312|0|0|0|0|0|62|14|31

PHP code that i am currently using

<?php

// Set Mysql Variables

$username = "root";
$auth = 'i-have-removed-it';
$db = mysql_connect("localhost", $username, $auth);
mysql_select_db("testdb",$db);


$file = "/tmp/map_datafile_test.txtt";
$fp = fopen($file, "r");
$data = fread($fp, filesize($file));
fclose($fp);

$output = str_replace("\t|\t", "|", $data);

$output = explode("\n", $output);

$language_id = "1";
$categories_id = 0;

foreach($output as $var) {
$categories_id = $categories_id + 1;

$tmp = explode("|", $var);
$GeoID = $tmp[0];
$X = $tmp[1];
$Y = $tmp[2];
$Wood = $tmp[3];
$Clay = $tmp[4];
$Iron = $tmp[5];
$Stone = $tmp[6];
$Food = $tmp[7];
$TerrainSpecificTypeID = $tmp[8];
$TerrainCombatTypeID = $tmp[9];
$RegionID = $tmp[10];



echo " categories_id: " . $categories_id . " Artikelgroep: " . $Artikelgroep . "<br>";


$sql = "INSERT INTO `World_Map`(`GeoID`, `X`, `Y`, `Wood`, `Clay`, `Iron`, `Stone`, `Food`, `TerrainSpecificTypeID`, `TerrainCombatTypeID`, `RegionID`) VALUES ('$GeoID','$X','$Y','$Wood','$Clay','$Iron','$Stone','$Food','$TerrainSpecificTypeID ','$TerrainCombatTypeID ','$RegionID')" or die("Insert failed: " . mysql_error());
mysql_query($sql);
}
echo "Done!";

?>

You are only defining a string, you still need to execture the query. And you should use a prepared statement to avoid sql injection. — jeroen
– jeroen, Commented Jan 4, 2017 at 13:38
You have a truckload of syntax errors, missing quotes being the most notable. — Jay Blanchard
– Jay Blanchard, Commented Jan 4, 2017 at 13:38
I missed copying over the mysql_query($sql); oops. and i will get to work fixing the syntax errors — kodabear
– kodabear, Commented Jan 4, 2017 at 13:42
To save yourself a headache, stop using the deprecated mysqli_ functions and switch to PDO or mysqli. Then take advantage of prepared statements and parameter binding so you won't need to worry about quoting your variables. — aynber
– aynber, Commented Jan 4, 2017 at 13:55
ok i got the program to run now but when it run it puts in all 0 — kodabear
– kodabear, Commented Jan 4, 2017 at 14:23

DaBra · Accepted Answer · 2017-01-04 14:29:57Z

2

You need to set the variables $GeoID, $X, $Y, $Wood, $Clay, ...

In the foreach loop you get each line of the file, and in $tmp you get each column. So $GeoID should be $tmp[0], $X should be $tmp[1] and so on.

answered Jan 4, 2017 at 14:29

DaBra

695 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kodabear Over a year ago

thanks this was the last thing i needed. now time to test if it will work with the which is 305mb

kodabear Over a year ago

two questions. what would be the best way to add in a date that the upload happen? and it seems it doesnt like the full data that is in the data-map how would i go about fixing this?

kodabear Over a year ago

around 8,600,000 lines in the full map file

Collectives™ on Stack Overflow

Importing text file into MySQL php program

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related