Consultation with pause? [closed]

Question

Consultation with pause? [closed]

Navigation

#1 by (1 votes)

-1

I'm doing a dynamic php menu where I would not use a query inside another, I'm currently looking at the CATEGORY table of the category name and inside it I'm searching in another table for the results that I have in the category field, in the CATEGORY table. Is there anything that shortens this code? or way to query directly on 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 yeah as for the title CONSULTA WITH PAUSE, it was something that I thought could give a certain type to each result of the 1st query to execute the second one automatically, the logic seems simple but the execution is complicated.

php sql mysqli bootstrap

asked by anonymous 03.01.2018 / 22:24

1 answer

I took a bd field capture and give this error Count numbers 1 in binary number after decimal base conversion

score 1 · Accepted Answer

There are two ways, one is with prepared query, I understand it is closer to what you'd like to describe with "PAUSE CONSULT," and the other is to use a left join to select all records from both tables by selecting rows of the left even though there are no associated lines on 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 tried either of the two examples, the first one should work, maybe need a few minor adjustments, the second I do not know the part that calls the columns with the table name, I do not remember if mysqli accepts so, the columns and put single alias for each one, then sure works.

You should use numeric keys to make relationships between tables, it is more efficient for the database to use text.