Database

Database

Loading the database library

$this->db->load('db');
$this->db->get('table');

Automatically load

in system/config/development.php ( for production : system/config/development.php )

public static $autoload=array("db");

SELECT

SELECT id FROM table

code will be

$result = $this->db->select('id')->get('table');
if($this->db->count() > 0) {
    echo $result[0]->id;
}

SELECT DISTINCT

SELECT DISTINCT('name') FROM table

code will be

$result = $this->db->distinct('name')->get('table');
if($this->db->count() > 0) {
    echo $result[0]->id;
}

WHERE

SELECT * FROM table WHERE id = 5

code will be

$this->db->where("id","5");
$result = $this->db->get("table");
if($this->db->count() > 0) {
    echo $result[0]->id;
}

Where AND

SELECT * FROM table WHERE id = 5 AND name = 'sample'

code will be

$this->db->where("id","5");
$this->db->where("name","sample");
$result = $this->db->get("table");
if($this->db->count() > 0) {
    echo $result[0]->id;
}

WHERE OR

SELECT * FROM table WHERE id = 5 OR name = 'sample'

code will be

$this->db->where("id","5");
$this->db->where_or("name","sample");
$result = $this->db->get("table");
if($this->db->count() > 0) {
    echo $result[0]->id;
}

WHERE Like

SELECT * FROM table WHERE name like 'sample%'

code will be

$this->db->where_or("name","sample","after");//key are both , before , after
$result = $this->db->get("table");
if($this->db->count() > 0) {
    echo $result[0]->id;
}

Another Example

SELECT * FROM table WHERE name like 'sample%' AND place like '%myplace'

code will be

$this->db->where_or("name","sample","after");
$this->db->where_or("place","my place","before");	$result = $this->db->get("table");
if($this->db->count() > 0) {
    echo $result[0]->id;
}

WHERE OR Like

SELECT * FROM table WHERE name like 'sample%' OR place like '%myplace'

code will be

$this->db->where_or("name","sample","after");
$this->db->where_or_like("place","my place","before");	$result = $this->db->get("table");
if($this->db->count() > 0) {
    echo $result[0]->id;
}

ORDER

SELECT * FROM table ORDER BY `name` ASC

code will be

$array['name']='ASC';
$this->db->order($array);
$result = $this->db->get("table");
if($this->db->count() > 0) {
    echo $result[0]->id;
}

LIMIT

SELECT * FROM table LIMIT 5

code

$this->db->limit(5);
$result = $this->db->get("table");
if($this->db->count() > 0) {
    echo $result[0]->id;
}


SELECT * FROM table LIMIT 5,10

$this->db->limit(10,5);
$result = $this->db->get("table");
if($this->db->count() > 0) {
    echo $result[0]->id;
}

INSERT

INSERT INTO table (name,place) VALUE ('sample','my place')

code:

$insert['name']='sample';
$insert['place']='my place';
$this->db->insert("table",$insert);

UPDATE

UPDATE table SET name = 'my place' WHERE id =1

code:

$this->db->where("id","1");
$update['name']='my place';
$this->db->update("table",$update);

DELETE

DELETE FROM table where id=1

code:

$this->db->where("id","1");
$this->db->delete("table");

JOIN

SELECT * FROM table INNER JOIN `other` ON `other`.id=`table`.other_id

code:

$this->db->join("other","`other`.id=`table`.other_id","INNER");
$this->db->get("table");

JOIN have 3 KEY , INNER,LEFT,*JOIN

GROUP BY

$this->db->group_by("name");

HAVING

$this->db->having("count(`name`)=1");

ROWS COUNT

$this->db->count();

RUN QUERY

$sql = "SELECT * FROM table";
$result = $this->db->query($sql);

GET SQL STRING

$this->db->select("id");
$this->db->where("name","sample");
$result = $this->db->get("table");
echo $this->db->sql;

ERROR

$this->db->select("id");
$this->db->where("name","sample");
$result = $this->db->get("table");
var_dump($this->db->err);

IS NULL

//SELECT * FROM table WHERE a IS NULL
$this->db->where_null("a");
$this->db->get("table");

IS NOT NULL

//SELECT * FROM table WHERE a IS NOT NULL
$this->db->where_null("a",false);
$this->db->get("table");

Braket

    //SELECT * FROM table WHERE ( a = '1' AND b = '2' ) OR ( a = '5' AND b = '6' ) 

     $this->db->bracket_open();
    $this->db->where("a","1");
    $this->db->where("b","2");
    $this->db->bracket_close();
    $this->db->bracket_open("OR");
    $this->db->where("a","5");
    $this->db->where("b","6");
    $this->db->bracket_close();

    $this->db->get("table");