How to insert data in MySQL table using CodeIgniter

  • Post author:
  • Post category:CodeIgniter
  • Post comments:0 Comments
  • Post last modified:October 5, 2020
  • Reading time:6 min(s) read

In this tutorial, we are going to learn how to insert data in MySQL table using CodeIgniter framework. In the previous blog, we have already discussed how to download Codeigniter and basic set up on your computer. You can also see the youtube tutorial Codeigniter download and basic set up and How to remove index.php from URL in Codeigniter | .htaccess | BeAppend. You just have to understand the MVC architecture step by step and understand the code line by line.

First, you have to create a database in http://localhost/phpmyadmin/,then just create your desired table. The database structure is given below :

-- phpMyAdmin SQL Dump
-- version 5.0.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jul 08, 2020 at 06:27 PM
-- Server version: 10.4.13-MariaDB
-- PHP Version: 7.4.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `ci`
--

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `number` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`, `number`, `created`) VALUES
(1, 'test1', '1234567890', '2020-06-27 22:26:53'),
(2, 'ghjhjh,kjkjkj', '1478522366965', '2020-06-27 22:27:24'),
(3, 'tnmy', '1234567890', '2020-06-27 22:27:58'),
(4, 'jonty', '1478528663', '2020-07-06 20:43:27'),
(5, 'jonty', '1478528663', '2020-07-06 20:43:55'),
(6, 'Bimal', '1478523698', '2020-07-06 20:44:21'),
(7, 'Bimal', '1478523698', '2020-07-06 20:46:42'),
(8, 'Somnath Bhunia', '7485961412', '2020-07-06 20:46:56'),
(9, 'Somnath Bhunia', '7485961412', '2020-07-06 22:08:47');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
COMMIT;

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
insert data in MySQL table using CodeIgniter

Now, Connect your database, to do so you have to go to myci\application\config\database.php file and fill your database credential(ie: you have to modify only three fields hostname, username, password, and a database name) like the below code:

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',//user name in localhost by default root
	'password' => '',///it will be blank becuse we have not set password while creating database
	'database' => 'ci',//this is our database name
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);

After that, create a form(example: form.php) in the view folder i.e: myci\application\views See the code below:

<!DOCTYPE html>
<html>
<head>
<title>CODEIGNITER FROM FOR DATA INSERT</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">  
<div class="card">
<div class="card-header">My Form</div>
<div class="card-body">
<form action="<?php echo base_url() ?>New_con/data_insert" method="post">
<div class="row">
<div class="col-md-6">
<div class="form-group">
<label>Name</label>
<input type="text" name="name" class="form-control" placeholder="Enter Your Name" required>
</div>
</div>
<div class="col-md-6">
<div class="form-group">
<label>Number</label>
<input type="tel" name="phone" class="form-control" placeholder="Enter Your phone number" required>
</div>
</div>
</div>
<input type="submit" name="submit" value="Save" class="btn btn-success form-group">
</form>
</div>
</div>
</div>
</body>
</html>

Now, create a function/method in your controller file to load the view. If you don’t know how to create a controller Please click here.

To load from view just create a function like below code:

public function form()
{
$this->load->view('form');///here we loaded your form.php file//
}

Now our form action is <?php echo base_url() ?>New_con/data_insert, to insert data you have to create a function in your controller like below code :

public function data_insert()
	{
		$name=$this->input->post('name');
		$phone=$this->input->post('phone');

		$capsule = array('name' =>$name ,'number'=>$phone );

		$msg=$this->My_model->save($capsule);

		echo $msg;

		$this->form();
	}

If you are submitting your form using post method then you have to type your code like below:

$name=$this->input->post('name'); ///name is your form input name and $name is just a variable to store the data

Now you have to encapsulate your data by using an array. In the array, the column-name of your data table will be in the single quote [ie: ‘name’] followed by a fat arrow (=>) then the individual variable in which you stored respective data. See the code below :

$capsule = array('name' =>$name ,'number'=>$phone );

Thereafter, you have to pass this data to your model like this:

 $this->My_model->save($capsule);

In the above code, My_model is our model name, and save() is the function within the My_model class.

To create a model you have to go to myci\application\models folder, then create a dot(.)PHP file. Keep in mind that the first letter of your file name should be in capital letters.(example: My_model.php).

So here is the complete  model code:

<?php 


class My_model extends CI_Model
{
	
 	public function save($capsule)
 	{
 		$insert=$this->db->insert('users',$capsule);
 		if ($insert) {
 		return $msg="Data Inserted Successfully";
 		}
 	}

} ?>

In the Codeigniter framework, we have to use the query builder functions. To insert data into a database you have to type only the below code:

$this->db->insert('users',$capsule);

In the above-mentioned code, $capsule is the parameter that has been sent by data_insert() function from the controller.

Here is the complete controller code insert data in MySQL table using CodeIgniter:
<?php
/**
 * 
 */
class New_con extends CI_Controller
{
	
	public function __construct()
	{
		parent:: __construct();

		$this->load->helper('url');
		$this->load->database();
		$this->load->model('My_model');
	}



	public function form()
	{
		$this->load->view('form');
	}
	public function data_insert()
	{
		$name=$this->input->post('name');
		$phone=$this->input->post('phone');

		$capsule = array('name' =>$name ,'number'=>$phone );

		$msg=$this->My_model->save($capsule);

		echo $msg;

		$this->form();
	}

}
?>

For source code mail us at [email protected].

Leave a Reply