List 3 tables in a drug dispensation?

Asked

Viewed 100 times

0

I’m making a system for my pharmacy: dispensing prescription-controlled drugs.

I made the table patients (id, name, Cpf, telephone, address, photo_document), dispensation (id_patient, id_medicament, quantity, crm, dat_prescription, dat_dispensation, photo_prescription) and medicines (id, medicine, laboratory).

Just who when I’m going to do the listing, would want all medications of the same prescription, 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 for my listing. Can someone help me?

Result so far: www.blocodochapolin.com.br/Fp_new/patients.php

The result I want to reach would be this: http://blocodochapolin.com.br/FP_novo/

Patient list

Ex: a patient may have multiple prescriptions, and each prescription would be a list of the medications in that prescription (dispensing).

php foreach

  • Listen, I don’t quite understand the question, especially about the term dispensation. Anyway, what I understand is that you want the listing to show the list of medicines for each dispensation of this link? If so, you can make the medicine table Join in the sql shown, to recover the name of it

  • This way I told you you’ll have the same patient and dispensing information being repeated for each medicine, so you’ll have to group it later. If that’s the case, just say the word and I’ll answer the question

  • Dispensation is the technical term of selling the remedy to a person. How would this grouping?

  • Oops, I just saw that the medicine table is already on Join

  • Yes, but look at the result: it shows the same patient 2x, and the drugs 2x. Wanted me to show the patient, and the medications dispensed to him in the same consultation.

1 answer

1


I accessed the link you left and apparently you managed to solve the problem, but anyway I will post a way to solve this because it can help other members.

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

<?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>
    ';
  }
?>

  • Follow your repaired SELECT: SELECT a.id as id_pac, a.nome, a.cpf, a.telefone, a.endereco, a.documento, b.crm, b.data_receita, b.data_dispensacao, b.receita,&#xA; a.documento, GROUP_CONCAT(c.medicamento SEPARATOR '|||') medicamentos, GROUP_CONCAT(b.quantidade SEPARATOR '|||') quantidades&#xA; FROM pacientes a&#xA; INNER JOIN dispensacao b ON a.id = b.id_paciente&#xA; INNER JOIN medicamentos c ON c.id = b.id_medicamento&#xA; GROUP BY a.id&#xA; ORDER BY a.nome

  • There is only one problem: if I have a new prescription, from the same patient, with another date, another CRM and other medicines, I would have to appear another piece of it, in a new list!

  • The AS is not necessary for the query to work, the fields foto_receita and foto_documento were the ones you put in your question. To fix the problem of the second recipe, just put a field id on the table dispensacao and do the SELECT as follows:

  • 1

    SELECT a.id id_pac, a.nome, a.cpf, a.telefone, a.endereco, a.documento, b.crm, b.data_receita, b.data_dispensacao, b.receita, a.documento, GROUP_CONCAT(c.medicamento SEPARATOR '|||') medicamentos, GROUP_CONCAT(b.quantidade SEPARATOR '|||') quantidades FROM pacientes a INNER JOIN dispensacao b ON a.id = b.id_paciente INNER JOIN medicamentos c ON c.id = b.id_medicamento GROUP BY a.id, b.id ORDER BY a.nome

  • Thanks Rodrigo, but it’s almost that. About the fields, I had put wrong, I’m sorry. In the case, there on the page, is displaying a field by medicine. 1 patient may have X recipes, with Y medications in each. It would have to be together: the medicines of each prescription, with each CRM, with each Date. That’s what I’m cracking my head on!

  • I don’t understand, you can edit your question and put an example of how you want the listing to look?

  • I edited there @Robertofagundes

  • Now I can understand what you need, to do this would be better to do two SELECT, can be?

  • Problem-free!!

  • 1

    I changed the answer code, make sure it works, I can’t test because I don’t have your database

  • Friend, I know I’m burning your patience, but it’s not yet that! Look at the link how it is! In case, when it is the same prescription, the medicines have to be together.

  • I got it!! I changed your select to this one: SELECT a.crm, a.data_receita, a.data_dispensacao, a.receita, GROUP_CONCAT(b.medicamento SEPARATOR '|||') medicamentos, GROUP_CONCAT(a.quantidade SEPARATOR '|||') quantidades FROM dispensacao a INNER JOIN medicamentos b ON b.id = a.id_medicamento WHERE a.id_paciente = {$ln['id']} GROUP BY data_receita&#Thanks @Robertofernanes

  • Please post on the website Sqlfiddle the structure and data of your table

  • blz, glad you could make it

Show 9 more comments

Browser other questions tagged php foreach

You are not signed in. Login or sign up in order to post.