CodeIgniter - Working with the database

CodeIgniter - Working with the database

Like any other framework, we need to interact with the database very often, and CodeIgniter makes this job easy for us. It provides a rich set of features for interacting with the database.

In this section, we will understand how the CRUD (Create, Read, Update, Delete) functions work with CodeIgniter. We will use the study table to select, update, delete, and insert data into the study table.

Database connection

We can connect to the database in two ways:

• Automatic connection. Auto connection can be done using the application/config/autoload.php file. Auto connect will load the database for each page. We just need to add the database library as shown below −

Automatic connection. Auto connection can be done using the application/config/autoload.php file. Auto connect will load the database for each page. We just need to add the database library as shown below −

$autoload['libraries'] = array('database');

• Manual Connection - If you only want to connect to the database for some pages, then we can move on to connecting manually. We can connect to the database manually by adding the following line in any class.

Manual Connection - If you only want to connect to the database for some pages, then we can move on to connecting manually. We can connect to the database manually by adding the following line in any class.

$this->load->database();

We are not passing any arguments here because everything is set in the database configuration file application/config/database.php

Insert an entry

To insert a record into the database, the insert() function is used, as shown in the following table:

Syntax

parameters

$table ( string ) - table name

$ set ( array ) — Associative array of field/value pairs

$escape ( bool ) - whether to escape values ​​and ids

returns

return type

The following example shows how to insert a record into the stud table . $data is the array where we have set the data, and in order to insert this data into the table pivot , we just need to pass this array to the insert function in the ^second argument.

$data = array(

   'roll_no' => '1',

   'name' => 'Virat'

);

$this->db->insert("stud", $data);

Record update

To update a record in the database, the update() function is used along with the set() and where() functions as shown in the tables below. The set() function will set the data to be updated.

Syntax

parameters

$ key ( mixed ) - field name or array of field/value pairs

$value ( string ) — Field value if $key is a single field

$escape ( bool ) - whether to escape values ​​and ids

returns

return type

The where() function decides which record to update.

Syntax

parameters

$ key ( mixed ) — Field name to compare, or an associative array

$value ( mixed ) - if one key, compared to this value

$escape ( bool ) - whether to escape values ​​and ids

returns

return type

Finally, the update() function will update the data in the database.

Syntax

parameters

$table ( string ) - table name

$ set ( array ) — Associative array of field/value pairs

$where ( string ) - WHERE clause

$limit ( int ) — LIMIT clause

returns

return type

$data = array(

   'roll_no' => '1',

   'name' => 'Virat'

);

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

$this->db->where("roll_no", '1');

$this->db->update("stud", $data);

Deleting an entry

To delete a record in a database, the delete() function is used, as shown in the following table:

Syntax

parameters

$ table ( mixed ) - The table(s) to delete; string or array

$where ( string ) - WHERE clause

$limit ( int ) — LIMIT clause

$reset_data ( bool ) - TRUE to reset the "write" clause of the request

returns

return type

Use the following code to delete an entry in the stud table . The first argument specifies the name of the table to delete the record, and the second argument decides which record to delete.

$this->db->delete("stud", "roll_no = 1");

Record selection

The get function is used to select a record in the database , as shown in the following table:

Syntax

parameters

$ table ( string ) - table to query the array

$limit ( int ) — LIMIT clause

$offset ( int ) — OFFSET clause

returns

return type

Use the following code to get all records from the database. The first statement selects all records from the "stud" table and returns an object that will be stored in the $query object. The second statement calls the result() function with the $query object to get all records as an array.

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

$data['records'] = $query->result();

Closing a connection

The database connection can be closed manually by executing the following code −

$this->db->close();

example

Create a controller class named Stud_controller.php and save it in application/controller/Stud_controller.php

Here is a complete example in which all of the above operations are performed. Before running the following example, create a database and table as instructed at the beginning of this chapter and make the necessary changes to the database configuration file stored in application/config/database.php

Create a model class named Stud_Model.php and store it in application/models/Stud_Model.php

Create a view file named Stud_add.php and save it to application/views/Stud_add.php

      <metacharset="utf-8">

         ";

            echo form_label('Name');

            echo form_input(array('id'=>'name','name'=>'name'));

            echo "
";

            echo form_submit(array('id'=>'submit','value'=>'Add'));

            echo form_close();

         ?>

Create a view file named Stud_edit.php and save it to application/views/Stud_edit.php

      <metacharset="utf-8">

Create a view file named Stud_view.php and save it to application/views/Stud_view.php

      <metacharset="utf-8">

      Add

Make the following changes to the route file in application/config/rout.php and add the following line to the end of the file.

$route['stud'] = "stud_controller";

$route['stud/add'] = 'Stud_controller/add_student';

$route['stud/add_view'] = 'Stud_controller/add_student_view';

$route['stud/edit/(\d+)'] = 'Stud_controller/update_student_view/$1';

$route['stud/delete/(\d+)'] = 'Stud_controller/delete_student/$1';

Now let's execute this example by visiting the following URL in a browser. Replace yoursite.com with your URL.

http://yoursite.com/index.php/stud