[kalong]

I've try to solve my problem using /topic/779-problem-with-where-and-set-relation/#entry3008

 

the result

Error Number: 1054
Unknown column 'j813436ef.client_id' in 'where clause'
SELECT `site`.*, j2bfe9d72.client_name AS s2bfe9d72 FROM (`site`) LEFT JOIN `clients` as j2bfe9d72 ON `j2bfe9d72`.`client_id` = `site`.`client_id` WHERE `j813436ef`.`client_id` = '2' LIMIT 25
Filename: /var/www/html/grocery2/models/grocery_crud_model.php
Line Number: 87

Use : $crud->where('client_id',$this->session->userdata('client'));

 

error

Error Number: 1052
Column 'client_id' in where clause is ambiguous
SELECT `site`.*, j2bfe9d72.client_name AS s2bfe9d72 FROM (`site`) LEFT JOIN `clients` as j2bfe9d72 ON `j2bfe9d72`.`client_id` = `site`.`client_id` WHERE `client_id` = '2' LIMIT 25
Filename: /var/www/html/grocery2/models/grocery_crud_model.php
Line Number: 87

I've test on mysql and the right query should be

SELECT `site`.*, j2bfe9d72.client_name AS s2bfe9d72 FROM (`site`) LEFT JOIN `clients` as j2bfe9d72 ON `j2bfe9d72`.`client_id` = `site`.`client_id` WHERE j2bfe9d72.client_id = '2' LIMIT 25

using

$crud->where('s2bfe9d72.client_id','2');

still get error

SELECT `site`.*, j2bfe9d72.client_name AS s2bfe9d72 FROM (`site`) LEFT JOIN `clients` as j2bfe9d72 ON `j2bfe9d72`.`client_id` = `site`.`client_id` WHERE `s2bfe9d72`.`client_id` = '2' LIMIT 25

solution if query to database directly should query

SELECT `site`.*, j2bfe9d72.client_name AS s2bfe9d72 FROM (`site`) LEFT JOIN `clients` as j2bfe9d72 ON `j2bfe9d72`.`client_id` = `site`.`client_id` WHERE j2bfe9d72.client_id = '2' LIMIT 25

j2bfe9d72.client_id without ` sign

 

 

Code :

  protected function _unique_join_name($field_name)
    {
     return 'j'.substr(md5($field_name),0,8);
    }
    
    function site()
    {
            $crud = new grocery_CRUD();
            $crud->set_theme('flexigrid');
            $crud->set_table('site');
            $crud->set_subject('Site');
            $crud->columns('site_name','client_id','address','phone','isactive');
            $crud->required_fields('site_name','client_id','address','phone','isactive');
            //$crud->set_relation('user_id','user','user_name');            
            //$crud->set_relation('unit_id','unit','unit_name');            
            //$crud->set_relation('plant_id','plant','plant_name');
            //$klien = $this->session->userdata('client');            
            $crud->set_relation('client_id','clients','client_name');
            //$crud->where('client_id',$this->session->userdata('client'));
            //$crud->where($this->_unique_join_name('visitas').'.client_id',$this->session->userdata('client'));
             $crud->where('s2bfe9d72.client_id','2');
            //$crud->where('client_id',$_SESSION['client']);
//            $crud->unset_add_fields('created_by','created_date');
//            $crud->unset_edit_fields('created_by','created_date');
$crud->unset_edit_fields('created_date','created_by');
$crud->field_type('created_by', 'hidden');
$crud->field_type('created_date', 'hidden');
$crud->callback_add_field('created_by',array($this,'add_field_callback_1'));
$crud->callback_add_field('created_date',array($this,'add_field_callback_2'));    
$crud->callback_add_field('isactive',array($this,'isactive_callback'));    
            $crud->display_as('isactive','Active')
                 ->display_as('client_id','Client') ;                
            $output = $crud->render();
            $this->_site_output($output);
    }

anyone know how to fix this problem ?

 

Thank you

 

[kalong]

I've fixed this problem :)

    function site()
    {
            $crud = new grocery_CRUD();
            $crud->set_theme('flexigrid');
            $crud->set_table('site');
            $crud->set_subject('Site');
            $crud->columns('site_name','client_id','address','phone','isactive');
            $crud->required_fields('site_name','client_id','address','phone','isactive');         
            $crud->set_relation('client_id','clients','client_name',array('isactive'=> 'Yes'));
            $crud->where('site.client_id',$this->session->userdata('client'));               
            $output = $crud->render();
            $this->_site_output($output);
    }

but why

$crud->set_relation('client_id','clients','client_name',array('isactive'=> 'Yes'));

part

array('isactive'=> 'Yes'));

not working ? in my table isactive no still shown http://i.imgur.com/6quZaek.png

 

 

[davidoster]

Hello [member=kalong] and welcome to the forums.

Can you please post the structure of the tables site, clients?

[kalong]

Thank you [member=davidoster] :)

 

Hello [member=kalong] and welcome to the forums.

Can you please post the structure of the tables site, clients?

 

Client

CREATE TABLE IF NOT EXISTS `clients` (
  `client_id` int(11) NOT NULL AUTO_INCREMENT,
  `client_code` varchar(50) NOT NULL,
  `client_name` varchar(50) NOT NULL,
  `client_size` enum('large','medium','small') NOT NULL,
  `engagement_start_date` date NOT NULL,
  `industry_id` int(11) NOT NULL,
  `main_contact_person` varchar(20) NOT NULL,
  `main_client_lead` varchar(20) NOT NULL,
  `project_address` text NOT NULL,
  `project_email` varchar(50) NOT NULL,
  `project_phone` varchar(20) NOT NULL,
  `billing_address` text NOT NULL,
  `billing_email` varchar(50) NOT NULL,
  `billing_phone` varchar(20) NOT NULL,
  `service_interval_standard` int(11) NOT NULL,
  `created_by` varchar(50) NOT NULL,
  `created_date` datetime NOT NULL,
  `isactive` enum('Yes','No') NOT NULL,
  PRIMARY KEY (`client_id`),
  KEY `industry_id` (`industry_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

Site

CREATE TABLE IF NOT EXISTS `site` (
  `site_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_name` varchar(50) NOT NULL,
  `client_id` int(11) NOT NULL,
  `address` text NOT NULL,
  `phone` varchar(20) NOT NULL,
  `created_by` varchar(40) NOT NULL,
  `created_date` date NOT NULL,
  `isactive` enum('Yes','No') NOT NULL,
  PRIMARY KEY (`site_id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

sorry for late reply

 

the query should look like (example)

select * from site where client_id=2 and isactive='Yes'

[kalong]

sorry for double posting. I've fixing this issue with two $crud->where

  function site()
    {
            $crud = new grocery_CRUD();
            $crud->set_theme('flexigrid');
            $crud->set_table('site');
            $crud->set_subject('Site');
            $crud->columns('site_name','client_id','address','phone','isactive');
            $crud->required_fields('site_name','client_id','address','phone','isactive');           
            $crud->set_relation('client_id','clients','client_name');
            $crud->where('site.client_id',$this->session->userdata('client'));
            $crud->where('site.isactive','Yes');    
            $output = $crud->render();
            $this->_site_output($output);
    }

thanks

[davidoster]

First of all you need to define for your tables what are the primary keys that you use.

Try this and see if it fixes your problems.

[bigtt76]

Hello David. I managed to use this line '$crud->where('site.client_id',$this->session->userdata('client'));' to get user's data separated from other users but cannot get the Admin user to view for all users. I'm using Ion_Auth.

 

Thanks in advance.