title: How to get Row Number when insert records from File to DB with LOAD DATA LOCAL INFILE, Transaction and Commit?
body: <p>How to get Row Number when insert records from File to DB with LOAD DATA LOCAL INFILE, Transaction and Commit?</p>
  
  <p>However, only getting <code>true</code> or <code>false</code>(in fail) with the <code>var</code> dump present</p>
  
  <p>Statement:</p>
  
  <pre><code>LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE tablename 
  FIELDS TERMINATED BY ',' 
  LINES TERMINATED BY ' ' ( `Col1`, `Col2`, `Col3`)
  </code></pre>
  
  <p>Script:</p>
  
  <pre><code>public function PDO_UL_IUPD($dbUsing, $stmtpre) {
          $started = microtime(true);
          $DB      = [];
          $val     = [];
          $conn    = new PDO(
              "mysql:host=" . DB_HOST . ";dbname=" . DB_PRE . "" . $dbUsing . "",
              DB_USERNAME,
              DB_PASS,
              array(
                  PDO::MYSQL_ATTR_LOCAL_INFILE       =&gt; TRUE,
                  PDO::MYSQL_ATTR_INIT_COMMAND       =&gt; "SET NAMES UTF8",
                  PDO::ATTR_EMULATE_PREPARES         =&gt; FALSE,
                  PDO::MYSQL_ATTR_USE_BUFFERED_QUERY =&gt; TRUE,
                  PDO::ATTR_ERRMODE                  =&gt; PDO::ERRMODE_EXCEPTION,
              ));
          $conn-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
          try {
              $conn-&gt;beginTransaction();
              $conn-&gt;exec("set names utf8");
              $count  = 0;
              $RowReg = 0;
              // our SQL statements
              foreach ($stmtpre as $stmt) {
                  $count++;
                  $conn-&gt;exec($stmt);
              }
              $RowReg = $conn-&gt;commit();
              echo var_dump($RowReg);
              $DB['SMG'] = "Correct Execution, a csv file has been dumped.&lt;br&gt;&lt;br&gt;";
              $DB['R']   = true;
          } catch (PDOException $e) {
              $DB['SMG'] = "Error: " . $e-&gt;getMessage();
              $DB['R']   = false;
          }
          return $DB;
      }
  </code></pre>
  
  <p><strong>Update1 Script:</strong></p>
  
  <p>Trying to get Row Number from <code>EXEC</code> return 0</p>
  
  <p>Script:</p>
  
  <pre><code>public function PDO_UL_IUPD($dbUsing, $stmtpre) {
          $started = microtime(true);
          $DB      = [];
          $val     = [];
          $conn    = new PDO(
              "mysql:host=" . DB_HOST . ";dbname=" . DB_PRE . "" . $dbUsing . "",
              DB_USERNAME,
              DB_PASS,
              array(
                  PDO::MYSQL_ATTR_LOCAL_INFILE       =&gt; TRUE,
                  PDO::MYSQL_ATTR_INIT_COMMAND       =&gt; "SET NAMES UTF8",
                  PDO::ATTR_EMULATE_PREPARES         =&gt; FALSE,
                  PDO::MYSQL_ATTR_USE_BUFFERED_QUERY =&gt; TRUE,
                  PDO::ATTR_ERRMODE                  =&gt; PDO::ERRMODE_EXCEPTION,
              ));
          $conn-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
          $conn-&gt;beginTransaction();
          try {
              $conn-&gt;exec("set names utf8");
              $count  = 0;
              $RowReg=0;
              $cReg=0;
              // our SQL statements
              foreach ($stmtpre as $stmt) {
                  //echo $stmt."&lt;br&gt;&lt;br&gt;&lt;br&gt;";
                  $count++;
                  $cReg=$conn-&gt;exec($stmt);
                  $RowReg=$RowReg+$cReg;
              }
              $conn-&gt;commit();
              echo var_dump($RowReg);
              $DB['SMG'] = "Correct Execution, a csv file has been dumped.&lt;br&gt;&lt;br&gt;&lt;br&gt;";
              $DB['R']   = true;
          } catch (PDOException $e) {
              $DB['SMG'] = "Error en Tiempo de Ejecucion: " . $e-&gt;getMessage();
              $DB['R']   = false;
          }
          return $DB;
      }
  </code></pre>
  
  <p><strong>Update 2 statement:</strong></p>
  
  <p>adding this Return 1</p>
  
  <pre><code>SET @row=0;
  LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE tablename 
  FIELDS TERMINATED BY ',' 
  LINES TERMINATED BY ' ' ( `Col1`, `Col2`, `Col3`)
  SET file_line_no = @row:=@row+1;
  </code></pre>