Just to learn SQL I wanted to make a hierarchy with a simple parent - child. Like the stack overflow badges (Parent: Question Badges, Child: Altruist).
This is my sql:
SELECT * FROM (`badge_types`) LEFT JOIN `badges` ON `badges`.`badge_type` = `badge_types`.`badge_type_id`
And here is what I get:
( [0] => stdClass Object ( [badge_type_id] => 2 [badge_type_title] => Participation Badges [badge_type_description] => Badges earning by participating in various areas of the site. [badge_type_order] => 2 [badge_id] => 1 [badge_name] => Autobiographer [badge_level] => 3 [badge_requirement] => Completed all user profile fields [badge_type] => 2 [badge_order] => 1 [badge_sites] => 0 ) [1] => stdClass Object ( [badge_type_id] => 1 [badge_type_title] => Experience Badges [badge_type_description] => Badges earned by amount of experience gain throughout the site. [badge_type_order] => 1 [badge_id] => 2 [badge_name] => Apprentice [badge_level] => 3 [badge_requirement] => Achieved 500 experience [badge_type] => 1 [badge_order] => 1 [badge_sites] => 0 ) )
How can I turn this into:
array( array( [badge_type_id] => 2 [badge_type_title] => Participation Badges [badge_type_description] => Badges earning by participating in various areas of the site. [badge_type_order] => 2 [badges] => array( array( [badge_id] => 1 [badge_name] => Autobiographer [badge_level] => 3 [badge_requirement] => Completed all user profile fields [badge_type] => 2 [badge_order] => 1 [badge_sites] => 0 ), array( [badge_id] => 2 [badge_name] => Example 2 [badge_level] => 3 [badge_requirement] => blah bla [badge_type] => 2 [badge_order] => 1 [badge_sites] => 0 ) ) ), array( [badge_type_id] => 1 [badge_type_title] => Experience Badges [badge_type_description] => Badges earned by amount of experience gain throughout the site. [badge_type_order] => 1 [badges] => array( array( [badge_id] => 2 [badge_name] => Apprentice [badge_level] => 3 [badge_requirement] => Achieved 500 experience [badge_type] => 1 [badge_order] => 1 [badge_sites] => 0 ), array( [badge_id] => 2 [badge_name] => Example 2 [badge_level] => 3 [badge_requirement] => Achieved 1000 experience [badge_type] => 1 [badge_order] => 1 [badge_sites] => 0 ) ) ) )
I can do it with multiple MySQL queries but ideally I just want to use one query if its possible?