Jump to content


Travar Lazica

Member Since 13 Aug 2013
Offline Last Active Aug 15 2013 06:15 PM
-----

Topics I've Started

Simple guide to executing custom queries

13 August 2013 - 04:33 PM

Let's say you want to execute a complex custom query that you want to pass as a query string to Grocery CRUD.

 

 Step 1. Create a custom model

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

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;
	}
}

Step 2. Code the controller

Add the following code to your controller and existing mechanic for outputting the Grocery CRUD interface.

$crud = new grocery_CRUD();
$crud->set_model('custom_query_model');
$crud->set_table('employees'); //Change to your table name
$crud->basic_model->set_query_str('SELECT * FROM employees'); //Query text here
$output = $crud->render();

When you assign a custom model to the grocery crud object ($crud), you can access it with $crud->basic_model, regardless of the custom model's name.

 

Hope this helped.

 

!Edit!

 

Another way you can execute temporary tables, that requires no tampering with custom models etc. is to create a temporary table in the database filled with data from the custom query and then just let Grocery CRUD do it's rendering the temp table.

 

Filling temporary tables in Microsoft SQL Server:

SELECT t.*
INTO #<name for the temporary table>
FROM (<query>) as t

* note the the preceding '#'  to the temporary table name, it is a prefix to the names of all temporary tables.

 

Filling temporary tables in MySQL:

CREATE TEMPORARY TABLE <name for the temporary table> AS (<query>)

For more info on temporary tables in CodeIgniter: http://ellislab.com/forums/viewthread/216656/