问题描述:

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.

My query for creating the users table is following:

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 a foreign keys.

In my form, I have select menu where I can choose to create a certain type of user. If I choose to create admin, form will not display the hidden select menus for school and subject. If I choose to create moderator, form will display the hidden select menus. This is done with JavaScript.

Everything works well when I create an admin. It will store all the values in the database and sets school_id and subject_id values to NULL. Problem occurs when I decide to create a moderator. It still sets the school_id and subject_id values to even though I've set those values in my select menus.

I know I need to build a dynamic query. Just don't know how to implement it in the right way in CodeIgniter. If I was just coding with spaghetti PHP, it would be a peace of cake, but since I desperately want to learn using MVC frameworks, that is not an option.

Here's my 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']

);

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

}

Should I include those school_id and subject_id values in the array or not?

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'),

);

// Here's where I'm trying to build the dynamic part

if($this->input->post('school_id') && $this->input->post('subject_id'))

{

$data['school_id'] = $this->input->post('school_id');

$data['subject_id'] = $this->input->post('subject_id');

}

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

{

// Inserted successfully

}

Obviously this is not the correct way to do this since it's not working. What should I do to make it work using best possible practices?

Thanks in advance !

网友答案:

In PHP, there is no one answer but a few depending on how you code. Just keep in mind to apply your logic all over your project and not change it suddenly for this or that model/controller.

As for me, I would do it like this :

NEVER trust user's input.

-Check for all your $data so that password/username (and whatever) are not empty ! You need to do this in javascript AND PHP !

-Seeing you won't have a lot of schools & subjects, you can check their validity (but you are using InnoDB so that's not necessary)

public function add_user($data)
{
    $minimalUser = array('username','password');
    if (count(array_diff($minimalUser, array_keys($data))) != 0) {
        return false;  //If a $data is missing, do not create the user !
    }

    $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' => isset($data['subject_id']) ? $data['subject_id'] : null,
            'school_id' => isset($data['school_id']) ? $data['school_id'] : null
        );
    $this->db->insert('users', $data);
}

This code works for, let's say, all your cases ! Moderator, Admin & end user.

An other point : Do a check on the email provided. Something with : filter_var("[email protected]", FILTER_VALIDATE_EMAIL)

网友答案:

Well actually that didn't work. Now I'm getting a database error about foreign keys:

Cannot add or update a child row: a foreign key constraint fails (`mydatabase`.`users`, CONSTRAINT `fk_school_id` FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`) ON UPDATE CASCADE)

    INSERT INTO `users` (`username`, `name`, `email`, `password`, `user_type`, `phone_number`, `subject_id`, `school_id`) VALUES ('xxx', 'xxx', 'xxx', '$2a$08$VT4QYX9kiy8i7ivLmbVVre7AQfiKxAQ.0E9jGF0PbS.PNVTTmpLK2', 'moderator', 'xxx', 0, 0)

Could it be because I use another controller and model to get those subjects and schools from the database?

Controller to get all the subjects and schools:

<?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');
    }


}

?>

Model to get all the schools and subjects:

<?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 my view (dashboard.php):

<?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" style="float: 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" style="margin-top: 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" style="margin: 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(); ?>
相关阅读:
Top