Dima Dz Dima Dz - 6 months ago 24
SQL Question

Convert result of mysql query into nested php array

I have a Mysql query:

SELECT
`pcm`.`ContactMechanismId` AS `ContactMechanismId`,
`tel`.`AreaCode` AS `AreaCode`,
`tel`.`PhoneNbr` AS `PhoneNbr`,
`tel`.`Extension` AS `Extension`,
`cmt`.`ContactMechanismTypeName` AS `ContactMechanismTypeName`,
`c`.`DialingCode` AS `DialingCode`,
`rt`.`RoleTypeName` AS `RoleTypeName`,
`cmpt`.`ContactMechanismPurposeTypeName` AS `ContactMechanismPurposeTypeName`
FROM
`PartyContactMechanisms` AS `pcm`
INNER JOIN
`Telephones` AS `tel` ON `pcm`.`ContactMechanismId` = `tel`.`ContactMechanismId`
INNER JOIN
`ContactMechanisms` AS `cm` ON `cm`.`ContactMechanismId` = `pcm`.`ContactMechanismId`
INNER JOIN
`ContactMechanismTypes` AS `cmt` ON `cmt`.`ContactMechanismTypeId` = `cm`.`ContactMechanismTypeId`
INNER JOIN
`Countries` AS `c` ON `tel`.`GeoId` = `c`.`GeoId`
INNER JOIN
`PartyContactMechanismPurposes` AS `pcmp` ON `pcm`.`ContactMechanismId` = `pcmp`.`ContactMechanismId`
INNER JOIN
`RoleTypes` AS `rt` ON `rt`.`RoleTypeId` = `pcm`.`RoleTypeId`
INNER JOIN
`ContactMechanismPurposeTypes` AS `cmpt` ON `pcmp`.`PurposeTypeId` = `cmpt`.`PurposeTypeId`


The result I get from Mysql is

+--------------------+----------+----------+-----------+--------------------------+-------------+---------------+---------------------------------+
| ContactMechanismId | AreaCode | PhoneNbr | Extension | ContactMechanismTypeName | DialingCode | RoleTypeName | ContactMechanismPurposeTypeName |
+--------------------+----------+----------+-----------+--------------------------+-------------+---------------+---------------------------------+
| 2 | 111 | 22222222 | 2222 | landline | 375 | customer | common phone |
| 2 | 111 | 22222222 | 2222 | landline | 375 | contractor | common phone |
| 2 | 111 | 22222222 | 2222 | landline | 375 | manufacturer | common phone |
| 2 | 111 | 22222222 | 2222 | landline | 375 | customer | primary phone |
| 2 | 111 | 22222222 | 2222 | landline | 375 | contractor | primary phone |
| 2 | 111 | 22222222 | 2222 | landline | 375 | manufacturer | primary phone |
| 2 | 111 | 22222222 | 2222 | landline | 375 | customer | secretary phone |
| 2 | 111 | 22222222 | 2222 | landline | 375 | contractor | secretary phone |
| 2 | 111 | 22222222 | 2222 | landline | 375 | manufacturer | secretary phone |
| 1 | 17 | 2905950 | | landline | 375 | customer | other phone |
+--------------------+----------+----------+-----------+--------------------------+-------------+---------------+---------------------------------+


I need is to convert the above result into the following array:

[
[ContactMechanismId, AreaCode, PhoneNbr, Extension, ContactMechanismTypeName, DialingCode, RoleTypeName[], ContactMechanismPurposeTypeName[]
]


In the arrays
RoleTypeName[]
and
ContactMechanismPurposeTypeName[]
go ALL possible values for a certain
ContactMechanismId
from the corresponding column of the mysql result.

How can I do this?

Answer

You can change the select statement like bellow using GROUP_CONCAT()

SELECT 
`pcm`.`ContactMechanismId` AS `ContactMechanismId`,
`tel`.`AreaCode` AS `AreaCode`,
`tel`.`PhoneNbr` AS `PhoneNbr`,
`tel`.`Extension` AS `Extension`,
`cmt`.`ContactMechanismTypeName` AS `ContactMechanismTypeName`,
`c`.`DialingCode` AS `DialingCode`,GROUP_CONCAT(
`rt`.`RoleTypeName`) AS `RoleTypeName`,GROUP_CONCAT(
`cmpt`.`ContactMechanismPurposeTypeName`) AS `ContactMechanismPurposeTypeName`

Then group the data using GROUP BY,You will get RoleTypeName and ContactMechanismPurposeTypeName as a comma seperated value.You can iterate using php loop and using explode() function to form the array

Comments