php - How to fill an intermediate table? -

i have 2 tables in manytomany relation:

table molécules:

id  | main_name | others … --- | --------- | ------ 1   | caféine   | others … 

table jsontextmining:

id | title  | molecule_name                       | others … ---|------- |-------------------------------------|------ 1  | title1 | colchicine, cellulose, acid, caféine| others … 

and 1 intermediate table:

table json_text_mining_molecule (it's exemple don't succeed fill it):

json_text_mining_id      | molecule_id ------------------------ | --------------- 1                        | corresponding molecule id's 1                        | corresponding molecule id's 2                        | corresponding molecule id's 

my problem molecule_name in jsontextmining string, need separate them before anything.

i tried :

$molecules = explode (', ', $jsontextmining→getmoleculename()); foreach ($molecules $molecule) { $jsontextmining->setmolecule($molecule); } $em->persist($jsontextmining); $em->flush; 

but think should loop on jsontexmining , honnest i'm not sure put part of code. on random page , code execute, should button ?

i know how fill table id's when there onetomany relation, use sql :

update table1 set id_relation1 = table2.id table2  table1.main_name = table2.main_name 

but code fill 1 column id , there's matter of string. there way these id's linked every molecule have several jsontextmining ?

you can first split string using regexp_split function:

select id, regexp_split_to_table(molecule_name,', ') m_name jsontextmining 

that give table of ids , names:

 id |    name ----+------------   1 | acid   1 | caffeine   1 | cellulose   1 | colchicine 

next, can read above, match names ids in molecule table , aggregate ids. put result in this:

select s.id, string_agg(m.id::text, ', ')  (select id, regexp_split_to_table(molecule_name,', ') m_name      jsontextmining) s, molecules m  m.main_name = s.m_name group s.id; 

which gives result:

 id | string_agg ----+------------   1 | 4, 1, 3, 2 (1 row) 

if don't want aggregate results , display them 1 row per molecule rid of string_agg , group by:

select s.id, m.id (select id, regexp_split_to_table(molecule_name,', ') m_name      jsontextmining) s, molecules m  m.main_name = s.m_name;