File paths with MySQL

anortrup

On a project that I am currently working on we are writing in a feature that will allow authorized end users to update their data related to their town by uploading a cvs file which we will use in conjunction with the mysql LOAD DATA INFILE mysql command to import the data. Currently I am using the following code to load the data into the database.

//escape out slashes in windows file path
$filename = mysql_real_escape_string($_FILES['file']['tmp_name']);

//insert new records
$sql = "LOAD DATA INFILE '$filename'
		INTO TABLE properties
		FIELDS
			TERMINATED BY ','
			OPTIONALLY  ENCLOSED BY '\"'";

if(!@mysql_query($sql, $connection)) {
	mysql_query("ROLLBACK");
	trigger_error("MySQL error " . mysql_errno() . ":" . mysql_error(), E_USER_ERROR);
	exit();
}

If I echo out the sql statement I get the following:

LOAD DATA INFILE 'C:\PHP\uploadtemp\php5F.tmp' INTO TABLE properties FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

Here the slashes are escaped out with an extra slash. However when I actualy run the command through mysql I get the following error:
Error: "MySQL error 0:" (error #256).

and I honestly have no idea what it means. Any help would be greatly appricated.

-Andy

juschillinnow

actually the extra slashes are not suppose to be there. In newer versions of LOAD DATA (when the made the stupid LOCAL change), somebody screwed up.

before you run the query, you need to either 'addslashes' or 'stripslashes' from the file location and it should work.

I don't remember which one it requires.

anortrup

that doesn't apear to fix the problem. I did however have another thought, what directory do these files need to be in, by default they would go into C:\php\uploadtemp but the mysql manual indicates that they must be in the database directory, do I have to move the file before I can use it?

-Andy

drawmack

you have to add slashes to the content of the file before you insert it into the database. Something like this is your best bet.

$inFile = fopen('filename.txt','r');

while($data = fread('filename.txt',(1024*1020))) {
  $data = addslashes($data);
  $sql = "INSERT INTO fileData (fileID,Data) VALUES ($fID,\"$data\")";
  mysql_query($sql);
} //end while

This allows you to store files of any size in the database by using two tables. If you put everything in one table then no query can exceed 1meg. Here is how you want to layout your database

CREATE TABLE IF NOT EXISTS fileInfo (
  fileID  BIGINT NOT NULL auto_increment,
  fileName VARCHAR(100) NOT NULL,
  fileSize    BIGINT  NOT NULL,
  fileType  VARCHAR(255) NOT NULL,
  description TEXT,
  PRIMARY KEY (ID),
  UNIQUE fileName (fileName)
);

CREATE TABLE IF NOT EXISTS fileData (
  ID BIGINT NOT NULL auto_increment,
  fileID BIGINT NOT NULL,
  Data  BLOB,
  PRIMARY KEY (ID)
)

of course fileID is an external key between the two tables, and the auto_increment id field in the fileData table allows you to rebuild the file by using an order by on this field. Even if multiple files are being inserted simultaneously the query

SELECT * FROM fileData WHERE fileID=1 ORDER BY ID

will still return the file pieces in order.

anortrup

Drewmack...that isn't quite what I'm working to, we have an exsisting database where we are trying to update the contents of that database from a cvs file not just chunk the file into one field.

-Andy

anortrup

Managed to figure it out on my own (with a little help from every one here). Turns out that if you want this command to work you need to move the file into the same directory of the database. In this case it was C:\mysql\data\database did this and it works fine.

-Andy