0

/beginnote This question was marked as 'duplicate'. It is different from the referenced question because in my application I cannot use LOAD IN as the input file will never be nicely formatted so I -have- to read it in line by line to test for valid records.

Also: in my working code I have changed from mysql_ to mysqli and I'm getting the same symptoms.

Also: I was told by my host provider that the browser errors I'm getting (500 or content encoding error) won't show up in the server log.

I'd like to re-open this and learn how to troubleshoot the problem. /endnote

I'm getting an error trying to insert records into MySQL from a CSV file in Firefox and Chrome. The CSV file has 300,000 lines. It will either be 'The page you are trying to view cannot be shown because it uses an invalid or unsupported form of compression.' or just an Error 500.

Is the problem that the buffer is getting filled up? I tried using ob_clean() and flush() but that did not work. Ideas?

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <title>Process uploaded CSV file for Seatac Noise</title>
  <link rel="stylesheet" type="text/css" href="style.css" />
</head>

<?php
error_reporting(E_ALL);
//echo "<pre>" . PHP_EOL;
$database = "jch";
$user     = "jch";
$password = "password";
$connector = 'jch.55.com';
$link = mysql_connect($connector,$user,$password);
mysql_select_db($database) or die( "Unable to select database");
$filter = $_POST["filter"];
$uploaddir = '/uploads';

$path = $uploaddir  . $filter . basename($_FILES['ufile']['name']);

if($ufile != none)
{ 
if (move_uploaded_file($_FILES['ufile']['tmp_name'], $path)) {
  echo "File is valid and was successfully uploaded.\n";

    $row = 0;
    if (($handle = fopen($path, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    $row++;

            if( strlen($data[4]) > 0 )          
            {

             $monitor_id = $data[1];                                                                                             
                 $time = date("Y-m-d H:i:s", strtotime($data[0]));  

             echo('<p>' . $time . ' rows processed: ' . $row . '</p>');

                 $noise_level = $data[2];                                                                                            
                 $flight_id = $data[4];                                                                                          
                 $aircraft_type = $data[3];                                                                                          

                 $query="INSERT INTO jch_noise_detail_copy (
                        monitor_id,                                                                                          
                        time,
                        noise_level,
                        flight_id,
                        aircraft_type) 
                        VALUES(" . 
                            "'" . $monitor_id . "','" .
                            $time . "'," .
                            $noise_level . ",'" .
                            $flight_id . "','" .
                            $aircraft_type . "')";

                        $result = mysql_query($query);
                        if( mysql_errno($link)!= 0 ){
                                echo $query . "\n";
                                echo mysql_errno($link) . ": " . mysql_error($link) . "\n"  ;
                        }
                }

        }  // end while
        mysql_close();


    }

}
else
    echo 'No Rows found matching this date or filter';  
}
mysql_close();
fclose($handle);
?>
</body>
</html>
jchwebdev
  • 4,154
  • 5
  • 18
  • 26
  • 1
    Please stop using `mysql_*` functions. They've been deprecated for over 3 years now and they will no longer work in PHP 7+. Your code is wide open to [SQL injection](https://en.wikipedia.org/wiki/SQL_injection). This basicly means that it would take less than a minute for any visitor of your website to do whatever they want with your database. That includes destroying it. Considder using [MySQLi](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php) instead in combination with [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement). – icecub Aug 22 '17 at 20:02
  • What version of PHP are you using? I notice you're using `mysql_*` commands, which have been deprecated for a very, very long time. Error 500 usually means there's a syntax or other fatal error with your code. What does your error log say? – JBH Aug 22 '17 at 20:02
  • 1. You probably should not import large files through a web interface. That's not what it was designed for. 2. If you insist on doing it, then at least use the tools mysql provided for importing data: load data infile sql command. – Shadow Aug 22 '17 at 20:03
  • **WARNING**: Do not suppress errors when calling methods with the `@` operator. If something goes wrong you want to know about it and will need to take corrective action, display a useful message for the user, log the problem, or all that and more. It also makes debugging issues like this a whole lot more complicated if you ignore errors that are trying to point out serious problems. – tadman Aug 22 '17 at 20:03
  • Use [`LOAD DATA INFILE`](https://dev.mysql.com/doc/refman/5.7/en/load-data.html) to [import a CSV into MySQL](https://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile). – tadman Aug 22 '17 at 20:04
  • just put this at the top of your code `set_time_limit(0);` things not error out – cmorrissey Aug 22 '17 at 20:13
  • 1. I updated the code to use MySQLi and it made no difference. 2. Unfortunately, it's not generating a PHP error. I'm on shared hosting and I have to wait 24 hrs to get a server log. 3. I didn't use LOAD DATA because the CSV file is not well-formatted. Many of the lines have garbage and need to be ignored. Is there, perhaps, a way to put conditionals (IF) in a LOAD DATA? If so, could someone post a short example? – jchwebdev Aug 23 '17 at 21:51
  • Try to increase memory limit: ini_set('memory_limit','1024M'); add this after : if($ufile != none) { in your code – Just_Do_It Sep 05 '17 at 14:39

1 Answers1

0

In this code you insert 300,000 rows with 300,000 separate inserts. Every time mysql_query() create a separate call from php to mysql.

You can optimize it using bulk inserts - MySQL Reference

At the end the query will look like:

INSERT INTO jch_noise_detail_copy (
    monitor_id,                                                                                          
    time,
    noise_level,
    flight_id,
    aircraft_type 
) VALUES ($monitor_id_0, time_0 ...), ($monitor_id_1, time_1, ...), ..., ($monitor_id_N, time_N, ...);
dmkov
  • 314
  • 1
  • 11