Insert Groups of Rows with PHP & MYSQL

Question

I'm trying to load data from a few hundred text files into a database.

I believe MYSQL is exiting out of the loop without inserting all the rows.

Can anyone suggest how to insert blocks of 1000 rows to the end of data, with PHP code?

$filenames_array = array();

foreach($filenames_array as $filename)
{

        $file_array  = file($filename);
        $file_value  = $file_array[0];
        $new_array  = explode(",", $file_value);
        $length         = count($new_array);


        for($i = 0; $i < $length; $i++)
        {

        $sql = "INSERT INTO `names`
         (`id`, `name`)  

         VALUES 
         ('',
         '" . $new_array[$i] . "'
         )";

        $result = mysql_query($sql) or die(mysql_error());
        echo $i . 'Row Inserted<br />';
        }

    }

Answer 1

you're probably trying to run too many INSERT statements in a single query.

look into PDO and prepared statements or use SQL syntax like this:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Answer 2

Is it possible that one of the entries you're trying to insert contains a single quote '? In this case, an error would occur and the the loop wouldn't finish.

You should always escape the values you insert into the database with mysql_real_escape_string to prevent problems like that, and to make sure you're not vulnerable to sql injection.

    $sql = "INSERT INTO `names`
            (`id`, `name`)   

            VALUES 
            ('',
            '" . mysql_real_escape_string($new_array[$i]) . "'
            )";

Answer 3

Why not combine every txt file into one big text file, and read it line by line? See the examples here http://php.net/manual/en/function.fgets.php

Mainly:

<?php
$handle = @fopen("/tmp/inputfile.txt", "r");
if ($handle) {
    while (!feof($handle)) {
        $buffer = fgets($handle, 4096);
        echo $buffer;
    }
    fclose($handle);
}
?>