php - mass insert in cakephp 3 -- how to use prepare statements instead -


i using cakephp 3.x

i need bulk inserts (> 5000 rows) in single move.

the usual orm way of using save or savemany slow (> 5 mins).

i have tried chaining ->values() , single execute() call. memory limit error.

the 1 works fast no errors writing raw query.

so wrote raw query way of doing it

 $rawquery = 'insert stock_discrepancies (`confo_id`, `actual`, `expected`, `da_sub_account_id`,`isin`, `account_name`, `ibm_code`, `stock_code`, `stock_name`, `discrepancy`, `missing_expected`, `missing_actual`, `sequence`) values ';       foreach ($data $entity) {         $rawqueryvalues = sprintf('("%1$s", %2$d, %3$d, "%4$s", "%5$s", "%6$s", "%7$s", "%8$s", "%9$s", %10$d, %11$d, %12$d, %13$d)', $confo_id, intval($entity->actual), intval($entity->expected), $entity->da_sub_account_id, $entity->isin, $entity->account_name, $entity->ibm_code, $entity->stock_code, $entity->stock_name, intval($entity->discrepancy), intval($entity->missing_expected), intval($entity->missing_actual), intval($entity->sequence));         $rawquery .= $rawqueryvalues . ',';     }     $rawquery = rtrim($rawquery, ',');     $this->controller->log($rawquery);     $stmt = $connection->execute($rawquery); 

it works. got down milliseconds.

but there're 2 problems.

  1. there's no sanitisation using prepared statement
  2. if of string contains " character, query breaks.

i found api bindvalue it's hard me add bindvalue midway through forloop because still building values. mean need run forloop twice?

  1. first time run forloop can have ?
  2. second time run forloop when put in bindvalues?

please advise.

thank you.


Comments

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -