2

I've got two queries that I'm running separately in a template, one after the other, like this:

<?php
  $sql = "UPDATE exp_channel_titles SET status = 'Registered' WHERE channel_id = '12' AND entry_id = {entry_id} AND status != 'Hidden';";
  $this->EE->db->query($sql);
?>

<?php
  $sql = "UPDATE exp_channel_data SET field_id_119 = 'Yes' WHERE channel_id = '12' AND entry_id = {entry_id} AND field_id_119 != 'Yes';";
  $this->EE->db->query($sql);
?>

Can someone tell me if and how to write these more efficiently within a single PHP tag/database call please? When I tried running them one after another within a single PHP tag, they failed to work. Thanks.

3

Right, you are performing two queries so you need two calls to the db (if you want to use CI). This is the only other way I could think to do reasonably do it, but it's actually more code (but not handwritten SQL).

$this->EE->db->where(array(
    'channel_id' => 12,
    'entry_id'   => $entry_id,
    'status !='  => 'Hidden'
));

$this->EE->db->update('channel_titles', array('status' => 'Registered'));


$this->EE->db->where(array(
    'channel_id'      => 12,
    'entry_id'        => $entry_id,
    'field_id_119 !=' => "Yes"
));

$this->EE->db->update('channel_data', array('field_id_119' => 'Yes'));
4
  • Thanks for this Justin. It's good to know how to do this properly using CI (I seem to doing a mix of two ways - I drifted partly into CI as the Query module doesn't support update queries). – Stephen Apr 18 '13 at 23:53
  • 1
    In terms of legibility (easy to see what's going on at a glance) and best practice, this would be your best bet. I can't imagine the performance benefits would be noticeable by using one SQL query instead of two. – Ian Apr 19 '13 at 8:54
  • 1
    I will just to Ian's comment that all your methods that interact with the database should be in a Model anyway. So I could combine those two functions above in a method in my add-on model class. Call it update_entry() or something and have it perform both calls. – Justin Kimbrell Apr 19 '13 at 8:56
  • Some good points here and I think I'm sold about going in this direction instead, thanks. – Stephen Apr 22 '13 at 11:50
3

You can use one query instead of two using the sql JOIN:

$sql = "UPDATE  exp_channel_titles ct 
LEFT JOIN exp_channel_data cd 
ON cd.entry_id = ct.entry_id 
SET ct.status = 'Registered', cd.field_id_119 = 'Yes'
WHERE ct.channel_id=12 AND cd.field_id_119 != 'Yes' AND ct.entry_id={entry_id} AND ct.status != 'Hidden'";

$this->EE->db->query($sql);
1
  • Thanks for this Bhashkar. That seems like a good way of combining my two standalone queries (I didn't know much about joins!). – Stephen Apr 18 '13 at 23:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.