LOAD DATA INFILE - Show number of rows imported gives wrong value
<p>I've been trying to import a csv file into a mysql database using LOAD DATA INFILE. </p>

<p>Everything is working more or less correctly, but when I use mysqli_info() or mysqli_affected_rows() they each show that no rows have been imported for the query. Even though I see the rows are being correctly imported.</p>

<p>A simplified version of what I am trying to do (Fewer columns that I am actually importing):</p>

<pre><code>$server = 'localhost';
$username = 'root';
$password = 'password123';
$database = 'database_name';

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

$files = scandir('imports/');

foreach($files as $file) {
   $import = 
        "LOAD DATA INFILE 'imports/$file'
        IGNORE INTO TABLE table_name
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES
        (@id, @name, @address, @product)
        set id=@id, name=@name, address=@address, product=@product";

    if(! $connect-&gt;query($import)) {
        echo 'Failed';
    }

    $connect-&gt;query($import);

    echo mysqli_affected_rows($connect);
}
</code></pre>

<p>mysqli_affected_rows() returns 0, while mysqli_info() states all rows have been skipped. Any idea why it's not displaying correctly?</p>

<p>Hopefully that's enough information. Thanks!</p>

<p>Edit:</p>

<p>I've been a bit too busy to work on this over the past few days, but I have decided that although it doesn't specifically answer my question Drew's answer of importing into a temporary table first seems to make the most sense so I have decided to go with that.</p>