separate array and query in bd

Question

separate array and query in bd

Navigation

#1 by (0 votes)

0

I have a $servicos variable that receives the list of services registered in the DB 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 them and make 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 it could ease the code, why not repeat the query that lists the services, in the next employees.

EX: NEXT EMPLOYEE:

$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>";

Entire code for an employee (I am repeating the same code for other employees only by changing the ending of the $ with the corresponding initial letter 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>

php mysql

asked by anonymous 02.12.2018 / 15:00

1 answer

Divide the contents of a post into several parts repeat data from the same query

score 0 · Answer 1

Then you have a way to use the MySQLi query itself, because you just query and list the servicos table to get the nome result. So we can simplify your code by transforming the query servicos into 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 it would look like this, 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 be doing (being the "dirty" code part, quicker and simpler of you understand). If there is a problem, just comment that we have a way to fix the code! :)