use of Unique in mySQL field cases Error |
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?
proof that an old dog can learn new tricks
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.
proof that an old dog can learn new tricks
Use INSERT IGNORE rather than INSERT.
MySQLi - Handling Dupliates What did you Try? What did you Get? What did you Expect?
Joined CodeIgniter Community 2009. ( Skype: insitfx )
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?
proof that an old dog can learn new tricks
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( What did you Try? What did you Get? What did you Expect?
Joined CodeIgniter Community 2009. ( Skype: insitfx )
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?
proof that an old dog can learn new tricks
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?
proof that an old dog can learn new tricks
|
Welcome Guest, Not a member yet? Register Sign In |