Forums

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?

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'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.

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.

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
                                )
                        }
                }
         }

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

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?

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

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.

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.