[SOLVED] List PHP plugin not working as expected

[thellie]

The plugin is set to run a MySQL query which deletes rows in related tables. The query works correctly in PHPMyAdmin, and the PHP plugin runs without an error, except it doesn't delete the rows. If I put in an integer instead of the variable, the plugin deletes the rows correctly.
I've checked what the variable pulls in and it's the correct data. I'm stumped after spending hours on this, trying all sorts of combinations... please look at the code to see if I'm missing something obvious.

Code with integer - deletes all related rows in all tables correctly

$appformid = intval('{vol_appform___id}');

$myDb = Joomla\CMS\Factory::getContainer()->get('DatabaseDriver');
$myQuery = $myDb->getQuery(true);
$myQuery="
DELETE vol_personal, vol_passport, vol_family, vol_checks, vol_insurance, vol_pickup FROM vol_appform
INNER JOIN vol_personal
INNER JOIN vol_passport
INNER JOIN vol_family
INNER JOIN vol_checks
INNER JOIN vol_insurance
INNER JOIN vol_pickup
WHERE
(vol_appform.id=vol_personal.appform_id AND
vol_appform.id=vol_passport.appform_id AND
vol_appform.id=vol_family.appform_id AND
vol_appform.id=vol_checks.appform_id AND
vol_appform.id=vol_insurance.appform_id AND
vol_appform.id=vol_pickup.appform_id)
AND vol_appform.id ='4';
";
$myDb->setQuery($myQuery);
$myDb->execute();

Code with variable

$appformid = intval('{vol_appform___id}');

$myDb = Joomla\CMS\Factory::getContainer()->get('DatabaseDriver');
$myQuery = $myDb->getQuery(true);
$myQuery="
DELETE vol_personal, vol_passport, vol_family, vol_checks, vol_insurance, vol_pickup FROM vol_appform
INNER JOIN vol_personal
INNER JOIN vol_passport
INNER JOIN vol_family
INNER JOIN vol_checks
INNER JOIN vol_insurance
INNER JOIN vol_pickup
WHERE
(vol_appform.id=vol_personal.appform_id AND
vol_appform.id=vol_passport.appform_id AND
vol_appform.id=vol_family.appform_id AND
vol_appform.id=vol_checks.appform_id AND
vol_appform.id=vol_insurance.appform_id AND
vol_appform.id=vol_pickup.appform_id)
AND vol_appform.id = $appformid;
";
$myDb->setQuery($myQuery);
$myDb->execute();

I've tried

$appformid = intval('{vol_appform___id}');
$appformid =(INT) '{vol_appform___id}';
$appformid ='{vol_appform___id}';

Here's the variable in another element to test if the appform_id is correct

$appformid = intval('{vol_appform___id}');

$myname = '{vol_appform___first_name}' . ' ' . '{vol_appform___last_name}';

return $myname .' = '. $appformid;

For the test row, the result is: newocn nocn = 5
which is the correct appform_id...

 

[troester]

You can't use placeholders.
http://fabrikar.com/forums/index.php?wiki/php-list-plugin/

PHP code - If no file selected then the php code entered in this textarea will be run. Do not encase your PHP code with "<?php ?>".
IMPORTANT: placeholders like {element___table}, {rowid} etc are not usable here and in php file, neither

http://fabrikar.com/forums/index.php?wiki/php-list-plugin/#accessing-the-selected-rows-data

 

[thellie]

D'oh!
Two steps forward, one step back... or is it the other way around..?

Thanks, I'll adjust as necessary.

 

[troester]

Learning by doing

 

[thellie]

Learning by doing

Ha! That's exactly what we say to our volunteers when they come here

btw, my PHP coding skills are 0-5% competent, so I'm looking for an alternative way to do this...
The wiki is helpful, but if your (my) coding is minimal, then there's no real coding samples to work from - just the relevant bit, which is not enough to work out how to do it. Ne'er mind... I'm light years ahead of where I was when I started using Fabrik

 

[thellie]

You can't use placeholders.
http://fabrikar.com/forums/index.php?wiki/php-list-plugin/

http://fabrikar.com/forums/index.php?wiki/php-list-plugin/#accessing-the-selected-rows-data

I think the PHP code below is correct, but I'm getting an error -

0 Call to undefined method FabrikEvalClass_ed7eab1417fbf978395458d3881d1d86::getSelected()

I'm running the code in the PHP Code field, not as a script. No placeholders in sight. I don't know what else to look for...

use Joomla\CMS\Factory;
use Joomla\CMS\Language\Text;

$selectedRows = $this->getSelected();

if (!empty($selectedRows)) {
    $db = Factory::getDbo();

    $db->transactionStart();

    try {
        $query = $db->getQuery(true)
            ->delete($db->quoteName('vol_personal'))
            ->where($db->quoteName('appform_id') . ' IN (' . implode(',', $selectedRows) . ')');
        $db->setQuery($query);
        $db->execute();

        $query = $db->getQuery(true)
            ->delete($db->quoteName('vol_passport'))
            ->where($db->quoteName('appform_id') . ' IN (' . implode(',', $selectedRows) . ')');
        $db->setQuery($query);
        $db->execute();

        $query = $db->getQuery(true)
            ->delete($db->quoteName('vol_family'))
            ->where($db->quoteName('appform_id') . ' IN (' . implode(',', $selectedRows) . ')');
        $db->setQuery($query);
        $db->execute();

        $query = $db->getQuery(true)
            ->delete($db->quoteName('vol_checks'))
            ->where($db->quoteName('appform_id') . ' IN (' . implode(',', $selectedRows) . ')');
        $db->setQuery($query);
        $db->execute();

        $query = $db->getQuery(true)
            ->delete($db->quoteName('vol_insurance'))
            ->where($db->quoteName('appform_id') . ' IN (' . implode(',', $selectedRows) . ')');
        $db->setQuery($query);
        $db->execute();

        $query = $db->getQuery(true)
            ->delete($db->quoteName('vol_pickup'))
            ->where($db->quoteName('appform_id') . ' IN (' . implode(',', $selectedRows) . ')');
        $db->setQuery($query);
        $db->execute();

        $db->transactionCommit();

        $this->setMessage(Text::_('COM_FABRIK_PLUGIN_DELETE_SUCCESS'), 'success');
    } catch (Exception $e) {

        $db->transactionRollback();

        $this->setMessage(Text::_('COM_FABRIK_PLUGIN_DELETE_ERROR'), 'error');
    }
} else {

    $this->setMessage(Text::_('COM_FABRIK_PLUGIN_DELETE_WARNING'), 'warning');
}

$this->setRedirect('index.php?option=com_fabrik&view=list&listid=' . $this->listId);

 

[thellie]

Never mind, I got it working.

use Joomla\CMS\Factory;

$app = Factory::getApplication();
$ids = $app->input->get('ids', array(), 'array');
$selectedRows = array_map('intval', $ids);

if (!empty($selectedRows)) {
    $db = Factory::getDbo();

    $db->transactionStart();

    try {
        $query = $db->getQuery(true)
            ->delete($db->quoteName('vol_personal'))
            ->where($db->quoteName('appform_id') . ' IN (' . implode(',', $selectedRows) . ')');
        $db->setQuery($query);
        $db->execute();

        $query = $db->getQuery(true)
            ->delete($db->quoteName('vol_passport'))
            ->where($db->quoteName('appform_id') . ' IN (' . implode(',', $selectedRows) . ')');
        $db->setQuery($query);
        $db->execute();

        $query = $db->getQuery(true)
            ->delete($db->quoteName('vol_family'))
            ->where($db->quoteName('appform_id') . ' IN (' . implode(',', $selectedRows) . ')');
        $db->setQuery($query);
        $db->execute();

        $query = $db->getQuery(true)
            ->delete($db->quoteName('vol_checks'))
            ->where($db->quoteName('appform_id') . ' IN (' . implode(',', $selectedRows) . ')');
        $db->setQuery($query);
        $db->execute();

        $query = $db->getQuery(true)
            ->delete($db->quoteName('vol_insurance'))
            ->where($db->quoteName('appform_id') . ' IN (' . implode(',', $selectedRows) . ')');
        $db->setQuery($query);
        $db->execute();

        $query = $db->getQuery(true)
            ->delete($db->quoteName('vol_pickup'))
            ->where($db->quoteName('appform_id') . ' IN (' . implode(',', $selectedRows) . ')');
        $db->setQuery($query);
        $db->execute();

        $db->transactionCommit();
    } catch (Exception $e) {
        $db->transactionRollback();
    }
}

 

[troester]

One thing:
$db = Factory::getDbo(); is deprecated.
Better do $db = Factory::getContainer()->get('DatabaseDriver');