How to deal with multiple queries?

Question

I have a question related to good programming practices. How to deal with multiple queries on the same page in PHP. For example, the code:

    $qr = "SELECT historico.*, funcionarios.nome FROM historico INNER JOIN funcionarios ON (historico.funcionario_id = funcionarios.funcionario_id)";
    $resultado = mysql_query($qr);

Just below on my page, I have to do another query:

    $qr2 = "SELECT diasplantao.*, funcionarios.nome FROM diasplantao INNER JOIN funcionarios ON (diasplantao.funcionario_id = funcionarios.funcionario_id)";      
    $resultado2 = mysql_query($qr2);

And so on, there may even be others. My question is: What is the best way to manage this? Is there any way to make sure the code doesn’t get so messy, with so many variables.

Answer 1

I particularly prefer to use full names that facilitate further debugging and maintenance by others. Example:

$queryFuncionarios 

$queryDiasPlantao

It makes it so much easier

Answer 2

One way to improve the organization of a procedural project is to separate php from html and break the code (macaroni) into functions. Also remember to use modern api’s for database connection avoiding mysql functions_*

Example:

macarrao.php

<?php
    $qr = "SELECT historico.*, funcionarios.nome FROM historico 
           INNER JOIN funcionarios
           ON (historico.funcionario_id = funcionarios.funcionario_id)";
    $resultado = mysql_query($qr);
?>

<table>
    <tr>
        <td>Nome</td>
        <td>Matricula</td>
        <td>Data entrada</td>
        <td>Data saida</td>
    </tr>

    <?php

    while($row = mysql_fetch_assoc($resultado)){
    echo
    '<tr>
        <td>'. $row['nome']  .'</td>
        <td>'. $row['matricula'] .'</td>
        <td>'. $row['entrada'] .'</td>
        <td>'. $row['saida'] .'</td>
    </tr>';
    ?>

</table>

1 - Remove the start code and the while from the.php folder and create a new file that can be sql/funcios.php, will stay that way:

include 'conexao.php';

function getHistorio($conexao){
    $sql = 'SELECT historico.*, funcionarios.nome FROM historico
            INNER JOIN funcionarios
            ON (historico.funcionario_id = funcionarios.funcionario_id)';
    $query = mysql_query($sql, $conexao) or die(mysql_error());

   $historicos = array();
   while($row = mysql_fetch_assoc($query)){
       $historicos[] = $row;
   }
    return $historicos;
}
//outras funções....

2 - Copy the html content of macaroni.php to a new, view/lista_historico.php file that will only have a foreach to list the histories

<?php
    include 'sql/funcionario.php';
    $historico = getHistorio($conexao);
?>
<table>
    <tr>
        <td>Nome</td>
        <td>Matricula</td>
        <td>Data entrada</td>
        <td>Data saida</td>
    </tr>

    <?php foreah($historico as $item){ ?>
    <tr>
        <td><?php echo $item['nome']; ?></td>
        <td><?php echo $item['matricula']; ?></td>
        <td><?php echo $item['entrada']; ?></td>
        <td><?php echo $item['saida']; ?></td>
    </tr>
  <?php } ?>

Recommended reading:

Flat PHP vs Symfony

Why should we not use mysql type functions_*?

Mysqli vs PDO - which is the most recommended to use?

Answer 3

Before executing multiple queries you need to analyze whether they are independent queries or not.

Imagine, for example, an action log system that records all actions taken by a particular user.

If you run two queries at the same time, one to enter and the other to register the inclusion by user X, if the first fails and the second fails, you get information inconsistency.

Of course, in a log system that is usually clean it is not enough to be a troubling problem, but it is a problem in fact.

Whereas if you try to insert and fail, the second query will not be executed and, breaking, you could still manipulate the error by informing the user that that specific action failed, rather than a general error of "something went wrong".