0

I have written a script to import CSV files into a mySQL database. However when I try to run the SQL from a PHP script it doesn't work. It does work when I put the SQL query into phpMyAdmin.

$server = 'localhost';
$username = 'root';
$password = 'password123';
$database = 'product';

$connect = new mysqli($server, $username, $password, $database);

if($connect->connect_error) {
    die('Connection Failed' . $connection->connect_error);
}

$files = scandir('imports/');

foreach($files as $file) {
    if ($file != '.' && $file != '..') {
        $import =
            "LOAD DATA INFILE 'imports/$file'
            IGNORE INTO TABLE temp_import
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
            LINES TERMINATED BY '\n'
            IGNORE 1 LINES
            (@deal_id, @redeemed_at, @wowcher_code, @deal_title, @customer_name, @house_name_number, @address_line_1, @address_line_2,
        @city, @county, @postcode, @email, @phone, @date_of_birth, @custom_field, @marketing_permission, @product_name, @product_options)
        set deal_id=@deal_id, redeemed_at=@redeemed_at, wowcher_code=@wowcher_code, deal_title=@deal_title, customer_name=@customer_name,
        house_name_number=@house_name_number, address_line_1=@address_line_1, address_line_2=@address_line_2, city=@city, county=@county,
        postcode=@postcode, email=@email, phone=@phone, date_of_birth=@date_of_birth, custom_field=@custom_field,
        marketing_permission=@marketing_permission, product_name=@product_name, product_options=@product_options";

        if(! $connect->query($import)) {
            echo 'Failed: ' . $file . PHP_EOL;
        }

        else {
            $connect->query($import);

            echo mysqli_affected_rows($connect) . PHP_EOL;
        }
    }
}

Can anyone see why this may not be working

Edward144
  • 493
  • 6
  • 28
  • 1
    `'imports/$file'` is a relative path! Make it a absolute path! – JustOnUnderMillions Nov 03 '16 at 16:05
  • And check: http://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile – JustOnUnderMillions Nov 03 '16 at 16:06
  • Thank you! It would have to be the most simple thing. I don't really understand why that fixes it though. with the relative path it still founds the files in the directory – Edward144 Nov 03 '16 at 16:08
  • 1
    What does _"doesn't work"_ mean? Error messages? Blanks screen? Computer catches on fire? – M. Eriksson Nov 03 '16 at 16:08
  • Relative paths are not always relative from that files location. If you include this file from another file, located in another folder, it will be relative from that files location. – M. Eriksson Nov 03 '16 at 16:09
  • @Edward144 That comes first in mind. Because i dont thing PHP is telling MYSQL about the current working directory. Check the Link, there are good examples... – JustOnUnderMillions Nov 03 '16 at 16:10

0 Answers0