0

I am trying to learn how to Bulk insert into the following database every 1000 $i rows.

$i = 0;
$data = array();
$param = '';

$sql = "INSERT INTO `myTable` ( `mycolumn`, `othercolumn` ) VALUES ";

while ( $csvfile = fgetcsv($handle, 0, "\t" ) ) {
                $param .= "(?, ?),";
                array_push($data, $csvfile[0], $csvfile[1]);
                if ((!empty($csvfile[0]) || !empty($csvfile[1])) && ($i % 1000 === 0)) {
                    $this->db->query($sql.$param . implode(", ", $data));
                    $param = "";
                    $data =array();
                }
                
                $i++;
            }

output:

{"error":{"code":500,"message":"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?),(?, ?),(?, ?),(?, ?),(?, ?),(?, ?),(?, ?),(?, ?),(?, ?),(?, ?),(?, ?),(?, ' at line 1"

How can I fix this algorithm? I haven't found any example that reassembles this so I am asking you. Thanks in advance.

helpsergio
  • 33
  • 1
  • 6
  • 1
    Why not check the generated query for errors? Could you simply dump it? Also, to simpify the debugging process, you should scale this down to writing the data every 5 or 10 rows – Nico Haase Feb 18 '22 at 15:44
  • 2
    `query()` doesn't use prepared statements. You'll have to prepare and execute for that. – aynber Feb 18 '22 at 15:46
  • Are you trying to use bind parameters? You create SQL with placeholders (albeit with a trailing comma) but then, right at the end, you try to inject values into the SQL code itself. I think you should try a single insert with prepared statements and, once you know how to use your library for that, extend it for the multiple values feature. – Álvaro González Feb 18 '22 at 15:49
  • @aynber I don't know how to do that. Can you provide a simple example? – helpsergio Feb 18 '22 at 15:53
  • @NicoHaase I am trying to print_r( ) the array, but it only printing the string inside the array instead the complete 'organized' array. The only error I have is this one. – helpsergio Feb 18 '22 at 15:54
  • Here are the instructions for [PDO](https://secure.php.net/manual/en/pdo.prepared-statements.php) and [mysqli](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). If you're using a library or framework for the database connection, you'll have to check their documentation. – aynber Feb 18 '22 at 16:01
  • If you have the data in a CSV file, why not use `LOAD DATA INFILE` to load it directly from the file? – Barmar Feb 18 '22 at 16:07
  • @Barmar Can you provide an example for a batch import of 1000 rows at a time? – helpsergio Feb 18 '22 at 17:04
  • Your logic of executing the query when `$i % 1000 == 0` is correct. You also have to do it at the end of the loop to get the last batch. – Barmar Feb 18 '22 at 17:07

0 Answers0