Import to MySQL Forbidden

[bridrod]

Hi, getting this error:

> Warning : mysqli_query(): LOAD DATA LOCAL INFILE forbidden*

When I try to import using a sample line like this:
LOAD DATA LOCAL INFILE ‘test.csv’ IGNORE INTO TABLE mytable FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES";

AFAIK php.ini needs to allow it, but I thought it was allowed.
Thanks,

[wackyblackie]

You need to import MySQL through phpMyAdmin.

[bridrod]

Is there a way to schedule the import there to happen automatically?

[jaikrishna.t]

From what I know, it’s a NO.

[Admin]

@bridrod

When I try to import using a sample line like this:

I think you may be misunderstanding how SQL queries work.

In short, SQL queries are just sent as text to the database server. The only thing that’s available for the database is what data is visible in the query string you’re sending.

So if you run LOAD DATA LOCAL INFILE, the server will try to load the file into the database from the local storage of the database server. The PHP script sending this query doesn’t magically transfer the text.csv file from the web server to the database server to do this. So you cannot use this to import data from a web server into a database server.

And you don’t have access to the local storage of the database server, so it’s simply not possible to load in data this way. That feature of MySQL is only useful if you have your own database server and you have administrator access to that server to access the MySQL data directory. So on any kind of service where the database server is managed by someone else (including all kinds of web hosting), this will not work.

@bridrod

AFAIK php.ini needs to allow it, but I thought it was allowed.

Whether an SQL query works or not is solely dependent on the database server. So there are no PHP settings that could affect this.

@bridrod

Is there a way to schedule the import there to happen automatically?

The easiest way to upload a CSV file to your database is by using the CSV import function of phpMyAdmin.

Automatic imports are also somewhat possible, but primarily useful if you need periodic imports (like automatically importing a file every day). But you’d need to write a PHP script to do this, and call it with a cron job.

[bridrod]

Thanks for explaining. I understand how it works and the limitations of having a SQL server running outside of the webserver. Now, I do need the automatic import since the data is changing daily and it’s being FTPed to the site. I have the PHP which I need to modify to use “something else” instead of the LOAD DATA LOCAL INFILE and it’s already setup as a cron job.

Any suggestion of another way to make the auto import possible?
Thanks,

[Admin]

@bridrod

Any suggestion of another way to make the auto import possible?

Just parse the uploaded CSV file in PHP, and use that data to generate a big INSERT query that adds the data from the file into the database.

---

A word of warning though: please keep in mind that we provide a website hosting service, not a data collection/processing/analysis service. We provide an FTP server for website administration, not for collecting data dumps. What you’re trying to do is not allowed officially and you may run into limits or be suspended because of it.

[bridrod]

Totally understood and appreciate the info!