I have used custom model
class Custom_query_model extends grocery_CRUD_model {
private $query_str = '';
function __construct() {
parent::__construct();
}
function get_list() {
$query=$this->db->query($this->query_str);
$results_array=$query->result();
return $results_array;
}
public function set_query_str($query_str) {
$this->query_str = $query_str;
}
}
/topic/1963-simple-guide-to-executing-custom-queries/
My controller :
<?php
/**
* home short summary.
*
* home description.
*
* @version 1.0
* @author justin
*/
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
session_start(); //we need to call PHP's session object to access it through CI
class Home extends CI_Controller {
function __construct()
{
parent::__construct();
if($this->session->userdata('logged_in'))
{
//some initialization
}
else
{
//If no session, redirect to login page
redirect('login', 'refresh');
}
$this->load->database();
$this->load->helper('url');
$this->load->library('grocery_CRUD');
}
function index()
{
$title = 'Home - Private Area';
$session_data = $this->session->userdata('logged_in');
$data['username'] = $session_data['username'];
$crud = new grocery_CRUD();
$crud->set_theme('datatables');
$crud->set_table('hprojecthr');
$crud->set_model('Custom_query_model');
$crud->set_relation('projectId', 'mproject', 'projectName');
$crud->display_as('categori1','TeamName');
$crud->display_as('categori2','Condtions');
$crud->display_as('category','Site');
$crud->display_as('projectId','Code');
$crud->columns('categori1', 'categori2', 'projectId', 'memberId', 'category', 'siteCode', 'year','month','d1', 'd2', 'd3', 'd4', 'd5', 'd6', 'd7', 'd8', 'd9', 'd10', 'd11', 'd12', 'd13', 'd14', 'd15', 'd16', 'd17', 'd18', 'd19', 'd20', 'd21', 'd22', 'd23', 'd24', 'd25', 'd26', 'd27', 'd28', 'd29', 'd30', 'd31');
$crud->basic_model->set_query_str('SELECT categori1, categori2, projectId, HR.category, HR.siteCode, HR.year,HR.month,HR.memberId, HR.d1, HR.d2, HR.d3, HR.d4, HR.d5, HR.d6, HR.d7, HR.d8, HR.d9, HR.d10, HR.d11, HR.d12, HR.d13, HR.d14, HR.d15,
HR.d16, HR.d17, HR.d18, HR.d19, HR.d20, HR.d21, HR.d22, HR.d23, HR.d24, HR.d25, HR.d26, HR.d27, HR.d28, HR.d29, HR.d30, HR.d31
FROM hprojecthr HR
left outer join mprojectcalendar on HR.category = mprojectcalendar.category
union all
SELECT "" as categori1, "" as categori2, "" as projectId, "" as category, "ON" as siteCode, "" as year, "" as month, "" as memberId,
sum(d1) as d1, sum(d2) as d2, sum(d3) as d3, sum(d4) as d4, sum(d5) as d5, sum(d6) as d6, sum(d7) as d7, sum(d8) as d8, sum(d9) as d9,
sum(d10) as d10, sum(d11) as d11, sum(d12) as d12, sum(d13) as d13, sum(d14) as d14, sum(d15) as d15, sum(d16) as d16, sum(d17) as d17, sum(d18) as d18,
sum(d19) as d19, sum(d20) as d20, sum(d21) as d21, sum(d22) as d22, sum(d23) as d23, sum(d24) as d24, sum(d25) as d25, sum(d26) as d26, sum(d27) as d27,
sum(d28) as d28, sum(d29) as d29, sum(d30) as d30, sum(d31) as d31
From hprojecthr
WHERE siteCode = "1"
union all
SELECT "" as categori1, "" as categori2, "" as projectId, "" as category, "OFF" as siteCode, "" as year, "" as month, "" as memberId,
sum(d1) as d1, sum(d2) as d2, sum(d3) as d3, sum(d4) as d4, sum(d5) as d5, sum(d6) as d6, sum(d7) as d7, sum(d8) as d8, sum(d9) as d9,
sum(d10) as d10, sum(d11) as d11, sum(d12) as d12, sum(d13) as d13, sum(d14) as d14, sum(d15) as d15, sum(d16) as d16, sum(d17) as d17, sum(d18) as d18,
sum(d19) as d19, sum(d20) as d20, sum(d21) as d21, sum(d22) as d22, sum(d23) as d23, sum(d24) as d24, sum(d25) as d25, sum(d26) as d26, sum(d27) as d27,
sum(d28) as d28, sum(d29) as d29, sum(d30) as d30, sum(d31) as d31
From hprojecthr
WHERE siteCode <> "1"
union all
SELECT "" as categori1, "" as categori2, "" as projectId, "" as category, "TOTAL" as siteCode, "" as year, "" as month, "" as memberId,
sum(d1) as d1, sum(d2) as d2, sum(d3) as d3, sum(d4) as d4, sum(d5) as d5, sum(d6) as d6, sum(d7) as d7, sum(d8) as d8, sum(d9) as d9,
sum(d10) as d10, sum(d11) as d11, sum(d12) as d12, sum(d13) as d13, sum(d14) as d14, sum(d15) as d15, sum(d16) as d16, sum(d17) as d17, sum(d18) as d18,
sum(d19) as d19, sum(d20) as d20, sum(d21) as d21, sum(d22) as d22, sum(d23) as d23, sum(d24) as d24, sum(d25) as d25, sum(d26) as d26, sum(d27) as d27,
sum(d28) as d28, sum(d29) as d29, sum(d30) as d30, sum(d31) as d31
From hprojecthr
' ); //Query text here
$output = $crud->render();
$this->layout->view('/home/home_view', $output, $title); // using master layout
}
function logout()
{
$this->session->unset_userdata('logged_in');
session_destroy();
redirect('home', 'refresh');
}
public function _example_output($output = null)
{
$this->load->view('example.php',$output);
}
}
?>
View :
A Database Error Occurred
Error Number: 1066
Not unique table/alias: 'j950be384'
SELECT * FROM (`hprojecthr`) LEFT JOIN `mproject` as j950be384 ON `j950be384`.`Id` = `hprojecthr`.`projectId` LEFT JOIN `mproject` as j950be384 ON `j950be384`.`Id` = `hprojecthr`.`projectId` LIMIT 10
Filename: C:\wamp\www\dain\system\database\DB_driver.php
Line Number: 330
what is solution for this problem?
sorry for my English.
Thanks.
