Load CSV file from php to mySQL

Beta2

Hi

I'm trying to load a csv file from my php upload page to a folder on my server and from there put it into my mySQL database.

This is the SQL i'm using to upload my file where my_upload_file.csv is the file i've just uploaded to the uploads folder and my_table_name is the name of the table I wish to load the data into.

LOAD DATA LOCAL INFILE './uploads/my_upload_file.csv' REPLACE INTO TABLE `my_table_name` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'

The problem is that i get this error :

The used command is not allowed with this MySQL version

If I remove the LOCAL word i get this error :

Access denied for user: 'user_user@localhost' (Using password: NO)

where user is my username

I think this is a security issue with hy host as they haven't given me the privilege to use FILE from mysql.

But im not too sure as i'm completely new to php and im no expert on mysql, or websites in general!!

Is this something I'll have to ask my host to fix??

Any help appreciated.

B2

Webmonkey

Beta2 wrote:

Hi

I'm trying to load a csv file from my php upload page to a folder on my server and from there put it into my mySQL database.

This is the SQL i'm using to upload my file where my_upload_file.csv is the file i've just uploaded to the uploads folder and my_table_name is the name of the table I wish to load the data into.

LOAD DATA LOCAL INFILE './uploads/my_upload_file.csv' REPLACE INTO TABLE `my_table_name` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'

The problem is that i get this error :

If I remove the LOCAL word i get this error : where user is my username

I think this is a security issue with hy host as they haven't given me the privilege to use FILE from mysql.

But im not too sure as i'm completely new to php and im no expert on mysql, or websites in general!!

Is this something I'll have to ask my host to fix??

Any help appreciated.

B2

Well you will have to do a database connection first, dunno if you have this done yet. It says using password No it doesn't look to me that you are connecting.

Add this:

mysqluser = "username";
mysqldb = "database";
mysqlserver = "serveripadd";
mysqlpass = "password;

mysql_connect($mysqlserver,$mysqluser,$mysqlpass) or die ("Error Connecting");
mysql_select_db($mysqldb) or die ("Error getting db");

Beta2

Yea I am connecting to the database fine, I can register someone, log them in out etc. I have that in a seperate script, which I include before I call the line in question.

Thanks anyway

B2

Beta2

I heard back from my host company they said :

That command can only be performed through a shell prompt for security reasons

and suggested i write a script to put each line in the database one by one. That sounds like a right pain:( and it'll slow down my system a lot.

Any suggestion please??

Thanks B2

daymobrew

Beta2 wrote:

Host company suggested i write a script to put each line in the database one by one. That sounds like a right pain:( and it'll slow down my system a lot.

Any suggestion please??

If this is a once-off or very irregular csv load you could do it by hand. If you have shell access then do the load using the command line mysql utility, otherwise you could have a php script that calls the system function (as if you were at the command line).

Putting each line into the database isn't that painful - just a while loop that reads from the file (may not need to split the line) and calls mysql_query. I doubt that it will slow your system down - a database server spends its life reading from and writing to its databases and I would hope is optimised for such transactions.

seamus

Aye, if it's a once-off or irregular load, you could write a PHP script to do it. I've had to do this before in work, moving stuff from excel into an online app.

With SQL you can write a single insert statement for hundreds of rows, instead of hundred of insert statements.

Beta2

daymobrew wrote:

If this is a once-off or very irregular csv load you could do it by hand. If you have shell access then do the load using the command line mysql utility, otherwise you could have a php script that calls the system function (as if you were at the command line).

Putting each line into the database isn't that painful - just a while loop that reads from the file (may not need to split the line) and calls mysql_query. I doubt that it will slow your system down - a database server spends its life reading from and writing to its databases and I would hope is optimised for such transactions.

Thanks for the reply,

The upload will be fairly regular, my site will allow users to upload a csv of contacts, so they may have a few thousand and do it regurlarly, I suppose I could write a script to do it one by one looping through them all, but if they have a large number of contacts this might slow down the system?

As I said i'm new to php, so i really appreciate your comments.

I think i'll try to write a script to do it one by one.

Cheers

B2

seamus

Beta2 wrote:

I think i'll try to write a script to do it one by one.

I'm not sure what the overhead is, but it has to be more efficient to do it in one statement.

For example, these two statements:

INSERT INTO mytable (col_a, col_b, col_c) VALUES ('$a', '$b', '$c');
INSERT INTO mytable (col_a, col_b, col_c) VALUES ('$x', '$y', '$z');

Can be rewritten as a single statement:

INSERT INTO mytable (col_a, col_b, col_c) VALUES ('$a', '$b', '$c'), ('$x', '$y', '$z');

As I say, I have no idea how much more efficient it is, but it has to be for hundreds of rows, right?

Just remember to check/escape all input from the .csv files to protect against anyone doing anything naughty.

Beta2

seamus wrote:

I'm not sure what the overhead is, but it has to be more efficient to do it in one statement.

For example, these two statements:

Can be rewritten as a single statement:

As I say, I have no idea how much more efficient it is, but it has to be for hundreds of rows, right?

Just remember to check/escape all input from the .csv files to protect against anyone doing anything naughty.

Thanks for the feedback, i'll have a stab at it later, i'm too busy today, i'll let you know how i get on.

B2