You could do something like this to iterate over the rows to build a multi dimensional array.
[php]// select spesific column instead of all (*), choose a name for the joined id column so we get both the monster id and the trait id instead of just one
$query = ‘SELECT monsters.id, monsters.name, monster_traits.id as trait_id, monster_traits.trait_name, monster_traits.description
FROM monsters
LEFT JOIN monster_traits ON monsters.id = monster_traits.monster_id
GROUP BY monsters.id
ORDER BY monsters.name ASC LIMIT 3’
$stmt = $db->prepare($query);
$stmt->execute();
$monsters = array();
while($row = $stmt->fetch()) { // each row is a monster + a trait
if(!isset($monsters[$row->id])) {
// we haven’t added this monster yet, let’s add the basic data
$monsters[$row->id] = array(
‘id’ => $row->id,
‘name’ => $monster->name,
‘traits’ => array()
);
}
// add the row trait data to a new array element in the current monsters traits
$monsters[$row->id][‘traits’][] = array(
‘id’ => $row->trait_id,
‘name’ => $row->trait_name,
‘description’ => $row->description
);
}
echo ‘
’;
print_r($monsters);
echo ‘
’;[/php]
[hr]
Then you can/should refactor the code so your controller code is as tidy as possible. So instead of all of the above you just need to do something like $monstersService->findLatestMonstersWithTraits(), and that service handles the messy code.
[hr]
You could alternatively use an ORM, like Doctrine2, Active Record or others. These support fetching child entities in an easier manner. In Doctrine2 the above code could look like this:
[php]// doctrine query language, here we reference the entity classes instead of the database tables
$dql = ‘SELECT m, t
FROM Monster m
JOIN m.traits t
GROUP BY m.id
ORDER BY m.name ASC LIMIT 3’;
// em, or entity manager is the class you use in Doctrine2 to pass entities (objects) to and from the database
$query = $em->createQuery();
$monsters = $query->getResult();[/php]
If the entities (php classes reflecting each table in your db - not counting join tables) are correctly set up with relations, this should yield the same result as the code above, without us having to manually create the multi dimensional array. Since the relations are in the entities we do not need to reference those here, we can simply say we want the monsters traits (m.traits), and each monster will know which traits belong to it.
Doctrine2 is used by the Symfony framework + others, I highly suggest having a look. It
[ul][li]supports multiple databases[/li]
[li]generates and updates the db scheme for you (join tables and all)[/li]
[li]allows you to lazy/eager load associated entites without any fuzz[/li]
[li]has lifecycle events for entities (hash password before create/update, etc)[/li]
[li]has a query builder to build queries instead of writing sql/dql[/li]
[li]has built in support for caching “compiled” entities, sql queries, query results to various cache stores[/li]
[li]etc[/li][/ul]
).