[michaeljean@ncf.ca]

Hello GCers!

 

I reallly like and appreciate this software.  I have a problem with a custom model that I set and use in a controller - the fields display ok, add and edit work ok but in the list view the search doesn't work for any of the fields.

 

After trying to search the list I see the following error using firebug:

    <div id="container">
        <h1>A Database Error Occurred</h1>
        <p>Error Number: 1054</p><p>Unknown column 'evaluation_type1' in 'where clause'</p><p>SELECT COUNT(*) AS `numrows`
FROM (`fm_session_activities`)
LEFT JOIN `fm_sessions` as jee357b77 ON `jee357b77`.`fm_session_id` = `fm_session_activities`.`fm_session`
LEFT JOIN `activities` as j69a25602 ON `j69a25602`.`activity_id` = `fm_session_activities`.`activity`
LEFT JOIN `evaluation_types` as j35b70d99 ON `j35b70d99`.`evaluation_type_id` = `fm_session_activities`.`evaluation_type`
WHERE `evaluation_type1` LIKE '%assessment%'</p><p>Filename: /home/kerow320/public_html/dev/FitMinds/models/grocery_crud_model.php</p><p>Line Number: 197</p>    </div>

model:

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

class Session_activities_join extends grocery_CRUD_Model
{
    public function __construct() {
        parent::__construct();
    }

    public function get_list()
    {
        $this->output->enable_profiler( TRUE );//Turns on CI debugging
        
        $this->db->select( 'fm_session_activities.*' );
        $this->db->select( 'session_info.fm_session_id as session_id' );
        $this->db->select( 'session_info.fm_session_start_time as session_start' );
        $this->db->select( 'activity_info.title_eng as activity_title' );
        $this->db->select( 'client_info.full_name as client' );
        $this->db->select( 'activity_category_info.name as activity_category' );
        $this->db->select( 'evaluation_type_info.name as evaluation_type1' );

        $this->db->from( 'fm_session_activities' );

        $this->db->join( 'activities as activity_info', 'activity_info.activity_id=fm_session_activities.activity', 'left' );
        $this->db->join( 'fm_sessions as session_info', 'session_info.fm_session_id=fm_session_activities.fm_session', 'left' );
        $this->db->join( 'clients as client_info', 'client_info.client_id = session_info.client', 'left' );
        $this->db->join( 'activity_categories as activity_category_info', 'activity_category_info.activity_category_id = activity_info.category', 'left' );
        // Join evaluation types
        $this->db->join( 'evaluation_types as evaluation_type_info', 'evaluation_type_info.evaluation_type_id = fm_session_activities.evaluation_type', 'left' );

        $results = $this->db->get()->result();
        
        return $results;
    }



}

controller:

    public function index( $session_id = NULL )
    {
        $crud = new grocery_CRUD();
        $data[ 'is_logged_in' ] = $this->ion_auth->logged_in();
    $data[ 'is_admin' ] = $this->ion_auth->is_admin();
        $customer = $this->customer_model->get_customer( $this->ion_auth->get_user_id() );

        // If the logged in user is not an administrator then provide the client view for the particular coach
        if( !$data[ 'is_admin' ] ) {
              $crud->unset_delete();
        }

        $data[ 'title' ] = 'Session Activities';

        $crud->set_table( 'fm_session_activities' );
        $crud->set_model( 'session_activities_join' );
        $crud->columns( 'session_id', 'session_start', 'client', 'activity_category', 'activity_title', 'evaluation_type1', 'score' );

        // If a session ID is passed to the controller then limit the activity list for the individual session
        if( isset( $session_id ) && $session_id > 0)
        {
            $crud->where( 'fm_session', $session_id );
        }

        $crud->set_relation( 'fm_session', 'fm_sessions', 'fm_session_id' );
        $crud->set_relation( 'activity', 'activities', 'title_eng' );
        $crud->set_relation( 'evaluation_type', 'evaluation_types', 'name' );

        $crud->display_as( 'session_id', 'Session ID' );
        $crud->display_as( 'session_start', 'Session Start' );
        $crud->display_as( 'activity_category', 'Category' );
        $crud->display_as( 'evaluation_type1', 'Evaluation Type' );
        $crud->display_as( 'activity_title', 'Activity' );

        $crud->set_subject( 'Session Activity' );

        $crud->unset_add();
        $crud->unset_delete();

        $crud->display_as( 'fm_session', 'Session' );

        $output = $crud->render();

        $this->load->view( 'templates/header', $data );
        $this->load->view( 'contents', $output );
        $this->load->view( 'templates/footer' );
    }

[michaeljean@ncf.ca]

Using a mysql view fixed my list search problem but now the edit doesn't work.

 

My mysql view:

create view session_activity_view as

select fm_session_id
, fm_session_activity_id
, fm_session_start_time
, client_full_name
, customer_full_name
, category_name
, activity_name
, document_link_eng
, answer_key_eng
, evaluation_type_name
, score

from fm_sessions
, fm_session_activities
, activities
, activity_categories
, clients
, customers
, evaluation_types

where fm_session_activities.fm_session = fm_sessions.fm_session_id
and fm_session_activities.activity = activities.activity_id
and activity_categories.activity_category_id = activities.category
and clients.client_id = fm_sessions.client
and evaluation_types.evaluation_type_id = fm_session_activities.evaluation_type

My controller:

        $crud = new grocery_CRUD();
        $crud->set_subject('Session Activity');
        $crud->unset_delete();
        $crud->unset_add();

        $crud->set_table( 'session_activity_view' );

        $crud->set_primary_key('fm_session_activity_id');

        $output = $crud->render();
        
        $this->load->view( 'templates/header', $data );
        $this->load->view( 'contents', $output );
        $this->load->view( 'templates/footer' );

I tried successfully updating my view in mysql using:

update `session_activity_view` set score = 2 where fm_session_activity_id = 11

However when I try in using grocery crud edit nothing appears to happen and I see an error in firebug:

NetworkError: 500 Internal Server Error
SyntaxError: JSON.parse: unexpected character at line 2 column 2 of the JSON data