Tagged: CodeIgniter Database interaction

Data Insertion in CodeIgniter 0

Data Insertion in CodeIgniter

In this tutorial we will learn about how to insert data into database in codeIgniter.
1.Create a database then configure your db credential in application/database.php

$active_group = 'default';
$query_builder = TRUE;
$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost', // your hostname
	'username' => 'root', // your DB username
	'password' => '',     // DB password
	'database' => 'cidb', // your 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
);

2. load some libraries and helpers in application/autoload.php

$autoload['libraries'] = array('database','session');
$autoload['helper'] = array('html','form');
  • Database library used for database related queries
  • Session library  used for display success / error message using flashdata.
  • Html Helper for used for link_tag
  • form helper used for form

First create a  view(registration.php) inside application/view folder

<?php echo form_open('registration',['name'=>'registration'])?>
<!---- Success Message ---->
<?php if ($this->session->flashdata('success')) { ?>
 <p style="color:red; font-size:16px; "><?php echo $this->session->flashdata('success'); ?></p>
<?php } ?>
<!---- Error Message ---->
<?php if ($this->session->flashdata('error')) { ?>
<p style="color:red; font-size:16px;"><?php echo $this->session->flashdata('error'); ?></p>
<?php } ?>
<p>
<?php echo form_label('Enter your Name', 'name'); ?>
<?php echo form_input(['name'=>'fullname','id'=>'fullname','placeholder'=>'Enter you full name','value'=>set_value('fullname')]);?>
<?php echo form_error('fullname',"<div style='color:red'>","</div>");?>
</p>
<p>
<?php echo form_label('Enter your Contact Mobile Number', 'mobnumber'); ?>
<?php echo form_input(['name'=>'mobnumber','id'=>'mobnumber','placeholder'=>'Enter your Contact Mobile Number','value'=>set_value('mobnumber')]);?>
<?php echo form_error('mobnumber',"<div style='color:red'>","</div>");?>
</p>
<p>
<?php echo form_label('Enter your Valid Email id', 'emailid'); ?>
<?php echo form_input(['name'=>'emailid','id'=>'emailid','placeholder'=>'Enter your Valid Email id','value'=>set_value('emailid')]);?>
<?php echo form_error('emailid',"<div style='color:red'>","</div>");?>
</p>
<p>
<?php echo form_label('Select gender','gender'); ?> :
<?php
$options=array(
'Male'=>'Male',
'Female'=>'Female',
'Other'=>'Other'
);
echo form_dropdown('gender',$options);?>
</p>
<p>
<?php echo form_label('Enter your address','address'); ?> <br />
<?php echo form_textarea(['name'=>'address','rows'=>'5','cols'=>'60','id'=>'address','class'=>'form-control','placeholder'=>'Enter your address','value'=>set_value('address')]);?>
<?php echo form_error('address',"<div style='color:red'>","</div>");?>
</p>
<p class="termcondtion">
<?php echo form_checkbox('TermsCondition','accept', TRUE); ?>
<?php echo form_label('Accept terms and conditions','terms'); ?>
</p>
<p class="login button">
<?php echo form_submit(['name'=>'submit','value'=>'Submit']);?>
</p>
</form>
<?php echo form_close();?>

Now create a controller (Registration.php) inside application/controller. You can also set your default controller in application/routes.php

$route['default_controller'] = 'Registration';
<?php
Class Registration extends CI_Controller{
public function index(){
//library form validation . you can also load this libray in application/autoload.php
$this->load->library('form_validation');
$this->form_validation->set_rules('fullname','Full Name','required');
$this->form_validation->set_rules('mobnumber','Mobile Number','required|numeric|exact_length[10]');
$this->form_validation->set_rules('emailid','Email id','required|valid_email');
$this->form_validation->set_rules('address','Address','required');
if($this->form_validation->run()){
$fname=$this->input->post('fullname');
$mnumber=$this->input->post('mobnumber');
$emaild=$this->input->post('emailid');
$gender=$this->input->post('gender');
$adress=$this->input->post('address');
$tc=$this->input->post('TermsCondition');
$this->load->model('Registration_Model');
$this->Registration_Model->insertdata($fname,$mnumber,$emaild,$gender,$adress,$tc);
}
else{
$this->load->view("registration");
}
}

Create a model (Registration_Model.php) inside application/model.

<?php
Class Registration_Model extends CI_Model{
public function insertdata($fname,$mnumber,$emaild,$gender,$adress,$tc){
$data=array(
			'fullName'=>$fname,
			'mobileNumber'=>$mnumber,
			'emailId'=>$emaild,
			'gender'=>$gender,
			'address'=>$adress,
			'termsCondition'=>$tc
		);
$sql_query=$this->db->insert('tblusers',$data);
if($sql_query){
$this->session->set_flashdata('success', 'Registration successful');
redirect('registration');
}
else{
$this->session->set_flashdata('error', 'Somthing went worng. Error!!');
redirect('registration');
}}
}

SQL Table structure for tblusers

CREATE TABLE `tblusers` (
  `id` int(11) NOT NULL,
  `fullName` varchar(150) DEFAULT NULL,
  `mobileNumber` char(12) DEFAULT NULL,
  `emailId` varchar(50) DEFAULT NULL,
  `gender` varchar(30) DEFAULT NULL,
  `address` mediumtext,
  `termsCondition` varchar(200) DEFAULT NULL,
  `postingDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

View Demo———————————————–

How to run this script

  1. Download the zip file .
  2. Extract the zip and copy registrationci folder
  3. Paste in root directory(For xampp htdocs and for wamp www)
  4. Open your browser put http://localhost/phpmyadmin
  5. Create a database cidb
  6. Import sql file tblusers.sql
  7. Run your script http://localhost/registrationci

Data Insertion in CodeIgniter (Download full source code)
Size: 2.76 MB
Version: v 1

Database interaction in CodeIgniter 0

Database interaction in CodeIgniter

Databases are the backbone behind any Web application. Without a database, you’d have nowhere to hold all of your data, and SQL queries can become long and cumbersome to type out. Thankfully, CodeIgniter gives us a brilliantly simple way to interact with our Database. The database library also makes changing between database types—from MySQL to Oracle, for example—easier, because it acts as a wrapper and provides many functions for us to use on the database.
Loading the library
Loading the Database library is slightly different from loading other libraries. This is because it is large and resides in a different folder, unlike the other libraries.

$this->load->database();

Performing simple queries
CodeIgniter gives us a function that we can pass a SQL Query to, and the query will be run on the  database. Here’s how it works:

$this->db->query('PUT YOUR SQL HERE');

This function is simple to use; you simply use this function in place of any native PHP functions you would use to run queries. This function will return TRUE or FALSE for write queries, and will return a dataset for read queries.
There is another function that you can use for very simple queries; this will only return TRUE or FALSE. It won’t let you cache your query or run the query timer.  In most cases you won’t want to use this function.

$this->db->simple_query('PUT YOUR SQL HERE');

The SQL code that you pass to these functions are database-dependent. Only Active Record queries are independent of any type of Database SQL.
Returning values
You can assign the function $this->db->query() to a variable. You can then run a number of helper functions on the variable in order to return the data in different formats. Take the following example:

$query = $this->db->query('SELECT * FROM 'users'')

Returning a result object
In this case, returning the result will return an array of objects, or an empty array if the query failed. You would usually use this function in a foreach loop.

foreach($query->result() as $row)
{ 
echo $row->username; 
echo $row->email;
 }

If your query does not return a result, the CodeIgniter User Guide encourages you to check for a failure before using this function.

if($query->num_rows > 0)
{ 
foreach($query->result() as $row){  
echo $row->username;    
echo $row->email;
}
}

Returning a result array You are also able to return the result dataset as an array. Typically, you would use this function inside a foreach loop as well.

foreach($query->result_array() as $row){
echo $row['username']; 
echo $row['email'];
 }

Returning a row object
If your query is only expected to return a single result, you should return the row by using the following function. The row is returned as an object.

if($query->num_rows() > 0){
$row = $query->row();   
echo $row->username;
echo $row->email;
}

You can return a specific row by passing the row number as a digit in the  first parameter.

$query->row(2);

Returning a row array
You can return a row as an array, if you prefer. The function is used in the same way as the previous example.

if($query->num_rows() > 0){ 
$row = $query->row_array();   
echo $row['username'];
echo $row['email'];
 }

You can return a numbered row by passing the digit to the first parameter, also.

$query->row_array(2);

Result helper functions
Besides the helper function that helps to return the dataset in different ways, there are some other more generalized helper functions.
 Number of rows returned
Used in the same way as the other helper functions, this will return the total number of rows returned from a query. Take the following example:

echo $query->num_rows();

Number of fields returned
Just like the previous function, this will return the number of fields returned by  your query.

echo $query->num_fields();

Free result
This function will remove the resource ID associated with your query, and free the associated memory. PHP will usually do this by default, although when using many queries you may wish to use this to free up memory space.

$query->free_result();