phpmysqlcodeigniterforeign-keysgrocery-crud

Grocery Crud set relation between 3 tables


I have 3 sql tables and they are with this form.

TEST

EXAMPLE

SOMETHING

So i want to take the name of the TEST table among with the id and send it to the table EXAMPLE, and then the example_id should be sent to SOMETHING table. Finishing the relation i want the name of the TEST table to be shown by the example_id in the SOMETHING table.

We have tried this

 $crud->set_relation_n_n('something','example','test','example_id','example_test_id','test_name');

And it takes the id of the TEST and it saves it to example_test_id but we want it to be saved at something_example_id and show the test_name. All the tables have foreign keys and primary keys. Hope you understand.


Solution

  • It would be easier for you if you were using custom model functions instead of set_relation_n_n function.

    First create a model function to get all the id and test_id from example table

    public function get_example_list()
        {
            $this->db->select('example_id, example_test_id');
            $this->db->order_by('example_id asc');
            return $this->db->get('example')->result();
        }
    

    Next, create a second model function to get tha test_name using the test_id from the first model

    public function get_test_names($id)
        {
            $this->db->select('test_name');
            $this->db->from('test');
            $this->db->where('test_id', $id);
            $name=$this->db->get();
            return $name->row()->test_name; 
        }
    

    in your controller use the above functions to create an array with the data you need

    $list = $this->your_model->get_example_list();
    
            foreach ($list as $column => $data) {
                $myarray[$data->example_id] = $this->your_model->get_test_names($data->example_test_id);
            }
    

    finally use "field_type" function to pass the array into something_example_id.

    $crud->field_type('something_example_id','dropdown',$myarray);
    

    Now. in something_example_id column you have a dropdown, displaying the test_name value and saving the example_id value on your database.