[richb201]

I want to limit the values in one varchar field  in mySQL  to be unique. I am finding that when my code tries to add another copy of the same varchar I get 


ERROR - 2020-07-21 00:13:54 --> Query error: Duplicate entry 'software engineer' for key 'item' - Invalid query: INSERT INTO `exclude` (`item`, `campaign`, `userid`) VALUES ('software engineer', 'Apple', '[email protected]')

I do want to not allow a duplicate, but I don't want an error to occur when an attempt is made. How can I inhibit the ERROR message? 

[php_rocs]

@richb201,

Suggestion: How about putting in a feature that does a preliminary check to make sure that the specific varchar item is unique prior to allowing you to add the record (maybe an ajax check once the field is filled in.

[richb201]

I am actually building the table with data from another table. So the user adds the field to table a, and in the callback for the insert, I also insert the field into table b. Table b is the one that I want unique. In table a, the field is not unique. Only in table b is it unique.

[InsiteFX]

Use INSERT IGNORE rather than INSERT.

MySQLi - Handling Dupliates

[jreklund]

Either INSERT IGNORE or ON DUPLICATE KEY UPDATE, depending on result you want.

[richb201]

Thanks. I am trying this in a callback:
```
          $data = array(
              'item' => $post_array['employee_title'],
              'campaign' => $this->session->userdata('campaign'),
              'userid' => $this->session->userdata('userid')
              );
              $this->db->insert_ignore ('titles', $data);
```
I am getting this error:
Call to undefined method CI_DB_mysqli_driver::insert_ignore

I don't see insert_ignore in the Query builder documentation, btw. 

Any ideas?

[jreklund]

You need to create that manually.

[InsiteFX]

These are not tested but give them a try, I would make sure to back up the database before
using these.

PHP Code:

$data = array(
    'item' => $post_array['employee_title'],
    'campaign' => $this->session->userdata('campaign'),
    'userid' => $this->session->userdata('userid')
);

$insertStr = $this->db->insert_string('titles', $data);
$sql = "INSERT IGNORE INTO", $insertStr);
$this->db->query($sql);

// or this one that escapes the data.

$this->db->query("INSERT IGNORE INTO titles(item, campaign, userid) VALUES ('" . 
$this->db->escape($data['item']) . "', '" . 
$this->db->escape($data['campaign']) . "', '" . 
$this->db->escape($data['userid']) . "')"); 


[richb201]

Thanks. I found this on the mysql website:

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

Can I assume that this is psuedo code to build the sql?

[richb201]

Insite,

still not working, I tried it both with the item field set to unique and not. Is there a log for mysql errors?
$data = array(
    'item' => $post_array['cost_center'],
    'campaign' => $this->session->userdata('campaign'),
    'userid' => $this->session->userdata('userid')
);
$string="INSERT IGNORE INTO exclude_choices(item, campaign, userid) VALUES (" . $this->db->escape($data[item]) . ", " .$this->db->escape($data[campaign]) . ", " .$this->db->escape($data[userid]) . ")";
$this->db->query($string);

You realize that this is being called within a callback and the original table in the controller is "employees" and not "exclude_choices"? I found some mention of moving the code dealing with another table into a function in "model" rather than in the controller. Any commentary on this plan?