How to use custom database query "Table" view in CCv6

we

webbusteruk 06 Jan, 2019

Is it possible to do an SQL query, then use the result of the query in a Table view?

In other words, can we write an SQL Query using 'Custom HTML' with the name somephpcode.

Then in the "Table provider" field for table, can we use eg. {view:somephpcode} to generate a table?

he

healyhatman 07 Jan, 2019

Well you wouldn't use Custom HTML you'd use PHP (under logic).

And yes. Except you'd use {var: not {view:

https://docs.joomla.org/Special:MyLanguage/Selecting_data_using_JDatabase

we

webbusteruk 07 Jan, 2019

We've tried using the PHP function, but it doesn't produce any output when using (var:phpcode}, unlike {var:read_data}. What variable should the SQL Query output as? Our example PHP SQL Query code:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('*');
$query->from($db->quoteName('#__chronoforms_data_test2'));
$db->setQuery($query);

$result= $db->loadAssocList();

return $result;

When that was put into Table provider field, it did not produce anything. We suppose it needs further processing to be used as data provider for the table, but unable to find anything relevant in the forums or FAQ.

he

healyhatman 07 Jan, 2019

1 Likes

You need to use {fn:phpcode} first
You also need to RETURN the result.

It also helps if you use {debug:} at the end of your event to see what your data looks like.

we

webbusteruk 07 Jan, 2019

Edit: Sorry healy, I was still editing and typing the reply posts.

In the debug, it shows (truncated):

[test] => Array
        (
            [returned] => Array
                (
                    [0] => Array
                        (
                            [aid] => 135
                            [created] => 2018-12-29 18:39:26
                        )
                )
        )

Compared to read_data:

[test] => Array
        (
            [log] => Array
                (
                    [0] => SELECT etc, etc
                    [1] => SELECT etc, etc
                )

            [var] => Array
                (
                    [0] => Array
                        (
                            [coursework] => Array
                                (
                                    [aid] => 155
                                    [created] => 2019-01-04 00:07:00
                                )
                        }
                }
         }

he

healyhatman 07 Jan, 2019

Looks like {var:test.returned} should work then

we

webbusteruk 07 Jan, 2019

Still doesn't output data in the table. It's still missing a further array key after [0] though (ie coursework, which was the model name for read_data), would that matter?

he

healyhatman 07 Jan, 2019

It shouldn't. Did you tick the"show all fields" or whatever box?

we

webbusteruk 07 Jan, 2019

Answer

Apparently the model name is needed as well. We converted the array into CCv6's format and it worked. Although it is a bit tedious.

Our solution (PHP code under Function):

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('*');
$query->from($db->quoteName('#__chronoforms_data_test2'));
$db->setQuery($query);

$result = $db->loadAssocList();

$table = array();
foreach ($result as $row)
{
   $table[] = array(
       array(
           'aid' => $row['aid'],
           'created' => $row['created']
       )
   );
}

return $table;

Also, we used {var:test} without the ".returned". in the Table provider field for our {view:table}. Final debug (truncated):

[test] => Array
        (
            [returned] => Array
                (
                    [0] => Array
                        (
                            [0] => Array
                                (
                                    [aid] => 155
                                    [created] => 2019-01-04 00:07:00
                                )

                        )
                )
         )

we

webbusteruk 07 Jan, 2019

Following some help from Stack Exchange, here's an adjustment to the final part of the php code:

foreach ($result as $key => $value){
    $table[$key] = ['model' => $value];}
return $table;

This will return all the fields (from the SQL query) to the table view, with the model name being 'model', just like how a read_data work.

he

healyhatman 07 Jan, 2019

Ahhh yep my bad didn't need the .returned

This is what happens when you answer forum questions at 12am in bed on a phone.

Forums

How to use custom database query "Table" view in CCv6