Nested SQL query, optimization solutions?

Question

PHP

Good time of the day.
I'm working on a small php project, separating code and design, I came up with the following:

<? foreach($categories as $cat): ?>
  <li><p class="categories"><?=$cat['title']?></p>
    <foreach(getSubCat($cat['cat_id']) as $subcat): ?>
      <p class="subcats"><?=$subcat['title']?></p>
    <? endforeach ?>
  </li>
<? endforeach ?>

The meaning of the above template is that the first array of categories is formed in the script, and when outputting, we simply work with the variable, but here is the second ... its content is different for each category, and we get it with the getSubCat function with a parameter in the form of the current category (and for we select it under categories), in the function a regular sql query occurs and an array with the results is returned.
And the trouble is that the same dull function request will have to be executed N number of times (depending on the number of subcategories). There is an idea to store the category ID (which is passed to the function) in the user's session, but how to store the result of the function's work - in the global array?
There must be some other solutions that I haven’t thought about yet, smarty and other templating solutions can only be considered as an example of code, the task is to manage native php without introducing them into the project, transferring the output to a script variable is also not a solution - the harmony of code and design is broken.

Answer 1

SELECT `cat`.`id` AS `catId`, `cat`.`title` AS `catTitle`, `subcat`.`title` AS `subcatTitle` 
    FROM `categories` AS `cat` 
        LEFT JOIN `subcategories` AS `subcat` ON `cat`.`id` = `subcat`.`cat_id` 
    ORDER BY `cat`.`id`

<?php
$cat = -1;
foreach ($result as $row) {
    if ($row['catId'] != $cat) {
        echo ($cat == -1 ? "" : "<\li>")."<li><p class='categories'>{$row['catTitle']}</p>";
        $cat = $row['catId'];
    }
    echo "<p class='subcats'>{$row['subcatTitle']}</p>";
}
if ($cat != -1)
    echo "</li>";
?>

Answer 2

keep comrade
phpforum.ru/txt/index.php/t56444.html
www.sideralis.org/baobab

Answer 3

Mike77 , thanks for the theory, but indeed SELECT with union can achieve the same results "on the fly" (and I didn't read the union options before and in vain ...), only then you have to divide the received data into several arrays. Not a frail volume can work out, for my particular case it is suitable, but it is not true for everyone.