SQL to prevent duplicate Insertions

[eluser]Volkof[/eluser]
Hi guys,

Can anybody point out to me where is the error I am making here. Basically what I'm trying to do is to prevent duplicate Insertions for the 'review' column.

For example, if someone types a review and presses the submit button twice, at least the model will check that the value is inserted on first button press and will not insert for second time.

Code:

INSERT INTO Review (review, userID, moduleID, recommendation)
VALUES ('test', 2, 235, 'easy')
WHERE NOT EXISTS
(SELECT review FROM Review
WHERE review = 'test')

and I got shot back by this error

Code:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS
(SELECT review FROM Review
WHERE review = 'test')' at line 3

but I couldnt figure out what is wrong. Can anyone enlighten me?

Thank you

[eluser]TheFuzzy0ne[/eluser]
Personally, I'd break that up into two queries. One to check if it's already in the database, and one to insert if it's not. There's the possibility of a race condition, but it's minimal.

Alternatively, you could create a trigger on your database table. This post may help: http://stackoverflow.com/questions/13613...s-in-mysql

[eluser]Otemu[/eluser]
Hi,

To add what TheFuzzy0ne just said, you should look at the codeigniter validation class "if someone types a review and presses the submit button" you could then check your database to see if a record exists.

Example showing a callback:

Code:

<?php

class Form extends CI_Controller {

public function index()
{
  $this->load->helper(array('form', 'url'));

  $this->load->library('form_validation');

  $this->form_validation->set_rules('username', 'Username', 'callback_username_check');
  $this->form_validation->set_rules('password', 'Password', 'required');
  $this->form_validation->set_rules('passconf', 'Password Confirmation', 'required');
  $this->form_validation->set_rules('email', 'Email', 'required|is_unique[users.email]');

  if ($this->form_validation->run() == FALSE)
  {
   $this->load->view('myform');
  }
  else
  {
   $this->load->view('formsuccess');
  }
}

public function username_check($str)
{
  if ($str == 'test')
  {
   $this->form_validation->set_message('username_check', 'The %s field can not be the word "test"');
   return FALSE;
  }
  else
  {
   return TRUE;
  }
}

}
?>

[eluser]Volkof[/eluser]
Thanks guys,

I understand race condition is still a problem even if I break up the code, so I decided to gray out the Submit button after onclick.
Just to share my solution

VIEW

Code:

//Gray out submitBTN to prevent multiple clicks
  $submitBTN = array(
      'name'      => 'submit',
      'onclick'  => "document.getElementById('submit').disabled = 'disabled'"
      );
  echo '<li id="postbtn">' .form_submit($submitBTN, 'Post Review', 'id="submit"'). '</li></ul>';

Controller

Code:

//Check for duplicated review
   $isDuplicated = $this->review_model->isDuplicateReview($data);

if(!($isDuplicated)){
    //Insert data into Review Table
    $this->review_model->insertReview($data);
}else{
echo "Duplicate entry!";
}

Model

Code:

//Check if the review is in DB already
public function isDuplicateReview($data)
{
  $sql = "SELECT review, userID, moduleID
    FROM Review
    WHERE review = ? AND userID = ? AND moduleID = ?
    ";
  $query = $this->db->query($sql, array($data['review'], $data['userID'], $data['moduleID']));
  //$query->result();
  
  //If there are rows, means this review is duplicated
  if($query->num_rows() > 0){
   return TRUE;
  }else{
   return FALSE;
  }
}