[elanouar]

I want to do something like this.

I have 3 tables:  utilisateur,groupe,usertogroupe :

CREATE TABLE IF NOT EXISTS `usertogroupe` 
(
  `idgroupe` bigint(8) NOT NULL,
  `idutilisateur` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `idusertogroupe` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`idusertogroupe`)
) 
CREATE TABLE IF NOT EXISTS `groupe` (
  `nomGroupe` varchar(30) COLLATE utf8_bin NOT NULL,
  `idGroupe` bigint(8) NOT NULL AUTO_INCREMENT,
  `idSection` bigint(20) NOT NULL,
  `idperiode` bigint(20) NOT NULL,
  PRIMARY KEY (`idGroupe`),
  KEY `idGroupe` (`idGroupe`)
)  
CREATE TABLE IF NOT EXISTS `utilisateur` (
  `pseudo` varchar(10) COLLATE utf8_bin NOT NULL,
  `pwd` varchar(30) COLLATE utf8_bin NOT NULL,
  `role` enum('Utilisateur','Administrateur','Support') COLLATE utf8_bin NOT NULL,
  `id_utilisateur` int(11) NOT NULL AUTO_INCREMENT,
  `cinpersonne` varchar(8) COLLATE utf8_bin NOT NULL DEFAULT '00000000',
  `actif` enum('oui','non') COLLATE utf8_bin NOT NULL,
  `email` varchar(30) COLLATE utf8_bin NOT NULL,
  `signature` varchar(30) COLLATE utf8_bin NOT NULL,
  `pwdEmail` varchar(30) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id_utilisateur`)
)  

utilisateur-> groupe is a n:n relationship

I am using set_relation_n_n to establish the relationship between the ‘utilisateur’ table and the groupe table.  I   created a form in which it contains a list. The list contain a list of groups that refers, filtered, by  a ‘utilisateur’;  the ‘utilisateur’ manage one or many groups. I tried to create a set_relation_n_n and filtered by the ‘utilisateur’. Unfortunately I’m unable to generate the needed list.

Would you help us to under pass this pb?

 $this->grocery_crud->set_table('utilisateur');
		 
		$this->grocery_crud->set_relation_n_n('Affectation','usertogroupe', 'groupe', 'idutilisateur', 'idgroupe','nomGroupe',null,array('idgroupe'=>28));// I have to change the idgroupe to idutilisateur		
		$this->grocery_crud->set_table('email');				
		$this->grocery_crud->columns('source','destination','sujet','contenu','Affectation');
		$this->grocery_crud->fields('source','destination','sujet','contenu' ,'Affectation');
			      	
		$this->grocery_crud->set_rules('contenu', 'contenu', 'trim|required|min_length[10]|max_length[280]|xss_clean');		 
		
		$this->grocery_crud->unset_texteditor('contenu','contenu');		

		$this->grocery_crud->unset_edit();

		$this->grocery_crud->callback_after_insert(array($this->grocery_crud, 'after_insert_email'));
 
        $output = $this->grocery_crud->render();
 
        $this->_example_output($output);   

Thanks in advance.

 

[elanouar]

any help !!

[Amit Shah]

Well u don't need to just array of key / pair values in a crud->where .. it also accepts string.. and what you can simple do is ...

$where = 'usertogroupe.idutilisateur = utilisateur.id and utilisateur.id = 22'; /// or whatever condition suites in here

//& then apply the $where

$this->grocery_crud->set_relation_n_n('Affectation','usertogroupe', 'groupe', 'idutilisateur', 'idgroupe','nomGroupe',null,$where));

[elanouar]

Tks Amit for your response I applied my query as you said :

$where = 'usertogroupe.idutilisateur=utilisateur.id_utilisateur and usertogroupe. idgroupe=groupe.idGroupe and utilisateur.id_utilisateur=9';	 
		$this->grocery_crud->set_relation_n_n('Affectation','usertogroupe', 'groupe', 'idutilisateur', 'idgroupe','nomGroupe',null,$where);

Unfortunately I had an erro as a reponse :

Error Number: 1054

Champ 'usertogroupe.idutilisateur=utilisateur.id_utilisateur' inconnu dans where clause

SELECT *, nomGroupe as s509db8ad FROM (`groupe`) WHERE `usertogroupe`.`idutilisateur=utilisateur`.`id_utilisateur` and usertogroupe. idgroupe=groupe.idGroupe and utilisateur.id_utilisateur=9 ORDER BY `groupe`.`nomGroupe`

Filename: C:\Program Files\EasyPHP-5.3.1\www\Bilelou\system\database\DB_driver.php

Line Number: 330

Thus I instantiated ,as figuring below, all tables and tried to test query into mysql and i had the needed result.

SELECT *, G.nomGroupe 
FROM groupe G,usertogroupe ug,utilisateur u
WHERE usertogroupe.idutilisateur=u.id_utilisateur 
and ug. idgroupe=G.idGroupe 
and u.id_utilisateur=9 
ORDER BY G.nomGroupe

Actually I 'm sure that my query is correct and I would like to get the same result with the set_relation_n_n function or other function !!

Is there any Idea !!!!!!!!!!!!!!!!

[davidoster]

You have multiple errors on your controller's function,

- first you set_table('utilisateur')

- then you set_relation_n_n on a field, affectation, that does not exist on the table utilisateur

- then you set_table again to another table

 

check your code again please! 

 

 

 $this->grocery_crud->set_table('utilisateur');

		 
		$this->grocery_crud->set_relation_n_n('Affectation','usertogroupe', 'groupe', 'idutilisateur', 'idgroupe','nomGroupe',null,array('idgroupe'=>28));// I have to change the idgroupe to idutilisateur		
		$this->grocery_crud->set_table('email');