• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
use of Unique in mySQL field cases Error

#1
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
Reply

#2
@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.
Reply

#3
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
Reply

#4
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 )
Reply

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

#6
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
Reply

#7
You need to create that manually.
Reply

#8
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']) . "')"); 
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply

#9
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
Reply

#10
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
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.