[JPintor]

Regards

The first sorry for my English, i'm spanish

I have a problem. I want to do a (WHERE) of a table whit relations.

The relations don't give errors:

$crud->set_relation('MEMBER','members','{NAME}.{APELLIDOS}');

$crud->set_relation('USER','user','{NAME},{APELLIDOS}');

and the WHERE too:

$crud->where('MEMBER',$_SESSION['code']);

both things separately work fine, but if we mix them:
an error appears, (but only if the field MEMBER is not primary key of the table)

this is error in the select:

A Database Error Occurred

Error Number: 1052

Column 'MEMBER' in where clause is ambiguous

SELECT `visitas`.*, CONCAT('', COALESCE(j0d9372a2.NAME, ''), ' ', COALESCE(j0d9372a2.APELLIDOS, ''), '') as s0d9372a2, CONCAT('', COALESCE(j422560cb.NAME, ''), ' ', COALESCE(j422560cb.APELLIDOS, ''), '') as s422560cb, CONCAT('Desde: ', COALESCE(j6bea6422.INICIO, ''), ' Hasta: ', COALESCE(j6bea6422.FIN, ''), '') as s6bea6422 FROM (`visitas`) LEFT JOIN `visitadores` as j0d9372a2 ON `j0d9372a2`.`MEMBER` = `visitas`.`MEMBER` LEFT JOIN `usuarios` as j422560cb ON `j422560cb`.`USER` = `visitas`.`USUARIO` LEFT JOIN `agenda` as j6bea6422 ON `j6bea6422`.`CITA` = `visitas`.`CITA` WHERE `MEMBER` = '1' LIMIT 25

Filename: myurl

Line Number: 330

something idea??
very thanks for the help and for the wonderfull GROCERY CRUD

[web-johnny]

Hello [member='JPintor'] and welcome to the forum. Perhaps this solution is a bit ugly but it will work.

Add this method to your Controller

    protected function _unique_join_name($field_name)

    {

	 return 'j'.substr(md5($field_name),0,8); 

    }

and then at your code just replace the:

$crud->where('MEMBER',$_SESSION['code']);

with :

$crud->where($this->_unique_join_name('visitas').'.MEMBER',$_SESSION['code']);

and it will work as you expect.

Cheers
Johnny

[JPintor]

thanks very much for your quick answer Johnny,

but really it don't work
this is myu original code, with the fields in Spanish:

public function visitas()

  {

  $crud = new grocery_CRUD();



  $crud->set_table('visitas');

  $crud->set_subject('Visita');



//this si works because VISITA is PK

  //$crud->where('VISITA','1');



  $crud->set_relation('VISITADOR','visitadores','{NOMBRE} {APELLIDOS}');

  $crud->set_relation('USUARIO','usuarios','{NOMBRE} {APELLIDOS}');

  $crud->set_relation('CITA','agenda','Desde: {INICIO} Hasta: {FIN}');



  $crud->callback_add_field('VISITA',array($this,'obtenerCodigoVisita'));



//the error

  //$crud->where('VISITADOR',$_SESSION['codigo']);



  $crud->where($this->_unique_join_name('visitas').'.VISITADOR',$_SESSION['codigo']);

//i try to delete the point: .'.



  $crud->display_as('KEY','CLAVE');



  $output = $crud->render();

  $this->_example_output($output);	  

  }





  function obtenerCodigoVisita()

  {

   include_once('conexion.php');

   $consulta = mysql_query("select max(VISITA) as max from visitas",$conexion)

	or die("Datos no disponibles");

   $fila = mysql_fetch_array($consulta);

   $max=$fila['max']+1;

  

   return ' <input type="text" maxlength="50" value="'.$max.'" name="VISITA" style="width:502px">';

  }





  protected function _unique_join_name($field_name)

		{

		 return 'j'.substr(md5($field_name),0,8);

		}

and this is the error, the code 1054, diferent of the old error (1052)
i don't know if this means something

A Database Error Occurred

Error Number: 1054

Unknown column 'j813436ef.VISITADOR' in 'where clause'

SELECT `visitas`.*, CONCAT('', COALESCE(j0d9372a2.NOMBRE, ''), ' ', COALESCE(j0d9372a2.APELLIDOS, ''), '') as s0d9372a2, CONCAT('', COALESCE(j422560cb.NOMBRE, ''), ' ', COALESCE(j422560cb.APELLIDOS, ''), '') as s422560cb, CONCAT('Desde: ', COALESCE(j6bea6422.INICIO, ''), ' Hasta: ', COALESCE(j6bea6422.FIN, ''), '') as s6bea6422 FROM (`visitas`) LEFT JOIN `visitadores` as j0d9372a2 ON `j0d9372a2`.`VISITADOR` = `visitas`.`VISITADOR` LEFT JOIN `usuarios` as j422560cb ON `j422560cb`.`USUARIO` = `visitas`.`USUARIO` LEFT JOIN `agenda` as j6bea6422 ON `j6bea6422`.`CITA` = `visitas`.`CITA` WHERE `j813436ef`.`VISITADOR` = '1' LIMIT 25

Filename: C:\Program Files\EasyPHP-5.3.6.1\www\plataforma\pruebavisitadores\crud\system\database\DB_driver.php

Line Number: 330

thanks again for your help!!!

[web-johnny]

Yes you are right I am sorry. The answer it was more simple than I thought. Try this one:

$crud->where('visitas.VISITADOR',$_SESSION['codigo']);

and for member:

$crud->where('visitas.MEMBER',$_SESSION['code']);

[JPintor]

oh yes oh yes man!

you're a machine very thanks really the complement is something great Johnny

[php_lover]

hi when i make relation is got that error

 

"

         A Database Error Occurred
        

Error Number: 1064

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 'AS s29a255fe
FROM (gen_locationsetup)
LEFT JOIN `gencurrency_setup` as j29a255fe' at line 1

SELECT `gen_locationsetup`.*, j29a255fe.Currency Code AS s29a255fe
FROM (gen_locationsetup)
LEFT JOIN `gencurrency_setup` as j29a255fe ON `j29a255fe`.`Currency_Code` = `gen_locationsetup`.`Currency_Code`
LIMIT 25

Filename: C:\wamp\www\Crud\CodeIgniter_2.0.0\system\database\DB_driver.php

Line Number: 330"

   

[Dedi Triyanto]

@Jpintor..

I think the error because ambigous name fields.. Are you sure that there is same fields name in your two tables? its ok if you make some identifier table name in front but I think it isnot good for fields name.. please make fields diferent every tables.. you can make with initial or prefix every your fields....

 

 

Thank u

[raghav24sep]

         A Database Error Occurred  Error Number: 1064

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 ''tb_customer'' at line 1

SHOW COLUMNS FROM 'tb_customer'

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

Line Number: 330

   

Please tell me the solution of the above code. Whenever im setting or not setting the table name above error occures.

Itz urgent. Please tell the solutions.

[davidoster]

Do you put on set_table single quotes ' ' twice?

$crud->set_table(''offices'');

The above ' are single quotes not double!

The above line should be $crud->set_table('offices');

[javi150985]

I had the same problem, i tried to use at the same time, $crud->where and $crud->set_relation.

But this answer  "$crud->where('field.TableName',$data);", solved my problem.

Very Thanks.