Thread

I’m using v2.5.2 of EE and getting a strange result on a simple update query, perhaps someone could shed some light on it.

Here is a the PHP code:

(string) $dBtable = "exp_channel_data INNER JOIN exp_channel_titles ON (exp_channel_data.entry_id = exp_channel_titles.entry_id)";
(string) $dBwhere = "exp_channel_data.site_id = $sid AND exp_channel_data.channel_id = $ch AND exp_channel_data.entry_id = $entry_id";

 $data = array(
  'status'        => $e_status,
  'edit_date'     => $e_edit,
  'title'         => $e_title,
  'field_id_53'   => $e_answerer,
  'field_id_76'   => $e_material,
  'field_id_51'   => $e_question,
  'field_id_52'   => $e_answer,
  'field_id_62'   => $e_general_answer
 );

$sql = $this->EE->db->update_string( $dBtable, $data, $dBwhere );
$query = $this->EE->db->query( $sql );
$res = $this->EE->db->affected_rows();

...check results and output XML.

The php variables should be self evident, the $e_ vars are for post data. Notice I am only updating a record that already exists in the channel and using the exp_channel_data and exp_channel_titles tables.

The strange part is the update query should have no effect on the categories for the entry (because they are kept in the exp_category_posts table) and yet for some reason when the query is run it wipes out all the categories that where assigned to the entry???

Is this some sort of undocumented EE feature when updating content in the exp_channel_data and exp_channel_titles tables? I’ve never run accross anything like this before using the EE classes.

If this behaviour was documented I’d understand, but I can’t find anything about it. I will probably switch the code over to using the Channel Entries API, however, that method is much more complicated to use vs. a simple update query (when you’re only changing a few fields data). I usually only use the Channel Entries API when adding new records.

I’m not sure I like the idea of some behind the scene manipulation of table data when performing what should be straight sql queries - makes me want to consider not to use the EE classes.

Any light that can be shed on this behaviour would be helpful, maybe I missed something here.

Thank You,
-Jon

Yep- shouldn’t be affecting the category at all.  About the only way it could via those tables would be if it changed the actual entry_id, which it shouldn’t be.

I tossed some variables in and tried it on mine- I put a deliberate error in, to easily see the resulting query:

UPDATE `exp_channel_datas` INNER JOIN exp_channel_titles ON (exp_channel_data.entry_id = exp_channel_titles.entry_id) SET `status` = ‘open’, `edit_date` = 1343771300, `title` = ‘title’, `field_id_53` = ‘’, `field_id_76` = ‘’, `field_id_51` = ‘’, `field_id_52` = ‘’, `field_id_62` = ‘’ WHERE exp_channel_data.site_id = 5 AND exp_channel_data.channel_id = 3 AND exp_channel_data.entry_id = 50

The error was the ‘s’ on that first exp_channel_data.

Do you mind trying the same thing- that or turning on profiling and hunting up the query in the output?  Either way is fine.  From the above, categories definitely should not be affected.  But let’s just make sure we know the exact query being output.  And you know?  If that’s normal, can you go ahead and check all the queries for the page via the profiler- see if you spot one that hits a category table.  If nothing leaps out, I’m more than happy to look as well if you attach a copy.

And last question for clarification- how do you know it’s wiping out the categories?  Is it because you see entry_id/cat_id pairs in exp_category_posts that are missing after running the query for that entry id?  Or is it they aren’t showing in the edit page for the entry any more?  Or something else.

Cause yep- weird.  And there shouldn’t be any EE/CI magic causing it to happen.

Thank you for checking it out, after following your advice (adding a debug routine to spit out the sql) I noticed that the template wasn’t even being called, I had sometime earlier converted it to use the API method - but hadn’t completed it (which is why the categories where being wiped out).

I didn’t notice it because we use a security wrapper for all ajax calls (think of it as a middleman between template calls), and so while the url pointing to the template I thought I was using was correct, the security wrapper had been modified to redirect the call to another template (sounds complicated but in practice it’s very simple).

So this one is all my fault. You can close this post.

I figured it had to be something on my end - I couldn’t imagine EE doing any such thing.

-Jon

😉  Glad you got it figured out.  Yep- I’ve done similar stuff myself.  I have literally lost hours editing the wrong file!

And yea- would have been super weird if EE was borking it in exactly that way.