How to display data correctly, minimizing the number of requests?

Question

PHP

Tell me the most correct way to output data from mysql. For example, I want to display an accordion. In the name of the accordion, I want to display the city and on the right display the number of elements inside. That is, this is one request. Inside the accordion, I want to display the dealers that belong to this city. That is, we get the second request in the cycle of the first one. And for example, I want to display another accordion next to it, which will display, say, dealers abroad. That's + 2 requests and one of them is in a loop. Ie it turns out a lot of requests. How to properly approach this problem?
For clarity, here is the output code for 1 accordion.

$sql = 'SELECT (SELECT COUNT(*) FROM `dilery` WHERE dilery.city_dil = city.id_city AND dilery.`status` != 1) as `count`, city.name_city, city.id_city
FROM dilery
INNER JOIN city ON dilery.city_dil = city.id_city
WHERE city.region != "1" AND dilery.`status` != 1
GROUP BY name_city
ORDER BY name_city';
$row_dealers = mysqli_query($link,$sql); 
while ($row = mysqli_fetch_array($row_dealers)) { ?>

<div class="card">
                    <div class="card-header pt-0 pb-0" id="headingOne">
                      <div class="pt-1 float-right">
                        <span class="badge badge-primary"> <?=$row['count']?></span>
                      </div>
                      <h5 class="card-title my-2">
                        <a href="#" data-toggle="collapse" data-target="#collapse-<?=$row['id_city']?>" aria-expanded="true" aria-controls="collapseO-<?=$row['id_city']?>"><?=$row['name_city']?>
                        </a>
                      </h5>
                    </div>
                    <div id="collapse-<?=$row['id_city']?>" class="collapse" aria-labelledby="headingOne" data-parent="#accordionExample">
                      <div class="card-body">
                        <div class="row">

$sql = 'SELECT dilery.name_dil, dilery.phone_dil, dilery.email_dil, dilery.site_dil
FROM dilery
INNER JOIN city ON dilery.city_dil = city.id_city
WHERE dilery.city_dil = '.$row['id_city'].' AND dilery.`status` != 1
ORDER BY name_city';

$row_dealers_item = mysqli_query($link,$sql); 
while ($row2 = mysqli_fetch_array($row_dealers_item)) { 
$phone = str_replace(',', '</li><li class="mb-1"><i class="fas fa-phone"></i> ',$row2['phone_dil']); ?>

<div class="col-12 col-lg-6">
                              <div class="card mb-3 border">
                                <div class="card-header">
                                  <h5 class="card-title mb-0"><?=$row2['name_dil']?></h5>
                                </div>
                                <div class="card-body pt-0">
                                  <ul class="list-unstyled mb-0">
                                    <?php
                                    if(!empty($row2['phone_dil'])) { ?>
                                      <li class="mb-1"><i class="fas fa-phone"></i> <?=$phone?>
                                    <?php
                                    }
                                    if(!empty($row2['email_dil'])) { ?>
                                      <li class="mb-1"><i class="fas fa-envelope"></i> <a href="mailto:<?=$row2['email_dil']?>"><?=$row2['email_dil']?></a></li>
                                    <?php	
                                    }
                                    if(!empty($row2['site_dil'])) { ?>
                                    <li class="mb-1"><i class="fas fa-globe"></i> <a href="https://<?=$row2['site_dil']?>" target="_blank"><?=$row2['site_dil']?></a></li>
                                    <?php	
                                    }
                                    ?>
                                  </ul>
                                </div>
                              </div>
                            </div>
                          <?php
                          }
                          ?>
                        </div>
                      </div>
                    </div>
                  </div>
                <?php
                }
                ?>
              </div>

Answer 1

You can apply the technique of one-time reading of the main entity (accordion) page by page, and for related information, query based on the received set with id identifiers.

Select ... from accordions
INNER JOIN city ...
Limit ?, ? /*смещение, количество*/

Select cities.id, ... from dilery
INNER JOIN city ...
Where cities.id in (список получен ранее)

And we form the result based on these two samples.
I advise you to read about the Materialized View Pattern , which suggests doing this in an even more optimal way.
Combined with caching gives good results.

Answer 2

Well here again the question sucked from a finger.
Minimizing the number of requests should not be an end in itself.
Are you playing your accordion? Fast? If so, leave it alone and don't touch anything.
If it's slow, then you need to speed it up. But not by changing the number of requests, but by speeding up the section that is stupid . This is a pancake simple truth, but for some reason it is inaccessible to the understanding of 95% of rascals. Which, in order to speed up work with network protocols, rush to replace double "quotes" with single ones.
It is necessary to change requests not because there are a lot of them, but because they are meaningless.
Here you have a grouping query that outputs the same data as the query below in the loop.
Question - WHY do you need this request at all?
Well, that is, the answer is clear - so that you can eat your favorite spaghetti, fixing the porridge from HTML, SQL, CSS, PHP, mysqli, pictures and in general everything and more, the main thing is that everything is in a heap.
And how can you find out at the beginning of the page what will be two lines below? Mystery of the century.
And if at least once in your life you try to write meaningful code that first receives data, processes it, and only then starts output, it turns out that you need one request in total.
Since requesting all dealers with one request, grouping them into arrays by region and city, and then stupidly displaying them, showing the number of elements in the array using the banal count() function, is perfectly possible in a mess.
And in the end, you don’t have to force the toaster regulars to strain their last convolutions to solve such complex problems as “caching”, “sending requests with one request” and other buzzwords that have nothing to do with reality.

Answer 3

Collect all requests of the necessary widgets in a stack and execute with one query to the database.
The answer is that you also parse the widgets in reverse order.
If the widgets are public, we periodically cache the result from the database and display it immediately for all users.
Key: Separate caching based on roles.