[David Leiva]

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]

same problem when use pagination...

[victor]

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

[davedriesmans]

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]

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]

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]

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

to see what happens.

[davedriesmans]

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

[davider]

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]

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]

Yes, this is the best answer.

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

 

Thanks.

[Oscar Romero]

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]

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]

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]

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

[Kukuh Setiawan]

 

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]

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]

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]

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

 

please refer to the provided solution in there..

 

Happy GCing :)

[Phpgeek Saintek Uin]

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]