CodeIgniter - Dynamic Query problem

[B_CooperA]

I asked this earlier at StackOverflow but didn't got any solutions from there, so I decided to post my problem in a more appropriate forum. 

 

So, I have form for my project where I can create users into database. There

are three types of user types, admins, moderators and end users. 

 

CREATE TABLE IF NOT EXISTS users (

 

 id SMALLINT(5) UNSIGNED PRIMARY KEY AUTO_INCREMENT,

 name VARCHAR(70) NOT NULL,

 email VARCHAR(70) NOT NULL,

 username VARCHAR(9) NOT NULL,

 password VARCHAR(60) NOT NULL,

 user_type ENUM('admin','moderator','enduser'),

 phone_number VARCHAR(10) NOT NULL,

 

 school_id SMALLINT(5) UNSIGNED DEFAULT NULL,

 CONSTRAINT fk_school_id

 FOREIGN KEY (school_id)

 REFERENCES schools(id)

 ON UPADTE CASCADE ON DELETE RESTRICT,

 

 subject_id SMALLINT(5) UNSIGNED DEFAULT NULL,

 CONSTRAINT fk_subject_id

 FOREIGN KEY (subject_id)

 REFERENCES subjects(id)

 ON UPDATE CASCADE ON DELETE RESTRICT

 

) ENGINE = INNODB;

 

So the school_id and subject_id are working as foreign keys.

 

Here's my model:

 

 

<?php


  class User extends CI_Model {

 

   public function add_user($data) {

 

   $data = array(

     'username' => $data['username'],

     'name' => $data['name'],

     'email' => $data['email'],

     'password' => $data['password'],

     'user_type' => $data['user_type'],

     'phone_number' => $data['phone_number'],

     'subject_id' => empty($data['subject']) ? null : $data['subject'],

     'school_id' => empty($data['school']) ?  null : $data['school']

    );

 

   $this->db->insert('users', $data);

  }

 }

?>

 

 

Here's my controller (part of it actually):

 

     $this->load->model('user');

 

      $name = ucfirst($this->input->post('f_name')). " " .ucfirst($this->input->post('l_name'));

 

      $data = array (

        'name'    => $name,

        'email'   => $this->input->post('email'),

        'username'   => $username,

        'password'   => $this->phpass->hash($password),

        'user_type'  => $this->input->post('user_type'),

        'phone_number'  => $this->input->post('phone_number'),

        'school_id'  => $this->input->post('school'),

        'subject_id'  => $this->input->post('subject')

       );

 

      if($this->user->add_user($data))

      {

                    // Send email to user

                }

 

 

My first thought was that it's not working because of my another model and controller designed to fetch all those subjects and schools from the database. I will include them in here as well, just in case: 

 

 

<?php

 

class Dashboard extends CI_Controller {

 

 public function __construct()

 {

 

  parent::__construct();

  $this->load->model('subject');

 }

 

 public function index() {

 

  $data['subjects'] = $this->subject->get_all_subjects();

  $data['schools'] = $this->subject->get_all_schools();

 

   $this->load->view('header');

  $this->load->view('dashboard', $data);

  $this->load->view('footer');

 } 

}

 

?>

 

<?php

 

class Subject extends CI_Model

{

 

 public function get_all_subjects()

 {

  $this->db->select('*')->from('subjects')->order_by('name', 'asc');

  $query = $this->db->get();

  return $query->result_array();

 }

 

 public function get_all_schools()

 {

 

  $this->db->select('*')->from('schools')->order_by('name', 'asc');

  $query = $this->db->get();

  return $query->result_array();

 }

}

 

 

?>

 

And the view for my dashboard where that add user form locates:

 

<?php echo form_open('users/add_user', $attributes); ?>

 

    <span class="close_form_button_add_user_form">X</span>

 

    <?php echo validation_errors('<div class="login_error">', '</div>'); ?>

 

    <div class="form_row">

    <input type="text" name="f_name" placeholder="Etunimi" class="half-width" />

    <input type="text" name="l_name" placeholder="Sukunimi" class="half-width"  right; margin-right: 24px;"/>

    </div>

 

 

    <div class="form_row">

    <input type="email" name="email" placeholder="Uuden käyttäjän sähköpostiosoite" class="user full-width" />

    </div>

 

 

    <div class="form_row">

    <input type="email" name="email_confirm" placeholder="Sähköpostiosoite uudelleen" class="user full-width" />

    </div>

 

    <div class="form_row">

    <input type="text" name="phone_number" placeholder="Puhelinnumero (0401234567)" class="full-width" />

    </div>

 

 

    <div class="form_row"  20px;">

    <select name="user_type" class="add_user_select" id="select_user_type">

        <option value="">Käyttäjätyyppi</option>

        <option value="admin">Hallinto</option>

        <option value="moderator">Hallinto 2</option>

        <option value="enduser">Pääkäyttäjä</option>

    </select>

 

    <input type="submit" name="add_user" value="Luo käyttäjä" class="login_submit" id="submit_user"  5px 24px 0px 0px;"/>

    </div>

 

    <div class="form_row" id="add_user_optional_information">

    <select name="school" class="add_user_select half-width">

        <option value="">Koulutalo</option>

        <?php  foreach($schools as $school) :?>

        <option value="<?=$school['id']?>"><?=$school['name']?></option>

        <?php endforeach; ?>

    </select>

 

    <select name="subject" class="add_user_select half-width">

        <option value="">Laji</option>

        <?php  foreach($subjects as $subject) :?>

        <option value="<?=$subject['id']?>"><?=$subject['name']?></option>

        <?php endforeach; ?>

    </select>

    </div>

 

<?php echo form_close(); ?>

 

After I submitted my form, I opened the developer tab, and checked what values were included in this post request and everything looked good in there. For some reason, it will always saves those subject_id and school_id as NULL.

 

 

Thanks in advance !

Edited April 15, 2014 by B_CooperA

[sKunKbad]

In your model, school should be school_id, and subject should be subject_id

<?php

  class User extends CI_Model {
 
   public function add_user($data) {
 
   $data = array(
     'username' => $data['username'],
     'name' => $data['name'],
     'email' => $data['email'],
     'password' => $data['password'],
     'user_type' => $data['user_type'],
     'phone_number' => $data['phone_number'],
     'subject_id' => empty($data['subject_id']) ? null : $data['subject_id'],
     'school_id' => empty($data['school_id']) ?  null : $data['school_id']
    );
 
   $this->db->insert('users', $data);
  }
 }
?>