When you are going to create a codeigniter web application using database you can not create it without using the insert, update, delete and retrieve codes of codeigniter. In this tutorial we will show you How to Insert Update Delete Retrieve data using Codeigniter in MySQL Database.
The popular php framework codeigniter is open source and it allows you to insert, update, delete and retrieve your data from the MySQL database using minimum lines of codes. In most of the cases you can perform database action using just two to three lines of code.
Requirements to Start:
If you are already a php developer then you need two things to get started with codeigniter.- localhost server Installation: To start this you need to
install a localhost server in your computer. There are many options on
the internet and you can download any of them. We will
recommend you to use xampp server which is a cross platform
server application.
- Download CodeIgniter: The second thing you need to get started is the codeigniter source file. If you already have the codeigniter source file then you can get started with that. If you don't have the source file then you can download it from their official website - https://www.codeigniter.com/download .You can also Download their Github repository from - https://github.com/bcit-ci/CodeIgniter .
- Set up Basic Codeigniter.
- Creation of MySQL Database.
- Set up a Template.
- Creation of Form.
- Insert Data.
- Creation of Table and Retrieve data.
- Creation of Edit Form and Update Data.
- Delete Data.
Set up Basic Codeigniter :
The first step to start performing insert, update, delete and retrieve your data from the MySQL database is set up codeigniter in localhost or in your web server. First set up your base url in 'config.php' file and set up url and file helper in 'autoload.php' file. On this tutorial we will use MySQL database, so you need to set up database library in 'autoload.php' file and database name, user and password in 'database.php' file. However, if you don't know how to configure base url, helpers and libraries in codeigniter you can check the tutorial - How to Install and Configure Codeigniter.Creation of MySQL Database:
Now open your phpmyadmin database manager and create a MySQL database.After creating a database create a table called - 'user' in your newly created database. You can also copy and pest the bellow code to create a table :
CREATE TABLE `user` ( `name` VARCHAR( 50 ) NOT NULL , `email` VARCHAR( 50 ) NOT NULL , `phone_no` BIGINT( 10 ) NOT NULL , ) ENGINE = MYISAM ;
As you can see we have used 'BIGINT' data type for phone_no field. The maximum value for an INT in MySQL Database is 2147483647 or 4294967295 if unsigned. That is because we use BIGINT instead of INT to store numeric values in MySQL Database.
Set up a Template:
Good look is always important to make your web application more choiceable. You can add custom css codes or you can set up any css frameworks like bootstrap on your codeigniter web application.You can check our previous tutorial on How to add Twitter Bootstrap in PHP CodeIgniter.
Creation of Form:
First go to application>>views folder. In views folder of codeigniter you can see the file - welcome_message.php , open this file from your favourite text editor and Create a HTML form.You can also copy and pest the bellow code in your view file.
<html> <head> <title>User Form</title> </head> <body> <?php echo form_open('welcome/adduser'));?> <h3>User Form</h3> <input type="text" name="name" required> <input type="email" name="email" required> <input type="text" name="phone_no" required> <button type="submit" >Submit</button> </form> </body> </html>
Now go to application>>Controllers , there will be a php file names 'Welcome.php'. Add form and url helper and load the database.
public function __construct() { parent::__construct(); $this->load->database(); $this->load->helper('url'); $this->load->helper('form'); }
Insert Data:
Add bellow code in your 'Welcome.php' controller file to insert data using Codeigniter in MySQL Database.
function user() { $data['name'] = $this->input->post('name'); // storing form field values in an array called $data(). $data['email'] = $this->input->post('email'); // In $data['email'],'email' is the database field name $data['phone_no'] = $this->input->post('phone_no'); $this->db->insert('user', $data); // Inserting data of $data[] array in 'user' table redirect(base_url() . 'index.php/alluser/', 'refresh'); }
Creation of Table and Retrieve data:
To create a table for showing all users data you have to create a new page. To create a new page go to application>>Controllers folder. Open 'Welcome.php' controller and add the bellow code.
function alluser() { $this->load->view('alluser'); }
Now go to application>>Views folder and create a new php file 'alluser.php'. Add the bellow code in 'alluser.php' to create a table and retrieve all data from user table.
<html> <head> <title>All User</title> </head> <body> <h3>All User</h3> <table> <tr> <th>Name</th> <th>Email</th> <th>Phone No</th> <th>Edit</th> <th>Delete</th> </tr> <?php $query = $this->db->get('user')->result_array();// Select Query foreach($query as $row): // Storing retrieved data on an array ?> <tr> <td><?php echo $row['name'];?></td> <td><?php echo $row['email'];?></td> <td><?php echo $row['phone_no'];?></td> <td><a href="<?php echo site_url('Welcome/edituser/'.$row['id']);?>">Edit</a></td> <td><a href="<?php echo site_url('Welcome/deleteuser/'.$row['id']);?>">Delete</a></td> </tr> <?php endforeach;?> </table> </body> </html>As you can see we have added two links, edit and delete on the table. The edit link will take you to the Edit form and the delete link will delete that row.
Creation of Edit Form and Update Data:
To create edit form in a new page go to application>>Views folder and create a new php file 'edituser.php'. Add the bellow code in 'edituser.php' to create a form.Now retrieve all data from user table of database into the respective form fields.<?php $id = $this->uri->segment(3);//getting the value of 3rd segment of url : $row['id'] in previous table. ?> <html> <head> <title>Edit User Form</title> </head> <body> <?php echo form_open('welcome/edituser/edit'.$id));?> // Here edit is the parameter of edituser function <h3>Edit User Form</h3> <input type="text" name="name" value="<?php echo $row['name'];?>" required> <input type="email" name="email" value="<?php echo $row['email'];?>" required> <input type="text" name="phone_no" value="<?php echo $row['phone_no'];?>" required> <input type="submit" name="edit_button" value="Submit"/> </form> </body> </html>
Now go to application>>Controllers folder. Open 'Welcome.php' controller and add the bellow code.
function edituser($param1 = '',$param2 = '') { if ($param1 == 'edit') { $data['name'] = $this->input->post('name'); // storing form field values in an array called $data(). $data['email'] = $this->input->post('email'); // In $data['email'],'email' is the database field name $data['phone_no'] = $this->input->post('phone_no'); $this->db->where('user_id', $param2); $this->db->update('user', $data); // Updating data of $data[] array in 'user' table where user id is $param2 redirect(base_url() . 'index.php/alluser/', 'refresh'); } $this->load->view('edituser'); }
Delete Data:
To delete data from the user table of MySQL database go to application>>Controllers folder. Open 'Welcome.php' controller and add the bellow code.
function deleteuser($param1 = '') { $this->db->where('user_id', $param1); $this->db->delete('user'); }
So these are the 9 simple steps to add Insert Update Delete Retrieve data using Codeigniter in MySQL Database. If you need any further assistance you can freely comment bellow.We love helping our viewers to solve their problems.
We hope our tutorial on 'How to Insert Update Delete Retrieve data using Codeigniter in MySQL Database' will help you design great applications. If you liked this article, then please subscribe our newsletters for daily updates.