There is a code, in a loop requests are made to the database. How to make by means of union one request?

foreach($ids as $id){ $oldId = ExecSQL_SelectOne("SELECT point_ref FROM {$this->model->MainTable} WHERE id =".$id); ExecSQL_NoSelect("INSERT INTO history (id, c_type, employee_ref, object_name, object_ref, c_data, c_timestamp) VALUES (nextval ('history_id_seq'), 'upd', ".$_SESSION['emp_id'].",'".$this->MC."','".$id."', '{\"Popup1\":{\"point_ref\":\"$oldId\"}}', '".date('dmY H:i:s')."')"); }
  • Here, the union seems not to be needed. You can make a select with the where id IN ($ids) parameter. And then from this data make a massive insert - Alexey Shimansky
  • and how to make a massive then? - Albert
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

Since, apparently, the ExecSQL_SelectOne methods and similar self-written ones, and I don’t know their exact work, I will try to describe the process in some arbitrary code.

There is a connection to the database $db_conn

There is an array with identifiers $ids = [2,3,666];

Select data from the database, including ID:

 $data = $db_conn->query('SELECT `field_one`, `field_two` FROM `table_1` WHERE id IN ('.implode(',', $ids).')' )->fetchAssoc;

So, as with the help of INSERT you can insert several lines, example:

 INSERT INTO example VALUES (100, 'Name 1', 'Value 1', 'Other 1'), (101, 'Name 2', 'Value 2', 'Other 2'), (102, 'Name 3', 'Value 3', 'Other 3'), (103, 'Name 4', 'Value 4', 'Other 4');

then we form an array of values for later insertion.

 $values = []; foreach ($data as $item) { $values[] = "('".$item['field_one']."','".($item['field_two'])."')"; }

and insert in one query:

 $query ="INSERT INTO `table_2` (field_from_one, field_from_two) VALUES".implode(", ", $values); $db_conn -> simpleQuery($query);

Since this example is illustrative, it is necessary to remember and at the same time take into account that it is worth applying prepared queries, placeholders and other methods to prevent SQL injections.

And also if there is a lot of data, then it should be split into several insertion requests, since There are restrictions on the amount of inserted data