2 Replies - 233 Views - Last Post: 23 December 2014 - 02:07 AM Rate Topic: -----

#1 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 831
  • Joined: 16-September 09

Parsing an array with multiple parents and combining children

Posted 21 December 2014 - 10:53 PM

Hey Guys - I asked a similar question in the database forum a few weeks ago and made some really good headway but not I'm stuck at a slightly different point. 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


This post has been edited by guyfromri: 21 December 2014 - 10:59 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Parsing an array with multiple parents and combining children

#2 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 831
  • Joined: 16-September 09

Re: Parsing an array with multiple parents and combining children

Posted 22 December 2014 - 09:58 AM

Answer

So I'm sure there's a php way to do this but I learned a whole different and much more usable approach while trying to figure this out. It's mysql.

GROUP_CONCAT, add a few delimiters to make it parsable by php and alias the strings. It works really well. Also noted, the grouping at the bottom is extremely important.

Hope this helps someone else :)

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
GROUP BY 04_rest_00_locations.id



Some links I found on this

GROUP_CONCAT explained
Why to use grouping
Was This Post Helpful? 0
  • +
  • -

#3 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 39
  • View blog
  • Posts: 898
  • Joined: 06-July 13

Re: Parsing an array with multiple parents and combining children

Posted 23 December 2014 - 02:07 AM

This query is still fairly hard to read. You should consider abbreviating table names (especially when they're as long and common as they are), for example:

SELECT t.column1, t.column3, t2.column4 FROM table AS t INNER JOIN table2 AS t2 ON t.column3 = t2.column5 WHERE t.column1 IS NOT NULL

This post has been edited by chris98: 23 December 2014 - 02:09 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1