2

Txt file:

942,Nike%27s+cities,2469,2,2,164
316,just+me,820,1,1,286
827,RES+4+GOLD,1523,1,1,214
1171,KnightBlood,1550,1,1,211
1172,athens,2095,2,2,177

The above file are alliances from a game i'm trying to make a tool for the format is the following:

id, name, points, towns, members, rank

In my database i'm storing 3 additional columns:

id, name, points, towns, members, rank, world_server, world_id, date

I've looked around and tried multiple different options/queries but I can't seem to get it to work. My current query, in PHP, is:

"LOAD DATA LOCAL INFILE /path/file.txt 
INTO TABLE alliance 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
SET world_server = $world[server], world_id = $world[id], date = $curDate"

I get that this is probably a duplicate question but I have searched through stackoverflow, mysql docs, etc... for multiple days now and I don't get what's wrong with my query. I am not getting any error messages either which doesn't help, my database just stays empty.

Hope someone can help, A desperate student.

MDB
  • 21
  • 3
  • Your current approach should have worked, I think. Is there any error output at all? Does any table get created? – Tim Biegeleisen Feb 19 '19 at 11:19
  • @TimBiegeleisen I get no error messages at all and the table is already created but it stays empty. – MDB Feb 19 '19 at 11:27
  • https://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile – Shibon Feb 19 '19 at 11:27
  • Does it have the extra three columns? Or only the columns in your text file? – Tim Biegeleisen Feb 19 '19 at 11:29
  • @TimBiegeleisen My database has 3 extra columns (shown above) compared to the text file. – MDB Feb 19 '19 at 11:30
  • Odd. I loaded your file into a table, there are "Row 1 doesn't contain data for all columns" warnings, but the data loads, last 3 columns were nulled. If I add some SET for one or more of the columns the data still loads. Can you post the table structure? Are the last columns NOT NULL, maybe? (Just guessing here) – Torbjörn Stabo Feb 19 '19 at 11:51
  • @TorbjörnStabo They are NOT NULL, they are a foreign key and part of the PRIMARY KEY so they can't be NULL... – MDB Feb 19 '19 at 11:56
  • @MDB Do you have access to **any** kind of command line interface? PHPMyAdmin? MySQL command line client? Anything that lets you run the load and view the results(warnings)? (Or add the extra PHP code for displaying warnings after the query) I'd be surprised if that comes out empty, and without **any** output here we can only guess. – Torbjörn Stabo Feb 19 '19 at 12:03
  • @TorbjörnStabo I have the script running as a cronjob and have the following code added: error_reporting(E_ALL); ini_set('display_errors', 1); It sends any output (only error messages) to my email and i'm not receiving anything so... – MDB Feb 19 '19 at 12:04
  • Added 'or die' statement to show sql errors but I still do not get any errors. – MDB Feb 19 '19 at 12:15
  • @MDB Those are good lines to have while debugging, but they do not cover everything. If MySQL decides to skip lines in the infile it doesn't necessarily mean that things crash. Like I said before there might be warnings instead, `SHOW WARNINGS;` is an useful query. Which is why command line or similar would be really useful here, issuing the show warnings right after the load data query, and being able to view the full result from the first query.. – Torbjörn Stabo Feb 19 '19 at 12:23
  • After trying around some more it ended up somehow being a syntax error due to the formatting of my code for clearer readability... – MDB Feb 19 '19 at 13:15

0 Answers0