In this  tutorial we are going to explain you  how to perform CodeIgniter select query to fetch data from database using date range and ID.

In this demo we are  using input box and date selector. In input box you can enter an ID which you want to search and  using date selector you can search data for a particular date or for a date range.

Now one can easy find a records between the two given date ranges.

Watch our live demo or download the Script file from below link, extract files and run on your Local server.

-: See Also :-

CodeIgniter Insert Data into Database
CodeIgniter Update Data In Database
CodeIgniter Delete Data From Database

 


 Watch the live demo or download code from the link given below


How to run file:

http://localhost/select_demo/index.php/select_tutorial

 


Tutorial Scripts in detail

Below are the details of the code used in this tutorial with proper explanation.

My SQL Code Segment:

To create database and table, execute following codes in your My SQL.


//First create database
CREATE DATABASE `select_database`;
//Then create table
CREATE TABLE  `employee_info` (
`emp_id` int(11) NOT NULL AUTO_INCREMENT,
`emp_name` varchar(255) NOT NULL,
`emp_date_of_join` date NOT NULL,
`emp_address` varchar(255) NOT NULL,
`emp_mobile` varchar(255) NOT NULL,
PRIMARY KEY (`emp_id`)
)

 

Controllers : select_tutorial.php

copy the below code in your controller.

<?php

if (!defined('BASEPATH'))
exit('No direct script access allowed');

class Select_Tutorial extends CI_Controller {

public function __construct() {
parent::__construct();
$this->load->model('employee_database');
}

public function index() {
$data['show_table'] = $this->view_table();
$this->load->view('select_form', $data);
}
public function view_table(){
$result = $this->employee_database->show_all_data();
if ($result != false) {
return $result;
} else {
return 'Database is empty !';
}
}

public function select_by_id() {
$id = $this->input->post('id');
if ($id != "") {
$result = $this->employee_database->show_data_by_id($id);
if ($result != false) {
$data['result_display'] = $result;
} else {
$data['result_display'] = "No record found !";
}
} else {
$data = array(
'id_error_message' => "Id field is required"
);
}
$data['show_table'] = $this->view_table();
$this->load->view('select_form', $data);
}

public function select_by_date() {
$date = $this->input->post('date');
if ($date != "") {
$result = $this->employee_database->show_data_by_date($date);

if ($result != false) {
$data['result_display'] = $result;
} else {
$data['result_display'] = "No record found !";
}
} else {
$data['date_error_message'] = "Date field is required";
}
$data['show_table'] = $this->view_table();
$this->load->view('select_form', $data);
}

public function select_by_date_range() {
$date1 = $this->input->post('date_from');
$date2 = $this->input->post('date_to');
$data = array(
'date1' => $date1,
'date2' => $date2
);
if ($date1 == "" || $date2 == "") {
$data['date_range_error_message'] = "Both date fields are required";
} else {
$result = $this->employee_database->show_data_by_date_range($data);
if ($result != false) {
$data['result_display'] = $result;
} else {
$data['result_display'] = "No record found !";
}
}
$data['show_table'] = $this->view_table();
$this->load->view('select_form', $data);
}

}

 

Views : select_form.php

Copy the below code in your view.

<html>
<head>
<title>CodeIgniter Select Demo</title>
<link rel="stylesheet" type="text/css" href="<?php echo base_url(); ?>css/style.css">
<link href='http://fonts.googleapis.com/css?family=Source+Sans+Pro|Open+Sans+Condensed:300|Raleway' rel='stylesheet' type='text/css'>
</head>

<body>
<div id="main">
<div id="note"><span><b>Note : </b></span> In this DEMO we have used a default table for record. </div>
<div class="message">
<?php
if (isset($read_set_value)) {
echo $read_set_value;
}
if (isset($message_display)) {
echo $message_display;
}
?>
</div>

<div id="show_form">
<h2>CodeIgniter Select By ID And Date</h2>
<?php
echo form_open('select_tutorial/select_by_id');
echo form_label('Select By ID : ');
$data = array(
'name' => 'id',
'placeholder' => 'Please Enter ID'
);
echo form_input($data);
echo "<div class='error_msg'>";
if (isset($id_error_message)) {
echo $id_error_message;
}

echo "</div>";
echo form_submit('submit', 'Show Record');
echo form_close();
echo form_open('select_tutorial/select_by_date');
echo form_label('Select By Date : ');
$data = array(
'type' => 'date',
'name' => 'date',
'placeholder' => 'yyyy-mm-dd'
);
echo form_input($data);
echo "<div class='error_msg'>";
if (isset($date_error_message)) {
echo $date_error_message;
}
echo "</div>";
echo form_submit('submit', 'Show Record');
echo form_close();
echo form_open('select_tutorial/select_by_date_range');
echo form_label('Select By Range Of Dates : ');
echo "From : ";

$data = array(
'type' => 'date',
'name' => 'date_from',
'placeholder' => 'yyyy-mm-dd'
);
echo form_input($data);
echo " To : ";

$data = array(
'type' => 'date',
'name' => 'date_to',
'placeholder' => 'yyyy-mm-dd'
);
echo form_input($data);
echo "<div class='error_msg'>";
if (isset($date_range_error_message)) {
echo $date_range_error_message;
}
echo form_submit('submit', 'Show Record');
echo form_close();
?>
<div class="message">
<?php
if (isset($result_display)) {
echo "<p><u>Result</u></p>";
if ($result_display == 'No record found !') {
echo $result_display;
} else {
echo "<table class='result_table'>";
echo '<tr><th>Employee ID</th><th>Employee Name</th><th>Joining Date</th><th>Address</th><th>Mobile</th><tr/>';
foreach ($result_display as $value) {
echo '<tr>' . '<td class="e_id">' . $value->emp_id . '</td>' . '<td>' . $value->emp_name . '</td>' . '<td class="j_date">' . $value->emp_date_of_join . '</td>' . '<td>' . $value->emp_address . '</td>' . '<td class="mob">' . $value->emp_mobile . '</td>' . '<tr/>';
}
echo '</table>';
}
}
?>

</div>
</div>
<?php
if (isset($show_table)) {
echo "<div class='emp_table'>";
if ($show_table == 'Database is empty !') {
echo $show_table;
} else {
echo '<caption>Employee Table</caption>';
echo "<table width='500px'>";
echo '<tr><th class="e_id">Employee ID</th><th>Employee Name</th><th>Joining Date</th><tr/>';
foreach ($show_table as $value) {
echo "<tr>" . "<td class='e_id'>" . $value->emp_id . "</td>" . "<td>" . $value->emp_name . "</td>" . "<td>" . $value->emp_date_of_join . "</td>" . "<tr/>";
}
echo '</table>';
}
echo "</div>";
}
?>
</div>
</body>
</html>

 

Models : employee_database.php

Create new class in your model as shown below.

<?php

if (!defined('BASEPATH'))exit('No direct script access allowed');

class Employee_Database extends CI_Controller {

public function show_all_data() {
$this->db->select('*');
$this->db->from('employee_info');
$query = $this->db->get();
if ($query->num_rows() > 0) {
return $query->result();
} else {
return false;
}
}

public function show_data_by_id($id) {
$condition = "emp_id =" . "'" . $id . "'";
$this->db->select('*');
$this->db->from('employee_info');
$this->db->where($condition);
$this->db->limit(1);
$query = $this->db->get();

if ($query->num_rows() == 1) {
return $query->result();
} else {
return false;
}
}

public function show_data_by_date($date) {
$condition = "emp_date_of_join =" . "'" . $date . "'";
$this->db->select('*');
$this->db->from('employee_info');
$this->db->where($condition);
$query = $this->db->get();
if ($query->num_rows() > 0) {
return $query->result();
} else {
return false;
}
}

public function show_data_by_date_range($data) {
$condition = "emp_date_of_join BETWEEN " . "'" . $data['date1'] . "'" . " AND " . "'" . $data['date2'] . "'";
$this->db->select('*');
$this->db->from('employee_info');
$this->db->where($condition);
$query = $this->db->get();
if ($query->num_rows() > 0) {
return $query->result();
} else {
return false;
}
}

}

 

CSS : style.css

Styling HTML Elements.

#main{
width:960px;
margin:50px auto;
font-family:raleway;
}
span{
color:red;
}
#note{
position: absolute;
left: 291px;
top: 28px;
}
h2{
background-color: #FEFFED;
text-align:center;
border-radius: 10px 10px 0 0;
margin: -10px -40px;
padding: 30px;
}
p{
color : blue;
}
p.no_found{
color : red;
}
hr{
border:0;
border-bottom:1px solid #ccc;
margin: 10px -40px;
margin-bottom: 30px;
}
#show_form{
width:300px;
float: left;
border-radius: 10px;
font-family:raleway;
border: 2px solid #ccc;
padding: 10px 40px 25px;
margin-top: 20px;
}
input[type=text],[type=date]{
width:100%;
padding: 10px;
margin-top: 8px;
border: 1px solid #ccc;
padding-left: 5px;
font-size: 16px;
font-family:raleway;
background-color: #FEFFED;
}
input[type=submit]{
width: 100%;
background-color:#FFBC00;
color: white;
border: 2px solid #FFCB00;
padding: 10px;
font-size:20px;
cursor:pointer;
border-radius: 5px;
margin-bottom: 15px;
}
.message{
position: absolute;
font-weight: bold;
font-size: 28px;
top:300px;
left: 862px;
width: 500px;
text-align: center;
}
.error_msg{
color:red;
font-size: 16px;
}
.emp_table{
position: absolute;
font-weight: bold;
font-size: 28px;
top:30px;
left: 862px;
width: 516px;
text-align: center;
}
table tr td {
border: 1px solid #CCCCCC;
padding : 5px 10px;
}
td.e_id{
padding-left:0;
text-align: center;
}
td.j_date{
width : 100px;
}
caption{
margin-left: 500px;
}
table {
border-collapse: collapse;
}
.result_table{
width : 750px;
margin-left: -120px;
}
th{
border: 1px solid #CCCCCC;
background-color: #FFBC00;
color: white;
padding : 10px 0;
}
.mob{
width:130px;
}

 

Conclusion:

So, this was all about selecting data from database by id or date using CodeIgniter framework. keep following us to learn more.