Relate 3 tables in a dispensing of medicines?

Question

Relate 3 tables in a dispensing of medicines?

Navigation

#1 by (1 votes)

0

I am making a system for my pharmacy: prescription drug dispensing.

I made the table patients (id, name, cpf, phone, address, photo_document), dispensing (patient_id, medication_id, quantity, crm, date_receita, ) and medicines (id, medicine, laboratory).

Only when I make the listing, I wanted all the same prescription drugs, for the same patient, with the same CRM, on the same date of prescription and dispensing.

<?php
header('Access-Control-Allow-Origin: *');

include 'init.php';

$sql = "SELECT *, pacientes.id AS id_pac FROM pacientes INNER JOIN dispensacao ON pacientes.id = dispensacao.id_paciente INNER JOIN medicamentos ON medicamentos.id = dispensacao.id_medicamento ORDER BY nome";
$query = $mysqli->query($sql);

while($ln = $query->fetch_array()){

$id_pac = $ln['id_pac'];
$nome_pac = $ln['nome'];
$cpf = $ln['cpf'];
$tel = $ln['telefone'];
$end = $ln['endereco'];
$imgdocumento = $ln['documento'];
$crm = $ln['crm'];
$data_receita = $ln['data_receita'];
$data_dispensacao = $ln['data_dispensacao'];
$imgreceita = $ln['receita'];
$nome_medic = $ln['medicamento'];
$qnt = $ln['quantidade'];

echo '<li class="accordion-item"><a href="#" class="item-content item-link">
                        <div class="item-inner">
                          <div class="item-title"><i class="icon f7-icons size-22">person</i> '.$nome_pac.'</div>
                        </div></a>
                      <div class="accordion-item-content">
                        <div class="content-block">
                            <p><hr></p>
                            <div class="row" style="margin-bottom: 15px">
                                <div class="col-50"><i class="icon f7-icons size-16">sort</i> <b>CPF</b></div>
                                <div class="col-50">'.$cpf.'</div>
                              </div>
                            <div class="row" style="margin-bottom: 15px">
                                <div class="col-50"><i class="icon f7-icons size-16">phone</i> <b>Telefone</b></div>
                                <div class="col-50">'.$tel.'</div>
                              </div>
                            <div class="row" style="margin-bottom: 15px">
                                <div class="col-50"><i class="icon f7-icons size-16">compose</i> <b>Endereço</b></div>
                                <div class="col-50">'.$end.'</div>
                              </div>
                            <div class="row" style="margin-bottom: 15px">
                                <div class="col-50"><i class="icon f7-icons size-16">today</i> <b>Data da Receita</b></div>
                                <div class="col-50"><a href="#" class="data-vencimento">'.date('d/m/Y', strtotime($data_receita)).'</a></div>
                              </div>
                            <div class="row" style="margin-bottom: 15px">
                                <div class="col-50"><i class="icon f7-icons size-16">today_fill</i> <b>Data da Dispensação</b></div>
                                <div class="col-50">'.date('d/m/Y', strtotime($data_dispensacao)).'</div>
                              </div>
                            <div class="row" style="margin-bottom: 15px">
                                <div class="col-50"><i class="icon f7-icons size-16">card</i> <b>CRM</b></div>
                                <div class="col-50">'.$crm.'</div>
                              </div>
                    <div class="row" style="margin-bottom: 15px">
                      <div class="col-50"><i class="icon f7-icons size-16">list</i> <b>Medicamentos Dispensados</b></div>
                      <div class="col-50">
                        <div class="data-table card centralized">
                          <table>
                            <thead>
                              <tr>
                                <th class="label-cell">Medicamento</th>
                                <th class="numeric-cell">Quantidade</th>
                              </tr>
                            </thead>
                            <tbody id="list_med">';

                             <tr>
                              <td class="label-cell">'.$nome_med.'</td>
                              <td class="numeric-cell">'.$qnt.'</td>
                             </tr>   


                            echo '</tbody>
                          </table>
                        </div>
                      </div>
                    </div>
                    <div class="row" style="margin-bottom: 15px">
                      <div class="col-50"><i class="icon f7-icons size-16">images</i> <b>Receita</b></div>
                      <div class="col-50"><img src="receitas/'.$imgreceita.'" width="50" height="50"></div>
                    </div>
                    <div class="row" style="margin-bottom: 15px">
                      <div class="col-50"><i class="icon f7-icons size-16">images_fill</i> <b>Documento</b></div>
                      <div class="col-50"><img src="receitas/'.$imgdocumento.'" width="50" height="50"></div>
                    </div>';
}

I'm not finding a solution to my listing. Can anyone help me?

Results so far: www.blocodochapolin.com.br/FP_novo/pacientes.php

The result I want to get here is this: link

Patient List

Ex: A patient can have several recipes, and each recipe would be a list with the drugs of this recipe (dispensation).

php foreach

asked by anonymous 05.07.2017 / 22:10

1 answer

Load extra data by the id received by the array Change background color and item text in GridView [closed]

score 1 · Accepted Answer

I accessed the link you left and apparently you were able to resolve the problem, but I'll post a way to resolve this as it can help other members.

The first change I made was to group the results by id of the patient. To bring all medicines and quantities, I used the function GROUP_CONCAT of MySql , this function is responsible for grouping the lines concatenating in a single cell. In PHP , I used the explode function to break this cell into a array , I did the same with the quantities, after that, I just made a for to scan the array and write the information. / p>

<?php
  header('Access-Control-Allow-Origin: *');

  include 'init.php';

  $sql = "
    SELECT a.id, a.nome, a.cpf, a.telefone, a.endereco, a.documento, a.documento
    FROM pacientes a
  ";
  $query = $mysqli->query($sql);

  while($ln = $query->fetch_array()){
    echo '
      <li class="accordion-item">
        <a href="#" class="item-content item-link">
          <div class="item-inner">
            <div class="item-title"><i class="icon f7-icons size-22">person</i> '.$ln['nome'].'</div>
          </div>
        </a>
        <div class="accordion-item-content">
          <div class="content-block">
            <p><hr></p>
            <div class="row" style="margin-bottom: 15px">
              <div class="col-50"><i class="icon f7-icons size-16">sort</i> <b>CPF</b></div>
              <div class="col-50">'.$ln['cpf'].'</div>
            </div>
            <div class="row" style="margin-bottom: 15px">
              <div class="col-50"><i class="icon f7-icons size-16">phone</i> <b>Telefone</b></div>
              <div class="col-50">'.$ln['telefone'].'</div>
            </div>
            <div class="row" style="margin-bottom: 15px">
              <div class="col-50"><i class="icon f7-icons size-16">compose</i> <b>Endereço</b></div>
              <div class="col-50">'.$ln['endereco'].'</div>
            </div>
    ';

    $sql = "
      SELECT a.crm, a.data_receita, a.data_dispensacao, a.receita,
      GROUP_CONCAT(b.medicamento SEPARATOR '|||') medicamentos, GROUP_CONCAT(b.quantidade SEPARATOR '|||') quantidades
      FROM dispensacao a
      INNER JOIN medicamentos b ON b.id = a.id_medicamento
      WHERE a.id_paciente = '.$ln['id'].'
      GROUP BY a.id
      ORDER BY a.nome
    ";
    $query1 = $mysqli->query($sql);

    while($lnReceita = $query1->fetch_array()){

      echo '
            <p><hr></p>
            <div class="row" style="margin-bottom: 15px">
              <div class="col-50"><i class="icon f7-icons size-16">today</i> <b>Data da Receita</b></div>
              <div class="col-50"><a href="#" class="data-vencimento">'.date('d/m/Y', strtotime($lnReceita['data_receita'])).'</a></div>
            </div>
            <div class="row" style="margin-bottom: 15px">
              <div class="col-50"><i class="icon f7-icons size-16">today_fill</i> <b>Data da Dispensação</b></div>
              <div class="col-50">'.date('d/m/Y', strtotime($lnReceita['data_dispensacao'])).'</div>
            </div>
            <div class="row" style="margin-bottom: 15px">
              <div class="col-50"><i class="icon f7-icons size-16">card</i> <b>CRM</b></div>
              <div class="col-50">'.$lnReceita['crm'].'</div>
            </div>
            <div class="row" style="margin-bottom: 15px">
              <div class="col-50"><i class="icon f7-icons size-16">list</i> <b>Medicamentos Dispensados</b></div>
                <div class="col-50">
                  <div class="data-table card centralized">
                    <table>
                      <thead>
                        <tr>
                          <th class="label-cell">Medicamento</th>
                          <th class="numeric-cell">Quantidade</th>
                        </tr>
                      </thead>
                      <tbody id="list_med">
      ';

      $medicamentos = explode('|||', $lnReceita['medicamentos']);
      $quantidades = explode('|||', $lnReceita['quantidades']);
      for ($i = 0; $i < count($medicamentos); $i++) {
        echo '
          <tr>
            <td class="label-cell">'.$medicamentos[$i].'</td>
            <td class="numeric-cell">'.$quantidades[$i].'</td>
          </tr>
      }

      echo '
                      </tbody>
                    </table>
                  </div>
                </div>
              </div>
              <div class="row" style="margin-bottom: 15px">
                <div class="col-50"><i class="icon f7-icons size-16">images</i> <b>Receita</b></div>
                <div class="col-50"><img src="receitas/'.$lnReceita['receita'].'" width="50" height="50"></div>
              </div>
      ';
    }

    echo '
              <div class="row" style="margin-bottom: 15px">
                <div class="col-50"><i class="icon f7-icons size-16">images_fill</i> <b>Documento</b></div>
                <div class="col-50"><img src="receitas/'.$ln['documento'].'" width="50" height="50"></div>
              </div>
    ';
  }
?>