Create PHP array from MySQL's GROUP_CONCAT

Question

I get data from MySQL query by using GROUP_CONCAT:

GROUP_CONCAT('id => ',assignments.userid,', assigned => ',assignments.assigned SEPARATOR ';') as assigneeids

And trying to convert it to PHP array.

$assignees = explode(';', $ticket['assigneeids']);
foreach($assignees as $assignee) {
    echo "$assignee\n"; // $assignee['id'] outputs 'i'
    echo gettype($assignee) . '\n';
}

But unfortunately $assignee becomes a string instead of array. Output:

id => 1001, assigned => 1419648601
string
id => 1002, assigned => 1419649207
string

What am I doing wrong?

Are you attempting to generate PHP code from a SQL query and expecting it to be evaluated as PHP? That won't work without eval(), but that is not the way to approach this. Can you clarify that your goal is to produce arrays with the 2D structure containing sub arrays array('id' => 1234, 'assigned' => 987654) ? — Michael Berkowski
– Michael Berkowski, Commented May 26, 2016 at 23:41
Can you post a sample of the source table? You probably just need a simpler query with a normal fetch loop, but a little bit of array manipulation. Is this GROUP_CONCAT() part of a larger query, or was it the only thing returned by your query? — Michael Berkowski
– Michael Berkowski, Commented May 26, 2016 at 23:43
Ok, post a bit more of the query then. Even if the query is complicated, the fetch process should still be simple. Or the query may need to be reorganized a little to allow the dimensions you need with the appropriate grouping. — Michael Berkowski
– Michael Berkowski, Commented May 26, 2016 at 23:45

Darren · Accepted Answer · 2016-05-26 23:50:20Z

2

You're concatenating it into a string, not an array. Would you not be better off doing something like this?

GROUP_CONCAT(assignments.userid,'_',assignments.assigned SEPARATOR ';') as assigneeids

Once fetched, you'll need to do some explode()'ing magic

$assignees = explode(';', $ticket['assigneeids']);
foreach($assignees as $assignee) {
    list($id, $assigned) = explode("_", $assignee);
    echo "$id\n";
    echo gettype($assigned) . '\n';
}

Example/Demo

answered May 26, 2016 at 23:50

Darren

13.1k4 gold badges44 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

scorpio1441 Over a year ago

That worked perfectly fine, but isn't this a little dirty workaround? =)

Darren Over a year ago

@AlexG Haha not exactly (only a bit), since you were on that path already! Since you want to create an array, you'd do more harm implementing eval(). Let PHP do your grunt work ! ;P

Barmar Over a year ago

I just learned something new. I didn't realize you could put multiple expressions in GROUP_CONCAT. I've always written GROUP_CONCAT(CONCAT(x, y, z))

scorpio1441 Over a year ago

It does look like I was on the wrong path, but my only other option is to do a separate query to get assignees, which is not cost effective....

Darren Over a year ago

@Barmar - "In MySQL, you can get the concatenated values of expression combinations. ............... The default separator between values in a group is comma (“,”). To specify a separator explicitly, use SEPARATOR followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''." - Source :-)

|

Collectives™ on Stack Overflow

Create PHP array from MySQL's GROUP_CONCAT

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related