How to show separate records for each array in a field

Applications Grid / Reports
#1

i have a grid with in each record a field with a group of id’s comma separated (array).
now i want to show a list of records where each id has it’s own record shown

example:

item | category | groups| date
1 | 3 | 123,67,93,14 | 14-05-1970
2 | 5 | 56,123,67| 23-09-1999

should be shown in a grid
item | category | groups| date
1 | 3 | 123 | 14-05-1970
1 | 3 | 67 | 14-05-1970
1 | 3 | 93| 14-05-1970
1 | 3 | 14 | 14-05-1970
2 | 5 | 56| 23-09-1999
2 | 5 | 123| 23-09-1999
2 | 5 | 67| 23-09-1999

suggestions?

#2

If the source database is MYSQL you can use GROUP_CONCAT() function.

#3

Group_concat merge te fileds together for all i know. i wan’t every array as a separate record.
or do i mis some functionality?

#4

Hi
try something like this
https://forums.mysql.com/read.php?10,383804,383897#msg-383897

hope it helps

regards

#5

No, you are right, that function is useful to get table A from table B, but you want the opposite, so I guess you need a procedure that return a recordset.

#6

I would do this:
$sql=" SELECT item , category , groups, date
FROM table “;
sc_lookup(rs,$sql) ;
foreach ({rs} as $key => $value)
{
$item= {rs[$key][0]};
$category= {rs[$key][1]};
$groups= {rs[$key][2]};
$date= {rs[$key][3]};
///
$work=explode(”,",$category);
foreach($category as $categoryid)
{
$sql2="INSERT into temp_table (item, category , groups,date)
VALUES (’$item’,’$categoryid’,’$groups’,’$date’) ";
sc_exec_sql($sql2);
}
}

//now just display the temp table instead of the regular table

#7

that should be a solution. i’ll check it out and let you know. thx

#8

i tried a different approach that get’s the same results. and used your’e solution to get an idea. tthx

combined two tables one with the array and ne with the actual value, then use FIND_IN_SET in a grid. that worked great.

1 Like