+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: use of Unique in mySQL field cases Error (/showthread.php?tid=77115)
use of Unique in mySQL field cases Error - richb201 - 07-20-2020
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?
RE: use of Unique in mySQL field cases Error - php_rocs - 07-20-2020
@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.
RE: use of Unique in mySQL field cases Error - richb201 - 07-20-2020
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.
RE: use of Unique in mySQL field cases Error - InsiteFX - 07-21-2020
Use INSERT IGNORE rather than INSERT.
MySQLi - Handling Dupliates
RE: use of Unique in mySQL field cases Error - jreklund - 07-21-2020
Either INSERT IGNORE or ON DUPLICATE KEY UPDATE, depending on result you want.
RE: use of Unique in mySQL field cases Error - richb201 - 07-22-2020
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?
RE: use of Unique in mySQL field cases Error - jreklund - 07-22-2020
You need to create that manually.
RE: use of Unique in mySQL field cases Error - InsiteFX - 07-22-2020
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']) . "')");
RE: use of Unique in mySQL field cases Error - richb201 - 07-22-2020
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?
RE: use of Unique in mySQL field cases Error - richb201 - 07-22-2020
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?