set relation grocery crud error

codeigniter, grocery-crud, join, mysql, php

Solution

I did your example:

Controller:

function medicine() 

{
     $crud = new grocery_CRUD();

     $crud->set_table('tbl_patient_medicine');
     $crud->required_fields('id_patient','name_medicine','dosis','start_date','treatment','time_per_day');
     $crud->columns('id_patient','name_medicine','dosis','start_date','treatment','time_per_day');
     $crud->fields('id_patient','name_medicine','dosis','start_date','treatment','time_per_day');

     $crud->set_relation('id_patient','tbl_patient','name');

     $output = $crud->render();  

     $this->_example_output($output); 
}

It works!

Edited:

$crud->set_relation('id_patient','tbl_patient','{name},  {att1},  {att2},  {att3}');

Problem

I have 2 tables ``` CREATE TABLE `tbl_patient` ( id_patient INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(25) NOT NULL DEFAULT "not available", att1 VARCHAR(5 ) NOT NULL DEFAULT "att1", att2 VARCHAR(25) NOT NULL DEFAULT "att2", att3 VARCHAR(25) NOT NULL DEFAULT "att3", CONSTRAINT `uc_Info_patient` UNIQUE (`id_patient`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; ``` and ``` CREATE TABLE `tbl_patient_medicine` ( id_patient_medicine INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, id_patient INTEGER NOT NULL, name_medicine VARCHAR(50) NOT NULL DEFAULT "", dosis VARCHAR(50) NOT NULL DEFAULT "", start_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, treatment VARCHAR(50) NOT NULL DEFAULT "", times_per_day VARCHAR(50) NOT NULL DEFAULT "", CONSTRAINT fk_ID_Patient_Medicine FOREIGN KEY (id_patient) REFERENCES `tbl_patient`(id_patient) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; ``` As you can see table patient_medicine is the intermediate table of between tbla_medicines, and table patient. Now I want to consult all data from tbl_patient_medicine with grocery crud like in this sqlfiddle supossing I pass the id in the uri (in the example will be id_patient=1) I have ``` public function details_medication($my_id = 0) { try{ $crud = new grocery_CRUD(); $crud->where('id_patient',$my_id); $crud->set_table('tbl_patient_medicine'); //HOW TO DO IT? $crud->set_relation('id_patient', 'tbl_patient', 'id_patient'); $output = $crud->render(); $this->_output($output); }catch(Exception $e){ show_error($e->getMessage().' --- '.$e->getTraceAsString()); } } ``` SO I have tried different ways but got errors like this: ``` A Database Error Occurred Error Number: 1052 Column 'id_patient' in where clause is ambiguous SELECT `tbl_patient_medicine`.*, j7a675883.id_patient AS s7a675883 FROM (`tbl_patient_medicine`) LEFT JOIN `tbl_patient` as j7a675883 ON `j7a675883`.`id_patient` = `tbl_patient_medicine`.`id_patient` WHERE `id_patient` = '1' LIMIT 25 ``` Line Number: 87

Original source