PHP - Parsing an array with multiple parents and combining children

Question

I've tried googling to figure this out but to no avail. Here's my problem...

I have a query that I run to pull data from 4 or 5 tables. It works great but if there are multiple children then the parent is returned as part of the array with multiple sub-arrays. I want to parse the array and retrieve the relevant data but I'm not sure of the best way to go about it. I know PHP has a strong subset of predefined functions that handle arrays and MySQL so I wasn't sure if there was something I was just missing. Also, I'm using PDO in case it makes a difference.

Here's my example. I have the following tables

Parent (locations) Address (addresses) Phone (phones) Geographic Data (geodata)

When I pull the data there should only be one line from each table except for phones. There could be 2 or 3 phone numbers listed. All great except my query then has the parent data with associated child data listed multiple times for each phone line. When I get the array, I've thought of two ways to handle it...but I'm not sure.

1) Loop the array and compare the current key to the previous key. If different, close old div, print new. If not, add new child to existing div. This seems like overkill for such a simple task and it seems a bit primitive considering the robust functions available for other things.

2) I've played with array_map and array_unique a little. With a combination of the two, I can get the unique keys out. The problem here is once I have the unique values, I'm not really sure how to grab the corresponding children and create a new array with children grouped under parent correctly.

Is there something I'm missing? I don't have any PHP to post but I am going to post my query just in case there is something I can do there to help as well. Also, I'm not looking for anyone to give me the code here - just a nudge in the right direction would be great.

Thanks in advance!!

SELECT 04_rest_00_locations.Name, 
04_rest_00_locations.Description, 
04_rest_00_locations.RNID,
04_rest_01_addresses.Add1,
04_rest_01_addresses.Add2,
02_driver_geodata.primary_city,
02_driver_geodata.state,
02_driver_geodata.zip,
04_rest_01_phones.AreaCode,
04_rest_01_phones.Prefix,
04_rest_01_phones.LineNum
FROM 04_rest_00_locations 
LEFT JOIN 04_rest_01_addresses
ON 04_rest_00_locations.id = 04_rest_01_addresses.RestID
AND GroupID 
IN (SELECT 04_rest_00_locations.GroupID 
    FROM 04_rest_00_locations 
    WHERE RestID = $RestID)
LEFT JOIN 04_rest_01_phones
ON 04_rest_00_locations.id = 04_rest_01_phones.RestID
AND 04_rest_01_phones.active = 1
AND 04_rest_01_phones.IsPublic = 1
AND 04_rest_01_phones.PhoneType = 1
LEFT JOIN 02_driver_geodata
ON 04_rest_01_addresses.CSZID = 02_driver_geodata.id
AND 04_rest_01_addresses.active = 1
AND 04_rest_01_addresses.AddType = 1
WHERE 04_rest_00_locations.active = 1
AND 04_rest_00_locations.Published = 1

Update - I've changed my query to the following it works great (thank you) but it only returns one row. Did I miss something? Thanks again!!

SELECT 04_rest_00_locations.Name, 
04_rest_00_locations.Description,
04_rest_00_locations.RNID,
04_rest_01_addresses.Add1, 
04_rest_01_addresses.Add2,
02_driver_geodata.primary_city, 
02_driver_geodata.state, 
02_driver_geodata.zip, 
GROUP_CONCAT(04_rest_01_phones.PhoneType, ";",  04_rest_01_phones.AreaCode, 04_rest_01_phones.Prefix, 04_rest_01_phones.LineNum) AS Phones
    FROM 04_rest_00_locations 
    LEFT JOIN 04_rest_01_addresses
    ON 04_rest_00_locations.id = 04_rest_01_addresses.RestID
    AND GroupID 
    IN (SELECT 04_rest_00_locations.GroupID 
        FROM 04_rest_00_locations 
        WHERE RestID = 1)
    LEFT JOIN 04_rest_01_phones
    ON 04_rest_00_locations.id = 04_rest_01_phones.RestID
    AND 04_rest_01_phones.active = 1
    AND 04_rest_01_phones.IsPublic = 1
    LEFT JOIN 02_driver_geodata
    ON 04_rest_01_addresses.CSZID = 02_driver_geodata.id
    AND 04_rest_01_addresses.active = 1
    AND 04_rest_01_addresses.AddType = 1
    WHERE 04_rest_00_locations.active = 1
    AND 04_rest_00_locations.Published = 1

You should do this in MySQL. By grouping the telephone numbers into one string. This should clarify: http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field — Mouser, Dec 22 '14 at 16:27
if I were you I would consider looking into using an alias for your table names. that query is VERY difficult to read. Also, why are you putting conditions from a different table on a left join? aka `AND 04_rest_01_addresses.active = 1` — John Ruddell, Dec 22 '14 at 16:28
to what @Mouser was saying. you can `GROUP_CONCAT()` the phone number and then if you want it to be in an array on the php side you can just do `explode(',', $phoneNumberString);` — John Ruddell, Dec 22 '14 at 16:30
Thanks guys. I thought about aliasing but I just wrote this up quick to make it work. Probably something I'll end up doing. I added the active condition there because those two tables have data in common and I thought it would be the best place. Is it something you think I should move? It works really well as far as pulling the appropriate data I need. — b3tac0d3, Dec 22 '14 at 16:34
@b3tac0d3 yes I think you should move those conditions to the WHERE since you are duplicating them over multiple joins. if it was specific for one then that would make sense :) — John Ruddell, Dec 22 '14 at 16:41
You need a `GROUP BY` clause to go with your `GROUP_CONCAT()`. — O. Jones, Dec 22 '14 at 16:53

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Sorry! Silly me - I found my answer. Here's the updated query - you have to group the data together to make it work.

SELECT loc.Name, loc.Description, loc.RNID, addr.Add1, addr.Add2, geo.primary_city, geo.state, geo.zip, GROUP_CONCAT(ph.PhoneType, ";",  ph.AreaCode, ph.Prefix, ph.LineNum) AS Phones
FROM 04_rest_00_locations loc 
LEFT JOIN 04_rest_01_addresses addr
    ON loc.id = addr.RestID
    AND GroupID IN 
    (   SELECT loc.GroupID 
        FROM loc 
        WHERE RestID = 1
    )
LEFT JOIN 04_rest_01_phones ph
    ON loc.id = ph.RestID
    AND ph.active = 1
    AND ph.IsPublic = 1
LEFT JOIN 02_driver_geodata geo
    ON addr.CSZID = geo.id
    AND addr.active = 1
    AND addr.AddType = 1
WHERE loc.active = 1
AND loc.Published = 1
GROUP BY loc.id

Here are some links I found that really helped with this. Thanks again for the help guys!

GROUP_CONCAT explained, Grouping and why it's important :)

wow,... I was thinking you had multiple phone numbers not separated phone number.. my bad. if there was some data I woulda recommended that right away haha good catch — John Ruddell, Dec 22 '14 at 17:00
It was both - multiple phone numbers that were each separated in to parts but this worked perfect and I got to learn something. I'm still curious what PHP has to offer here but this is a great solution. Thanks again — b3tac0d3, Dec 22 '14 at 17:18

PHP - Parsing an array with multiple parents and combining children

1 Answers1