Codeigniter - Query builder #database

advance-query-builder

$sql = "SELECT * FROM some_table WHERE id IN ? AND status = ? AND author = ?";
$this->db->query($sql, array(array(3, 6), 'live', 'Rick'));
//The resulting query will be:
//SELECT * FROM some_table WHERE id IN (3,6) AND status = 'live' AND author = 'Rick'

check whether the record is exist

$query = $this->db->query('SELECT `id` FROM 'my_table' WHERE `id` = $id LIMIT 1');
$row = $query->row_array();
if ($row != NULL){
	//exist
} else {
	//not exist
}

connect to second database

//Establish connection to second database
$db['secondDB'] = array(
  'dsn'   => '',
  'hostname' => 'localhost',
  'username' => 'root',
  'password' => '',
  'database' => 'database_name',
  'dbdriver' => 'mysqli',
  'dbprefix' => '',
  'pconnect' => TRUE,
  'db_debug' => TRUE,
  'cache_on' => FALSE,
  'cachedir' => '',
  'char_set' => 'utf8',
  'dbcollat' => 'utf8_general_ci',
  'swap_pre' => '',
  'encrypt' => FALSE,
  'compress' => FALSE,
  'stricton' => FALSE,
  'failover' => array()
);
$db2 = $this->load->database($db['secondDB'], TRUE);
//if you put the second db configuration in config.php file, then use this
//$db2 = $this->load->database('secondDB', TRUE);

//Execute a query
$query = $db2->get_where($table_name, array('id' => $id)
                                              , 1, 0); //limit, offset
$row = $query->row_array();

count row in table

$query = $this->db->query("SELECT `id` FROM `".$table."`");
$total_rows = $query->num_rows();

count-all-rows-in-a-table

$total_records = $this->db->count_all_results($table);

custom where

$where = "name='Joe' AND status='boss' OR status='active'";
$this->db->where($where);

$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);
//https://www.codeigniter.com/user_guide/database/query_builder.html

$this->db->select('*')->from('my_table')
        ->group_start()
                ->where('a', 'a')
                ->or_group_start()
                        ->where('b', 'b')
                        ->where('c', 'c')
                ->group_end()
        ->group_end()
        ->where('d', 'd')
->get();

// Generates:
// SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd'

handling errors

if ( ! $this->db->simple_query('SELECT `example_field` FROM `example_table`'))
{
        $error = $this->db->error(); // Has keys 'code' and 'message'
}

insert record

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

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

Query helper methods

$this->db->insert_id()
//The insert ID number when performing database inserts.

$this->db->affected_rows()
//Displays the number of affected rows, when doing “write” type queries (insert, update, etc.).

$this->db->last_query()
//Returns the last query that was run (the query string, not the result). Example:

$this->db->count_all()
//Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter. Example: echo $this->db->count_all('my_table');

$this->db->insert_string()
//This function simplifies the process of writing database inserts. It returns a correctly formatted SQL insert string. Example:
//$data = array('name' => $name, 'email' => $email, 'url' => $url);
//$str = $this->db->insert_string('table_name', $data);
//will produce  INSERT INTO table_name (name, email, url) VALUES ('Rick', 'rick@example.com', 'example.com')

$this->db->update_string()
//This function simplifies the process of writing database updates. It returns a correctly formatted SQL update string. Example:
//$data = array('name' => $name, 'email' => $email, 'url' => $url);
//$where = "author_id = 1 AND status = 'active'";
//$str = $this->db->update_string('table_name', $data, $where);
//produce: UPDATE table_name SET name = 'Rick', email = 'rick@example.com', url = 'example.com' WHERE author_id = 1 AND status = 'active'

Result Helper Methods

//https://www.codeigniter.com/user_guide/database/results.html

$query = $this->db->query('SELECT * FROM my_table');
echo $query->num_rows();
//-------------------------------------------------------------------------------------
$query = $this->db->query('SELECT * FROM my_table');
echo $query->num_fields();

//-------------------------------------------------------------------------------------
//This method sets the internal pointer for the next result row to be fetched. It is only useful in combination with unbuffered_row().
//It accepts a positive integer value, which defaults to 0 and returns TRUE on success or FALSE on failure.

$query = $this->db->query('SELECT `field_name` FROM `table_name`');
$query->data_seek(5); // Skip the first 5 rows
$row = $query->unbuffered_row();

//-------------------------------------------------------------------------------------
//It frees the memory associated with the result and deletes the result resource ID. Normally PHP frees its memory automatically at the end of script execution. However, if you are running a lot of queries in a particular script you might want to free the result after each query result has been generated in order to cut down on memory consumption.

$query = $this->db->query('SELECT title FROM my_table');
foreach ($query->result() as $row)
{
        echo $row->title;
}
$query->free_result();  // The $query result object will no longer be available
$query2 = $this->db->query('SELECT name FROM some_table');
$row = $query2->row();
echo $row->name;
$query2->free_result(); // The $query2 result object will no longer be available
//-------------------------------------------------------------------------------------

run a query

$sql = "SELECT * FROM $table_name WHERE `sync_status` = 'new' LIMIT 1";
$query = $this->db->query($sql);
$result = $query->row_array(); //return array
if ($result == NULL){
  echo 'No data'; 
}

run-a-query.php

$table_name = '';
$query = $this->db->get_where($table_name, array('simple_sku' => $simple_sku)
                                      , 1, 0); //limit, offset
$row = $query->row_array();

select-distinct

$column = '';
$table_name = '';
$limit = '';
$query = $this->db->query("SELECT DISTINCT($column) AS $column 
					FROM $table_name 
					WHERE `availability_instock` = 1 
					ORDER BY '$column' DESC 
					LIMIT $limit");
$return =  $query->result_array();

select-one-row

$query = $this->db->get_where($table_name, array('simple_sku' => $simple_sku)
                                      , 1, 0);
$row = $query->row_array();

simple search

$search = '20% raise';
$sql = "SELECT id FROM table WHERE column LIKE '%" .
    $this->db->escape_like_str($search)."%' ESCAPE '!'";
$query = $this->db->query($sql);
$result = $query->result_array()

unbuffered_row

//This method returns a single result row without prefetching the whole result in memory as row() does. If your query has more than one row, it returns the current row and moves the internal data pointer ahead.

$query = $this->db->query("YOUR QUERY");

while ($row = $query->unbuffered_row())
{
        echo $row->title;
        echo $row->name;
        echo $row->body;
}

//Available:
//$query->unbuffered_row();               // object
//$query->unbuffered_row('object');       // object
//$query->unbuffered_row('array');        // associative array

update record

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

$this->db->where('id', $id);
$this->db->update('mytable', $data);
// Produces:
//
//      UPDATE mytable
//      SET title = '{$title}', name = '{$name}', date = '{$date}'
//      WHERE id = $id

utf8mb4 supported

$query = $this->db->query("SET NAMES 'utf8mb4'");

walk through results

In addition, you can walk forward/backwards/first/last through your results using these variations:

$row = $query->first_row()
$row = $query->last_row()
$row = $query->next_row()
$row = $query->previous_row()
By default they return an object unless you put the word “array” in the parameter:

$row = $query->first_row(‘array’)
$row = $query->last_row(‘array’)
$row = $query->next_row(‘array’)
$row = $query->previous_row(‘array’)