Sometimes it’s the stupid little things that trip you up. Just recently, I encountered an odd problem with db_delete() which I couldn’t figure out at the time; and, in fact, kept barking up the wrong tree until I found a proper example to work from. Even then I didn’t realise what I’d been doing wrong until yesterday.
Very simply, I was calling db_delete() with an IN condition, like so:
$mytable_res = db_select('my_table','mt') ->distinct() ->fields('mt',array('key')) ->execute(); $key_arr = array(); foreach($mytable_res as $row) { $key_arr[] = $row->key; } db_delete('my_other_table','mot') ->condition('iid', $key_arr, 'IN') ->execute();
The first thing you’ll notice is that I should have been using db_query->fetchCol() to get the array, instead of tediously building it via PHP. I didn’t twig on that until later, because running that db_delete gave me an error: Argument 2 passed to db_delete() must be an array, string given
What did that mean? Googling didn’t really help. It was either something so obvious it didn’t bear mentioning, or something so obscure no one was talking about it. After a bit of thought I found an example to work from in the Drupal code base; specifically, in function aggregator_aggregator_remove modules/aggregator/aggregator.processor.inc:
$iids = db_query('SELECT iid FROM {aggregator_item} WHERE fid = :fid', array(':fid' => $feed->fid))->fetchCol(); if ($iids) { db_delete('aggregator_category_item') ->condition('iid', $iids, 'IN') ->execute(); }
I adapted that to my own tables and fields (using fetchCol(), too), and it worked fine, but I still didn’t understand why my first attempt didn’t work. Finally, after some trial and error, I found the answer: db_delete doesn’t take a table alias. I was so used to using them in select functions that I automatically added one this time, and then kept on having a blind spot about it.
So, yeah. I wasn’t sure if I wanted to blog about this, but hey, we all make stupid mistakes, right? Might as well own it, learn, and move on.