title: Build a batch query for MySQL insert each 1000 items
body: <p>I need to perform a batch insert in MySQL/MariaDB but since data is dynamic I need to build the proper SQL query. In a few steps:</p>
  
  <ul>
  <li>I should find whether the current row exists or not in table - this is the first SELECT inside the loop</li>
  <li>Right now I have 1454 but have to insert around 150k later, is better a batch query than 150k INSERT per item on the loop</li>
  <li>If record already exists I should update it if doesn't then I should insert ,I just not care about UPDATE yet and the code you're seeing is only for INSERT</li>
  </ul>
  
  <p>So here is what I am doing:</p>
  
  <pre><code>// Get values from Csv file as an array of values
  $data = convertCsvToArray($fileName);
  echo "DEBUG count(data): ", count($data), "\n";
  
  $i = 0;
  $sqlInsert = "INSERT INTO reps(veeva_rep_id,first,last,email,username,lastLoginAt,lastSyncAt,display_name,rep_type,avatar_url,createdAt,updatedAt) ";
  
  // Processing on each row of data
  foreach ($data as $row) {
      $sql = "SELECT id,lastSyncAt FROM reps WHERE veeva_rep_id='{$row['Id']}'";
      echo "DEBUG: ", $sql, "\n";
      $rs = $conn-&gt;query($sql);
  
      if ($rs === false) {
          echo 'Wrong SQL: '.$sql.' Error: '.$conn-&gt;error, E_USER_ERROR;
      } else {
          $rows_returned = $rs-&gt;num_rows;
  
          $veeva_rep_id = "'".$conn-&gt;real_escape_string($row['Id'])."'";
          $first = "'".$conn-&gt;real_escape_string(ucfirst(strtolower($row['FirstName'])))."'";
          $last = "'".$conn-&gt;real_escape_string(ucfirst(strtolower($row['LastName'])))."'";
          $email = "'".$conn-&gt;real_escape_string($row['Email'])."'";
          $username = "'".$conn-&gt;real_escape_string($row['Username'])."'";
          $display_name = "'".$conn-&gt;real_escape_string(
                  ucfirst(strtolower($row['FirstName'])).' '.ucfirst(strtolower($row['LastName']))
              )."'";
  
          // VALUES should be added only if row doesn't exists
          if ($rows_returned === 0) {
  
              // VALUES should be append until they reach 1000
              while ($i % 1000 !== 0) {
                  $sqlInsert .= "VALUES($veeva_rep_id,$first,$last,$email,$username,NOW(),NOW(),$display_name,'VEEVA','https://pdone.s3.amazonaws.com/avatar/default_avatar.png',NOW(),NOW())";
                  ++$i;;
              }
  
              // QUERY should be output to console to see if it's right or something is wrong
              echo "DEBUG: ", $sqlInsert, "\n";
  
              // QUERY should be executed if there are 1000 VALUES ready to add as a batch
  
              /*$rs = $conn-&gt;query($sqlInsert);
  
              if ($rs === false) {
                  echo 'Wrong SQL: '.$sqlInsert.' Error: '.$conn-&gt;error, E_USER_ERROR;*/
              }
          } else {
              // UPDATE
              echo "UPDATE";
          }
      }
  }
  </code></pre>
  
  <p>But this line of code: <code>echo "DEBUG: ", $sql, "\n";</code> is not outputting nothing to console. I must be doing something wrong but I can't find what. Can any help me to build the proper batch query and to execute it each 1000 values append?</p>
  
  <p><strong>Proper output should be:</strong></p>
  
  <pre><code>DEBUG count(data): 1454
  DEBUG: SELECT id,lastSyncAt FROM reps WHERE veeva_rep_id='00580000008ReolAAC'
  DEBUG: SELECT id,lastSyncAt FROM reps WHERE veeva_rep_id='005800000039SIWAA2'
  ....
  DEBUG: INSERT INTO reps(veeva_rep_id,first,last,email,username,lastLoginAt,lastSyncAt,display_name,rep_type,avatar_url,createdAt,updatedAt) VALUES(...), VALUES(...), VALUES(...)
  </code></pre>
  
  <p><strong>Obtained result:</strong></p>
  
  <pre><code>DEBUG count(data): 1454
  DEBUG: SELECT id,lastSyncAt FROM reps WHERE veeva_rep_id='00580000008RGg6AAG'
  DEBUG: INSERT INTO reps(veeva_rep_id,first,last,email,username,lastLoginAt,lastSyncAt,display_name,rep_type,avatar_url,createdAt,updatedAt)
  DEBUG: SELECT id,lastSyncAt FROM reps WHERE veeva_rep_id='00580000008RQ4CAAW'
  DEBUG: INSERT INTO reps(veeva_rep_id,first,last,email,username,lastLoginAt,lastSyncAt,display_name,rep_type,avatar_url,createdAt,updatedAt)
  .... // until reach 1454 results
  </code></pre>
  
  <p>The table is empty so it should never goes through <code>ELSE</code> condition (UPDATE one).</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>With help from the answer this is how the code looks now:</p>
  
  <pre><code>$data = convertCsvToArray($fileName);
  echo "DEBUG count(data): ", count($data), "\n";
  
  $i = 1;
  $sqlInsert = "INSERT INTO reps(veeva_rep_id,first,last,email,username,lastLoginAt,lastSyncAt,display_name,rep_type,avatar_url,createdAt,updatedAt) VALUES";
  
  foreach ($data as $row) {
      $sql = "SELECT id,lastSyncAt FROM reps WHERE veeva_rep_id='{$row['Id']}'";
      $rs = $conn-&gt;query($sql);
  
      if ($rs === false) {
          echo 'Wrong SQL: '.$sql.' Error: '.$conn-&gt;error, E_USER_ERROR;
      } else {
          $rows_returned = $rs-&gt;num_rows;
  
          $veeva_rep_id = "'".$conn-&gt;real_escape_string($row['Id'])."'";
          $first = "'".$conn-&gt;real_escape_string(ucfirst(strtolower($row['FirstName'])))."'";
          $last = "'".$conn-&gt;real_escape_string(ucfirst(strtolower($row['LastName'])))."'";
          $email = "'".$conn-&gt;real_escape_string($row['Email'])."'";
          $username = "'".$conn-&gt;real_escape_string($row['Username'])."'";
          $display_name = "'".$conn-&gt;real_escape_string(
                  ucfirst(strtolower($row['FirstName'])).' '.ucfirst(strtolower($row['LastName']))
              )."'";
  
          if ($rows_returned === 0) {
              if ($i % 1000 === 0) {
                  file_put_contents("output.log", $sqlInsert."\n", FILE_APPEND);
                  $sqlInsert = "INSERT INTO reps(veeva_rep_id,first,last,email,username,lastLoginAt,lastSyncAt,display_name,rep_type,avatar_url,createdAt,updatedAt) VALUES";
              } else {
                  $sqlInsert .= "($veeva_rep_id,$first,$last,$email,$username,NOW(),NOW(),$display_name,'VEEVA','https://pdone.s3.amazonaws.com/avatar/default_avatar.png',NOW(),NOW()), ";
              }
  
              $i++;
          } else {
              echo "UPDATE";
          }
      }
  }
  </code></pre>
  
  <p>But still buggy because:</p>
  
  <ul>
  <li>I have got a first empty INSERT query: <code>INSERT INTO reps(veeva_rep_id,first,last,email,username,lastLoginAt,lastSyncAt,display_name,rep_type,avatar_url,createdAt,updatedAt) VALUES</code></li>
  <li>I have got a second INSERT query with 1000 VALUES() append, but what happened with the rest? The remaining 454?</li>
  </ul>
  
  <p>Can any give me another tip? Help?</p>