Insert if not exists possible with CI?

[eluser]webnology[/eluser]
Hi,

is there a way to make a statement in CI like, I only need to add a record if this record does not yet exist? I'm filling a table with 2 Id's. a_id and b_id. I only need to add this record if the combination for these 2 is not yet in the db.

Can anyone guide me here, please?

Michel

[eluser]danmontgomery[/eluser]
KISS:

Code:

$query = $this->db->select('a_id')->where('a_id', $a_id)->where('b_id', $b_id)->get('my_table')->get();
if($query->num_rows() == 0) {
  $this->db->insert('my_table', array( 'a_id' => $a_id, 'b_id' => $b_id ));
}

[eluser]Michael Wales[/eluser]
If you have these two columns indexed as unique you can just perform the insert. MySQL will see they already exist and continue on with its day.

Doesn't require the additional select query noctrum suggested and uses less resources than INSERT ... ON DUPLICATE KEY UPDATE.

[eluser]danmontgomery[/eluser]
[quote author="Michael Wales" date="1265403654"]If you have these two columns indexed as unique you can just perform the insert. MySQL will see they already exist and continue on with its day.

Doesn't require the additional select query noctrum suggested and uses less resources than INSERT ... ON DUPLICATE KEY UPDATE.[/quote]

True, but only viable if a_id and b_id have a 1:1 relationship, and there is only one entry for each

[eluser]webnology[/eluser]
Yes, this gives me an error if I try to add the same combination again.

Error Number: 1062
Duplicate entry '10' for key 1
INSERT INTO `products2customers` (`product_id`, `customer_id`) VALUES ('10', '4133')

[eluser]Michael Wales[/eluser]
If you turn off db_debug it should roll right on through - like I said, not the most graceful solution but the most efficient (as long as MySQL isn't logging errors).

Otherwise, you can use noctrum's pre-select() or it wouldn't be very hard to modify the Active Record MySQL driver for an insert_or_update() method.

[eluser]sophistry[/eluser]
[quote author="Michael Wales" date="1265407391"]Otherwise, you can use noctrum's pre-select() or it wouldn't be very hard to modify the Active Record MySQL driver for an insert_or_update() method.[/quote]

@MW... now you are ex-chieftain you advise CI core hacking?

wouldn't it be "better" (as in more pleasure during core upgrades) to leave the db driver code alone and put the insert_or_update() method in an extended model?

just sayin'

[eluser]Michael Wales[/eluser]
I never said edit the core class file - I simply said to modify the MySQL driver. I guess I should have explained it better - monkey-patch the MySQL driver.

[eluser]sophistry[/eluser]
no worries! ... quick question... is the MySQL driver in Active Record not a core file?

[eluser]Michael Wales[/eluser]
Bah - I'm doing a horrible job of explaining this: yes, it's a core class file. Modify it by extending it, monkey-patch it.

Thanks for keeping me on my toes sophistry.