Consultation with pause?

Question

well I am making a php dynamic menu where I would not use query within another, I am currently searching from the CATEGORY table the name of the category and within it I am searching in another PAGE table the results that have in the category field the same that has in the CATEGORY table. Is there anything that shortens this code ? or query way directly in the table that can give the same result?

Menu example:

   // primeira consulta pego da tabela categoria todos os tipos de categorias que existe
   $results = mysqli_query($db, "SELECT * FROM categorias");
   //faco o loop
   while ($row = mysqli_fetch_array($results)) { 
   // defino a categoria 
   $categoria = $row['titulo'];?>
    // imprimo o nome do menu
   <li> <a href="#homeSubmenu" data-toggle="collapse" aria-expanded="false">
         <?php echo $row['titulo']; ?></a>
    /// abro a categoria sub menu
    <ul class="collapse list-unstyled" id="homeSubmenu">

    // faco a segunda consulta
    <? $results = mysqli_query($db, "SELECT * FROM paginas WHERE categoria = '$categoria' ");
    while ($row = mysqli_fetch_array($results)) { ?>
    <li><a href="#"><?php echo $row['titulo']; ?></a></li>
    // fecho as 2 consultas
    <?php }} ?>

Oh yes as for the title QUERY WITH PAUSE, it was something I thought could give a certain type to each result of the first query run the second automatically, the logic seems simple more complicated execution ta.

Answer 1

There are two ways, one is with query prepared, understand that it is closer than you want to describe with "PAUSE QUERY", and the other is to use a left Join to select all records of both tables, selecting left lines even if there are no associated lines in the right.

Prepared query

<?php
// prepara a query de páginas
$stmtPaginas = mysqli_prepare($db, "SELECT * FROM paginas WHERE categoria = ? ");
mysqli_stmt_bind_param($stmtPaginas, "s", $categoria);

// lê as categorias
$results = mysqli_query($db, "SELECT * FROM categorias");
// faz o loop
while ($row = mysqli_fetch_array($results)) { 
  // Coloca valor na variável categoria, que é a chave da tabela paginas
  $categoria = $row['titulo'];
  // abre o menu
  ?><li> <a href="#homeSubmenu" data-toggle="collapse" aria-expanded="false">
  <?php echo $row['titulo']; ?></a>
  <ul class="collapse list-unstyled" id="homeSubmenu">
  <?php
  // executa a query preparada
  mysqli_stmt_execute($stmtPaginas);
  $resultPaginas = mysqli_stmt_get_result($stmtPaginas);
  while ($rowPaginas = mysqli_fetch_array($resultPaginas)) {
    ?>
  <li><a href="#"><?php echo $rowPaginas['titulo']; ?></a></li>
  <?php 
  }
  ?>
  </ul></li>
  <?php
}
// fecha a query preparada
mysqli_stmt_close($resultPaginas);
?>

Left Join

<?php
// primeira consulta pego da tabela categoria todos os tipos de categorias que existe
$results = mysqli_query($db, "SELECT categorias.*, paginas.* FROM categorias LEFT JOIN paginas ON paginas.categoria = categorias.titulo");
// Define algumas variáveis de controle
$ultima_categoria = null;
//faz o loop
while ($row = mysqli_fetch_array($results)) { 
  // se é uma nova categoria, imprime o li de categoria, e se não for a primeira, fecha o ul e li anterior
  if ($ultima_categoria != $row['categorias.titulo']){
    if (!is_null($ultima_categoria)) {
      ?></ul></li><?php
    }
    ?><li><a href="#homeSubmenu" data-toggle="collapse" aria-expanded="false">
    <?php echo $row['categorias.titulo']; ?></a><ul class="collapse list-unstyled" id="homeSubmenu"><?php
    $ultima_categoria = $row['categorias.titulo'];
  }
  // Se existir página nessa linha, imprime
  if (!is_null($row['paginas.titulo'])) {
    ?><li><a href="#"><?php echo $row['paginas.titulo']; ?></a></li><?php
  } 
}
// a última categoria fica aberta, entao testa se houve categoria, se houve, fecha aqui
if (!is_null($ultima_categoria)) {
  ?></ul></li><?php
}
?>

I have not tested any of the two examples, the first should work, maybe need a few small adjustments, the second do not know the part that calls the columns with the table name, I do not remember if the mysqli accepts so, it would be best to specify all columns and put unique alias for each one, then sure it works.

You should use numeric keys to make relationships between tables, it’s more efficient for the database than using text.