Kate Kate - 3 years ago 166
MySQL Question

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

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;
}

Answer Source

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.
    }
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download