⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

ajax_list_info ambiguous field in search



David Leiva

David Leiva
  • profile picture
  • Member

Posted 27 September 2012 - 17:43 PM

Hello,

I have a table with some fields and some relations with others.

I get the list view with no problem, but when i try to filter the results (using the search feature in flexigrid table) I get:

500 Internal Server Error - http://localhost/bb/admin/manage/persons/ajax_list_info"

With firebug i got the server response and the problem is that the query is ambiguous in one field..

Error Number: 1052</p><p>Column 'name' in where clause is ambiguous</p><p>
how could i sove this ?

thanks

David Leiva

David Leiva
  • profile picture
  • Member

Posted 27 September 2012 - 18:14 PM

same problem when use pagination...

victor

victor
  • profile picture
  • Member

Posted 29 September 2012 - 13:07 PM

Hi! Columns must have unique names ! You have same columns in both tables.

davedriesmans

davedriesmans
  • profile picture
  • Member

Posted 31 May 2013 - 14:14 PM

i have the same problem : Column 'ID' in where clause is ambiguous 

... and I always use ID as my the primary key :)

 

i wondering how you autocreate then the /edit/1 and /delete/1 etc... ?

 

 

 


davidoster

davidoster
  • profile picture
  • Member

Posted 01 June 2013 - 07:23 AM

Hello and welcome to the forums [member=davedriesmans].

Please post your controller's code and the table(s) structure so we can help you out.


davedriesmans

davedriesmans
  • profile picture
  • Member

Posted 04 June 2013 - 14:48 PM

controller

function funbas()
{
        try{
            $crud = new grocery_CRUD();

            $crud->set_table('funbas');
            $crud->set_relation('bibref', 'funbasBib', 'bib_titel');
            $crud->set_subject('funbas');
            $crud->columns('ID', 'bibref', 'GENUS', 'SOORTNAAM', 'SPECIES', 'NEDNAAM', 'NEDSYN');
            $output = $crud->render();
            $this->view_output($output, $data);


        }catch(Exception $e){
            show_error($e->getMessage().' --- '.$e->getTraceAsString());
        }
}
 
--
-- Table structure for table `funbas`
--

CREATE TABLE IF NOT EXISTS `funbas` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `exportNUMMER` double DEFAULT NULL,
  `GENUS` varchar(40) DEFAULT NULL,
  `SOORTNAAM` varchar(180) DEFAULT NULL,
  `SPECIES` varchar(120) DEFAULT NULL,
  `bibref` int(11) DEFAULT NULL,
  `SYNONIEM` varchar(150) DEFAULT NULL,
  `status` varchar(4) DEFAULT NULL,
  `SUBGENERIE` varchar(22) DEFAULT NULL,
  `NEDNAAM` varchar(80) DEFAULT NULL,
  `NEDSYN` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10928 ;
 
--
-- Table structure for table `funbasBib`
--

CREATE TABLE IF NOT EXISTS `funbasBib` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `AUTEUR` varchar(200) DEFAULT NULL,
  `bib_titel` varchar(300) DEFAULT NULL,
  `JAAR` varchar(8) DEFAULT NULL,
  `UITGEVER` varchar(180) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=340 ;
 
 
The error is when i do a search, 
in /funbel/crud/funbas/ajax_list_info i get:
 


Column 'ID' in where clause is ambiguous


SELECT *FROM (`funbas`)LEFT JOIN `funbasBib` as jc410d1f0 ON `jc410d1f0`.`ID` = `funbas`.`bibref`WHERE `ID` LIKE '%overzicht%'OR `jc410d1f0`.`bib_titel` LIKE '%overzicht%'OR `GENUS` LIKE '%overzicht%'OR `SOORTNAAM` LIKE '%overzicht%'OR `SPECIES` LIKE '%overzicht%'OR `NEDNAAM` LIKE '%overzicht%'OR `NEDSYN` LIKE '%overzicht%'
when i remove the $crud->set_relation('bibref', 'funbasBib', 'bib_titel'); the search is fine (but of course on the id and not on the relation)

davidoster

davidoster
  • profile picture
  • Member

Posted 04 June 2013 - 22:24 PM

Try to change the columns line like this : $crud->columns('bibref', 'GENUS', 'SOORTNAAM', 'SPECIES', 'NEDNAAM', 'NEDSYN');

to see what happens.


davedriesmans

davedriesmans
  • profile picture
  • Member

Posted 05 June 2013 - 07:38 AM

@davidoster that did the trick... grocerycrud seems to be wonderful new world  :)


davider

davider
  • profile picture
  • Member

Posted 24 June 2013 - 14:37 PM

In my opinion that's not a good answer because:

 - you should be able to choose any db field name

 - I need to search by that particular field so it muast be present in columns array

 

I found this trick:

$crud->set_relation('ID','funbas','ID');


davidoster

davidoster
  • profile picture
  • Member

Posted 24 June 2013 - 22:29 PM

In my opinion that's not a good answer because:

 - you should be able to choose any db field name

 - I need to search by that particular field so it muast be present in columns array

 

I found this trick:

$crud->set_relation('ID','funbas','ID');

 

True. In your case you can try to put the table name also 'mytable.my_primary_key' and see if it solves the problem.


davider

davider
  • profile picture
  • Member

Posted 25 June 2013 - 07:08 AM

Yes, this is the best answer.

Then you could invoke display_as method in order to "clean" field name.

 

Thanks.


Oscar Romero

Oscar Romero
  • profile picture
  • Member

Posted 28 August 2013 - 21:13 PM

Hello guys!

 

I believe that the problem is when two or more tables have the same field name, In my case I need to filter with the field of the base table, Today I had this problem, fix the Grocery_CRUD.php file following this steps...

 

1- Modify the get_columns method.

2- Modify the showList method.

 

1.1- Add these lines before the first foreach:

 

$ci =& get_instance();
$base_table_fields  = $ci->db->list_fields($this->basic_db_table);
 

1.2- Add the next code inner the second foreach:


else 
{
$fields = $base_table_fields;
foreach ($fields as $field_name) 
{
if ($field_name == $column) 
{
$new_column = $this->basic_db_table.'.'.$column;
$column = $new_column;
$this->columns[$col_num] = $new_column;
$this->display_as[$column] = ucfirst(str_replace('_',' ', $field_name));
}
}
}
2.1 - Add this foreach after the $data->columns = $this->get_columns();
 
foreach ($data->columns as $column) 
{
$new_column_name = substr(strstr($column->field_name, "."), 1);


if ( ! empty($new_column_name)) 
{
$column->field_name = $new_column_name;
}
}
This work for me, Try with a test environment or separate example.

 

Thanks and sorry for my English.

 

Regards!

 


davidoster

davidoster
  • profile picture
  • Member

Posted 28 August 2013 - 23:43 PM

Hello and welcome to the forums [member=oscar romero].

Thank you for your contribution.

You could post it as an issue also here: https://github.com/scoumbourdis/grocery-crud/issues


Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 29 August 2013 - 15:03 PM

That is exactly the 1 i was saying in 1 of our last conversation if you remember David.. only thing is .. i missed out as where :( .. but thanx for reminding :)


davidoster

davidoster
  • profile picture
  • Member

Posted 30 August 2013 - 04:32 AM

Yeap, I remember [member=Amit Shah]. ;)


Kukuh Setiawan

Kukuh Setiawan
  • profile picture
  • Member

Posted 05 September 2013 - 07:05 AM

 

Hello guys!

 

I believe that the problem is when two or more tables have the same field name, In my case I need to filter with the field of the base table, Today I had this problem, fix the Grocery_CRUD.php file following this steps...

 

1- Modify the get_columns method.

2- Modify the showList method.

 

1.1- Add these lines before the first foreach:

 

$ci =& get_instance();
$base_table_fields  = $ci->db->list_fields($this->basic_db_table);
 

1.2- Add the next code inner the second foreach:


else 
{
$fields = $base_table_fields;
foreach ($fields as $field_name) 
{
if ($field_name == $column) 
{
$new_column = $this->basic_db_table.'.'.$column;
$column = $new_column;
$this->columns[$col_num] = $new_column;
$this->display_as[$column] = ucfirst(str_replace('_',' ', $field_name));
}
}
}
2.1 - Add this foreach after the $data->columns = $this->get_columns();
 
foreach ($data->columns as $column) 
{
$new_column_name = substr(strstr($column->field_name, "."), 1);


if ( ! empty($new_column_name)) 
{
$column->field_name = $new_column_name;
}
}
This work for me, Try with a test environment or separate example.

 

Thanks and sorry for my English.

 

Regards!

 

 

Hi.. can you tell the exact position to type your code? or can you post your modified version of showList() and get_columns() method? i had a problem when i use "search all" in flexigrid.

 

Thanks


Oscar Romero

Oscar Romero
  • profile picture
  • Member

Posted 06 September 2013 - 23:14 PM

Hello Kukuh, Sorry for answer too late .

 

Is correct, If you are receiving the same message, Is because I missed this scenario  :rolleyes: :

Error Number: 1054

Unknown column 'columnName' in 'where clause'

I will fix this error, I'm using the 1.3 version, with a custom library/model for Twitter bootstrap.

 

Regards!


Terence Vusile Silonda

Terence Vusile Silonda
  • profile picture
  • Member

Posted 26 September 2014 - 13:23 PM

I got this same error. Unfortunately, for me, playing around with fields did not solve it. The reason I was getting the error is because a table was relating to itself. Let's say i have a table of users, and some of the users would be like parents of other users, so I would relate a table to itself. 

 

In my situation, I didn't need to show the relation in the view page, so what I did was to get the state, and only do the relation on edit, like so:

 

function users()
{

$state = $crud->getState();
   


   if($state == 'edit')
   {
          $crud->set_relation('Parent','users','{FirstName} {LastName}', array('Active' => '1'));
       
   }

// more code follows

Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 27 September 2014 - 05:15 AM

/topic/1975-bug-in-the-search/

 

please refer to the provided solution in there..

 

Happy GCing :)


Phpgeek Saintek Uin

Phpgeek Saintek Uin
  • profile picture
  • Member

Posted 14 November 2014 - 04:05 AM

I got this same error. Unfortunately, for me, playing around with fields did not solve it. The reason I was getting the error is because a table was relating to itself. Let's say i have a table of users, and some of the users would be like parents of other users, so I would relate a table to itself. 

 

In my situation, I didn't need to show the relation in the view page, so what I did was to get the state, and only do the relation on edit, like so:

function users()
{

$state = $crud->getState();
   


   if($state == 'edit')
   {
          $crud->set_relation('Parent','users','{FirstName} {LastName}', array('Active' => '1'));
       
   }

// more code follows


 

I Have the same problem like this, and this is my solution. Its work for me. I am using Grocery crud v.1.4.1 in CI 1.7.x
[attachment=858:grocery crud solution for search problem on table with relation.png][attachment=858:grocery crud solution for search problem on table with relation.png]