CodeIgniter: Unknown column 'xxx' in 'field list'

Question

Here's my exported BD table:

CREATE TABLE `hta_users` (
  `id` int(11) NOT NULL auto_increment,
  `nombre` varchar(100) collate utf8_spanish_ci NOT NULL,
  `apellidos` varchar(255) collate utf8_spanish_ci NOT NULL,
  `nif` varchar(10) collate utf8_spanish_ci NOT NULL,
  `direccion` varchar(255) collate utf8_spanish_ci NOT NULL,
  `cp` varchar(5) collate utf8_spanish_ci NOT NULL,
  `poblacion` varchar(255) collate utf8_spanish_ci NOT NULL,
  `provincia` int(2) NOT NULL,
  `telefono` varchar(9) collate utf8_spanish_ci NOT NULL,
  `edad` int(3) NOT NULL,
  `retribucion` int(1) NOT NULL,
  `entidad` varchar(4) collate utf8_spanish_ci NOT NULL,
  `oficina` varchar(4) collate utf8_spanish_ci NOT NULL,
  `dc` varchar(2) collate utf8_spanish_ci NOT NULL,
  `cc` varchar(10) collate utf8_spanish_ci NOT NULL,
  `centro` varchar(255) collate utf8_spanish_ci NOT NULL,
  `email` varchar(255) collate utf8_spanish_ci NOT NULL,
  `especialidad` int(2) NOT NULL,
  `parent` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `nif` (`nif`,`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci AUTO_INCREMENT=1 ;

Here's my model function:

function add_user( $nombre, $apellidos, $nif, $direccion, $cp, $poblacion, $provincia, $telefono, $edad, $retribucion, $cc_entidad, $cc_oficina, $cc_dc, $cc_cc, $centro, $email, $especialidad, $parent )
{
    $tbl = $this->db->dbprefix('users');

    if( $retribucion == 1 )
    {
        $sql = array(
            'nombre' => $nombre,
            'apellidos' => $apellidos,
            'nif' => $nif,
            'direccion' => $this->db->escape($direccion),
            'cp' => $cp,
            'poblacion' => $poblacion,
            'provincia' => $provincia,
            'telefono' => $telefono,
            'edad' => $edad,
            'retribucion' => $retribucion,
            'entidad' => $cc_entidad,
            'oficina' => $cc_oficina,
            'dc' => $cc_dc,
            'cc' => $cc_cc,
            'centro' => $centro,
            'email' => $email,
            'especialidad' => $especialidad,
            'parent' => $parent
        );
    }
    else
    {
        $sql = array(
            'nombre' => $nombre,
            'apellidos' => $apellidos,
            'nif' => $nif,
            'direccion' => $this->db->escape($direccion),
            'cp' => $cp,
            'poblacion' => $poblacion,
            'provincia' => $provincia,
            'telefono' => $telefono,
            'edad' => $edad,
            'retribucion' => $retribucion,
            'centro' => $centro,
            'email' => $email,
            'especialidad' => $especialidad,
            'parent' => $parent
        );
    }

    $this->db->insert($tbl, $sql); 

    if( $this->db->affected_rows() == 0 ) return false;
    else return true;
}

Here's my controller piece of code:

if( $this->users->add_user($nombre, $apellidos, $nif, $direccion, $cp, $poblacion, $provincia, $telefono, $edad, $retribucion, $cc_entidad, $cc_oficina, $cc_dc, $cc_cc, $centro, $email, $especialidad, $parent) )
{
    $data['form_error'] = 'Se ha añadido al usuario.';

    $data['module'] = 'registro';
    $this->load->view('template', $data);
}
else
{
    $data['form_error'] = 'Se ha producido un error al agregar al usuario a la BD.';

    $data['module'] = 'registro';
    $this->load->view('template', $data);
}

And that's the error i'm getting:

A Database Error Occurred

Error Number: 1054

Unknown column 'entidad' in 'field list'

INSERT INTO `hta_users` (`nombre`, `apellidos`, `nif`, `direccion`, `cp`, `poblacion`, `provincia`, `telefono`, `edad`, `retribucion`, `entidad`, `oficina`, `dc`, `cc`, `centro`, `email`, `especialidad`, `parent`) VALUES ('nombre', 'apellidos', '12345678Q', '\'elm st 666\'', '08008', 'Barcelona', '1', '666555666', '2', 1, '9999', '9999', '99', '9999999999', 'home', '[email protected]', '1', '0')

Can someone help? I don't know what's happening nor why... :/

Answer 1

Here is an article I wrote that will help you with debugging CodeIgniter ActiveRecord. Basically use $this->db->last_query() to see what ActiveRecord built your query to be and run it in phpMyAdmin to see if the query itself is valid.

There are a few other tips too, but from what I can see here everything looks fine.

Sneaky tip: you can use:

return !$this->db->affected_rows() == 0;

instead of:

if( $this->db->affected_rows() == 0 ) return false;
else return true;

Answer 2

Well, after some hours of harddebuggin' got it working... :P

Same database table structure.

My new model function:

function add_user( $user_data )
{
    $tbl = $this->db->dbprefix('users');

    $this->db->insert($tbl, $user_data);

    return !$this->db->affected_rows() == 0;
}

My new controller piece of code:

$user_data = array(
    'nombre'        => $this->input->post('nombre'),
    'apellidos'     => $this->input->post('apellidos'),
    'nif'           => $this->input->post('nif'),
    'direccion'     => $this->db->escape($this->input->post('direccion')),
    'cp'            => $this->input->post('cp'),
    'poblacion'     => $this->input->post('poblacion'),
    'provincia'     => $this->input->post('provincia'),
    'telefono'      => $this->input->post('telefono'),
    'edad'          => $this->input->post('edad'),
    'retribucion'   => $this->input->post('retribucion'),
    'entidad'       => $this->input->post('cc_entidad'),
    'oficina'       => $this->input->post('cc_oficina'),
    'dc'            => $this->input->post('cc_dc'),
    'cc'            => $this->input->post('cc_cc'),
    'centro'        => $this->input->post('centro'),
    'email'         => $this->input->post('email'),
    'especialidad'  => $this->input->post('especialidad'),
    'parent'        => $this->session->userdata('parent')
);

// db adding
if( $this->users->add_user($user_data) )
{
    // logged in!!
}
else
{
    // grrr error!!
}

It looks much pretty now! :)

Hope this helps some lost souls to DO NOT construct the AR data array into the model, but the controller.