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
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
This post has been edited by guyfromri: 21 December 2014 - 10:59 PM