Возвращает массив массивов массивов объектов

Вот код, который извлекает данные из каждой таблицы в отдельные массивы, а затем строит из нее окончательную структуру данных.

Он довольно общий, так как вам нужно только указать, какие поля являются общими ключами (первичные и внешние ключи, которые, как предполагается, имеют одинаковое имя) для каждой пары таблиц:

 function loadTable($dbh, $table) { // Perform simple table select, and return result set $sth = $dbh->prepare("SELECT * FROM $table"); $sth->execute(); $rows = $sth->fetchAll(PDO::FETCH_ASSOC); return $rows; } function connectChildren($parents, $children, $name, $common_keys) { /* Returns $parents array, but with each element extended with * a $name key, which is an array of matching $children elements. * The match is made by comparing the values for each of the $common_keys * in both arrays. * When a $children element is added to the $name array, its $common_keys * are removed from it as they are already known in the $parents element. * (this removal behaviour is optional and could be left out) */ $index = []; // Build a temporary index to associate $parents elements by their // primary key value (can be composite) foreach ($parents as $i => $parent) { $primary_key = []; foreach ($common_keys as $common_key) { $primary_key[] = $parent[$common_key]; } $index[implode("|", $primary_key)] = $i; $parents[$i][$name] = []; } // Main algorithm: inject $children into $parents foreach($children as $child) { $foreign_key = []; // Collect foreign key value foreach ($common_keys as $common_key) { $foreign_key[] = $child[$common_key]; // Remove foreign key from child unset($child[$common_key]); } // Find the corresponding $parents element via the index $i = $index[implode("|", $foreign_key)]; $parents[$i][$name][] = $child; } return $parents; } // Step 1: load all the table data $rows_dep = loadTable($dbh, "departments"); $rows_job = loadTable($dbh, "jobs"); $rows_peep = loadTable($dbh, "peeps"); // Step 2: connect the data, layer by layer, in bottom-up order: $rows_job = connectChildren($rows_job, $rows_peep, "peeps", ["deperatment_id", "job_id"]); $rows_dep = connectChildren($rows_dep, $rows_job, "jobs", ["deperatment_id"]); print_r ($rows_dep); с function loadTable($dbh, $table) { // Perform simple table select, and return result set $sth = $dbh->prepare("SELECT * FROM $table"); $sth->execute(); $rows = $sth->fetchAll(PDO::FETCH_ASSOC); return $rows; } function connectChildren($parents, $children, $name, $common_keys) { /* Returns $parents array, but with each element extended with * a $name key, which is an array of matching $children elements. * The match is made by comparing the values for each of the $common_keys * in both arrays. * When a $children element is added to the $name array, its $common_keys * are removed from it as they are already known in the $parents element. * (this removal behaviour is optional and could be left out) */ $index = []; // Build a temporary index to associate $parents elements by their // primary key value (can be composite) foreach ($parents as $i => $parent) { $primary_key = []; foreach ($common_keys as $common_key) { $primary_key[] = $parent[$common_key]; } $index[implode("|", $primary_key)] = $i; $parents[$i][$name] = []; } // Main algorithm: inject $children into $parents foreach($children as $child) { $foreign_key = []; // Collect foreign key value foreach ($common_keys as $common_key) { $foreign_key[] = $child[$common_key]; // Remove foreign key from child unset($child[$common_key]); } // Find the corresponding $parents element via the index $i = $index[implode("|", $foreign_key)]; $parents[$i][$name][] = $child; } return $parents; } // Step 1: load all the table data $rows_dep = loadTable($dbh, "departments"); $rows_job = loadTable($dbh, "jobs"); $rows_peep = loadTable($dbh, "peeps"); // Step 2: connect the data, layer by layer, in bottom-up order: $rows_job = connectChildren($rows_job, $rows_peep, "peeps", ["deperatment_id", "job_id"]); $rows_dep = connectChildren($rows_dep, $rows_job, "jobs", ["deperatment_id"]); print_r ($rows_dep); 

Выходные данные:

 Array ( [0] => Array ( [deperatment_id] => 1 [department_name] => HR [jobs] => Array ( [0] => Array ( [job_id] => 1 [job_name] => Idiot [peeps] => Array ( [0] => Array ( [peep_name] => Smith ) ) ) [1] => Array ( [job_id] => 2 [job_name] => Fool [peeps] => Array ( [0] => Array ( [peep_name] => Jones Major ) [1] => Array ( [peep_name] => Jones Minor ) ) ) ) ) [1] => Array ( [deperatment_id] => 2 [department_name] => Software [jobs] => Array ( [0] => Array ( [job_id] => 4 [job_name] => Software guru [peeps] => Array ( [0] => Array ( [peep_name] => Mr. In-the-wrong-department ) [1] => Array ( [peep_name] => Mawg ) ) ) [1] => Array ( [job_id] => 5 [job_name] => PFY [peeps] => Array ( [0] => Array ( [peep_name] => William Topaz McGonagall ) ) ) ) ) [2] => Array ( [deperatment_id] => 3 [department_name] => Accounts [jobs] => Array ( [0] => Array ( [job_id] => 6 [job_name] => Number cruncher [peeps] => Array ( [0] => Array ( [peep_name] => Blaise Pascal ) [1] => Array ( [peep_name] => Isaac Newton ) ) ) ) ) ) 

Затем вы можете продолжить работу с json_encode($departments) и т. Д.

Если вы хотите вывести древовидную структуру, вам не нужно использовать циклы. Вы можете просто сделать следующий запрос:

 SELECT p.`peep_name`, j.* FROM `peeps` p INNER JOIN `jobs` j ON j.job_id = p.job_id 

который вернет структуру, такую ​​как:

 peep_name deperatment_id job_id job_name Smith 1 1 Idiot Jones Major 1 2 Fool Jones Minor 1 2 Fool Mr. In-the-wrong-department 2 4 Software guru Mawg 2 4 Software guru William Topaz McGonagall 2 5 PFY Blaise Pascal 3 6 Number cruncher Isaac Newton 3 6 Number cruncher 

Затем вы сможете вывести эти данные в этом формате или объединить эти данные в древовидную структуру желаемого формата. Например, вы можете вывести его в следующем формате:

 { "departments": [ { "ID": 1, "jobs": [ { "ID": 1, "Name": "Idiot" "Peeps": [ "Smith" ] }, { "ID": 2, "Name": "Fool" "Peeps": [ "Jones Major", "Jones Minor" ] } ] }, // etc. ] }