xpdo - insert multidimensional arrays into db-table - up to 5 rows at once

Question

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.

Most of the data shall be written to different existing database-tables, which is so far working fine for each table.

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.

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:

 $sql_str = $xpdo->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->bindParam (":id", $id);
  $sql_str->bindParam (":k_id", $id);
  $sql_str->bindParam (":z_id", $_POST['zaehlernummer']);
  $sql_str->bindParam (":zaehlertyp", $_POST['zaehlertyp']);
  $sql_str->bindParam (":befestigung", $_POST['befestigung']);
  $sql_str->bindParam (":messung", $_POST['messung']);
  $sql_str->bindParam (":wandlerfaktor", $_POST['wandlerfaktor']);
  $sql_str->bindParam (":eigentumwandler", $_POST['eigentumwandler']);
  $sql_str->bindParam (":zaehlerart", $_POST['zaehlerart']);
  $sql_str->bindParam (":spannungsebene", $_POST['spannungsebene']);
  $sql_str->bindParam (":zugang", $_POST['zugang']);
  $sql_str->bindParam (":anmerkungen", $_POST['anmerkungen']);

  try{
      $sql_str->execute();
  }

The table "zaehler_test" looks like this:

+----+------+------+------------+-------------+---------+---------------+-----------------+------------+----------------+--------+-------------+
| 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      |
+----+------+------+------------+-------------+---------+---------------+-----------------+------------+----------------+--------+-------------+

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.

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.

After a lot of research I stumbled upon a script, with which it might be able to insert all data at once to the db-table, but it uses arrays for doing so.

So now after the form gets submitted I created several arrays from the posted values. The arrays look like this:

Array "zaehler1"

Array
(
    [id] => db6aac9dbe239c0e0095afee9d74cab4_879012
    [k_id] => db6aac9dbe239c0e0095afee9d74cab4_879012
    [z_id] => 111
    [zaehlertyp] => 1
    [befestigung] => 1
    [messung] => 1
    [wandlerfaktor] => 
    [eigentumwandler] => 0
    [zaehlerart] => 1
    [spannungsebene] => 1
    [zugang] => 2
    [anmerkungen] => 
)

Array "zaehler2"

Array
(
    [id] => db6aac9dbe239c0e0095afee9d74cab4_879012
    [k_id] => db6aac9dbe239c0e0095afee9d74cab4_879012
    [z_id] => 222
    [zaehlertyp] => 2
    [befestigung] => 1
    [messung] => 1
    [wandlerfaktor] => 
    [eigentumwandler] => 0
    [zaehlerart] => 1
    [spannungsebene] => 1
    [zugang] => 2
    [anmerkungen] => 
)

Array 3 to 5 look the same, all can have different (key-)values (the numerics).

The script I found at the earlier mentioned link looks like this:

// multiple queries
$stmt = $pdo->prepare('INSERT INTO table SET memberID=:memberID, programID=:programID, date_added=NOW()');
$data = array(155, 165, 175, 185);
foreach($data as $d) {
    $stmt->execute(array(':memberID' => $memberid, ':programID' => $d));
}

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:

// multiple queries
$stmt = $xpdo->prepare('INSERT INTO zaehler_test SET id=:id, k_id=:k_id, z_id=:z_id');
$data = $zaehler1;
foreach($data as $d) {
    $stmt->execute(array(':id' => $id, ':k_id' => $id, ':z_id' => $d));
}

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.

Can someone please guide me to the right direction, I'm kinda lost after several hours of trying and research.

Thank you for your time!

Answer 1

Modx - xPDO is our name for open eXtensions to PDO. It's a light-weight ORB (object-relational bridge) library built directly on PDO. It implements the very simple, but effective Active Record pattern for data access

Try this:

//express each set of values in to a separate array
$data = array(
      [155, 165, 175],
      [155, 165, 175] 
   );
$stmt = $xpdo->prepare('INSERT INTO zaehler_test SET id=?, k_id=?, z_id=?');
foreach($data as $d) {
    $stmt->execute($d);
}