php - Build a batch query for MySQL insert each 1000 items -

i need perform batch insert in mysql/mariadb since data dynamic need build proper sql query. in few steps:

• i should find whether current row exists or not in table - first select inside loop
• right have 1454 have insert around 150k later, better batch query 150k insert per item on loop
• if record exists should update if doesn't should insert ,i not care update yet , code you're seeing insert

so here doing:

// values csv file array of values $data = convertcsvtoarray($filename); echo "debug count(data): ", count($data), "\n";  $i = 0; $sqlinsert = "insert 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 $row) {     $sql = "select id,lastsyncat reps veeva_rep_id='{$row['id']}'";     echo "debug: ", $sql, "\n";     $rs = $conn->query($sql);      if ($rs === false) {         echo 'wrong sql: '.$sql.' error: '.$conn->error, e_user_error;     } else {         $rows_returned = $rs->num_rows;          $veeva_rep_id = "'".$conn->real_escape_string($row['id'])."'";         $first = "'".$conn->real_escape_string(ucfirst(strtolower($row['firstname'])))."'";         $last = "'".$conn->real_escape_string(ucfirst(strtolower($row['lastname'])))."'";         $email = "'".$conn->real_escape_string($row['email'])."'";         $username = "'".$conn->real_escape_string($row['username'])."'";         $display_name = "'".$conn->real_escape_string(                 ucfirst(strtolower($row['firstname'])).' '.ucfirst(strtolower($row['lastname']))             )."'";          // values should added if row doesn't exists         if ($rows_returned === 0) {              // values should append until 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 output console see if it's right or wrong             echo "debug: ", $sqlinsert, "\n";              // query should executed if there 1000 values ready add batch              /*$rs = $conn->query($sqlinsert);              if ($rs === false) {                 echo 'wrong sql: '.$sqlinsert.' error: '.$conn->error, e_user_error;*/             }         } else {             // update             echo "update";         }     } } 

but line of code: echo "debug: ", $sql, "\n"; not outputting nothing console. must doing wrong can't find what. can me build proper batch query , execute each 1000 values append?

proper output should be:

debug count(data): 1454 debug: select id,lastsyncat reps veeva_rep_id='00580000008reolaac' debug: select id,lastsyncat reps veeva_rep_id='005800000039siwaa2' .... debug: insert reps(veeva_rep_id,first,last,email,username,lastloginat,lastsyncat,display_name,rep_type,avatar_url,createdat,updatedat) values(...), values(...), values(...) 

obtained result:

debug count(data): 1454 debug: select id,lastsyncat reps veeva_rep_id='00580000008rgg6aag' debug: insert reps(veeva_rep_id,first,last,email,username,lastloginat,lastsyncat,display_name,rep_type,avatar_url,createdat,updatedat) debug: select id,lastsyncat reps veeva_rep_id='00580000008rq4caaw' debug: insert reps(veeva_rep_id,first,last,email,username,lastloginat,lastsyncat,display_name,rep_type,avatar_url,createdat,updatedat) .... // until reach 1454 results 

the table empty should never goes through else condition (update one).

edit

with answer how code looks now:

$data = convertcsvtoarray($filename); echo "debug count(data): ", count($data), "\n";  $i = 1; $sqlinsert = "insert reps(veeva_rep_id,first,last,email,username,lastloginat,lastsyncat,display_name,rep_type,avatar_url,createdat,updatedat) values";  foreach ($data $row) {     $sql = "select id,lastsyncat reps veeva_rep_id='{$row['id']}'";     $rs = $conn->query($sql);      if ($rs === false) {         echo 'wrong sql: '.$sql.' error: '.$conn->error, e_user_error;     } else {         $rows_returned = $rs->num_rows;          $veeva_rep_id = "'".$conn->real_escape_string($row['id'])."'";         $first = "'".$conn->real_escape_string(ucfirst(strtolower($row['firstname'])))."'";         $last = "'".$conn->real_escape_string(ucfirst(strtolower($row['lastname'])))."'";         $email = "'".$conn->real_escape_string($row['email'])."'";         $username = "'".$conn->real_escape_string($row['username'])."'";         $display_name = "'".$conn->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 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";         }     } } 

but still buggy because:

• i have got first empty insert query: insert reps(veeva_rep_id,first,last,email,username,lastloginat,lastsyncat,display_name,rep_type,avatar_url,createdat,updatedat) values
• i have got second insert query 1000 values() append, happened rest? remaining 454?

can give me tip? help?

consider using insert ignore table check if record exists. how 'insert if not exists' in mysql? if haven't done so, make veeva_rep_id primary key insert ignore work

also check out using pdo transactions, prepared statements , dynamically generating queries using pdo pdo prepared inserts multiple rows in single query

<?php  $sql = 'insert ignore reps(veeva_rep_id,first,last,email,username,lastloginat,lastsyncat,display_name,rep_type,avatar_url,createdat,updatedat) values ';  $insertquery = array(); $insertdata = array();  /*  assuming array csv  $data = array(     0 => array('name' => 'robert', 'value' => 'some value'),     1 => array('name' => 'louise', 'value' => 'another value') ); */  foreach ($data $row) {     $insertquery[] = '(:veeva_rep_id' . $n . ', :first' . $n . ', :last' . $n . ', :email' . $n . ', :username' . $n . ', :lastloginat' . $n . ', :lastsyncat' . $n . ', :display_name' . $n . ', :rep_type' . $n . ', :avatar_url' . $n . ', :createdat' . $n . ', :updatedat' . $n . ')';     $insertdata['veeva_rep_id' . $n] = $row['name'];     $insertdata['first' . $n] = $row['value'];     $insertdata['last' . $n] = $row['name'];     $insertdata['email' . $n] = $row['value'];     $insertdata['username' . $n] = $row['name'];     $insertdata['lastloginat' . $n] = $row['value'];     $insertdata['lastsyncat' . $n] = $row['value'];     $insertdata['display_name' . $n] = $row['name'];     $insertdata['rep_type' . $n] = $row['value'];     $insertdata['avatar_url' . $n] = $row['value'];     $insertdata['createdat' . $n] = $row['name'];     $insertdata['updatedat' . $n] = $row['value'];      $n++; }  $db->begintransaction();  if (!empty($insertquery) , count($insertquery)>1000) {     $sql .= implode(', ', $insertquery);      $stmt = $db->prepare($sql);     $stmt->execute($insertdata); }  $db->commit();  print $sql . php_eol; 

let me know if helps.