CodeIgniter-how to make a select in the bank query?
I am making a patient list application for surgery and using CodeIgniter and Bootstrap. My doubt is how to make a select in the bank query so that it shows me in the table only the results that do not have the status_paciente = "realizado"
I'm not using model to do the query for now, I added in controler itself. The table in use is the "patients" and the patient I don't want to show in select is the one that has the "status_patient" with value = "accomplished".
My Controler:
public function index()
{
$this->db->select('*');
$this->db->join('medico','id_medico=idMedico','inner');
$dados['paciente'] = $this->db->get('paciente')->result();
$this->load->view('includes/html_header');
$this->load->view('includes/menu');
$this->load->view('listar_paciente', $dados);
$this->load->view('includes/html_footer');
}
My View: (only the part where the table showing the query data is)
<h4>Lista de Pacientes</h4>
<div class="table-responsive">
<table class="table table-striped table-sm" id="myTable">
<thead>
<tr>
<th hidden>ID</th>
<th>Ordem</th>
<th style="width:100px">Data</th>
<th>Prontuario</th>
<th>Nome</th>
<th>Sexo</th>
<th>Procedimento</th>
<th>OPME</th>
<th>Observações</th>
<th>LAB</th>
<th>Risco</th>
<th>Raio X</th>
<th>Outros</th>
<th>Médico</th>
<th>Status</th>
<th></th>
<th></th>
</tr>
</thead>
<tbody>
<?php foreach($paciente as $pac){?>
<tr>
<td hidden><?= $pac->id; ?></td>
<td><?= $pac->ordem; ?></td>
<td><?= date('d/m/y',strtotime($pac->data)); ?></td>
<td><?= $pac->prontuario; ?></td>
<td><?= $pac->nome; ?></td>
<td><?= $pac->sexo=='m'?'Masculino':'Feminino'; ?></td>
<td><?= $pac->procedimento; ?></td>
<td><?= $pac->opme; ?></td>
<td><?= $pac->observacoes; ?></td>
<td><?= $pac->lab; ?></td>
<td><?= $pac->risco; ?></td>
<td><?= $pac->raiox; ?></td>
<td><?= $pac->outros; ?></td>
<td><?= $pac->abreviacao; ?></td>
<td><?= $pac->status_paciente; ?></td>
<td>
<a href="<?= base_url('nir/alterar/'. $pac->id)?>" class="btn btn-primary btn-group">Atualizar</a></td>
<td><a href="<?= base_url('nir/excluir/'. $pac->id)?>" class="btn btn-danger btn-group" onclick="return confirm('Deseja Realmente Remover Este Paciente?')">Remover</a>
</td>
</tr>
<?php }?>
</tbody>
</table>
</div>
2 answers
Hello All right? For your example specified above, you can do:
$this->db->select('*');
$this->db->join('medico','id_medico=idMedico','inner');
$this->db->where_not_in('status_paciente', 'realizado');
$dados['paciente'] = $this->db->get('paciente')->result();
Following the documentation of the Codeigniter , to make a SELECT through the Class Query Builder (class for handling database queries), is:
- all records in a table:
$query = $this->db->get('mytable');
- you can enter select code in SQL as well (which in your case can give a Where status_patient NOT IN ('accomplished') :
$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4) AS amount_paid', FALSE);
$query = $this->db->get('mytable')
- using WHERE
$this->db->where('status_paciente != "realizado"); or
$this->db->where('status_paciente !=', $status_realizado); or even better:
$this->db->where_not_in('status_paciente', 'realizado');
See here the documentation for more information. (the example above follows the documentation of the most current version of CodeIgniter, if it is different from the one you use, refer to the same version).
I hope I contributed!
I believe it is just to make the select the way you want, I have an example in which I make a query to the bank, I will pass here see if it helps you
$sql = "select login, senha from data where login ='$login' and senha ='$senha'";