⚠ 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

Unknown column 'representante' in 'where clause', please somebody helpme



frsanchez

frsanchez
  • profile picture
  • Member

Posted 27 November 2015 - 08:05 AM

/*this is the code in my controller*/

            $crud = new grocery_CRUD();
            $crud->set_model('promocion_grocery_model');            

            $crud->set_table('promocion');

                $crud->where('estado','revision');    
            $crud->set_subject('promoción');
                    

            $crud->columns('nombre','representante','telefono_contacto');
            $crud->display_as('telefono_contacto','teléfono de contacto');
            $crud->display_as('nombre_empresa','nombre de empresa');
            $crud->display_as('img','imagen');


            $crud->unset_edit();
            $crud->unset_delete();
            $crud->unset_export();
            $crud->unset_print();

            $crud->fields('nombre','id_empresa','ubicaciones','precio','img','descripcion','tipo_pago','calificacion','fecha_publicacion','estado');    

            $crud->display_as('id_empresa','empresa');
            
            if ($crud->getState() == 'add' OR $crud->getState() == 'edit')
            {

            $this->load->model('empresa_model');
            $data = $this->empresa_model->get_arr_nombres();    
            $crud->field_type('id_empresa','dropdown',$data);
                

            $this->load->model('ubicacion_model');
            $data = $this->ubicacion_model->get_arr_all();
    
            $crud->field_type('ubicaciones','multiselect',$data);

            $crud->field_type('tipo_pago','enum',array('efectivo','online'));
            }
            $crud->field_type('nombre','string');
            $crud->field_type('empresa','string');
            $crud->field_type('precio','integer');
            $crud->field_type('descripcion','text');
            $crud->field_type('calificacion','invisible');
            $crud->field_type('fecha_publicacion','invisible');
            $crud->field_type('estado','invisible');

            $crud->set_field_upload('img','rest/img');


            $crud->required_fields('nombre', 'id_empresa','precio','img','descripcion','tipo_pago');
            $crud->set_rules('precio','Precio','numeric');


               $crud->callback_before_insert(array($this,'callback_before_insert_promocion'));
           
            $crud->callback_read_field('ubicaciones', array($this,'callback_field_ubicaciones_promocion'));
        
            $crud->unset_read_fields('id_empresa');

            $output = $crud->render();


/*this is de code of my custom model with join*/


 function get_list()
    {

     if($this->table_name === null)
      return false;
    
     $select = "{$this->table_name}.*";
    
  $select .= ",empresa.id_empresa as id_emp, empresa.nombre as nombre_empresa, empresa.representante, empresa.telefono as telefono_contacto";
 
     if(!empty($this->relation))
      foreach($this->relation as $relation)
      {
       list($field_name , $related_table , $related_field_title) = $relation;
       $unique_join_name = $this->_unique_join_name($field_name);
       $unique_field_name = $this->_unique_field_name($field_name);
      
    if(strstr($related_field_title,'{'))
        $select .= ", CONCAT('".str_replace(array('{','}'),array("',COALESCE({$unique_join_name}.",", ''),'"),str_replace("'","\\'",$related_field_title))."') as $unique_field_name";
       else      
        $select .= ", $unique_join_name.$related_field_title as $unique_field_name";
      
       if($this->field_exists($related_field_title))
        $select .= ", {$this->table_name}.$related_field_title as '{$this->table_name}.$related_field_title'";
      }
    
     $this->db->select($select, false);
    
  $this->db->join('empresa','empresa.id_empresa = '. $this->table_name . '.id_empresa');
  $this->db->order_by('id_promocion', 'ASC');
  $results = $this->db->get($this->table_name)->result();
    
     return $results;
    }



/*this are the tables that i am using*/


CREATE TABLE IF NOT EXISTS `promocion` (
  `id_promocion` int(11) NOT NULL AUTO_INCREMENT,
  `id_empresa` int(11) NOT NULL,
  `nombre` text,
  `calificacion` double DEFAULT NULL,
  `precio` text,
  `img` text,
  `descripcion` text,
  `fecha_publicacion` datetime NOT NULL,
  `likes` int(11) NOT NULL DEFAULT '0',
  `estado` text NOT NULL,
  `tipo_pago` text NOT NULL,
  `ubicaciones` varchar(1) NOT NULL,
  PRIMARY KEY (`id_promocion`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='datos referentes a las promociones' AUTO_INCREMENT=18 ;


CREATE TABLE IF NOT EXISTS `empresa` (
  `id_empresa` int(11) NOT NULL AUTO_INCREMENT,
  `nombre` text NOT NULL,
  `representante` text NOT NULL,
  `telefono` text NOT NULL,
  `promociones` varchar(1) NOT NULL,
  `ubicacion` varchar(1) NOT NULL,
  PRIMARY KEY (`id_empresa`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='datos de las empresas' AUTO_INCREMENT=12 ;



then when I do a search I get this error

<p>Error Number: 1054</p><p>Unknown column 'representante' in 'where clause'</p><p>SELECT *
FROM `promocion`
WHERE `estado` = 'revision'
OR  `nombre` LIKE '%555%' ESCAPE '!'
OR  `representante` LIKE '%555%' ESCAPE '!'
OR  `telefono_contacto` LIKE '%555%' ESCAPE '!'
HAVING `estado` = 'revision'</p><p>Filename: C:/xampp/htdocs/odi/application/models/Grocery_crud_model
.php</p><p>Line Number: 189</p>    </div>



guys sorry for my english is bad, i hope somebody helpme :wacko:

 

 


Paul Savostin

Paul Savostin
  • profile picture
  • Member

Posted 28 November 2015 - 03:18 AM

I was investigate your situation

U can search only by that fields which present in main table and relate table by ->set_relation('id_empresa','empresa','representante'), then search will work!

If u need more related field only one thing u can do - join all fields that u need in one column like

->set_relation('id_empresa','empresa','{representante} {telefono})  without custom model


in list column will be all in one and search u will make by only that column where u have multiplie values from related table!

 

 

Hope it helps

P.S. Maybe try another theme like datatables then u will not have such problem cause search not using ajax - all happens with JS


frsanchez

frsanchez
  • profile picture
  • Member

Posted 02 December 2015 - 01:06 AM

thanks bro for your answer

 

I was investigate your situation

U can search only by that fields which present in main table and relate table by ->set_relation('id_empresa','empresa','representante'), then search will work!

If u need more related field only one thing u can do - join all fields that u need in one column like

->set_relation('id_empresa','empresa','{representante} {telefono})  without custom model

in list column will be all in one and search u will make by only that column where u have multiplie values from related table!

 

 

Hope it helps

P.S. Maybe try another theme like datatables then u will not have such problem cause search not using ajax - all happens with JS

thanks bro for your answer :)

 

but the real solution is to find the way of modify the query that is executed when the form #filtering_form is submitted, for now i am going to add two more fields to my table "empresa" that takes the value of "id_empresa" automatically when insert data to be able to use set_relation() and then search will work! but i will have to modify my database and modify my model of data with unnecessary fields -_-

 

i am going to be investigating about this, if i find the solution I will communicate you :)