List php plugin - query execution fails

[Jaanus]

I am intended to add one little feature to a list using list php plugin but failed so far.

I have two lists - one of them shows blog post drafts from another database (used by my Wordpress blog) and second is related to it, containing comments and suggestions for blog post drafts. Details view of a post draft shows with content plugin in view's footer also the list with comments and suggestions that are related to this post. People can add to each row
1) excerpt from post draft (exact copy-paste)
2) suggestion how should it actually written (only the alternate version, nothing else)
3) comment about this suggestion.

Now the expected feature:
when the main author approves fully the suggestion, he/she choose a button "Approved" in list view (that should run the php plugin) and click on it. As result the part of the blog post draft would be replaced with a new version.

When I tested with a static text in the list php plugin then it did this work. But the UPDATE query fails when trying to use SELECT query results for it, although both queries are generated correctly.

Can anyone say whether there's something wrong with the following code (and how to fix it if any) or this is a bug?

$item = $model->getTable();
$app = JFactory::getApplication();
$ids = $app->input->get('ids', array(), 'array');
$id = implode(',', $ids);
 
$db = FabrikWorker::getDbo();
 
$curtab = $db->quoteName($item->db_table_name);
$c_id = $db->quoteName('id');
$c_parent = $db->quoteName('parent_id');
$c_orig = $db->quoteName('orig_text');
$c_sugg = $db->quoteName('suggested');
 
$query = "SELECT $c_parent, $c_orig, $c_sugg FROM $curtab WHERE $c_id IN($id)";
$db->setQuery($query);
$rows = $db->loadObjectList();
 
$db2 = FabrikWorker::getDbo(false, 2);
$other = $db2->quoteName('wp_posts');
$o_id = $db2->quoteName('ID');
$o_post = $db2->quoteName('post_content');
foreach ($rows as $row)
{
$q = $db2->getQuery(true);
 
$fields = array(
    $o_post . ' = REPLACE(' . $o_post . ', ''. $row->orig_text . '', '' . $row->suggested . '')'
);
 
$conditions = array(
    $o_id . ' = ' . $row->parent_id
);
 
$q->update($other)->set($fields)->where($conditions);
 
$db2->setQuery($q);
$db2->execute();
$statusMsg = $q;
}

 

[troester]

Did you try to debug?
e.g.
...
$result = $db2->execute();
var_dump($result,$fields,$conditions,$q);exit;
...

 

[Jaanus]

So, after some hours with the help from troester and then rob the key of secret was found The importance of $db->quote() is obvious. In my case $db2->quote(). Here follows the right, really working code:

<?php
 
$item = $model->getTable();
$app = JFactory::getApplication();
$ids = $app->input->get('ids', array(), 'array');
$id = implode(',', $ids);
 
$db = FabrikWorker::getDbo();
 
$curtab = $db->quoteName($item->db_table_name);
$c_id = $db->quoteName('id');
$c_parent = $db->quoteName('parent_id');
$c_orig = $db->quoteName('orig_text');
$c_sugg = $db->quoteName('suggested');
 
$query = "SELECT $c_parent, $c_orig, $c_sugg FROM $curtab WHERE $c_id IN($id)";
$db->setQuery($query);
$rows = $db->loadObjectList();
$db2 = FabrikWorker::getDbo(false, 2);
$other = $db->quoteName('wp_posts');
$o_id = $db->quoteName('ID');
$o_post = $db->quoteName('post_content');
foreach ($rows as $row)
{
$q = $db2->getQuery(true);
 
$fields = array(
    $o_post . ' = REPLACE(' . $o_post . ', '. $db2->quote($row->orig_text) . ', ' . $db2->quote($row->suggested) . ')'
);
 
$conditions = array(
    $o_id . ' = ' . $row->parent_id
);
 
$q->update($other)->set($fields)->where($conditions);
 
$db2->setQuery($q);
$db2->execute();
}
?>