Active Record in CodeIgniter | PHP Gurukul

CodeIgniter uses a modified version of the Active Record Database Pattern. This pattern allows information to be retrieved, inserted, and updated in your database with minimal scripting. In some cases only one or two lines of code are necessary to perform a database action. CodeIgniter does not require that each database table be its own class file. It instead provides a more simplified interface.

Selecting data

All of the functions in this section will build SQL SELECT queries. All of the SQL in this section is MySQL; other database systems may differ slightly.

$this->db->get();

The simplest query that you can do with Active Record is to select a full database table. This is done with one single function:

1

$this->db->get();

This would create the SQL query:

1

SELECT * FROM `table_name`;

This function has three parameters. The first is the name of the database table. The second lets you set a limit, and the third lets you set an offset.

1

$query = $this->db->get('table_name', 10, 20);

This would then produce the SQL query:

1

SELECT * FROM `table_name` LIMIT 20, 10;

$this->db->get_where();

This function works in much the same way as the previous function. The only difference is that the second parameter should be passed as an array. The array should have the name of the field and the value to use to fill in the WHERE part of your query.

1

$query = $this->db->get('table_name', array('id' => $id), 10, 20);

This would produce the following SQL query:

1

SELECT * FROM 'table_name' WHERE 'id' = $id LIMIT 10, 20;

$this->db->select();

This function allows you to write the SELECT portion of your query. Take a look at the following example:

1

2

$this->db->select('name, username, email');

$query = $this->db->get('users');

The SQL query produced from this function will be :

1

SELECT name, username, email FROM `users`;

You should take note that when using this function, and any of the other functions that let you write a portion of your query, that you still need to use the get() function to actually produce and run the query.

If you are selecting everything form your database (*) then you do not need to use this function as CodeIgniter assumes that you mean to select everything.

$this->db->from();

This function allows you to write the FROM portion of your query. This is basically the same as using the get() function, although it is slightly more readable. You can use whichever method you prefer.

1

2

$this->db->from('table_name');

$query = $this->db_>get();

$this->db->join();

This function lets you write the JOIN part of your query. Here’s an example:

1

2

3

4

$this->db->select('*');

$this->db->from('blogs');

$this->db->join('comments', 'comments.id = blogs.id');

$query = $this->db->get();

You can specify a different type of join in the third parameter. You can choose from left, right, outer, inner, left outer, and right outer.

1

$this->db->join('comments', 'comments.id = blogs.id', 'left');

$this->db->where();

This function is used to build the WHERE portion of your query. This function can be used in a variety of ways.

Single key or value method

1

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

Multiple key or value method

1

2

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

$this->db->where('email', $email);

Associative array method

You can pass values to the where() function by using an associative array. Take a look at the following example:

1

2

$array = array('name' => $name, 'email' => $email);

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

You can include operators in the array, just as you would use in the first parameter.

1

2

$array = array('name !=' => $name, 'email' => $email);

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

$this->db->like();

This function allows you to write the LIKE portion of your query, and functions in almost the exact same way as the where() method.

Single key or value method

This method is the same as the where() method, when used as follows:

1

$this->db->like('name', $name);

Multiple key or value method

This method is also the same as the where() method. Multiple calls will be chained together.

1

2

$this->db->like('name', $name);

$this->db->like('email', $email);

$this->db->group_by();

This function lets you write the GROUP BY portion of your query.

1

2

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

$this->db->group_by(array('name', 'title'));

$this->db->order_by();

This lets you write the ORDER BY portion of your query. The first parameter is for your field name. The second is the type of order that you want to use, and can be asc, desc, or random.

1

$this->db->order_by('name', 'desc');

You can also pass a string as the first parameter.

1

$this->db->order_by('name desc, title asc');

Multiple function calls can also be used, as for other functions.

1

2

$this->db->order_by('name', 'desc');

$this->db->order_by('title', 'asc');

$this->db->limit();

This function lets you add a LIMIT specification to your query. The first parameter will be the number to limit to, and the second parameter let’s you set an offset.

1

$this->db->limit(10, 20);

Click here : https://phpgurukul.com/active-record-in-codeigniter/

Inserting data

Inserting data using Active Record is a very simple process, and there are just two functions that you may need to use in order to insert data into your database.

1

$this->db->insert();

This will generate an insert string based upon the data that you supply to it. The first parameter is the name of the table that you want to add the data to, and the second parameter can either be an array or an object of the data.

1

2

3

$data = array('name' => 'Bob Smith',

'email' => '[email protected]');

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

This would then produce the following SQL statement:

1

2

INSERT INTO mytable (name, email)

VALUES ('Bob Smith', '[email protected]');

$this->db->update();

This will generate an update string based upon the data that you supply to it.

The first parameter is the name of the table you want to add the data to, and the second parameter can either be an array or an object of the data. The third,

optional parameter enables you to set the WHERE clause of your SQL query.

1

2

3

$data = array('name' => 'Bob Smith','email' => '[email protected]');

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

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

This would then produce the following SQL statement:

1

UPDATE mytable SET name = 'Bob Smith', email = '[email protected]';

You can optionally use the where() method to set the where clause of the query. Here’s how you would use the third parameter to set the WHERE clause:

1

2

$data = array('name' => 'Bob Smith','email' => '[email protected]');

$this->db->update('table_name', $data, 'id = 5');

$this->db->delete();

This function accepts two parameters. The first is the name of the table, and the second should be an array from which to build the WHERE clause.

1

$this->db->delete('table_name', array('id' => 5));

You can also use the where() function to build the WHERE clause:

1

2

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

$this->db->delete('table_name');

An array of table names can be passed into this function, if you wish to delete more than one table.

1

2

3

$tables = array('table1', 'table2', 'table3');

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

$this->db->delete($tables);

Source https://www.codeigniter.com/userguide2/database/active_record.html

PHP Gurukul

Welcome to PHPGurukul. We are a web development team striving our best to provide you with an unusual experience with PHP. Some technologies never fade, and PHP is one of them. From the time it has been introduced, the demand for PHP Projects and PHP developers is growing since 1994. We are here to make your PHP journey more exciting and useful.

Website : https://phpgurukul.com