Codeigniter tutorial

Codeigniter Database Where and Delete Beginners Tutorial Part-4

Views: 749

Codeigniter Database Where and Delete Beginners Tutorial Part-4

In previous tutorial we learned about the update(), replace() and set methods of codeigniter database, in this tutorial we will learn about the delete method and available where methods of codeigniter. So let us start.

Codeigniter delete() tutorial

Deleting Data With Codeigniter “$this->db->delete()”

The codeigniter delete() method allows us to let remove a row from out table, this works as same as the MySql delete method, suppose you have users in your database table and you want to remove one user from that table then the delete method of codeigniter will let you do so. Let us see an example.

Suppose we have a user table and we want to remove the user with id 4 from the table then we will use the following method to remove that user using codeigniter delete.

Usage:

$id=4;
$data=array(
'id'=>$id
);                            
$this->db->delete('user', $data);

Note: On the above query we are passing the id and the table name to the codeigniter delete() parameter. This will generate the following sql query

DELETE FROM user WHERE id=$id

Output:

delete() model:

codeigniter delete model

before delete():

codeigniter before delete

after delete():

Result After Delete Method

It is also possible to use codeigniter where methods with the codeigniter delete() method, next example elaborates how to use where methods with codeigniter delete().

Using Codeigniter where() With delete()

The codeigniter delete() method itself doesn’t give that much flexibility, if I want to delete a row with multiple identifiers then I can’t use the delete() method because of its limitation of single identifier, so here comes the where() method, it allows us to use multiple identifiers, the where() method can be used with delete method, below given example explains it’s usage:

Usage:

$id=4;
$this->db->where('id', $id);
$this->db->delete('user');

Note: in the above codeigniter query request, the where clause will set the identifier, you can use multiple where() methods and codeigniter will generate the query.

Output:

delete() model:

codeigniter where delete model

Before where() delete():

codeigniter before where delete

After where() delete()

Codeigniter after where delete

 

Codeigniter where() Methods Tutorial

Codeigniter Where “$this->db->where()” method

The codeigniter where() method allows to select a specific data from table, the where() clause if repeated then it will generate a SQL query with “AND” parameters and append the query. There are 4 ways in codeigniter of using the where clause.

Simple Key and Value Method

In simple key value method we pass two values to our where clauses, the key and the value and append with multiple where clause to get a complete query, see the example below.

$this->db->where(key, value);

This will generate the string: WHERE key=value

If we repeat the where clause then:

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

$this->db->where('username', 'jhonabcd');

It will generate the string:

WHERE `name`='jhon' AND `username`='jhonabcd'

Custom Key and Value Method

In custom key value methods we can use MySql operators for comparison and selecting our row. This gives us flexibility of comparing rows in codeigniter. See example below

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

$this->db->where('username', 'jhonabcd');

This will generate the query:

WHERE `name`!='robert' AND `username`='jhonabcd'

Associative Array Method

This method allows us to pass array to our where() clause and the query builder will align the array forming our SQL query. See example below

$data=array('name!='=> 'robert', 'username'=> 'jhonabcd');

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

This will generate the query:

WHERE `name`!='robert' AND `username`='jhonabcd'

The Custom Query Method

We can pass your custom query to codeigniter where() method and it will use our custom query for comparison. See the example below

$this->db->where("`name`!='robert' AND `username`='jhonabcd'");

This will generate the query:

WHERE `name`!='robert' AND `username`='jhonabcd'

Codeigniter or_where() method “$this->db->or_where()”

The codeigniter or_where() method allows us to use “OR” clause in our SQL query. It can be used in following manner.

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

$this->db->or_where('username', 'flying');

The above codeigniter syntax will generate the following query:

WHERE `name`!='robert' OR `username` ='flying'

Codeigniter where_in() Method “$this->db->where_in()”

The codeigniter where_in() method can be used to match specific values, we can also use it with our where() clauses.

$data=array('robin', 'taylor');

$this->db->where_in('username', $data);

The above codeigniter code will generate the following query:

WHERE username IN ('robin', 'taylor')

Codeigniter or_where_in() Method “$this->db->or_where_in()”

The codeigniter or_where_in() method allows us to let generate a SQL WHERE IN query with OR clause. See the example below.

$data=array('robin', 'taylor');

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

$this->db->or_where_in('username', $data);

The above codeigniter codes will generate the following SQL query:

WHERE `id`='4' OR `username` IN ('robin', 'taylor')

Codeigniter or_where_not_in() method “$this->db->or_where_not_in()”

The codeigniter or_where_not_in() method allows us let prepend our SQL “IN” request with NOT. See example below

$data=array(‘robin’, ‘taylor’);

$this->db->where(‘id’, ‘4’);

$this->db->or_where_not_in(‘username’, $data);

The above codeigniter code will generate the following SQL query:

WHERE `id`=’4′ OR `username` NOT IN (‘robin’, ‘taylor’)

That’s all for this tutorial folks, in next tutorial we will start up with codeigniter forms, and learn how we can create and process forms using codeigniter. If you have any suggestions or questions please do comment below. If you found this tutorial helpful then share with your friends.

Comments: 1

Your email address will not be published. Required fields are marked with *