In this example we will discuss about how to retrieve a record or data from MySQL database using CodeIgniter framework PHP.
For retrieve data from MySQL database using CodeIgniter framework first we have to create a table in data base.
After create a table in the MySQL database you need to insert record or data on it.If you want to know how to insert data in CodeIgniter framework please visit the link : Insert data in CodeIgniter.
The SELECT statement is used to retrieve data from one or more tables:
The SQL query for retrieve specific column.
SELECT column_name(s)
FROM table_name
or we can use the * character to retrieve ALL columns from a table:
SELECT *
FROM table_name
To learn more about SQL, please visit our SQL tutorial.
We use 3 file for retrieve students data.
CREATE TABLE crud (
`id` int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`email` varchar(30) NOT NULL
);
<?php
class Crud extends CI_Controller
{
public function __construct()
{
/*call CodeIgniter's default Constructor*/
parent::__construct();
/*load database libray manually*/
$this->load->database();
/*load Model*/
$this->load->model('Crud_model');
}
/*Display*/
public function displaydata()
{
$result['data']=$this->Crud_model->display_records();
$this->load->view('display_records',$result);
}
}
?>
<?php
class Crud_model extends CI_Model
{
/*View*/
function display_records()
{
$query=$this->db->get("crud");
return $query->result();
}
}
<html>
<head>
<title>Display records</title>
<style>
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
</style>
</head>
<body>
<table width="600" border="0" cellspacing="5" cellpadding="5">
<tr style="background:#CCC">
<th>Sr No</th>
<th>First_name</th>
<th>Last_name</th>
<th>Email Id</th>
</tr>
<?php
$i=1;
foreach($data as $row)
{
echo "<tr>";
echo "<td>".$i."</td>";
echo "<td>".$row->first_name."</td>";
echo "<td>".$row->last_name."</td>";
echo "<td>".$row->email."</td>";
echo "</tr>";
$i++;
}
?>
</table>
</body>
</html>
Now run the program on your browser with the below URL:
http://localhost/codeIgniter/index.php/Crud/displaydata
After fetch data the table look like this.
| Id | first name | last name | Email Id |
|---|---|---|---|
| 1 | Divyasundar | Sahu | divyasundar@gmail.com |
| 2 | Hritika | Sahu | hritika@gmail.com |
| 3 | Milan | Jena | milanjena@gmail.com |