Database connectivity in CodeIgniter
This is a very important section to create any web application. This is the base. We have to create a database and connect it with the model, to SELECT, INSERT, DELETE or UPDATE the data included in database tables using queries.
First you need to create a database in localhost/phpmyadmin. Then configure the file application/config/database.php file, as I had mentioned in my first CodeIgniter article. Set your hostname, username, password and database. Do not think about the other configurations and keep them as it is. My database is "ci" and I have already set it. In this article I'm going to retrieve some data from a table in the database and display it. So create a table called users or any other name. Structure is given below.
Insert the data of 2 or 3 users and save them. Later we can retrieve them.
Use the controller file - User_data.php and view file - user_view.php that I used in previous article. Edit the view and make it with no content in the table. Here, userArray is the array I used to fetch and store the data. If you are confused, read the article; How to pass data from Model to View. The codes are like this.
User_data.php
<?php
class User_data extends CI_Controller {
public function index() {
$this->load->model('user_model');
$data["userArray"] = $this->user_model->return_data();
$this->load->view("user_view",$data);
}
}
?>
user_view.php
<!DOCTYPE html>
<html>
<head>
<title>User Details</title>
</head>
<body>
<table>
<tr>
<th></th>
<th></th>
<th></th>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
</table>
</body>
</html>
Next create the the model file called User_model.php In this file, first load the database function. Then create a SQL query to get the data. Use a print_r($queryName) to check whether db is connected or not. It will output the following if there are no errors.
<?php
class User_model extends CI_Model {
public function return_data() {
$this->load->database();
$query = $this->db->query("SELECT * FROM users");
print_r($query);
}
}
?>
Call the controller; user_data in your browser.
Output :
Then comment out that print_r line and include he following two lines after the declaring of query. Here, result_array is a function in CodeIgniter to get the data into an array. Then print it to display the data in an array.
$query->result_array();
print_r($query->result_array());
When we call with this code, output will be like this.
To look these results in a more formatted way, <pre> tag can be used in the model file. How to use?
Edit the model like this .
<?php
class User_model extends CI_Model {
public function return_data() {
$this->load->database();
$query = $this->db->query("SELECT * FROM users");
$query->result_array();
echo "<pre>";
print_r($query->result_array());
echo "</pre>";
}
}
?>
Again call the controller; user_data in your browser.
Then we need to arrange the view as we want. We have to use foreach loop to get the data into a loop and print row by row in the table.
<!DOCTYPE html>
<html>
<head>
<title>User Details</title>
</head>
<body>
<table>
<tr>
<th>ID</th>
<th>Username</th>
<th>Email</th>
</tr>
<?php
foreach ($userArray as $key => $value) {
echo "<pre>";
print_r($value);
echo "<pre>";
}
?>
</table>
</body>
</html>
Then comment out the highlighted 3 lines of codes in the controller and place the following line after the $query->result_array(); line, to see the results in an array. No need to change the view. Then try to call for the controller in the browser.
return $query->result_array();
Output :
Final model code:
<?php
class User_model extends CI_Model {
public function return_data() {
$this->load->database();
$query = $this->db->query("SELECT * FROM users");
$query->result_array();
return $query->result_array();
}
}
?>
Now, we need to output the formatted result as a table. So place the follwing code within the foreach loop in the view.
echo "<tr>
<td>".$value['id']."</td>
<td>".$value['username']."</td>
<td>".$value['email']."</td>
</tr>";
Final view code :
<!DOCTYPE html>
<html>
<head>
<title>User Details</title>
</head>
<body>
<table>
<tr>
<th>ID</th>
<th>Username</th>
<th>Email</th>
</tr>
<?php
foreach ($userArray as $key => $value) {
echo "<tr>
<td>".$value['id']."</td>
<td>".$value['username']."</td>
<td>".$value['email']."</td>
</tr>";
}
?>
</table>
</body>
</html>
We simply echo <tr> and <td> as this is a PHP code unlike HTML. Then values of the keys are inserted into <td> tags. Be careful to use commas correctly. If you place a single comma wrong, output will be an error.
Final output :
So this is the end of the article. We connected the files with a database and fetched the data in a table into a view file. I think you have got a clear idea about working with MVC architechture.
Try it!
0 Comments