Jump to content


Problem with WHERE and SET-RELATION

where set relations

  • Please log in to reply
9 replies to this topic

#1 JPintor

JPintor

    Newbie

  • Members
  • Pip
  • 5 posts
  • LocationAlcazar de San Juan, España

Posted 16 August 2012 - 09:36 AM

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
Javier Pintor

#2 web-johnny

web-johnny

    grocery CRUD Author

  • Administrators
  • 1,137 posts
  • LocationLondon

Posted 16 August 2012 - 10:10 AM

Hello 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
Posted Image

#3 JPintor

JPintor

    Newbie

  • Members
  • Pip
  • 5 posts
  • LocationAlcazar de San Juan, España

Posted 16 August 2012 - 10:33 AM

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!!!
Javier Pintor

#4 web-johnny

web-johnny

    grocery CRUD Author

  • Administrators
  • 1,137 posts
  • LocationLondon

Posted 16 August 2012 - 10:39 AM

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']);

Posted Image

#5 JPintor

JPintor

    Newbie

  • Members
  • Pip
  • 5 posts
  • LocationAlcazar de San Juan, España

Posted 16 August 2012 - 10:43 AM

oh yes oh yes man!

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

#6 php_lover

php_lover

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 14 February 2013 - 07:48 AM

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"

   


#7 Dedi Triyanto

Dedi Triyanto

    Newbie

  • Members
  • Pip
  • 4 posts

Posted 16 February 2013 - 03:23 PM

@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



#8 raghav24sep

raghav24sep

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 25 March 2013 - 01:42 PM


         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.



#9 davidoster

davidoster

    Grocery CRUD Ninja

  • Advanced Member
  • PipPipPip
  • 1,068 posts
  • LocationAthens, Greece

Posted 25 March 2013 - 02:31 PM

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');


____________________________________________________________

 

rtfm_small.jpg---!!!Please read these guidelines before asking to the forums!!!---

____________________________________________________________

 

David Oster aka George Pasparakis,
http://odphotography.com
http://eletter.gr


#10 javi150985

javi150985

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 10 January 2019 - 03:20 PM

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.







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users