Hello All, I've got a question for the MySQL/PHP experts. Basically, I'm wondering what is the best way to pull multiple recordsets from multiple recordsets across multiple tables.
Say you have 1 user that has friends and each of those friends have children.
I'm trying to figure out the most efficient way to generate an array/list of each of the friends and a subsequent array/list of children from each of the friends.
One way is a SELECT query using with GROUP_CONCAT/CONCAT and then exploding the results into PHP arrays and trying to match up the results using foreach loops.
GROUP_CONCAT (DISTINCT CONCAT(children.kp_id,'|',childen.firstname,'|', children.lastname)ORDER BY children.age DESC SEPARATOR ';') AS children,
GROUP_CONCAT(DISTINCT CONCAT(friends.kp_id,'|',friends.firstname,'|', friends.lastname) ORDER BY friends.kp_id ASC SEPARATOR ';') AS friends
LEFT JOIN friends ON users.kp_id =friends.kf_userid
LEFT JOIN children ON friends.kp_id = children.kf_parentid
WHERE users.kp_id = $userid
GROUP BY users.kp_id, friends.kp_id
This is where I would explode the friends group into an array, and then use a foreach loop to explode the each array element into a multi-dimensional array containing the friend details. I would then have to do the same thing with the children group, and then later in the code have to match up each child record with each parent/friend record.
Another option would be to perform a query on the friends table to get the friend details and then loop through the results using PHP and query the children table for friend .
Neither option seems very efficient, and I was wondering if there is a better way to achieve these results.
Thanks for any advice!