[Manu Sharma]

Im getting this error while searching and if i use alias while setting relation then searching works but on add and edit page i get the another error,
 

 

Column 'first_name' in where clause is ambiguous

SELECT * FROM (`users`) LEFT JOIN `users` as j6be37982 ON `j6be37982`.`id` = `users`.`parent_id` WHERE `first_name` LIKE '%vip%' OR `email_id` LIKE '%vip%' OR `user_type` LIKE '%vip%' OR `company_name` LIKE '%vip%'

Filename: C:\wamp\www\event_scheduler\system\database\DB_driver.php

Line Number: 366

 

function index()
    {
     try{
        
            if(!$this->common->check_login() ) {
                redirect('/admin/login');
            }
            $crud = new grocery_CRUD_extended();

        
            $crud->set_table('users');
            $crud->set_subject('Users');
            $crud->required_fields('salutation','first_name','last_name','username','user_type',
                          'company_type','company_name','company_address1','state','zipcode',
                          'company_email','phone_country_code','phone_number');
            
            
            

            
            
            $crud->columns('first_name','email_id','user_type','company_name');
            $crud->set_relation('parent_id','users','username',array('parent_id' => 0));
            $crud->fields('salutation','first_name','middle_name','last_name','username','email_id','user_type',
                          'company_type','company_name','company_address1','company_address2','state','city','zipcode',
                          'company_email','phone_country_code','phone_number','extension_type','extension_number','is_sms',
                          'parent_id','status');
            
            $crud->set_rules('email_id','Email','valid_email|required');
            $crud->set_rules('compamy_email','Compamy Email','valid_email|required');
            
            $crud->unique_fields('email_id','username');
            
            $crud->display_as('parent_id','Manager Name');
            
            $crud->callback_after_insert(array($this,'GetLatLong_callback'));
            $crud->callback_after_update(array($this,'GetLatLong_callback'));
            
             $crud->callback_after_insert(array($this, 'send_mail'));
            
            $output = $crud->render();
            $output->title = 'Manage Users';
            $output->all_userdata = $this->session->all_userdata();
            $this->load->view('../views/templates/header',$output);
            $this->common->_crud_output($output);
            $this->load->view('../views/templates/footer');
            
        }catch(Exception $e){
            show_error($e->getMessage().' --- '.$e->getTraceAsString());
        }
    }

 

 

When i change the relation like this -> $crud->set_relation('parent_id','users u','username',array('u.parent_id' => 0));

using alias then searching starts working but on add page error comes:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '., users u.username as s6be37982 FROM (`users` u) WHERE `u`.`parent_id` = 0 AND' at line 1

SELECT users u., users u.username as s6be37982 FROM (`users` u) WHERE `u`.`parent_id` = 0 AND `u`.`parent_id` = 0 ORDER BY `s6be37982`

 

 

[Manu Sharma]

Now I'm dong it like this ->

if($crud->getState() != 'add' && $crud->getState() != 'edit')
{
   $crud->set_relation('parent_id','users u','u.username',array('parent_id' => 0));
}
else
{
   $crud->set_relation('parent_id','users','username',array('parent_id' => 0));
}

I think this is not the right way, there should be something else what I must have missed.

[davidoster]

Hello [member=manu sharma] and welcome to the forums.

Let me understand this correctly,

 

1. you run grocery crud on table users,

$crud->set_table('users');

 

2. then later you set a relation by using the very same table user

$crud->set_relation('parent_id','users','username',array('parent_id' => 0));

 

for what reason exactly? to make the parent_id to get the value of 0?

 

Exactly why do you do this? What are you trying to achieve?

Logically this is wrong. But you need to explain what you are trying to get by this set_relation in order to help you.

 

For your information, the set_relation function is used in order to get infromation from a related table as it says on the manual and not from the same table!

[Manu Sharma]

Hi,

 

Thanks.

 

I'm managing sub users in same table using parent_id, when parent_id = 0 then its parent user and otherwise it will have primary id of other user from same table as its parent_id so that I can show the parent users in the dropdown from same table while adding more users, thats why i need it, i was doubtful that this way of managing the database is wrong. I should have taken two different tables or is there any better way with which I can sort it out or this db structure is totally unacceptable.

 

Regards,

Manu

[davidoster]

I believe the two (2) tables approach is a better way.

[Manu Sharma]

thanks