Selecting data from database in CodeIgnitor

PHP and CodeIgnitor

SQL queries allow us to  retrieve, insert and update data and information in your database. CodeIgnitor uses Active record database pattern. The values are escaped automatically by the system so it helps to produce the  safer queries.

SQL queries in CodeIgnitor helps in retrieval of  data from database.

The following SQL query functions allow you to build SQL SELECT statements.

1.1 $this->db->get();

This SQL Query is used to retrieve all the records from database.

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

// It produces SELECT * FROM table_name statement

The second and third parameters in SQL query enable you to set a limit and offset clause:

$query = $this->db->get('tableName', 20, 30);

// Produces: SELECT * FROM tableName LIMIT 30, 20 (in MySQL. 

Above SQL Query is assigned to a variable named $query, which can be used to show the results:

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

foreach ($query->result() as $row)

{

    echo $row->name;

}

1.2. $this->db->get_where();

This SQL Query is  identical to the above SQL Query.In addition to above SQL Query  it permits and helps you to add a "where" clause in the second parameter of the SQL Query, instead of using the db->where() SQL Query:

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

1.3 $this->db->select();

This SQL Function permits you to write the SELECT portion of your SQL query:

$this->db->select('sid, name, rollno');
$query = $this->db->get('tableName');

// Produces sql statement as: SELECT sid, name, rollno FROM tableName;

Note: If you want to select all (*) from a table you do not need to use this SQL Query. When  select portion is omitted, CodeIgniter assumes you want  to SELECT *  from tableName

1.4 $this->db->from();

This SQL query permits you to write the FROM portion of your SQL Statement:

$this->db->select('sid, name, rollno');

$this->db->from('tableName');

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

// This SQL Statement produces SQL statement as: SELECT sid, name, rollno FROM tableName;

1.5 $this->db->where();

This SQL query enables you to add WHERE clauses using one of four Techinques:

All values passed to SQL statement  are escaped automatically and  produce safer queries.

1.5.1 Simple key/value method:

$this->db->where('rollno', $rollno); 

// Produces: WHERE rollno= 106

Notice that the equal sign is added for you.

If you use multiple where SQL query  they will be chained together with AND between them:

$this->db->where('rollno', $rollno);

$this->db->where('class', $class);

$this->db->where('status', $status); 

// WHERE rollno=  106 AND class= 11 AND status = 'active'

1.5.2 Custom key/value method:

You can include an operator in the first parameter of SQL Query in order to control the comparison:

$this->db->where('name !=', $name);

$this->db->where('rollno <', $rollno); 

// This SQL Statement produces: WHERE name != 'Ram' AND rollno < 45

1.5.3 Associative array method:

$array = array('sid' => $sid, 'name' => $name, 'status' => $status);

$this->db->where($array); 

// This SQL Statement produces: WHERE sid= 106 AND name = 'Ram' AND status = 'active'

You can include your own operators using this method as well:

$array = array('faculty!=' => $faculty, 'rollno <' => $rollno, 'date >' => $date);

$this->db->where($array);

1.5.4 Custom string:

You can write your own clauses manually:

$where = "name='Ram' AND status='Manager' OR status='active'";

$this->db->where($where);

$this->db->where() SQL statement accepts an optional third parameter. If you set this  third parameter of SQL query  to FALSE, CodeIgnitor will not try to protect your field or table names of your database with backtricks.

$this->db->where('MATCH (fieldName) AGAINST ("value")', NULL, FALSE);

We keep on updating such tutorials. 

For more such tutorials like our Facebook page. 

Web Programming Tutorials

Reference https://ellislab.com/codeigniter/user-guide/database/active_record.html