php - Looking for advice/best practice on building objects from query results -

im going try best explain myself. not 100% sure how word this. have data in multiple tables like

tbl_rooms tbl_tables tbl_people_sitting_at_table 

all these tables have there linking id's , want build "room" object , not query db in loops. right have pseudocode

foreach ($rooms $room) {     $room->tables = get_all_the_table_by_room_id($room->room_id);  ..... foreach ($room->tables $table) {     $table->people_sitting_at_table = get_all_the_people_by_table_id($table->table_id); 

what have work don't amount of queries required build object. heres example looking send back

 stdclass object ( [id] => 15 [room_name] => room_name [tables] => array     (          [0] => stdclass object              (                  [id] => 34                  [table_name] => table_name                  [people_sitting_at_table] => array                      (                          [0] => stdclass object                              (                                  [id] => 45                                  [name] => name 

what im having hard time knowing how organize if data front.. if tables in room , people @ tables how people nested under right table efficiently not sure if enough info point across, if not let me know here sql exaple

set sql_mode="no_auto_value_on_zero";   set time_zone = "+00:00";    create table `tbl_people_sitting_at_table` (     `id` int(11) not null auto_increment,     `name` varchar(100) not null,     `table_id` int(11) not null,     primary key (`id`) ) engine=innodb  default charset=latin1 auto_increment=3 ;  insert `tbl_people_sitting_at_table` (`id`, `name`, `table_id`) values (1, 'jim', 1), (2, 'bob', 1);  create table `tbl_rooms` (   `id` int(11) not null auto_increment,   `name` varchar(100) not null,   primary key (`id`) ) engine=innodb  default charset=latin1 auto_increment=3 ;  insert `tbl_rooms` (`id`, `name`) values (1, 'room one'), (2, 'room two');  create table `tbl_tables` (   `id` int(11) not null auto_increment,   `name` varchar(100) not null,   `room_id` int(11) not null,   primary key (`id`) ) engine=innodb  default charset=latin1 auto_increment=3 ;  insert `tbl_tables` (`id`, `name`, `room_id`) values (1, 'table one', 1), (2, 'table two', 1); 

i think looking called model-mapper design pattern.

your model simple class stores columns in database. create 1 model instance per row. mapper actual sql queries, loads each row new model instance. instance, have room model , room mapper. have personroom model , personroom mapper. personroom mapper let select personrooms have given room id.

one thing can @ doctrine. alternatively, can write yourself. wrote code generator can point code generator @ table , write model/mapper code me. can use models , mappers.