PHP/MySQL - Best Way To Query Multiple Recordsets Across Multiple Recordsets

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

FROM users
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!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Please post the CREATE TABLE statements and a set of test data to load the tables.  Then show us the results you want to get from the queries.  Once we have your SSCCE we can give you tested and working code samples.
MurfurFull Stack DeveloperCommented:
Actually, it is not as hard as you might think - I had to do just this a few years ago for Region/Country relationship for the company's global address book.

In my case the region and country were both columns in the same table but it can be done either way. Here's a simple example of relating two tables of data (food groups and flavours) that I did for another EE question eons ago and I tidied up the display by only showing the food group once:

MurfurFull Stack DeveloperCommented:
Thanks Ray. Deffo look better that way...

Also, AtHigh, see here for a working demo:
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

athighAuthor Commented:
Murfur, thank you for your response.  That is one way I had thought about doing it, but my fear was that repeatedly executing the SQL query in the PHP loop would put extra load on the server.  What do you think?
MurfurFull Stack DeveloperCommented:
You misunderstand - this way the SQL is only executed once (unless you reload the page!)
Because of the JOIN in the query, the results (behind the scenes) actually come out as

Fruit            Apple
Fruit            Lemon
Fruit            Pear
Meat            Beef
Meat            Lamb
Meat            Pork
Vegetable      Carrot
Vegetable      Tomato

but the code does not display the group name more than once until the group name changes.

in your case the results of the query would be something like this:

Friend      Child
John      Helen
John      James
Steve      Dan
Steve      Alice
Steve      Jenny

but what you would see on the page is this:

Friend      Child
John      Helen
Steve      Dan
athighAuthor Commented:
Ok, I'm following you, but I'm not sure how I would be able to use it in this situation.  

That said, now that I'm back at the office, I can post some of the SQL code.  We are working with people, studies, reports, and equipment.  Basically, one person has multiple studies and each study has multiple devices and multiple reports.  So it is a one to many to many relationship.  The webpage is supposed to provide a profile of the person, and a study history listing each study and all of the additional relevant information.  

In the code I'm providing, I was toying with the idea of using a single SELECT query with a lot of GROUP_CONCAT statements to get the details of each study as they relate to the person and each report and equipment as they relate to the study. I would then explode those into a PHP array and try to match everything up later in the PHP code, but I feel like that is a really clumsy way to do it.  The other option was to execute a query on the equipment table for each study and a query on the report table for each study, but that seems like it would waste processing power.

Murfur, I like the idea that you have proposed, but I'm not sure how to use it here.  Ideas?
MurfurFull Stack DeveloperCommented:
Given that you have many to many, I imagine that the user would struggle to view all of the result data at the same time and crunching that much data every time the page loads would also make the query and the page download slower - not good.

I would be inclined to look at breaking it out into separate queries so that the user gets an overview of people and the studies they have done. Then if they want to drill down to more data on a particular study they click on it. This way you will still get a decent response time as the query will be considerably smaller than trying to get all of the data and the user will be less miffed than having to wait for a slow page that then makes them do lots of scrolling. Besides, it's a terrible waste of processing power to load 50 people, hundreds of studies and thousands of reports given that the user can only look at one report at a time.

1. do the initial person/studies in a similar vein to my demo
2. put the study/reports query into AJAX so that the visitor only calls up the reports for the study they actually clicked on.

Depending on how you want to display the profile, you could make it a little more user friendly by adding counters to the output

person study1 (2)
person study2 (5)
person study3  (3)

and then the next query effectively gives you

study2 report
study2 report
study2 report
study2 report
study2 report

but of course you would present it in a MUCH nicer way! :) and because a picture paints a thousand words, here you go, albeit a picture made of text!


Study 1 Name (2 reports)
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua... [more]

Study 2 Name (5 reports)
Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo...[more]

which then leads to


Report 1 Title
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo...

Report 2 Title
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
athighAuthor Commented:
Yeah, I figured that was going to be the best way to go, and that is what we've been doing thus far.  Just hoping there would be a clever workaround.
MurfurFull Stack DeveloperCommented:
Great minds think alike... That IS the clever workaround ;)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.