Codeigniter Tutorials

Home » Codeigniter »

How to insert data in database?

The $data is an array in which we have set the data and to insert this data to the table employee, we just need to pass this array to the insert function in the 2nd argument.

$data = array(
'id' => '101',
'name' => 'mahtab',
'address' => 'delhi'
);

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

Example 2 : By Form submit

File name : index.php

<!DOCTYPE html>
<html>
<head>
<title>Baby Form Add</title>
</head>
<body>
<form method="post" action="<?php echo site_url('baby_form/savingdata'); ?>">
<table>
<tr>
<td>Name:</td>
<td>:</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>Meaning:</td>
<td>:</td>
<td><input type="text" name="meaning"></td>
</tr>
<tr>
<td>Gender:</td>
<td>:</td>
<td><input type="text" name="gender"></td>
</tr>
<tr>
<td>Religion:</td>
<td>:</td>
<td><input type="text" name="religion"></td>
</tr><br><br>
<tr>
<input type="submit" name="submit" value="Save">
</tr>
</table>
</form>
</body>
</html>

controller class

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Baby_form extends CI_Controller {

public function index()
{
$this->load->view("baby_form_add");
}
function savingdata()
{
//this array is used to get fetch data from the view page.
$data = array(
'name' => $this->input->post('name'),
'meaning' => $this->input->post('meaning'),
'gender' => $this->input->post('gender'),
'religion' => $this->input->post('religion')
);
//insert data into database table.
$this->db->insert('baby',$data);

redirect("baby_form/index");
}
}
?>

Updating a Record

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

$data = array(
'roll_no' => ‘1’,
'name' => ‘Virat’
);

$this->db->set($data);
$this->db->where("roll_no", ‘1’);
$this->db->update("stud", $data);

Deleting a Record

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

Use the following code to to delete a record in the stud table. The first argument indicates the name of the table to delete record and the second argument decides which record to delete.

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

Selecting a Record

To select a record in the database, the get function is used, as shown in the following table.

Use the following code to get all the records from the database. The first statement fetches all the records from “stud” table and returns the object, which will be stored in $query object. The second statement calls the result() function with $query object to get all the records as array.

$query = $this->db->get("stud");
$data['records'] = $query->result();

Closing a Connection

Database connection can be closed manually, by executing the following code −

$this->db->close();

Complete Example :-

Create a controller class called Stud_controller.php and save it at application/controller/Stud_controller.php

<?php
class Stud_controller extends CI_Controller {

function __construct() {
parent::__construct();
$this->load->helper('url');
$this->load->database();
}

public function index() {
$query = $this->db->get("stud");
$data['records'] = $query->result();

$this->load->helper('url');
$this->load->view('Stud_view',$data);
}

public function add_student_view() {
$this->load->helper('form');
$this->load->view('Stud_add');
}

public function add_student() {
$this->load->model('Stud_Model');

$data = array(
'roll_no' => $this->input->post('roll_no'),
'name' => $this->input->post('name')
);

$this->Stud_Model->insert($data);

$query = $this->db->get("stud");
$data['records'] = $query->result();
$this->load->view('Stud_view',$data);
}

public function update_student_view() {
$this->load->helper('form');
$roll_no = $this->uri->segment('3');
$query = $this->db->get_where("stud",array("roll_no"=>$roll_no));
$data['records'] = $query->result();
$data['old_roll_no'] = $roll_no;
$this->load->view('Stud_edit',$data);
}

public function update_student(){
$this->load->model('Stud_Model');

$data = array(
'roll_no' => $this->input->post('roll_no'),
'name' => $this->input->post('name')
);

$old_roll_no = $this->input->post('old_roll_no');
$this->Stud_Model->update($data,$old_roll_no);

$query = $this->db->get("stud");
$data['records'] = $query->result();
$this->load->view('Stud_view',$data);
}

public function delete_student() {
$this->load->model('Stud_Model');
$roll_no = $this->uri->segment('3');
$this->Stud_Model->delete($roll_no);

$query = $this->db->get("stud");
$data['records'] = $query->result();
$this->load->view('Stud_view',$data);
}
}
?>

Model class :-

Create a model class called Stud_Model.php and save it in application/models/Stud_Model.php

<?php
class Stud_Model extends CI_Model {

function __construct() {
parent::__construct();
}

public function insert($data) {
if ($this->db->insert("stud", $data)) {
return true;
}
}

public function delete($roll_no) {
if ($this->db->delete("stud", "roll_no = ".$roll_no)) {
return true;
}
}

public function update($data,$old_roll_no) {
$this->db->set($data);
$this->db->where("roll_no", $old_roll_no);
$this->db->update("stud", $data);
}
}
?>

view class :-

Create a view file called Stud_add.php and save it in application/views/Stud_add.php

<!DOCTYPE html>
<html lang = "en">

<head>
<meta charset = "utf-8">
<title>Students Example</title>
</head>
<body>
<?php
echo form_open('Stud_controller/add_student');
echo form_label('Roll No.');
echo form_input(array('id'=>'roll_no','name'=>'roll_no'));
echo "<br/>";

echo form_label('Name');
echo form_input(array('id'=>'name','name'=>'name'));
echo "<br/>";

echo form_submit(array('id'=>'submit','value'=>'Add'));
echo form_close();
?>
</body>
</html>

View Edit

Create a view file called Stud_edit.php and save it in application/views/Stud_edit.php

<!DOCTYPE html>
<html lang = "en">

<head>
<meta charset = "utf-8">
<title>Students Example</title>
</head>

<body>
<form method = "" action = "">

<?php
echo form_open('Stud_controller/update_student');
echo form_hidden('old_roll_no',$old_roll_no);
echo form_label('Roll No.');
echo form_input(array('id'⇒'roll_no',
'name'⇒'roll_no','value'⇒$records[0]→roll_no));
echo "
";

echo form_label('Name');
echo form_input(array('id'⇒'name','name'⇒'name',
'value'⇒$records[0]→name));
echo "
";

echo form_submit(array('id'⇒'sub mit','value'⇒'Edit'));
echo form_close();
?>

</form>
</body>

</html>

view

Create a view file called Stud_view.php and save it in application/views/Stud_view.php

<!DOCTYPE html>
<html lang = "en">

<head>
<meta charset = "utf-8">
<title>Students Example</title>
</head>

<body>
<a href = "<?php echo base_url(); ?>
index.php/stud/add_view">Add</a>

<table border = "1">
<?php
$i = 1;
echo "<tr>";
echo "<td>Sr#</td>";
echo "<td>Roll No.</td>";
echo "<td>Name</td>";
echo "<td>Edit</td>";
echo "<td>Delete</td>";
echo "<tr>";

foreach($records as $r) {
echo "<tr>";
echo "<td>".$i++."</td>";
echo "<td>".$r->roll_no."</td>";
echo "<td>".$r->name."</td>";
echo "<td><a href = '".base_url()."index.php/stud/edit/"
.$r->roll_no."'>Edit</a></td>";
echo "<td><a href = '".base_url()."index.php/stud/delete/"
.$r->roll_no."'>Delete</a></td>";
echo "<tr>";
}
?>
</table>

</body>

</html>

Make the following change in the route file at application/config/routes.php and add the following line at the end of 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 us execute this example by visiting the following URL in the browser. Replace the yoursite.com with your URL.

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

Itechtuto