retrieving data from multiple classes

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
benperth
Forum Newbie
Posts: 2
Joined: Thu Jul 03, 2008 10:06 pm

retrieving data from multiple classes

Post by benperth »

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.
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Re: retrieving data from multiple classes

Post by tecktalkcm0391 »

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.
benperth
Forum Newbie
Posts: 2
Joined: Thu Jul 03, 2008 10:06 pm

Re: retrieving data from multiple classes

Post by benperth »

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.
I could not do it in one big query, because a user can have multiple groups. So the ouput would be something similar to:
Image
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Re: retrieving data from multiple classes

Post by tecktalkcm0391 »

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...
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: retrieving data from multiple classes

Post by Eran »

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
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: retrieving data from multiple classes

Post by Mordred »

benperth wrote: I want to order the results by group or by first_name.
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.

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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: retrieving data from multiple classes

Post by Ollie Saunders »

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 Reply