+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: SQL to prevent duplicate Insertions (/showthread.php?tid=57454)
SQL to prevent duplicate Insertions - El Forum - 03-15-2013
[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 3but I couldnt figure out what is wrong. Can anyone enlighten me?
Thank you
SQL to prevent duplicate Insertions - El Forum - 03-15-2013
[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/1361340/how-to-insert-if-not-exists-in-mysql
SQL to prevent duplicate Insertions - El Forum - 03-15-2013
[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;
}
}
}
?>SQL to prevent duplicate Insertions - El Forum - 03-15-2013
[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;
}
}