import CSV into SQL table works on XAMPP but not on the online server

Question

I made a function in PHP to write into a SQL table the data from a bunch of csv files that are present in a folder.

The function perfectly works on the XAMPP server on my PC, it creates the table and it writes thousands of lines taken from dozens of CSV files, but when I exported the function on the online-server the table is correctly created, the CSV files are correctly opened (I checked it with print_r), but the CSV data are not written into the and SQL table and no error messages are displayed.

This is my code:

<?PHP
      include('sys/dbconnect.php');
      $sql1 = "CREATE TABLE IF NOT EXISTS 
                          milk
                          (
                             milk_record int(10) NOT NULL,
                             panel_number int(3) NOT NULL,
                             ...etc.... (41 columns in total)
                             PRIMARY KEY (milk_record)
                        )";

     if($conn->query($sql1) === TRUE) {echo "Table ready <br>";} 
     else { echo "Error creating database: " . $conn->error . "<br>";}
     $conn->close();

     $dir    = 'csvfiles/';
     $files = scandir($dir);

     $fileCount = 0;
     foreach($files as $fn) 
     { 
        if($fileCount < 2) { $fileCount++; continue;}
        $filename=$dir.$fn; 
        
        if ($fn)
        {
            $directory = 'csvfiles/';
            // echo "- file: ".$directory.$fn." <br> ";
            
            include('sys/dbconnect.php');
            ini_set('auto_detect_line_endings',TRUE); 
            $file = fopen($filename, "r");
                      
             set_time_limit (240);
             while ($getData = fgetcsv($file, 0, ',', '"'))
             {
                 // print_r($getData);
                  
                  $sql = "INSERT INTO milk 
                            (
                                milk_record,
                                panel_number,
                                ....etc.... (41 columns in total)
                            ) 
                          VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) "; 
                 
                       $stmt = mysqli_prepare($conn, $sql);
                       
                       $stmt->bind_param
                       (    "sssssssssssssssssssssssssssssssssssssssss",
                            $getData[0],  $getData[1],  $getData[2],  $getData[3],  $getData[4],  $getData[5],  $getData[6],  $getData[7],  $getData[8],  $getData[9],
                            $getData[10], $getData[11], $getData[12], $getData[13], $getData[14], $getData[15], $getData[16], $getData[17], $getData[18], $getData[19],
                            $getData[20], $getData[21], $getData[22], $getData[23], $getData[24], $getData[25], $getData[26], $getData[27], $getData[28], $getData[29],
                            $getData[30], $getData[31], $getData[32], $getData[33], $getData[34], $getData[35], $getData[36], $getData[37], $getData[38], $getData[39],
                            $getData[40]
                       );
                       
                       $sql = "INSERT into milk() values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                       
                       $stmt->execute();
             }
             fclose($file);  
               
             $delete_first_line =  "DELETE FROM milk WHERE milk_record =0;";
             $delete = mysqli_query($conn, $delete_first_line);

             mysqli_close($conn);

?>

Have you got PHP error logging enabled on the server? see these guides to set up PHP error logging, and then to enable mysqli to throw exceptions when SQL errors occur: https://stackify.com/php-error-logs-guide/ (php error logging/reporting) https://stackoverflow.com/a/14578644/5947043 (mysqli exception handling) — ADyson, Aug 13 '20 at 12:30
Hi Will, yes, it executes correctly. I dropped the tabled and the command creates it — giorgio, Aug 13 '20 at 14:42
So if there are no errors, then you need to debug the code. Add some lines to log data to another file (or output to the screen if you prefer) showing what path the code actually takes. I'm wondering if somehow it doesn't find any rows in the CSV, or some such thing. Otherwise it's hard to see how it could be failing to insert, and yet not crashing. You need to dig deeper and figure out what the code really is doing. — ADyson, Aug 13 '20 at 14:58
Consider carefully checking your `dbconnect.php` script when switching servers. You might not have adjusted connection object to online server database. — Parfait, Aug 13 '20 at 16:30
ok I found the error by enabling (..in the correct way...) the MYSQLY-ERROR-REPORT recommended by @ADyson (thanks!). If there are some fields not properly formatted, the table in XAMPP accepts them anyway, but the table on the online-server does not accept them and it stops immediately at row 1, when it reads the header, returning the error "incorrect integer value: 'milk_record' ". — giorgio, Aug 14 '20 at 01:39
So now the new question is: **how do I set the online-server to accept values that are not perfectly formatted?** I have created both the tables (the one on the XAMPP server and the one on the online-server) with the same "CREATE TABLE" code and I'm trying to write the same CSV files, so the problem _should not_ be in the format of the table or in the format of the fields.... Thanks again to all !! — giorgio, Aug 14 '20 at 01:39
the online server is probably executing in **STRICT MODE**. I tried to change the settings by launching the query **SET @@global.sql_mode='';** but I don't have the SUPER privileges required and I can't access the my.ini file. — giorgio, Aug 14 '20 at 02:35
you'll need to contact the server administrator to see if you can change that. Or, if your values aren't valid integers, try storing them in a varchar column instead. Or, if you're trying to insert empty strings into an integer column - don't. Adjust your script so that it inserts NULL instead when it finds an empty value in the CSV. — ADyson, Aug 14 '20 at 07:58
Thanks @ADyson, I added a condition to jump the _header-row_ of the CSV files, I removed the column that sometimes get bad formatted data (they are not strictly required) and I replaced the command "INSERT INTO" with **INSERT IGNORE INTO** to prevent the _duplicated-primary-key-error_ when the function tries to insert an existing record and now it works. Thanks again!! — giorgio, Aug 14 '20 at 10:25
ah yes, headers would certainly be a problem if you're trying to insert integers. Probably not a useful thing to have as a row in your table anyway, so it would make sense to skip it regardless. Glad you got it working anyhow. — ADyson, Aug 14 '20 at 10:29

import CSV into SQL table works on XAMPP but not on the online server

0 Answers0