title: xpdo - insert multidimensional arrays into db-table - up to 5 rows at once
body: <p>I have a form in which the user can add up to 5 counters with different values, beides a lot of different other values via inputs and selects. </p>
  
  <p>Most of the data shall be written to different existing database-tables, which is so far working fine for each table.</p>
  
  <p>So far I used an existing script that is able to add one row to the different existing database-tables, after the form got submitted. </p>
  
  <p>One of the tables is called "zaehler_test", which needs to get inserted one row for each counter from the $_POST-values the user can add in the form. This edited script is able to do that:</p>
  
  <pre><code> $sql_str = $xpdo-&gt;prepare("INSERT INTO zaehler_test (id, k_id, z_id, zaehlertyp, befestigung, messung, wandlerfaktor, eigentumwandler, zaehlerart, spannungsebene, zugang, anmerkungen) VALUES (:id, :k_id, :z_id, :zaehlertyp, :befestigung, :messung, :wandlerfaktor, :eigentumwandler, :zaehlerart, :spannungsebene, :zugang, :anmerkungen)");
  
    $sql_str-&gt;bindParam (":id", $id);
    $sql_str-&gt;bindParam (":k_id", $id);
    $sql_str-&gt;bindParam (":z_id", $_POST['zaehlernummer']);
    $sql_str-&gt;bindParam (":zaehlertyp", $_POST['zaehlertyp']);
    $sql_str-&gt;bindParam (":befestigung", $_POST['befestigung']);
    $sql_str-&gt;bindParam (":messung", $_POST['messung']);
    $sql_str-&gt;bindParam (":wandlerfaktor", $_POST['wandlerfaktor']);
    $sql_str-&gt;bindParam (":eigentumwandler", $_POST['eigentumwandler']);
    $sql_str-&gt;bindParam (":zaehlerart", $_POST['zaehlerart']);
    $sql_str-&gt;bindParam (":spannungsebene", $_POST['spannungsebene']);
    $sql_str-&gt;bindParam (":zugang", $_POST['zugang']);
    $sql_str-&gt;bindParam (":anmerkungen", $_POST['anmerkungen']);
  
    try{
        $sql_str-&gt;execute();
    }
  </code></pre>
  
  <p>The table "zaehler_test" looks like this:</p>
  
  <pre><code>+----+------+------+------------+-------------+---------+---------------+-----------------+------------+----------------+--------+-------------+
  | id | k_id | z_id | zaehlertyp | befestigung | messung | wandlerfaktor | eigentumwandler | zaehlerart | spannungsebene | zugang | anmerkungen |
  +----+------+------+------------+-------------+---------+---------------+-----------------+------------+----------------+--------+-------------+
  | 1  | 1234 | 1234 |     1      |      2      |    3    |      25       |        4        |     2      |     0          |   1    |      3      |
  +----+------+------+------------+-------------+---------+---------------+-----------------+------------+----------------+--------+-------------+
  </code></pre>
  
  <p>So this works fine so far. But now I need to get the data from the other possible counters the user might have added in the form.</p>
  
  <p>I first tried to insert the data one by one for each counter by simply dupicating the script and editing the related $_POST-variables, but this did not work.</p>
  
  <p>After a lot of research I stumbled upon a <a href="https://stackoverflow.com/questions/4629022/how-to-insert-an-array-into-a-single-mysql-prepared-statement-w-php-and-pdo">script</a>, with which it might be able to insert all data at once to the db-table, but it uses arrays for doing so.</p>
  
  <p>So now after the form gets submitted I created several arrays from the posted values.
  The arrays look like this:</p>
  
  <p><strong>Array "zaehler1"</strong></p>
  
  <pre><code>Array
  (
      [id] =&gt; db6aac9dbe239c0e0095afee9d74cab4_879012
      [k_id] =&gt; db6aac9dbe239c0e0095afee9d74cab4_879012
      [z_id] =&gt; 111
      [zaehlertyp] =&gt; 1
      [befestigung] =&gt; 1
      [messung] =&gt; 1
      [wandlerfaktor] =&gt; 
      [eigentumwandler] =&gt; 0
      [zaehlerart] =&gt; 1
      [spannungsebene] =&gt; 1
      [zugang] =&gt; 2
      [anmerkungen] =&gt; 
  )
  </code></pre>
  
  <p><strong>Array "zaehler2"</strong></p>
  
  <pre><code>Array
  (
      [id] =&gt; db6aac9dbe239c0e0095afee9d74cab4_879012
      [k_id] =&gt; db6aac9dbe239c0e0095afee9d74cab4_879012
      [z_id] =&gt; 222
      [zaehlertyp] =&gt; 2
      [befestigung] =&gt; 1
      [messung] =&gt; 1
      [wandlerfaktor] =&gt; 
      [eigentumwandler] =&gt; 0
      [zaehlerart] =&gt; 1
      [spannungsebene] =&gt; 1
      [zugang] =&gt; 2
      [anmerkungen] =&gt; 
  )
  </code></pre>
  
  <p><em>Array 3 to 5 look the same, all can have different (key-)values (the numerics).</em> </p>
  
  <p>The script I found <a href="https://stackoverflow.com/questions/35440455/pdo-insert-a-simple-array-into-mysql-database">at the earlier mentioned link</a> looks like this:</p>
  
  <pre><code>// multiple queries
  $stmt = $pdo-&gt;prepare('INSERT INTO table SET memberID=:memberID, programID=:programID, date_added=NOW()');
  $data = array(155, 165, 175, 185);
  foreach($data as $d) {
      $stmt-&gt;execute(array(':memberID' =&gt; $memberid, ':programID' =&gt; $d));
  }
  </code></pre>
  
  <p>I tried to adapt it to my needs and wanted first to try it with the first array only and also only with a few data, but I failed. This is what I've got so far:</p>
  
  <pre><code>// multiple queries
  $stmt = $xpdo-&gt;prepare('INSERT INTO zaehler_test SET id=:id, k_id=:k_id, z_id=:z_id');
  $data = $zaehler1;
  foreach($data as $d) {
      $stmt-&gt;execute(array(':id' =&gt; $id, ':k_id' =&gt; $id, ':z_id' =&gt; $d));
  }
  </code></pre>
  
  <p>By using this script I get 12 rows added (one for each array-key), which is obviously wrong. I expected to get one row added with the related values to each key. After that I wanted to try how to get this to work with those up to 5 arrays I mentioned above.</p>
  
  <p>Can someone please guide me to the right direction, I'm kinda lost after several hours of trying and research.</p>
  
  <p>Thank you for your time!</p>