How to prevent inserting if id and date already existed in the database?

Question

The table name is "evaluation" with column player_id,player_eval and eval_date.
I evaluate players let's say everyday, so if I evaluate the specific player, the data will be save in the database. Now if I will save the same player at the same day the data will not be inserted by using the player_id and eval_date.

Any Idea how to do it? Thanks

PS: player_id isn't a primary key so I can insert the player in the same table with the same id in different date.

 public function update() {
 $players = array();

foreach ($this->input->post('checkbox') as $checkbox) {
    list($value, $player_id) = explode('::', $checkbox);
    self::update_player($players, $player_id, 'player_att1', $value);
}
foreach ($this->input->post('checkbox2') as $checkbox) {
    list($value, $player_id) = explode('::', $checkbox);
    self::update_player($players, $player_id, 'player_att2', $value);
}
foreach ($this->input->post('checkbox3') as $checkbox) {
    list($value, $player_id) = explode('::', $checkbox);
    self::update_player($players, $player_id, 'player_att3', $value);
}
foreach ($this->input->post('checkbox4') as $checkbox) {
    list($value, $player_id) = explode('::', $checkbox);
    self::update_player($players, $player_id, 'player_att4', $value);
}
$this->load->model('Evaluation_model');
foreach ($players as $player_id => $data) {
    $this->Evaluation_model->editview($player_id, $data);
}
redirect(base_url('Evaluations/evaluate'));
}

static function update_player(&$players, $id, $property, $value) {
if (!isset($players[$id])) {
    $players[$id] = array(
        'player_id' => $id,
        'player_att1' => 0,
        'player_att2' => 0,
        'player_att3' => 0,
        'player_att4' => 0,
        'player_atotal' => 0
    );
}
$players[$id][$property] = $value;
$players[$id]['player_atotal'] += $value;
}

score 1 · Accepted Answer · answered Sep 30 '17 at 14:14

1

First you need to check whether the player with the specified date is existing or not by using simple checking the count of the player as follows

/* This will get you the count of the player for the specific date */
    $playerAlreadyExistsCount = $this->db
    ->where('player_id', $palyerId)
    ->where('eval_date', $evalDate)
    ->count_all('evaluation');

    if($playerAlreadyExistsCount > 0){
       //Player already exists. You can set a flashdata and show the user that the player is already inserted
    }else{
       //Player is not existing so create the new player here.
    }

answered Sep 30 '17 at 14:14

Channaveer Hakari

2,769
3
34
45

How can I execute this? "$playerAlreadyExistsCount = $this->db" just starting. – Kate Sep 30 '17 at 14:32
You need to put the above code in your codeigniter model. Here I have done a chaining which can also be written in one line as $this->db->where()->where()->count_all('table_name'); If you do chaining then it will be lot simpler. – Channaveer Hakari Sep 30 '17 at 14:43

score 0 · Answer 2 · answered Sep 30 '17 at 14:13

You can prevent the insertion of a row when is already present another one with the same "id" and same "date" using the SQL UNIQUE Constraints. https://stackoverflow.com/a/3474309/2768318, altering your database.

If you want to avoid the insertion via PHP, you should first read all the PLAYER with that id, and then check if there is another with the same date.

How to prevent inserting if id and date already existed in the database?

2 Answers2