CodeIgniter 3 – Converting MySQL Queries to CodeIgniter

This article shows you how to convert your – MySQL query in codeigniter. Here I will show you both MySQL Query and CodeIgniter Query, which are mostly used in our daily programming. You can also comment down which Query you want to convert and I will help you with that.

The first step you need to do is create a table in your database and add some columns to work on them. Here I created a database named CodeIgniter and a table with name users and column names are id,user_name,user_email,user_phone, address, password.

CREATE TABLE users (id int(11) PRIMARY KEY AUTO_INCREMENT , user_name varchar(10) , user_email varchar(40) , user_phone varchar(20) , password varchar(20) , address text)

Insert Data

MySQL

INSERT INTO `users`(`id`, `user_name`, `user_email`, `user_phone`, `password`, `address`) VALUES ('1','vbloggers','[email protected]','123456789','hyderabad','bloggers');

CodeIgniter

Here you need to set your field name and field values in an array. To print your created query you can use the below query. you need to add it after writing the query, here for each query I will comment where you can put it so you can uncomment it and check.

echo $this->db->last_query();die;
$param['user_name']  = 'vbloggers';
$param['user_email'] = '[email protected]';
$param['user_phone'] = '123456789';
$param['address']    = 'vbloggers';
$param['password']   = 'hyderabad';

$this->db->insert('users',$param);
//echo $this->db->last_query();die;
return $this->db->insert_id();

Update Data

MySQL

UPDATE `users` SET `user_name`='vbloggers',`user_email`='[email protected]',`user_phone`=9876543210,`password`='123456',`address`='hyderabad' WHERE id = 1

CodeIgniter

Here I just want to update query in codeigniter model. Let’s an example of- user phone, address, and password for the user whose id is 1.

$param['user_phone']  = '123456789'; 
$param['address']     = 'vbloggers'; 
$param['password']    = 'hyderabad'; 
$fieldName            = 'id';
$fieldValue           = '1';
$this->db->where($fieldName,$fieldVallue);
$this->db->update('users',$param);
//echo $this->db->last_query();die; 

Delete Data

MySQL

DELETE FROM users WHERE id = 1

CodeIgniter

$fieldName            = 'id';
$fieldValue           = '1';
$this->db->delete('users',array($fieldName=>$fieldVallue));
//echo $this->db->last_query();die; 

Select Data

MySQL 

To select all data you can use ( * ) or if you want limited data you can add like select user_name,user_name etc..

//To get all users data from users table
SELECT user_name,user_email FROM users;
// To get single user data from users table
SELECT user_name,user_email FROM users where id = 1;

CodeIgniter

//To get all users data from users table
$this->db->select('*') 
OR 
$this->db->select('user_name,user_email');
$this->db->from('users'); 
$query = $this->db->get();
//echo $this->db->last_query();die;
if($query->num_rows() > 0):
   return $query->result_array();
else:
  return false;

endif;
//To get single user data from users table
$this->db->select('*') 
OR 
$this->db->select('user_name,user_email');
$this->db->from('users'); 
$this->db->where('id','1');
$query = $this->db->get();
//echo $this->db->last_query();die;
if($query->num_rows() > 0):
   return $query->row_array();
else:
  return false;
endif;

Order By

MySQL 

SELECT user_name,user_email FROM users ORDER BY id ASC; // for ascending order
SELECT user_name,user_email FROM users ORDER BY id DESC; // for descending order

CodeIgniter

$this->db->select('*')  
$this->db->from('users');  
$this->db->order_by('id','ASC'); 
$query = $this->db->get(); 
//echo $this->db->last_query();die; 
if($query->num_rows() > 0):    
      return $query->result_array(); 
else:  
      return false;  
endif;

Group By

MySQL 

SELECT user_name,user_email FROM users GROUP BY id ASC; // Only Group By
SELECT user_name,user_email FROM users GROUP BY id , ORDER BY id DESC; // Both Group By and order By

CodeIgniter

//Only Group by

$this->db->select('*')  
$this->db->from('users');  
$this->db->group_by('id'); 
$query = $this->db->get(); 
//echo $this->db->last_query();die; 
if($query->num_rows() > 0):    
    return $query->result_array(); 
else:  
    return false;  
endif;

//Both Group by and Order By

$this->db->select('*')  
$this->db->from('users');  
$this->db->group_by('id); 
$this->db->order_by('id','ASC'); 
$query = $this->db->get(); 
//echo $this->db->last_query();die; 
if($query->num_rows() > 0):    
    return $query->result_array(); 
else:  
    return false;  
endif;

Limit & Offset

Limit shows you how much data you need, Like if you need  10 you can pass 10 it returns you, 10 users.

Offset defines that it let you decide from where to start getting data. Like if you have 100 users and you need users from 50 to 60. So, in this case, you need to set limit 10 and offset 50 so it  

MySQL 

SELECT user_name,user_email FROM users LIMIT 10 // Only Limit
SELECT user_name,user_email FROM users LIMIT 10 OFFSET 2; // Both Limit and offset

CodeIgniter

//Only Limit

$this->db->select('*')  
$this->db->from('users');  
$this->db->limit(10); 
$query = $this->db->get(); 
//echo $this->db->last_query();die; 
if($query->num_rows() > 0):    
    return $query->result_array(); 
else:  
    return false;  
endif;

//Both Limit and Offset 
$this->db->select('*')  
$this->db->from('users');  
$this->db->limit(10,2); //10 is llimit & 2 is offset
$query = $this->db->get(); 
//echo $this->db->last_query();die; 
if($query->num_rows() > 0):    
    return $query->result_array(); 
else:  
    return false;  
endif;


Between, where & limit

Limit returns records between your provided condition. Like if you want users whose id is between 10 and 30, it returns records from 10 to 30.

MySQL 

SELECT user_name,user_email FROM users where id between 10 AND 30  // Only Between
SELECT user_name,user_email FROM users where user_name = "vbloggers" AND id between 5 AND 7 // between and where
SELECT user_name,user_email FROM users where user_name = "vbloggers" AND id between 5 AND 7 LIMIT 2 // between,where and limit

CodeIgniter

//Only Between
$this->db->where('id BETWEEN 10 AND 20');


//Between and where
$this->db->where('user_name','vbloggers');
$this->db->where('id BETWEEN 10 AND 20');

//Between , where & limit
$this->db->where('user_name','vbloggers');
$this->db->where('id BETWEEN 10 AND 20');
$this->db->limit(5);

Leave a Reply

Your email address will not be published. Required fields are marked *

The maximum upload file size: 2 MB. You can upload: image, document. Links to YouTube, Facebook, Twitter and other services inserted in the comment text will be automatically embedded.