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");