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

KISS:

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.

Michael Wales - 05 February 2010 04:00 PM

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.

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

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’)

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.

Michael Wales - 05 February 2010 05:03 PM

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.

@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’

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. :D

no worries! ... quick question… is the MySQL driver in Active Record not a core file?

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.

oh, of course, actually, it is, as they say, “my bad” for not understanding you were referring to extending the mysql driver rather than hacking it (which seemed out-of-character advice from an experienced developer like you!) i’ve never heard the phrase “monkey patch” so i didn’t catch it. i looked it up at wikipedia, here’s a link for the curious: http://en.wikipedia.org/wiki/Monkey_patch the “pitfalls” section explains some problems with monkey-patching. it also indicates that the term refers to a range of modifications one can do.

but, after all wouldn’t it be better to extend the model class (or look into one of the many fine model extensions available) than to re-jigger just one of the available db drivers… also, if you were to pursue a “monkey-patch” of any part of the DB core wouldn’t it be the Active_rec.php file - the one that has insert and update functions already?