Stopping insertion to the database when the data already exists with Codeigniter

Question

I'd like to know how to prevent an insert into a database when the data is already existing inside it. And how to make an onclick window with a message that alerts the user that the data they're inserting is already inside the database.

How I get inputs from my view goes a little bit like this:

<form method="post" action="<?php echo base_url();?>index.php/Controller/insertdata">    
    <h3>Select Course Code:</h3>
    <select id="employeeid" name="empid">
        <option value="" selected="selected">---Select Course Code---</option>
        <?php foreach ($e_id as $row4): ?>
        <option label="<?php echo $row4['EmpID']; ?>" value="<?php echo $row4['EmpID']; ?>" <?php echo set_select('course', $row4['EmpID'], False); ?>> <?php echo $row4['EmpID'] ; ?> </option>
        <?php endforeach; ?>
    </select>
    <input type="Submit" value="Submit" id="submite_id">

It takes data from the database into the select tag. My database has Employee ID, Employee name, Dept. code, and assignment.

Here's my Controller:

public function insertdata() {
    $this->model->setdata();
    $data['results'] = $this->model->emp_all();
    $this->load->view('edit_view', $data);
}

It inserts the data into the database and redirects the user to a view.

And finally here's how I insert it into the database in my Model:

public function setData(){
    $f1 = $_POST['empid'];
    $f2 = $_POST['empname'];
    $f3 = $_POST['deptcode'];
    $f4 = $_POST['assignment'];

$this->db->query("INSERT INTO employeeDB VALUES('$f1', '$f2', '$f3', '$f4')");
}

The catch is that the users should not be able to assign an employee id, and name to a duplicate assignment on the same department.

Any reply, comment, and insight will be appreciated. Thanks in advance!

Before insert do a select query with where clause if it's returns any row it's duplicate otherwise you can insert it . — JYoThI, Aug 31 '17 at 06:02
How can I check if it has a duplicate? Inside an if statement? — Amiel Vargas, Aug 31 '17 at 06:03
just do this select query before insert `select * from table name where empid=value and empname=value and deptcode=value` if it's return any row it's duplicate otherwise it's not duplicate . — JYoThI, Aug 31 '17 at 06:05
Just put a `'` in any of the values that should prevent an insert ;p — Lawrence Cherone, Aug 31 '17 at 06:09
https://stackoverflow.com/questions/15898599/insert-query-check-if-record-exists-if-not-insert-it — Mayank Vadiya, Aug 31 '17 at 06:09
^Thanks for that! I didn't know there was a "where not" query — Amiel Vargas, Aug 31 '17 at 06:12
Is this correct? $this->db->query("INSERT INTO databasename VALUES('$f1', '$f2', '$f3', '$f4') WHERE NOT EXISTS (Select * from databasename where databasename.empid = '$f1' AND databasename.empname = '$f2' AND databasename.deptcode = '$f3')"); — Amiel Vargas, Aug 31 '17 at 06:16

score 0 · Answer 1 · answered Aug 31 '17 at 06:10

0

You can use INSERT IGNORE INTO query. It won't insert record in table if data is already exists.

Please have look here. Hope this will help.

answered Aug 31 '17 at 06:10

Ashish Tiwari

1,114
1
8
20

Apparently it only works if you have a primary or unique key. But my empdb has around 2 primary keys(empid and deptcode) so I dunno if that'll work – Amiel Vargas Aug 31 '17 at 07:32

JItendra Rana · Answer 2 · 2017-08-31T06:41:59.490

0

You can use Codeingiter form_validation library.

$this->load->library('form_validation');
$this->form_validation->set_rules('empid', 'Employee ID', 'required|trim|employeeDB.empid');

Where the last Parameter employeeDB.empid is used as table_name.column_name. So your Controller will be as below.

public function insertdata() {

    $this->load->library('form_validation');
    $this->form_validation->set_rules('empid', 'Employee ID', 'required|trim|employeeDB.empid');

    if ($this->form_validation->run() == FALSE) {
        // Form Validation Failed. 
        $this->load->view('myform');
    } else {
        // Form Validation Passed. 
        $this->model->setData();
        $data['results'] = $this->model->emp_all();
        $this->load->view('edit_view', $data);
    }        
}

edited Aug 31 '17 at 06:41

answered Aug 31 '17 at 06:16

JItendra Rana

119
1
8

Thanks! Will try this ASAP! – Amiel Vargas Aug 31 '17 at 06:20
tried it, I can't seem to insert into the database anymore even the non-duplicate ones – Amiel Vargas Aug 31 '17 at 06:39
Just check your code if data insertion. Check if setData() function is being called in your model. There might be a small systex error or something. – JItendra Rana Aug 31 '17 at 06:41
I have just copied your previous code and wrapped it inside conditional statement. It should work fine if it was working previously. – JItendra Rana Aug 31 '17 at 06:42

score 0 · Answer 3 · answered Aug 31 '17 at 07:04

0

in sql server use can use NOTEXIST for protect to duplicate insertion

answered Aug 31 '17 at 07:04

Rahul mishra

39
3

Stopping insertion to the database when the data already exists with Codeigniter

3 Answers3