0

have to import a CSV file in a table of MySql db using a PHP script. The CSV file is the following:

"giorno","lmedollton","changedolleuro","euroton","lmesterton","delnotiz","girm","sgm"
"2016-09-02", "100.01", "4005.09", "5000", "1.09", "120.09", "100.5", "200.77"
"2016-09-03", "150.01", "4205.09", "5600", "1.10", "150.09", "300.5", "300.77"

The table structure is the following:

#   Campo   Tipo    Collation   Attributi   Null    
    1   id  int(20)         No  Nessuno AUTO_INCREMENT   
    2   giorno  date            Sì  NULL    
    3   lmedollton  float           Sì  NULL         
    4   changedolleuro  float           Sì  NULL         
    5   euroton float           Sì  NULL         
    6   lmesterton  float           Sì  NULL         
    7   delnotiz    float           Sì  NULL          
    8   girm    float           Sì  NULL         
    9   sgm float           Sì  NULL

There are two many problems:

1) Using this PHP code:

    $csvFile = "../scripts/tabella.csv";

        $db = @mysql_connect('***', '***', '***');
        @mysql_select_db('***');

        $query = 'LOAD DATA LOCAL INFILE \' '. $csvFile .' \' INTO TABLE rame FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' 
(giorno,lmedollton,changedolleuro,euroton,lmesterton,delnotiz,girm,sgm)';

        if(!mysql_query($query)){
            die(mysql_error());
        }
        //@mysql_query($query);

        mysql_close($db);

mysql_error returns 'file not found'; I tried to use the realpath function also but the error is the same. The CSV file is in the site server in www.valuebiz.it/valuebiz/scripts/tabella.csv and its permissions are 777. Using this path like a URL I can download tabella.csv correctly. Which is the correct path that I've to use?

2) I tried to work the query in my localhost and it runs but every fields of the added records are NULL. The table has 9 fields include id field (auto-increment): the first is a DATE type and others are FLOAT type. Why are inserted values NULL?

Can you help me, please? Thanks!

Federica
  • 19
  • 5
  • 6
    ***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Oct 05 '16 at 12:37
  • Have you tried the full, rather than the relative path? – Jay Blanchard Oct 05 '16 at 12:38
  • 1
    `LOAD DATA LOCAL INFILE` is only usable for files residing in the local file system of the mysql server. Are you sure the mysql server and the http server are running _on the same system / file system'_? – arkascha Oct 05 '16 at 12:40
  • yes but the result is the same.. – Federica Oct 05 '16 at 12:40
  • 1
    Please be precise: `www.valuebiz.it/valuebiz/scripts/tabella.csv` is _not_ a path, it is maybe part of a URL, but that does not help here – arkascha Oct 05 '16 at 12:41
  • @arkascha I'm not sure they running on the same system/file system.. Can I know it, please? – Federica Oct 05 '16 at 12:45
  • Well, what host do you use in your `mysql_connect()` call? The local one or another one? – arkascha Oct 05 '16 at 12:46
  • 1
    Sidenote: *"mysql_error returns 'file not found'"* - MySQL won't throw that, "PHP" will. MySQL doesn't know what your system structure is. Check for permissions also and the correct "path". – Funk Forty Niner Oct 05 '16 at 12:47
  • @arkascha I indicated filezilla folders structure.. – Federica Oct 05 '16 at 12:47

0 Answers0