Forum Moderators: coopster
I have a working script where I upload a textfile to my server, then read it into my mysql database. I have only written this as a trial system so far, but it works apart for one thing I cant understand.
When I upload a file to my site (its a development box I have full control of) via ftp and read the file in to MySQL using the LOAD DATA.... command, the file is written to the table perfectly (or if I execute my php script to read data from file -> dbase). However when I upload it via my browser to the same directory on the server, the file somehow gets altered - and then when I insert the values into my table, the alignment is all a mess, whether I use my php script or the MySQL command line to insert the data file. If I download the browser-uploaded file via ftp or view it in vi on my server, it looks fine...
The contents of the file (plain text file, created in vi, notepad, anything) are just 2 things - a date and an item (both varchar fields) e.g.
021904 webmasterworld
021904 testing
... etc
The seperation string between the date and name has been tested as a space and a tab, neither fixes my problem. I have used a lot of different variants in my php insert statement - from simply "LOAD DATA LOCAL INFILE '/path/to/$myfile' INTO TABLE record (date, name)"; to trying FIELD TERMINATED BY etc - perhaps I just haven't hit the right combination yet?
Anyway to sum up:
- If I upload by ftp, my php script will read in the file no problem, as will MySQL command line.
- if I upload by browser (multipart/form-data) the resulting text file on the server ends up somehow changed, and the values I insert end up all over the place e.g.
date name id
¦ 02¦ some text ¦ ¦
¦ 0219¦ next line ¦ ¦
- basically its just all over, missing lines, combining them etc. Very confusing...
Anyone have any ideas? My upload file code is...
<?php
if (isset($_POST['submit'])) {
$add = $_FILES['recordfile']['name'];
move_uploaded_file($_FILES['recordfile']['tmp_name'], "upload/path/" . $_FILES['recordfile']['name']);
}?>
thanks!
Have you tried the following?
use vi to check the text file to Unix format, and the upload.
change the extention of the file from .txt to .dat or .xyz
replace all of the spaces used to seperate the file to tabs.
Combinations of these....
If I were in your shoes I would be looking for a cause that is between the windows client to the Unix, which is not there in a straight FTP, which in this case is the Browser. IF you are using IE, download FireFox and try that.
These aren't suggestions for fixing this, these are for clues as to the cause. Once you know that, then you can arrange to deal with it.
Hope that helps. I'll keep a monitor on here, to see how you are faring.
I am about to try a different browser, will report back if anything 'interesting' occurs.
I have tried commas, spaces, tabs, e.g. my latest query was ... INTO TABLE record FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' and the data file was containing -
022004,fdkhafldkhfkjs
022004,idafiwufirlyiuyrgfia
022004,kajsdhfaushdfuasu
- text was all one word, new lines and again the same issue - if I upload via browser and load the file in, "select * from record;" gives me a huge mash of chars, where as straight ftp upload - no probs!
I'm wondering if its a server configuration, either with apache/php or something else. It's running SuSE 8.0 Pro at the minute. Nothing in the logs points to a problem, and if I navigate to the file in IE - box ip/path/to/file.txt - it looks absolutely fine. the file was 88 bytes under unix and in windows, I'm really lost now ;)
Anyway onto that browser test...
LOAD DATA LOCAL INFILE '/www/files/eh.dat' INTO TABLE record FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (date, name);
+--------+----------------------------+----+
¦ date ¦ name ¦ id ¦
+--------+----------------------------+----+
¦ 1 ¦kdjfhdkjfhkjdshfkjdfh
¦ 2 ¦fjldk
¦ 3 ¦lkjfdlksfjlkf
¦ 4 ¦
¦ 5 ¦fjdlkfjldkfjldk
¦ 6 ¦dkf
¦ 7 ¦jldkfjdifjidojfiopd
¦ 8 ¦4 ¦ doifjldkfjldkfjlkdfjldkfj
¦ 022004 ¦ dljkfdljfh ¦ 9 ¦
+--------+----------------------------+----+
The table doesn't stay formatted exactly how I see it on the board, but perhaps you get some idea of whats going on :/
At first I thought it was because I wasn't mentioning the ID field (which is just a basic auto increment integer field) but it works when I upload the file via ftp so... *shrug*
It seems the file DOES want something after the field 'name' before the next column, 'id'. It now works via browser upload and ftp upload if I put a comma (or whatever char I choose as long as the SQL statement references it of course) after the second record, i.e.
022004,kdjfhdkjfhkjdshfkjdfh,
and say
... INTO TABLE record FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (date, name);
Quite why this worked without a seperator before the final column before is a mystery to me, as I would think that if I was using the same statement it would be failing both then and now.. sigh.
Anyway, now I can smile and tell my boss it works ;)
I hate them, because I JUST WANT TO GET THE DMAN UPLOAD DONE NOW! please?
;-)
