codeigniter scripts

CodeIgniter select query function is used to fetch the result from one table but, one or more table data fetch to use join query. SELECT operation is also called as READ and one among the database CRUD process.

The following functions allow you to build SQL SELECT statements.

$this->db->get()

Let’s create a get method in Codeigniter scripts

$query = $this->db->get('tbl_user');  // Query: SELECT * FROM tbl_user

The get method provides second and third parameters enable you to set a limit and offset clause.

$query = $this->db->get('tbl_user', 5, 10);
// Query: SELECT * FROM tbl_user LIMIT 10, 5

In above function is assigned to a variable named $query, which can be used to show the results using foreach loop.

$query = $this->db->get('tbl_user');
foreach ($query->result() as $value)
{
    echo $value->firstname;
}

Compiles the selection query just like $this->db->get() but does not run the query. $this->db->get_compiled_select() method simply returns the SQL query as a string.

$sql = $this->db->get_compiled_select('tb_user');
echo $sql;codeigniter scripts
//Output:  SELECT * FROM `tb_user`

get_compiled_select method work with parameter

echo $this->db->limit(10,20)->get_compiled_select('tb_user', FALSE);
// Output: SELECT * FROM `tb_user` LIMIT 20, 10

echo $this->db->select('name, email, password')->get_compiled_select();
// Output: SELECT `name`, `email`, `password`

$this->db->get_where()

Codigniter $this->db->get_where() allows you to create a sql slect query having the WHERE Clause.

Note: get_where() was formerly known as getwhere(), which has been removed

$query = $this->db->get_where('tb_user', array('id' => $id), $limit, $offset);

$this->db->select()

Permits you to write the SELECT portion of your query.

$this->db->select('name, email, password');
$query = $this->db->get('tb_user');
// Query: SELECT name, email, password FROM tb_user

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names. This is useful if you need a compound select statement where automatic escaping of fields may break them.

$this->db->select("(SELECT SUM(order.amount) FROM order WHERE order.invoice_id = 1')
                   AS amount_paid", FALSE);
$query = $this->db->get('order_item');