Codeigniter - How to make a select in the database query?

Question

I’m doing a list application of patients for surgery and using the CodeIgniter and Bootstrap. My doubt is how to make a select in the database query so that it shows me in the table only the results that do not have the status_paciente = "realizado"

I’m not using the model to make the query for now, I added on own controler. The table in use is the "patients" and the patient I do not want to show in select is the one that has the "status_patient" value = "performed".

Man 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:(just the part where this table showing the query data)

<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>

Answer 1

I believe that it is just to make the select the way you want, I have an example where 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'";

Answer 2

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 Codeigniter, to make a SELECT through the class Query Builder(class for handling database queries), is:

• All records of a table:

$query = $this->db->get('mytable');

• You can enter the select code in SQL as well (which in your case can give a WHERE status_patient NOT IN ('performed') :

$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');

Consult here documentation for further information. (the above example follows the documentation of the most current version of Codeigniter, if different from the one you use, see the same version).

I hope I contributed!