Jump to content


Multiple join tables

CodeIgniter query join Crud

  • Please log in to reply
8 replies to this topic

#1 vladbutterfly

vladbutterfly

    Member

  • Members
  • PipPip
  • 16 posts

Posted 13 May 2013 - 02:11 PM

Hi GCruders

 

I have this database

project: id,#customer_id

customer:id,#customer_group_id

customer_group:#id

customer_group_text: (#customer_group_id,#laguage_id ) composite primary key,description

 

 

I want to apply the GCRUD on the description from the customer_group_text

The main table is Project

 

here my controller

 

 

 public function project_management()
     { $this->load->view('include/header.php');
        $crud = new grocery_CRUD();
        $crud->set_table('project');
        $crud->set_subject('Project');

         $crud->set_relation('customer_id', 'customer', 'name');
        $crud->display_as('customer_id','Customer Name');
         $crud->set_relation('manager_id', 'resource', 'first_name');
        $crud->display_as('customer_id','Customer Name');
        $crud->set_relation('project_status_id', 'project_status', 'id');
        $crud->display_as('project_status_id','Status Identifier');
        $crud->add_fields('description','manager name','customer_id','customer_group','project_status_id','project_status_text','redmine_id','change_date');
        //$crud->callback_column('redmine_id',array($this,'_callback_redmine_url'));
        
        $crud->callback_add_field('customer_group', array($this, 'add_field_callback_1'));
        $crud->callback_before_insert(array($this, '_callback_before_insert'));
        

        $output = $crud->render();
        $this->_example_output($output);
        

    }
    
    
    function add_field_callback_1()
    {

        $html = '<script type="text/javascript">';
        $html.= '$(document).ready(function() {';
        $html.="$('#field-customer_id').change(function()
{
    var val =  $(this).val();
    if(val!='')
        {
        $('#customer_group').removeAttr('disabled');
        $('#customer_group').trigger('liszt:updated');
        }});
        });</script>";

        $this->db->select('description');
                  $this->db->from('customer_group_text');
                  $this->db->join('customer_group', 'customer_group.id = customer_group_text.customer_group_id');
                  $this->db->join('customer', 'customer.customer_group_id=customer_group.id');
                  $query = $this->db->get();
        $html.= '<select name="customer_group" id="customer_group" class="chosen-select" data-placeholder="Customer group " disabled="disabled">';
        
                if ($result)
                {
                    $html.='<option value=""></option>';
                    foreach ($result as $item)
                    {
                        $html.='<option value="' . $item['id'] . '">' . $item['inactive'] . '</option>';
                    }
                };
        $html.= '</select>';
        return $html;
    }

    function _callback_before_insert($post_array)
    {
        if ($post_array['customer_group'])
        {
            $data = array('customer_group_id' => $post_array['customer_group']);
            $this->db->update('customer', $data, array('id' => $post_array['customer_id']));
        }
        unset($post_array['customer_group']);
        return $post_array;
    }

 

 

----------------->

with the simple set_relation it works fine but adding these joins get many errors and a strange behaviours

A PHP Error was encountered

Severity: Notice

Message: Undefined variable: result

Filename: controllers/main.php

Line Number: 78

hellllllllllllp please

 

 



#2 vladbutterfly

vladbutterfly

    Member

  • Members
  • PipPip
  • 16 posts

Posted 13 May 2013 - 02:28 PM

This actually a vision of what I want to have but :(

Attached Thumbnails

  • crud.png


#3 heruprambadi

heruprambadi

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts
  • LocationIndonesia

Posted 14 May 2013 - 06:30 AM

you have to define variable $result first.



#4 vladbutterfly

vladbutterfly

    Member

  • Members
  • PipPip
  • 16 posts

Posted 14 May 2013 - 10:12 AM

still having more issues

Attached Thumbnails

  • Unbenannt.png


#5 heruprambadi

heruprambadi

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts
  • LocationIndonesia

Posted 14 May 2013 - 01:05 PM

Ok. I know how to solve it, but it's secret.



#6 zrmba

zrmba

    Member

  • Members
  • PipPip
  • 16 posts

Posted 15 May 2013 - 02:30 AM

Really!

didn't knew there were secrets around..



#7 vladbutterfly

vladbutterfly

    Member

  • Members
  • PipPip
  • 16 posts

Posted 15 May 2013 - 03:36 PM

Now no errors but Not the result That I want to have

    public function project_management()
     { $this->load->view('include/header.php');
        $crud = new grocery_CRUD();
        $crud->set_table('project');
        $crud->set_subject('Project');

         $crud->set_relation('customer_id', 'customer', 'name');
        $crud->display_as('customer_id','Customer Name');
         $crud->set_relation('manager_id', 'resource', 'first_name');
        $crud->display_as('customer_id','Customer Name');
        $crud->set_relation('project_status_id', 'project_status', 'id');
        $crud->display_as('project_status_id','Status Identifier');
        $crud->add_fields('description','manager name','customer_id','customer_group','project_status_id','project_status_text','redmine_id','change_date');
        
        $crud->callback_add_field('customer_group_text', array($this, 'add_field_callback_1'));
        $crud->callback_before_insert(array($this, '_callback_before_insert'));
        

        $output = $crud->render();
        $this->_example_output($output);
        

    }
    
    
    function add_field_callback_1()
    {

        $html = '<script type="text/javascript">';
        $html.= '$(document).ready(function() {';
        $html.="$('#field-customer_id').change(function()
{
    var val =  $(this).val();
    if(val!='')
        {
        $('#customer_group_text').removeAttr('disabled');
        $('#customer_group_text').trigger('liszt:updated');
        }});
        });</script>";

        $result=  $this->db->select('description');
                  $this->db->from('customer_group_text');
                  $this->db->join('customer_group', 'customer_group.id = customer_group_text.customer_group_id');
                  $this->db->join('customer', 'customer.customer_group_id=customer_group.id');
                  $query = $this->db->get();
        $html.= '<select name="customer_group_text" id="customer_group_text" class="chosen-select" data-placeholder="Customer group " disabled="disabled">';
        
                if ($result)
                {
                    $html.='<option value=""></option>';
                    foreach ($result as $item)
                    {
                        $html.='<option value="' . $item['cutomer_group_id'] . '">' . $item['description'] . '</option>';
                    }
                };
        $html.= '</select>';
        return $html;
    }

    function _callback_before_insert($post_array)
    {
        if ($post_array['customer_group_text'])
        {
            $data = array('customer_group_id' => $post_array['customer_group_text']);
            $this->db->update('customer', $data, array('id' => $post_array['customer_id']));
        }
        unset($post_array['customer_group_text']);
        return $post_array;
    }



#8 vladbutterfly

vladbutterfly

    Member

  • Members
  • PipPip
  • 16 posts

Posted 15 May 2013 - 03:37 PM

the main reason is to get this

I have a component primary key in customer_group_text so depending on the customer_group and the langage I can have different descriptoin for the same customer group with different laguage the set_relation is not sufficiant to reliate each customer to his (description) for example for customer 1 I want to have customer group (Hospital,Hopital) in a scroll list

Thanks again If you can help me



#9 Ajith de Silva

Ajith de Silva

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 18 November 2013 - 05:44 PM

I had the same question and following is the solution that I did.
1. Create a view with mySQL join statements for the tables that I want to join
2. Look for the state ($cur_state=$crud->getState() ;) and see if it is either "list,ajaxlist,read or success" and then I set the $crud->set_table('VIEW_NAME');
       and also set the primary key $crud->set_primary_key('KEY','VIEW_NAME');
3. for the else part I use the table name itself so that it will help add,edit operation.
 
this solution working very nicely.
 
$crud = new grocery_CRUD();
$cur_state=$crud->getState();
$crud->set_subject('Patient Notes');        
/* Use the mySQL view to display the data with related tables */ if(($cur_state=="list") || ($cur_state=="ajax_list") || ($cur_state=="read") || ($cur_state=="success"))
{ $crud->set_table('patientsnotes_vw'); $crud->columns('noteID','Clinic_No','note','noteCreated','username'); $crud->set_primary_key('noteID','patientsnotes_vw');
 
   if($cur_state=="read")
   {
    $crud->unset_fields('noteCreatedBy','user_id','patientID');                      
       }
}
else
{
        /* Use the patient_note table itself for add/edit operation */
        $crud->set_table('patient_notes');
    $crud->columns('noteID','Clinic_No','note','noteCreated','username');
    $crud->required_fields('note');
    $crud->field_type('noteCreatedBy', 'hidden', $this->userID);
    $crud->field_type('patientID', 'hidden', 1);
    $crud->unset_add_fields('noteCreated');
    $crud->unset_edit_fields('noteCreated');        
 }
 
  $crud->display_as('username','Note Created by');
  $crud->display_as('noteCreated','Note Created at');       
  $output = $crud->render();






Also tagged with one or more of these keywords: CodeIgniter, query, join, Crud

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users