How to handle Tree structures returned from SQL query using PHP?

Question

This is a "theoretical" question.

I'm having trouble defining the question so please bear with me.

When you have several related tables in a database, for example a table that holds "users" and a table that holds "phones"

both "phones" and "users" have a column called "user_id"

select user_id,name,phone from users left outer join phones on phones.user_id = users.user_id;

the query will provide me with rows of all the users whether they have a phone or not.

If a user has several phones, his name will be returned in 2 rows as expected.

columns=>|user_id|name|phone|
row0 = > | 0 |fred|NULL|
row1 = > | 1 |paul|tlf1|
row2 = > | 1 |paul|tlf2|

the name "paul" in the case above is a necessary duplicate which in the RDMS's eye's is not a duplicate at all! It will then be handled by some server side scripting language, for example php.

How are these "necessary duplicates" actually handled in real websites or applications? as in, how are the row's "mapped" into some usable object model.

p.s. if you decide to post examples, post them for php,mysql,sqlite if possible.

edit:

Thank you for providing answers, each answer has interpreted the question differently and as such is different and correct in it's own way.

I have come to the conclusion that if round trips are expensive this will be the best way along with Jakob Nilsson-Ehle's solution, which was fitting for the theoretical question.

If round trips they are cheap, I will do separate selects for phones and users as 9000 suggests, if I need to show a single phone for every user, I will give a primary column to the phones and join it with the user select like Ollie Jones correctly suggests.

even though for real life applications I'm using 9000's answer, I think that for this unrealistic question Jakob Nilsson-Ehle's solution is most appropriate.

Answer 1

Your data model inherently allows a user to have 0, 1, or more phones.

You could get your database to return either 0 or 1 phone items for each user by employing a nasty hack, like choosing the numerically smallest phone number. (MIN(phone) ... GROUP BY user). But numerically comparing phone numbers makes very little sense.

Your problem of ambiguity (which of several phone numbers) points to a problem in your data model design. Take a look, if you will, at some common telephone-directory apps. A speed-dial app on a mobile phone is a good example. Mostly they offer ways to put in multiple phone numbers, but they always have the concept of a primary phone number.

If you add a column to your phone table indicating number priority, and make it part of your primary (unique) key, and declare that priority=1 means the user's primary number, your app will not have this ambiguous issue any more.

Answer 2

You can't easily get a tree structure from an RDBMS, only a table structure. And you want a tree: [(user1, (phone1, phone2)), (user2, (phone2, phone3))...]. You can optimize towards different goals, though.

• Round-trips are more expensive than sending extra info: go with your current solution. It fetches username multiple times, but you only have one round-trip per entire phone book. May make sense if your overburdened MySQL host is 1000 miles away.

• Sending extra info is more expensive than round-trips, or you want more clarity: as @martinho-fernandes suggests, only fetch user IDs with phones, then fetch user details in another query. I'd stick with this approach unless your entire user details is a short username. With SQLite I'd stick with it at all times just for the sake of clarity.

Answer 3

The thing I would probably do in this case in PHP would be to use the userId in a PHP array and then use that to continuosly update the users

A very simple example would be

$result = mysql_query('select user_id,name,phone from users left outer join phones on phones.user_id = users.user_id;');
$users = Array();
while($row = mysql_fetch_assoc($result)) {
   $uid =$row['user_id'];
   if(!array_key_exists($uid, $users)) {
      $users[$uid] = Array('name' => $row['name'], 'phones' => Array());
   }
   $users[$uid]['phones'][] = $row['phone']; 
}

Of course, depending on your programming style and the complexity of the user data, you might define a User class or something and populate the data, but that is fundamentally how I would would do it.

Answer 4

Sound's like you're confusing the object data model with the relational data model - Understanding how they differ in general, and in the specifics of your application is essential to writing OO code on top of a relational database.

Trivial ORM is not the solution.

There are ORM mapping technologies such as hibernate - however these do not scale well. IME, the best solution is using a factory pattern to manage the mapping properly.