how to change the query?

Question

How to change the following query with the help of query builder codeigniter?

$query=$this->db->query("select a.*,b.nama from transaksi a,
                                    anggota b
                                    where a.no_transaksi='$nomor' and a.no_transaksi
                                    not in(select no_transaksi from pengembalian)
                                    and a.nomor_anggota=b.nomor_anggota");

Note: just want to know another way

you should take a look at `sql join`. [join](https://www.w3schools.com/sql/sql_join.asp) — Badiparmagi, Aug 16 '17 at 05:44
Have a look at this https://www.codeigniter.com/userguide3/database/query_builder.html — Jaymin, Aug 16 '17 at 05:44

score 0 · Answer 1 · answered Aug 16 '17 at 06:05

Try this:

$this->db->select('a.*, b.nama');
$this->db->from('transaksi a, anggota b');
$this->db->where('a.no_transaksi', $nomor);
$this->db->where('`a.no_transaksi` NOT IN (SELECT `no_transaksi` FROM `pengembalian`)', NULL, FALSE);
$this->db->where('a.nomor_anggota = b.nomor_anggota');
$result = $this->db->get()->result();

The ,NULL,FALSE in the where() tells CodeIgniter not to escape the query, which may mess it up.

Source: subquery in codeigniter active record

how to change the query?

1 Answers1