0

I have this function to insert some data in my mysql database:

function insertRow($conn, $row, $header) {
    // find indexes
    
    // vorname
    $firstNameIndex = array_search('vorname', $header, true);

    // name / nachname
    $lastNameIndex = array_search('name', $header, true);
    if($lastNameIndex === false) $lastNameIndex = array_search('nachname', $header, true);
    
    // anrede
    $anredeIndex = array_search('anrede', $header, true);
    
    // street
    $streetIndex = array_search('straße', $header, true);
    
    // Hausnummer
    $hausnummerIndex = array_search('hausnummer', $header, true);
    
    // Telefon
    $telefonIndex = array_search('telefon', $header, true);
    
    // PLZ
    $plzIndex = array_search('plz', $header, true);
    
    // ORT
    $ortIndex = array_search('ort', $header, true);
    
    
    $anrede = $row[$anredeIndex];
    $firstName = $row[$firstNameIndex];
    $lastName = $row[$lastNameIndex];
    $street = $row[$streetIndex];
    $houseNumber = $row[$hausnummerIndex];
    $telefon = $row[$telefonIndex];
    $plz = $row[$plzIndex];
    $ort = $row[$ortIndex];
    $title = 'unbekannt';
    $geburtsdatum = 0;
    $hnr_zusatz = "";
    $timestamp = time();
    
    
    $sql = <<<EOSQL
    INSERT INTO adressen (anrede, vorname, nachname, strasse, hnr, telefon1, id_lieferung, priv_gew, 
    status, titel, geburtsdatum, hnr_zusatz, plz, ort, telefon2, mail, sperrung, sperrgrund, optin, 
    optindat, timestamp, zusatz1) VALUES ("$anrede", "$firstName", "$lastName", "$street", 
    "$houseNumber", "$telefon", 0, 0, 0, "$title", $geburtsdatum, "$hnr_zusatz", "$plz", "$ort", 0, "", 
    0, 0, 0, 0, $timestamp, 0);
    EOSQL;
    
    if (!$conn->query($sql) === TRUE) {
        echo "Error: cant add this row " . $sql . "<br/>" . $conn->error;
        print_r($row);
        echo "___________________<br/>";
    }
    
}

It works for small data but when I try it with a file more than 4 MB it doesn't work. I do not have access to the php.ini file sadly. These inserts come originally from an excel file. I wanted to try plupload but didn't understand how to implement it to my method.

Caconde
  • 4,177
  • 7
  • 35
  • 32
Mad Scientist
  • 857
  • 4
  • 16
  • 43
  • Do you have enouh permission for to upload a file onto MySQL server where this file will be available for LOAD DATA? – Akina Nov 23 '20 at 16:37
  • Check `upload_max_filesize` and `post_max_size` in php.ini – Marcel Burkhard Nov 23 '20 at 16:38
  • @MarcelBurkhard unfortunately I don't have access to the php.ini file – Mad Scientist Nov 23 '20 at 16:40
  • 1
    Please note that the way you're building your query is unsafe. You're open to [SQL injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). You should use [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead. – El_Vanja Nov 23 '20 at 16:48
  • Maybe you can change a .user.ini file? https://www.php.net/configuration.file.per-user – Marcel Burkhard Nov 23 '20 at 16:48
  • **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Nov 23 '20 at 20:42
  • If you can, use ]`LOAD DATA INFILE` from a CSV source](https://stackoverflow.com/questions/11077801/import-csv-to-mysql-table/11077961). Alternatively, `LOAD DATA` with some other format. Third option: Multi-insert. – tadman Nov 23 '20 at 20:43
  • This insertion function looks extremely inefficient since it looks up the row indexes each time, for each row, when those never change. Consider making an associative array *once* like `[ 'ort' => 1, ... ]` with the index positions for each so you can quickly look them up. – tadman Nov 23 '20 at 20:44
  • @MadScientist can you post the error message? The file is already on the server or the error happens during upload? – Caconde Nov 24 '20 at 10:22

0 Answers0