PHP consultation repeating the medicine

Question

PHP consultation repeating the medicine

Asked 6 years, 10 months ago

Viewed 76 times

1

To explain my case, I first need to explain how a medicine dispensation of the Popular Pharmacy works (the program of the Federal Government).

I’m making a system for better dispensing organization here at my pharmacy.

How the dispensation works?

1) The patient arrives at the pharmacy carrying the prescription, document with photo and CPF.

2) If the prescription is valid (it is valid for 180 days from the date of the consultation), we dispense the medicines for the dose equivalent to 30 days.

3) The dispensation is done normally and saved in the BD.

Obs:

A patient may have X valid prescriptions;
A prescription can be Y medications;

Well, the system was working well, when I realized that I was updating the dispensation, when it needs to be registered (for a later consultation). The system displays all the patient’s recipes normally, using the following query:

Full PHP query that displays all recipes per patient:

 $sql1 = "SELECT a.crm, a.data_receita, a.data_dispensacao, a.receita, a.id_paciente, b.classe, a.id AS id_disp, 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";
 $query1 = $mysqli->query($sql1);

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

echo '
      <div id="'.$lnReceita['id_disp'].'" class="card receita-dispensada card-receitas-'.$lnReceita['id_disp'].'">
        <div class="card-content">
          <div class="card-content-inner">
            <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-receita">'.date('d/m/Y', strtotime($lnReceita['data_receita'])).'</a> <span class="apagar_receita" style="left: 90%; position: absolute"><a href="#">Apagar</a></span> </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"><a href="#" data-receita="'.$lnReceita['data_receita'].'" data-classe="'.$lnReceita['classe'].'" data-crm="'.$lnReceita['crm'].'" data-idpac="'.$lnReceita['id_paciente'].'" class="data-dispensacao">'.date('d/m/Y', strtotime($lnReceita['data_dispensacao'])).'</a></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 receita" 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 id="img_receita_'.$lnReceita['id_disp'].'" src="'.PATH_IMGS.''.$lnReceita['receita'].'" width="100" height="100"></div>
                </div>
              </div>
              <div class="row">
                <div class="col-50"></div>
                <div class="col-50 theme-orange">
                  <a href="#" class="button button-fill acoes_print" style="margin-top: -5px"><i class="material-icons color-white icon_btn">print</i><span class="label_btn"> Imprimir</span></a>
              </div>
            </div>
          </div>
        </div>                         
';
  }

 echo '  </div>
        <div class="row">
          <a style="padding-bottom: 15px; height: 30px" href="#" class="col-100 button button-fill add_receita"><i class="icon f7-icons size-12 color-white">add_round</i> Adicionar Receita</a>
        </div>
        </div>
        </div>
        </div>';
 }

THE MISTAKE:

With each dispensation, it is added to the BD, and the only thing that changes from a patient, is the data_dispensation, which, in the above consultation, REPEATS the medicine.

Structure of the BD:

    -- --------------------------------------------------------

--
-- Estrutura para tabela `dispensacao`
--

CREATE TABLE `dispensacao` (
  `id` int(11) NOT NULL,
  `id_paciente` int(11) NOT NULL,
  `id_medicamento` int(11) NOT NULL,
  `quantidade` int(11) NOT NULL,
  `crm` int(11) NOT NULL,
  `data_receita` date NOT NULL,
  `data_dispensacao` date NOT NULL,
  `receita` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Estrutura para tabela `medicamentos`
--

CREATE TABLE `medicamentos` (
  `id` int(11) NOT NULL,
  `medicamento` varchar(255) NOT NULL,
  `laboratorio` varchar(255) NOT NULL,
  `classe` varchar(500) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Estrutura para tabela `pacientes`
--

CREATE TABLE `pacientes` (
  `id` int(11) NOT NULL,
  `nome` varchar(255) NOT NULL,
  `cpf` varchar(20) NOT NULL,
  `telefone` varchar(30) NOT NULL,
  `endereco` varchar(255) NOT NULL,
  `documento` varchar(255) NOT NULL,
  `procuracao` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Estrutura para tabela `usuarios`
--

CREATE TABLE `usuarios` (
  `id` int(11) NOT NULL,
  `nome` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `senha` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Índices de tabelas apagadas
--

--
-- Índices de tabela `dispensacao`
--
ALTER TABLE `dispensacao`
  ADD PRIMARY KEY (`id`);

--
-- Índices de tabela `medicamentos`
--
ALTER TABLE `medicamentos`
  ADD PRIMARY KEY (`id`);

--
-- Índices de tabela `pacientes`
--
ALTER TABLE `pacientes`
  ADD PRIMARY KEY (`id`);

--
-- Índices de tabela `usuarios`
--
ALTER TABLE `usuarios`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT de tabelas apagadas
--

--
-- AUTO_INCREMENT de tabela `dispensacao`
--
ALTER TABLE `dispensacao`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=284;
--
-- AUTO_INCREMENT de tabela `medicamentos`
--
ALTER TABLE `medicamentos`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;
--
-- AUTO_INCREMENT de tabela `pacientes`
--
ALTER TABLE `pacientes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=116;
--
-- AUTO_INCREMENT de tabela `usuarios`
--
ALTER TABLE `usuarios`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

What I hope:

Only do not repeat the medicine with each new dispensing of the same prescription for the patient!

Thanks in advance!

The GROUP BY should be in the code of the medicinal product and not on the date of prescription, no?

– Sam

2017/08/14 at 19:18
I tried that too, but when the patient has 2 recipes, it only displays one. =/

– Guilherme Lirio

2017/08/14 at 19:33
1

Try to put, before closing the loop, a variable with the code you are repeating, and at the beginning an IF comparing if this variable is different from the code loaded at the end. Something like that: for(...){ if(codigo_receita != código_vindo_do_banco){ exibe a receita }; codigo_receita=código_vindo_do_banco;}

– Sam

2017/08/14 at 19:43
1

If you can post the question the code of the loop pulling the data from the database would be easier to exemplify.

– Sam

2017/08/14 at 19:45
Okay, I’ll try later @Davidsamm Thank you!

– Guilherme Lirio

2017/08/14 at 19:47
@Davidsamm edited with full query!

– Guilherme Lirio

2017/08/15 at 03:49
1

Try to put array_unique() in $medicamentos, being like this: $medicamentos = array_unique(explode('|||', $lnReceita['medicamentos']));. This eliminates repeated items in the array.

– Sam

2017/08/15 at 17:12
@Davidsamm worked! Could you post the reply and a brief explanation? Thank you!

– Guilherme Lirio

2017/08/15 at 19:54

Show 3 more comments

2 answers

1

SQL is returning drugs in duplicity. An easy way to solve this without tampering with SQL is to eliminate duplicity in the Array $medicamentos with the function array_unique():

$medicamentos = array_unique(explode('|||', $lnReceita['medicamentos']));

Reference: https://www.w3schools.com/php/func_array_unique.asp

Browser other questions tagged php

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

by viana • **27,141** points · Answer 1 · 2017-08-14T18:48:15+00:00

0

Use the DISTINCT not to return repeated items. See:

SELECT DISTINCT a.crm, a.data_receita, a.data_dispensacao ...

Yes, I tried but still repeats.

– Guilherme Lirio

2017/08/14 at 18:52
@Guilhermelirio and if you put a COUNT(DISTINCT b.medicamento) for example. Try ai.

– viana

2017/08/14 at 18:55
Oh no, but in this case maybe I would have to group by name of the medicine too. I’m thinking here a little. It would be interesting to leave in your question the scripts (not the script image) to create this table. This makes reproduction easier for those who try to help you.

– viana

2017/08/14 at 18:58
Humm, all right, I’ll put you up here (though I don’t know how to do this right kkkk).

– Guilherme Lirio

2017/08/14 at 18:59
1

There in the BD Structure, instead of putting the image, put the CREATE TABLE ... bla bla bla... because it makes it easier for people who are responding, or trying to answer =D

– viana

2017/08/14 at 19:00
Ready! @acklay

– Guilherme Lirio

2017/08/14 at 19:04

Show 1 more comment