title: How to process CSV with 100k+ lines in PHP?
body: <p>I have a CSV file with more than 100.000 lines, each line has 3 values separated by semicolon. Total filesize is approx. 5MB.</p>
  
  <p><strong>CSV file is in this format:</strong></p>
  
  <pre><code>stock_id;product_id;amount
  ==========================
  1;1234;0
  1;1235;1
  1;1236;0
  ...
  2;1234;3
  2;1235;2
  2;1236;13
  ...
  3;1234;0
  3;1235;2
  3;1236;0
  ...
  </code></pre>
  
  <p>We have 10 stocks which are indexed 1-10 in CSV. In database we have them saved as 22-31.</p>
  
  <p>CSV is sorted by stock_id, product_id but I think it doesn't matter.</p>
  
  <p><strong>What I have</strong><br></p>
  
  <pre><code>&lt;?php
  
  session_start();
  
  require_once ('db.php');
  
  echo '&lt;meta charset="iso-8859-2"&gt;';
  
  // convert table: `CSV stock id =&gt; DB stock id`
  $stocks = array(
      1  =&gt; 22,
      2  =&gt; 23,
      3  =&gt; 24,
      4  =&gt; 25,
      5  =&gt; 26,
      6  =&gt; 27,
      7  =&gt; 28,
      8  =&gt; 29,
      9  =&gt; 30,
      10 =&gt; 31
  );
  
  $sql = $mysqli-&gt;query("SELECT product_id FROM table WHERE fielddef_id = 1");
  
  while ($row = $sql-&gt;fetch_assoc()) {
      $products[$row['product_id']] = 1;
  }
  
  $csv = file('export.csv');
  
  // go thru CSV file and prepare SQL UPDATE query
  foreach ($csv as $row) {
      $data = explode(';', $row);
      // $data[0] - stock_id
      // $data[1] - product_id
      // $data[2] - amount
  
      if (isset($products[$data[1]])) {
          // in CSV are products which aren't in database
          // there is echo which should show me queries
          echo "  UPDATE t 
                  SET value = " . (int)$data[2] . " 
                  WHERE   fielddef_id = " . (int)$stocks[$data[0]] . " AND 
                          product_id = '" . $data[1] . "' -- product_id isn't just numeric
                  LIMIT 1&lt;br&gt;";
      }
  }
  </code></pre>
  
  <p>Problem is that writing down 100k lines by <code>echo</code> is soooo slow, takes long minutes. I'm not sure what MySQL will do, if it will be faster, or take ± the same time. I have no testing machine here, so I'm worry about testing in on prod server.</p>
  
  <p>My idea was to load CSV file into more variables (better array) like below, but I don't know why.</p>
  
  <pre><code>$csv[0] = lines 0      - 10.000;
  $csv[1] = lines 10.001 - 20.000;
  $csv[2] = lines 20.001 - 30.000;
  $csv[3] = lines 30.001 - 40.000;
  etc. 
  </code></pre>
  
  <p>I found eg. <a href="https://stackoverflow.com/questions/2162497/efficiently-counting-the-number-of-lines-of-a-text-file-200mb">Efficiently counting the number of lines of a text file. (200mb+)</a>, but I'm not sure how it can help me. </p>
  
  <p>When I replace <code>foreach</code> for <code>print_r</code>, I get dump in &lt; 1 sec. The task is to make the foreach loop with database update faster.</p>
  
  <p>Any ideas how to updates so many records in database?<br>
  Thanks.</p>