separate array and query in bd

Question

have a variable $servicos that receives the list of services registered in the BD through this code.

$query_servicos = "SELECT * FROM servicos ORDER BY nome ASC";
$result_servicos = mysqli_query($conectar, $query_servicos);
$servicos = array();
while ($linhas_servicos = mysqli_fetch_assoc($result_servicos)){
$servicos[] = $linhas_servicos['nome'];
}
$servicos = implode( ' ', $servicos);

I need to separate the data to list and perform a query.

$buscatotala = "SELECT * FROM dia WHERE dia='$datec' AND func='Alex' AND servicos LIKE '%$servicos%'";
$resultado_totala = mysqli_query($conectar, $buscatotala);
$totala = mysqli_num_rows($resultado_totala);

// Preciso que essa parte se repita, preenchendo a tabela com os dados do funcionário.                                                 
echo "
     <tr>
         <td>".$servicos[]."</td>
         <td>";
             if ($totala != '0'){
             echo "<font color='red'>";
             }
         echo $totala."</font></td>
     </tr>";

so could ease the code, why would not repeat the query listing the services, in the next officials.

EX: NEXT OFFICIAL:

$buscatotali = "SELECT * FROM dia WHERE dia='$datec' AND func='Italo' AND servicos LIKE '%$servicos%'";
$resultado_totali = mysqli_query($conectar, $buscatotali);
$totali = mysqli_num_rows($resultado_totali);

echo "
     <tr>
         <td>".$servicos[]."</td>
         <td>";
             if ($totali != '0'){
             echo "<font color='red'>";
             }
         echo $totali."</font></td>
     </tr>";

whole code for an employee (I am repeating the same code for the other employees just by swapping the termination of the $ with the initial letter corresponding to the employee):

<div class="accrodion-regular">
<div id="accordion3">
<div class="card my-0">
   <div class="card-header1" id="headingOne">
      <h5 class="mb-0">
         <button class="btn btn-link collapsed" data-toggle="collapse" data-target="#collapseOne" aria-expanded="false" aria-controls="collapseOne">
         <span class="fas mr-3 fa-angle-down"></span>Serviços
         </button>
      </h5>
   </div>
   <div id="collapseOne" class="collapse" aria-labelledby="headingOne" data-parent="#accordion3" style="">
      <div class="card-body p-0">
         <div class="table-responsive">
            <table class="table no-wrap p-table">
               <tbody>
                  <?php
         $query_listserva = "SELECT * FROM servicos ORDER BY nome ASC";
         $result_listserva = mysqli_query($conectar, $query_listserva);
                     while ($linhas_listserva = mysqli_fetch_assoc($result_listserva)){
                     $servicosa = $linhas_listserva['nome'];
                     $buscatotala = "SELECT * FROM dia WHERE dia='$datec' AND func='Alex' AND servicos LIKE '%$servicosa%'";
                     $resultado_totala = mysqli_query($conectar, $buscatotala);
                     $totala = mysqli_num_rows($resultado_totala);

                     echo "
                        <tr>
                            <td>".$servicosa."</td>
                            <td>";
                                if ($totala != '0'){
                                echo "<font color='red'>";
                                }
                            echo $totala."</font></td>
                        </tr>";
                     }
                  ?>
               </tbody>
            </table>
         </div>
      </div>
   </div>
</div>

Answer 1

So there is a way to use by the query itself Mysqli, because you just query and list the table servicos to obtain the result nome. So we can simplify your code by transforming the query servicos in a WHERE within the query itself. See the example:

<?php 
if (isset($_POST['userservico'])) {
    $userServ = $_POST['userservico'];
    $datec = $_POST['dateserv'];
    $servico = $_POST['servico'];

    $conectar = mysqli_connect("localhost","root","","testes");

    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $queryGeral = "SELECT serv.nome, d.dia, d.func, d.servicos FROM servicos AS serv LEFT JOIN dia AS d ON d.func = '" . $userServ . "' WHERE d.dia = '" . $datec . "' AND d.servicos LIKE '%" . $servico . "%' ORDER BY serv.nome ASC";

    $resultGeral = mysqli_query($conectar, $queryGeral);
    $totali = mysqli_num_rows($resultGeral);
    $servicos = array();
    while ($linhas = mysqli_fetch_assoc($resultGeral)){
        $servicos[] = $linhas['nome'];
    }

    $servicos = implode(' ', $servicos);
    echo "
    <tr>
    <td>".$servicos."</td>
    <td>";
    if ($totali != '0'){
        echo "<font color='red'>";
    }
    echo $totali."</font></td>
    </tr>";
}
?>

<form method="POST">
    Nome Funcionario:<br>
    <input type="text" name="userservico"><br><br>
    Dia:<br>
    <input type="text" name="dateserv"><br><br>
    Servico:<br>
    <input type="text" name="servico"><br><br>
    <button type="submit">Consultar</button>
</form>

Basically I would stay that way there, I could not have the means to test the code, so there may be some redundancy in the code, but basically this is what you will do (Being the part of code "dirty", fast and simple to understand). In case there’s a problem, just comment that we find a way to fix the code! :)