View data from two mysql tables in one page in PHP

Question

Good afternoon, I have a system where you register patients.

I have two tables in my bank, TABLE 1 = hospitalizations, TABLE 2 = patients_backup. I want to display data from the TWO tables, on a page (vizuinternacao.php) only in PHP.

There’s one field in the two tables, where the data are the same, example:

field nome of TABLE 1 = (Mateus silva oliveira)

field paciente of TABLE 2 = (Mateus silva oliveira)

I tried to do it this way: (list.php)

<?php

$sql="";
require('conexaobd.php');

if (isset($_GET["pesquisa"])) {
$nome = $_GET["pesquisa"];
$sql = "SELECT numero_de_processo, paciente, data_de_entrada, convenio, tipo_de_internacao, data_saida FROM internacoes WHERE paciente LIKE '%$nome%' ORDER BY paciente ASC";
}else

$sql = "SELECT internacoes.numero_de_processo, internacoes.paciente, internacoes.data_de_entrada, internacoes.convenio, internacoes.tipo_de_internacao, internacoes.data_saida, pacientes_backup.sus FROM internacoes, pacientes_backup WHERE pacientes_backup.nome = internacoes.paciente ORDER BY internacoes.paciente ASC";


$resultado = mysqli_query($link, $sql);

$inc = 0;

// LAÇO DE REPETIÇÃO
while ($cont = mysqli_fetch_array($resultado)) {

    echo "
       <tr> 

                <td>".$cont['paciente']."</td>   
                <td>".converte_data($cont['data_de_entrada'])."</td> 
                <td>".$cont['convenio']."</td>
                <td>".$cont['tipo_de_internacao']."</td>
                <td>".converte_data($cont['data_saida'])."</td>

                 <td> 
                 <a href='vizuinternacao.php?numero_de_processo=".$cont['numero_de_processo']." &paciente=".$cont['paciente']." &sus=".$cont['sus']." target='new_blank'>
                <span class='role orange'> 
               <i class='fa fa-eye'></i>
                </span>
                </a>
                </td>

          </tr> 
    ";
}  
?>

Even then I think it’s right, when I click on a patient, the field already appears sus(which is from the other table) exact in the url.

Now my page where I want to display: (vizuinternacao.php)

<?php 
require('conexaobd.php');

$numero_de_processo = $_GET['numero_de_processo'];
$sql = mysqli_query($link, "SELECT * FROM internacoes WHERE numero_de_processo = '$numero_de_processo' ");
while ($cont = mysqli_fetch_array($sql)) {
?>

            <!-- MAIN CONTENT-->
            <br>
                <div class="section__content section__content--p30">
                    <div class="container-fluid">
                        <div class="row">
                            <div class="col-lg-12"> 
                                <div id="bk">

                                    <div class="card-body">
                                        <div class="card-title">
                                           <h3><center><i style="font-family: 'Source Code Pro', monospace;">Clínica terapêutica Jeova Rafha - <a target="_blank" href="listagem_internacao.php" style="color: red">Dados da clínica</a></i></center></h3> 
                                        </div>  

                                        <form method="post" enctype="multipart/form-data">
<?php 
$sus = $_GET['sus'];
$sql = mysqli_query($link, "SELECT * FROM pacientes_backup WHERE sus = '$sus' ");
while ($cont = mysqli_fetch_array($sql)) {
// EXIBINDO A IMAGEM
echo "<img src='".$cont['foto']."' class='alignright'/>"; }  
?>

                                          <div class="form-row">
                                             <div class="col">
                                                <label for="cc-payment" class="control-label mb-1"><b>Paciente</b></label>
                                                <select name="paciente" disabled="disabled" class="form-control custom-select">
                                                  <option value="<?php echo $cont['paciente'];?>"><?php echo $cont['paciente'];?></option>
                                                        <?php
                                                require('conexaobd.php');

                                            $sql = "SELECT nome FROM pacientes";


                                            $resultado = mysqli_query($link, $sql);

                                            $inc = 0;

                                            while ($cont = mysqli_fetch_array($resultado)) {
                                                echo "
                                                 <option>".$cont['nome']."</option>  
                                                ";
                                            }


                                            $sql = mysqli_query($link, "SELECT * FROM internacoes WHERE numero_de_processo = '$numero_de_processo' ");
                                            while ($cont = mysqli_fetch_array($sql)) {


                                            ?>   
                                                 </select>
                                             </div> 


                                              <div class="col">
                                                <label for="cc-payment" class="control-label mb-1">Numero de processo</label>
                                                <input id="cc-pament" name="numero_de_processo" readonly="readonly" type="text" class="form-control" aria-required="true" aria-invalid="false" value="<?php echo $cont['numero_de_processo'];?>">
                                            </div>
                                          </div>
                                          <br>
                                           <div class="form-row">
                                              <div class="col">
                                                <label for="cc-payment" class="control-label mb-1">Data de entrada</label>
                                                <input id="cc-pament" name="data_de_entrada" readonly="readonly" type="date" class="form-control" aria-required="true" aria-invalid="false" value="<?php echo $cont['data_de_entrada'];?>">
                                            </div>

// ETC, SÃO MUITOS CAMPOS... ESTOU FECHANDO CORRETAMENTE

Not displaying any error. But also not displaying the field foto in the case...

Thanks in advance to anyone who tries to help me, hugs!

Answer 1

Use Inner Join

SELECT internacoes.*, pacientes_backup.* FROM internacoes INNER JOIN pacientes_backup on internacoes.nome = pacientes_backup.nome WHERE ...

And also I recommend using parentheses in subquerys for help in readability, and instead of keeping the name in the two tables, it is good you create a foreign key in the taabela hospitalizations referencing the patient id

Answer 2

SELECT C.nome [table 1], F.paciente AS [table 2] FROM table 1 AS C INNER JOIN table AS F ON C.nome = F.paciente