The Database Library

Active Record Class

CodeIgniter has a library called Active Record (AR) that can help you write your queries without writing any SQL. It’s pretty powerful and the better way to go when you’re no SQL expert or aren’t sure how to protect your queries against SQL injections.

Inserting a row

$data = array(
   'title' => 'My title' ,
   'name' => 'My Name' ,
   'date' => 'My date'
);

$this->db->insert('mytable', $data);

// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')

Selecting data

Selecting a unique result field

$this->db->where('username', 'john');
$query = $this->db->get('users');
return $query->row()->password;

Selecting an array (return all its columns)

$this->db->where('username', 'john');
$query = $this->db->get('users');
return $query->result();

Selecting an array (return one row only)

$this->db->where('username', 'john');
$query = $this->db->get('users');
return $query->row();

Selecting an array (return only the columns you want)

$this->db->where('username', 'john');
$query = $this->db->get('users');
foreach ($query->result() as $row)
{
    $data_array[] = $row->column_name;
}
return $data_array;

Selecting data using MySQL query

$q = '
            SELECT *
            FROM my_table
        ';
        $query = $this->db->query($q);
        return $query->result();

Sorting data

The first parameter contains the name of the column you would like to order by. The second parameter lets you set the direction of the result. Options are asc or desc, or random.

$this->db->order_by("title", "desc");

Updating a row

$data=array('first_name'=>'John','last_name'=>'Doe','email'=> 'johndoe@domain.com');

$this->db->where('id', '3');

$this->db->update('users', $data);

Deleting a row

$this->db->where('id', 2); // delete row with id 2

$this->db->delete('users'); // from table named 'users'

Searching a column with like

$this->db->like('name', 'greece');
$query = $this->db->get('countries');

Checking if a record exists in a table

We can just check MySql table to verify whether a record exist or not. Here we are not interested in collecting the actual data from table. We are interested in getting a reply of TRUE or FALSE from our command.

$this->db->where('id', 2);
$this->db->from('users');
$n_results = $this->db->count_all_results();
if ($n_results == 0)
{
    // not found in table
    return false;
} else
{
    // found in table
    return true;
}

Count

$this->db->like('title', 'match');
 $this->db->from('my_table');
 echo $this->db->count_all_results();
 // Produces an integer, like 17 

Query Helper Functions

Returns the last query that was run (the query string, not the result). Example:

echo $this->db->last_query();

Read the original documentation page for more.

Post A Comment

Anti-Spam Quiz: