Databases
StackOverflow2013
Postsdbo
POInserting values from multidim. array using PDO takes a long time. Is there a better way?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInserting values from multidim. array using PDO takes a long time. Is there a better way?
    text
    copied!<p>I got an array of 650 rows. Inserting this using PDO takes between 10-15 seconds on my local computer. That's very slow. Is this because of disk read/write? Or could it be something else?</p> <p>This is my array (first 4 rows):</p> <pre><code>Array ( [0] =&gt; Array ( [0] =&gt; 3 [1] =&gt; 1 ) [1] =&gt; Array ( [0] =&gt; 3 [1] =&gt; 2 ) [2] =&gt; Array ( [0] =&gt; 3 [1] =&gt; 5 ) [3] =&gt; Array ( [0] =&gt; 8 [1] =&gt; 1 ) ) </code></pre> <p>And this is my code: </p> <pre><code>$stmt = $this-&gt;db-&gt;prepare("INSERT INTO sl_link_store_category (item_a_ID, item_b_ID) VALUES (:item_a_ID, :item_b_ID)"); foreach($my_array as $row) { $stmt-&gt;execute(array(':item_a_ID' =&gt; $row[0], ':item_b_ID' =&gt; $row[1])); } </code></pre> <p><strong>SOLUTION</strong> </p> <p>For those who is wondering, her eis my solution for inserting multiple rows<br> using only one <code>$stmt-&gt;execute</code>:</p> <pre><code> $input_arr; // This array one has lots of values $sql = "INSERT INTO sl_link_store_category (field_a, field_b) VALUES "; $i = 0; // I create the query string with unique prepared values // I could probably have used a for loop since I'm not using any // values from $row foreach($input_arr as $row) { $i++; $sql .= "(:field_a_$i, :field_a_$i), "; } // Remove the last comma (and white space at the end) $sql = substr(trim($sql), 0, -1); $stmt = $this-&gt;db-&gt;prepare($sql); // I need to create a new associative array with field name // matching the prepared values in the SQL statement. $i = 0; $arr = array(); foreach($input_arr as $row) { $i++; $arr[":field_a_$i"] = $row[0]; $arr[":field_b_$i"] = $row[1]; } $stmt-&gt;execute($arr); } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload