Insert data from repeating group into another table

[marcelhavlik]

Hello, I need to insert data from repeating group into another table. I have to created a PHP plugin (onBeforeCalculations) and started with this code, but have an issue with arrays.

foreach ($this->data['fabrik_vyroba_47_repeat___id'] as $key) {
  $query="INSERT into fabrik_vyroba_51_repeat (parent_id, vyroba_polozky_odvadeni_pozice, vyroba_polozky_odvadeni_polozka) values ('$id', '$this->data[fabrik_vyroba_47_repeat___vyroba_polozky_vypocet_pozice][$key]', '$this->data[fabrik_vyroba_47_repeat___vyroba_polozky_vypocet_np_delka][$key]')";
  $db->setQuery($query);
  $db->query();
  }

 

[cheesegrits]

Any element data which is built on the 'element list' model, which is elements that can have multiple options (database joins, dropdowns, checkboxes, radiobuttons, etc) will be an array. If it's a join element with a "multi select" type (checkbox, multiselect) there's the added complication that the data is actually stored in a separate mapping table. If it's a checkbox element (or similar), you'll also need to convert that array to JSON to store it.

So I'd need to know what element types vyroba_polozky_odvadeni_pozice and vyroba_polozky_odvadeni_polozka are.

-- hugh

 

[marcelhavlik]

Hugh, all of these elements you mentioned are field elements.
fabrik_vyroba_47_repeat___vyroba_polozky_vypocet_pozice is field element
fabrik_vyroba_47_repeat___vyroba_polozky_vypocet_np_delka is calc element

 

[cheesegrits]

Oops, thought I'd replied.

Remove the single quotes from around your values. In PHP, single quotes supress evaluation of variable, so you'd be storing literally '$this->data[fabrik_vyroba_47_repeat___vyroba_polozky_vypocet_pozice][$key]'.

Let me know.

-- hugh

 

[marcelhavlik]

Hugh, I've got this error message when removed the single quotes:
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 '[fabrik_vyroba_47_repeat___vyroba_polozky_vypocet_pozice][1], Array[fabrik_vyrob' at line 1

foreach ($this->data['fabrik_vyroba_47_repeat___id'] as $key) {
  $query="INSERT into fabrik_vyroba_51_repeat (parent_id, vyroba_polozky_odvadeni_pozice, vyroba_polozky_odvadeni_polozka) values ('$id', $this->data[fabrik_vyroba_47_repeat___vyroba_polozky_vypocet_pozice][$key], $this->data[fabrik_vyroba_47_repeat___vyroba_polozky_vypocet_np_delka][$key])";
  $db->setQuery($query);
  $db->query();
  }

 

[cheesegrits]

// I assume $db and $id are set up by some other code, insert that here
$query = $db->getQuery(true);
foreach ($this->data['fabrik_vyroba_47_repeat___id'] as $key => $value) {
  $query->clear()
      ->insert('fabrik_vyroba_51_repeat')
      ->set('parent_id = ' . $db->quote($id))
      ->set('vyroba_polozky_odvadeni_pozice = ' . $db->quote($this->data[fabrik_vyroba_47_repeat___vyroba_polozky_vypocet_pozice][$key]))
      ->set('vyroba_polozky_odvadeni_polozka = ' . $db->quote($this->data[fabrik_vyroba_47_repeat___vyroba_polozky_vypocet_np_delka][$key]));
  $db->setQuery($query);
  $db->execute();
}

If that doesn't work, you'll need to ...

var_dump($data);exit;

... as the first line, and paste the output here.

What about when editing a record? At the moment this code only ever inserts new rows, you probably need to modify it to upsert (so updates if repeat row already exists, inserts if it doesn't).

-- hugh

 

[marcelhavlik]

Works like a charm!!! Really appreciate your help Hugh!
In this table I need only to insert records, but what is the best practise, if I will need to update record if already exists for this parent_ID and key (for example vyroba_polozky_odvadeni_polozka)?

 

[marcelhavlik]

Last question...
How to get value of dropdown element from this repeating group via this method?

I need to use in different form ON DUPLICATE KEY therefore I have created this code:

$id = '{fabrik_vyroba___id}';
$NP_polozka = $this->value[fabrik_vyroba_47_repeat___vyroba_polozky_vypocet_nosnypas_raw][$key];
$db->setQuery("INSERT INTO fabrik_vyroba_41_repeat (parent_id, vyroba_blokace_polozka, vyroba_blokace_pocet) VALUES ($id,$NP_polozka,1,) ON DUPLICATE KEY UPDATE vyroba_blokace_pocet=vyroba_blokace_pocet+1");
$db->execute();

Thank you

 

[cheesegrits]

Dropdowns will submit as an array. If it's a multiselect, you'll have to json_encode() it. If it's a single select, you'll have to access the first entry in the array and save that value.

-- hugh