Php mysql join to subarray with field names

Question

I trying to join table using ONE query into sub array with column name => column value..

Short table(1) "users" structure with data:

user_id   email        ...
1         [email protected]    ...
2         [email protected]    ...

Short table(2) "users_permissions" structure with data:

user_id   plugin_enter  offers_view  ...
1         1             0             ... 
2         1             1             ...

If i use classic method - join left

SELECT `uperms`.*, `u`.*
FROM (`users` as u)
LEFT JOIN `users_permissions` as uperms ON `u`.`user_id` = `uperms`.`user_id`

I get classic output

[0] = array(
  'user_id' => 1,
  'email' => [email protected],
  'plugin_enter' => 1,
  'offers_view' => 0
),
[1] = array(
  'user_id' => 2,
  'email' => [email protected],
  'plugin_enter' => 1,
  'offers_view' => 1,
  ...
),

All i need is output into subarray as this:

[0] = array(
  'user_id' => 1,
  'email' => [email protected],
  'permissions => array(
      'plugin_enter' => 1,
      'offers_view' => 0
  ),
),
...

Is this possible to do with ONE query?

Table2 (permissions) contains about 60 columns. Is possible to CONCAT column's names with column value, if is joined to Table1 only one row?

Change your table2 to something like uid, name, value to avoid 60 colums and impossible to maintain queries. — Ôrel
– Ôrel, Commented May 23, 2017 at 7:27

Barmar · Accepted Answer · 2017-05-23 08:06:09Z

2

MySQL doesn't have arrays or nested structures, so it's not possible to do this in SQL.

Change your query so you give all the fields from users_permissions a consistent naming style. Then you can use a PHP loop to collect all the array elements whose keys match that pattern into the permissions array.

Query:

SELECT u.*, up.plugin_enter AS perm_plugin_enter, up.offers_view AS perm_offers_view, ...
FROM users AS u
JOIN users_permissions AS up ON u.user_id = up.user_id

PHP:

foreach ($all_results as &$row) {
    $permissions = array();
    foreach ($row as $key => $value) {
        if (strpos($key, 'perm_') === 0) {
            $permission[substr($key, 5)] = $value;
            unset($row[$key]);
        }
    }
    $row['permissions'] = $permissions;
}

You could do it by concatenating all the column names and values in the table:

SELECT u.*, CONCAT_WS(',', CONCAT('plugin_enter:', plugin_enter), CONCAT('offers_view:', offers_view), ...) AS permissions
FROM users AS u
JOIN users_permissions AS up ON u.user_id = up.user_id

Then your PHP code can use explode() to split $row['permissions'] into array of name:value pairs, and then convert those to key=>value in the PHP array.

Another solution is to redesign your users_permissions table:

user_id permission_type value
1       plugin_enter    1
1       offers_view     0
...
2       plugin_enter    1
2       offers_view     1
...

Then you can query:

SELECT u.*, GROUP_CONCAT(permission_type, ':', value) AS permission
FROM users AS u
JOIN users_permissions AS up on u.user_id = up.user_id

edited May 23, 2017 at 8:06

answered May 23, 2017 at 7:32

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

6 Comments

drozdo Over a year ago

Thank for opinion, but permissions table has not all columns with same "ending"

Barmar Over a year ago

OK, I show another way to do it that doesn't depend on the columns following a particular naming scheme. It assigns aliases in the query.

Barmar Over a year ago

There's nothing that will do it automatically. You could write a simple script to create the query from the DDL.

Barmar Over a year ago

I don't understand your question. Is it about my answer or is it about the answer you found in the other question?

drozdo Over a year ago

sorry, not about your answer .. i posted new possible solution of main question.

|

drozdo · Accepted Answer · 2017-05-23 08:59:55Z

0

Another possible sollution is to add prefixes to query.
Inspired by post: https://stackoverflow.com/a/9926134/2795923

SELECT `u`.*, ':prefix_start:', `uperms`.*, ':prefix_end:'
FROM (`users` as u)
LEFT JOIN `users_permissions` as uperms ON `u`.`user_id` = `uperms`.`user_id`

Output array looks like this:

[0] => array(
   'user_id' => 1
   'email' => [email protected],
   'prefix_start' => 
   'plugin_enter' => 1,
   'offers_view' => 0
   'prefix_end' => 
)
...

Then easy PHP script to add all array data between prefix_start and prefix_end into own subarray.

answered May 23, 2017 at 8:59

drozdo

5155 silver badges12 bronze badges

Collectives™ on Stack Overflow

Php mysql join to subarray with field names

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related