Hi,
My problem:
I have a user class, a group class and a contact class.
User can belong to multiple groups and have multiple contacts.
In my user class I have a function $user->getAll();
This function should return a nested array of users (from the database) along with their groups and contact data.
Rather than doing a big SQL statement and then looping through the results to make an array, I would like to have a function in the group class $group->getAllForUser($user_id) and likewise for the contact class.
But let's say I want to order the results by group or by first_name (which is stored in the contact class), how would I go about doing this? Because the data is coming from multiple queries, so it's not like I can just put an 'ORDER BY' at the end of a query.
Would sorting the nested array by php be a big overhead?
Is it better to represent rowsets as an array of objects? Or just a nested array?
I have struggled with this problem for quite a while and have never come up with a good solution.
Any help or info on best practices would be much appreciated.
Cheers, Ben.
retrieving data from multiple classes
Moderator: General Moderators
- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida
Re: retrieving data from multiple classes
You should be able to do everything in MySQL Queries... if you only need the users info, only request it, and then sort in the Query, and everything else sounds like one MySQL Query could do it.
Re: retrieving data from multiple classes
I could not do it in one big query, because a user can have multiple groups. So the ouput would be something similar to:tecktalkcm0391 wrote:You should be able to do everything in MySQL Queries... if you only need the users info, only request it, and then sort in the Query, and everything else sounds like one MySQL Query could do it.

- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida
Re: retrieving data from multiple classes
You could re-structure you database, which it looks like it would be a good idea, to just have a list of group id's like:
1,3,6,8 and just explode() the groups into the array.
Unless you are doing this from multiple tables...
1,3,6,8 and just explode() the groups into the array.
Unless you are doing this from multiple tables...
Re: retrieving data from multiple classes
What is the view you are trying to create? what does "$group->getAllForUser($user_id)" supposed to retrieve from the database?
Generally speaking you should be able to do it in one query, it's best for performance and most reliable for sorting table data. You then could iterate over the data in PHP and group some information together in an inner array if needed
Generally speaking you should be able to do it in one query, it's best for performance and most reliable for sorting table data. You then could iterate over the data in PHP and group some information together in an inner array if needed
Re: retrieving data from multiple classes
What results exactly are these? It sounds like you're mixing ideas in your head. If you're getting data for one user, there's no sense in sorting it on his last name or whatever.benperth wrote: I want to order the results by group or by first_name.
You have one table whose records are in two many-to-many relationships with two other tables
I see two possibilities:
1. JOIN and live with the duplicated data from the first table.
2. Three queries.
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
Re: retrieving data from multiple classes
As others have indicated, this is something you can and should be doing in SQL. If you can't then you need to restructure your database so that you can. You can certainly "order by" from queries that involve many tables and many-to-many joins.
Post your db schema for more help.
Post your db schema for more help.