(SOLVED) Generate report from database

mbeley · Jan 19, 2016

Hi,

I have several sql views, all of them include at least one of 2 fields: 'personne_responsable' and 'produit'.
I imported the views into Fabrik tables and recreated the joins.
I would like to generate a report including Fabrik views (list and details) + the sql views with a filter on these 2 fields.

In my example below I'm including only one sql view in order to make it simple.

Using the Fabrik content plugin, I created an article that looks like the report I want.
The article's content looks like this:

Code:

<h3>Produit Cosm?tique<br />{fabrik view=element list=2 rowid=1 element=produit___nom}</h3>
<p>{fabrik view=details id=10 rowid=3}</p>
<p>{fabrik view=list id=63 usekey=personne_responsable|produit rowid=1|3}</p>

I have no idea how to replace the rowids 1 and 3 with variables.
It looks like the Form PHP plugin does exactly this.
Unfortunately I have zero experience in PHP.
So I went ahead and created a new form (called RSPC, the report's name) with 2 elements: a database join ('responsable') and a cascading dropdown ('produit')
I'm trying the following code, but it's not doing anything, just creating the record in the database.

Code:

$db = JFactory::getDBO();
defined('_JEXEC') or die();
$resp = $formModel->getElementData('rspc___responsable', true);
$prod = $formModel->getElementData('rspc___produit'), true;
echo "<h3>Produit Cosm?tique<br />{fabrik view=element list=2 rowid=$prod element=produit___nom}</h3>";
echo {fabrik view=details id=10 rowid=$resp};
echo {fabrik view=list id=63 usekey=personne_responsable|produit rowid=$prod|$resp}

Please help!
Thanks,
Mathieu

cheesegrits · Jan 19, 2016

Nope, the PHP form plugin won't do what you need.

I think you are on the right track with using another form with the two elements on it. But what you need to do is put a redirect on it, to the article page that contains your content plugins, and append some query string args to the redirect url, like &produit={___produit_raw}&responsable={___responsable_raw}. That assumes it's a "tableless" form, if not then prepend the tablename to the ___elementname.

Then in your article, you'll need to embed some PHP, using something like Sourcerer (from NoNumber), pick up the query string args, and echo the plugin strings kind of like you tried in the form plugin ...

Code:

[source]
$app = JFactory::getApplication();
$resp = $app->input->get('responsable', '');
$prod = $app->input->get('produit', '');
if (!empty($resp) && !empty($prod)) {
   echo "{fabrik view=form id=X ___produit_raw=$prod ___responsable_raw=$resp}"
   echo "<h3>Produit Cosm?tique<br />{fabrik view=element list=2 rowid=$prod element=produit___nom}</h3>";
   echo "{fabrik view=details id=10 rowid=$resp}";
   echo "{fabrik view=list id=63 usekey=personne_responsable|produit rowid=$prod|$resp}";
}
else {
   echo "fabrik view=form id=X";
}
[/source]

Note that the for id=X is your search form. You could expand that to default the two values from the query string if you want.

Although I'm not convinced that you need to be using 'usekey', if what you are trying to do is filter on those elements, you'd probably more likely need ...

Code:

echo "{fabrik view=list id=63 yourtable___personne_responsable_raw=$resp yourtable___produit_raw=$prod}

... in other words, set up element filters rather than a usekey.

-- hugh

mbeley · Jan 19, 2016

Hi Hugh,

Thanks for your reply.
I'm going to try your solution and I'll come back to you.

cheesegrits said:
set up element filters rather than a usekey.

In the detail view that I just published, usekey gives me the expected result while element filters do not seem to work.
Article is visible here: http://securite-cosmetique.fr/rapports/5-rspc
I have put both lines in my article:

Code:

{fabrik view=details id=71 produit_exposition_view___personne_responsable=3 produit_exposition_view___produit=1}
{fabrik view=details id=71 usekey=personne_responsable|produit rowid=3|1}

Thanks,
Mathieu

cheesegrits · Jan 19, 2016

Your first post was a view=list, not a view=details. The 'usekey' doesnt work for lists, ony form/details view.

-- hugh

mbeley · Jan 20, 2016

OK the form is now setup with a redirect plugin.
It's redirecting to this URL:
http://www.securite-cosmetique.fr/i...ent&view=article&id=5&produit=1&responsable=3

But my article does not work : blank page
Here is the sourcerer code:

Code:

{source}
[[?php
$app = JFactory::getApplication();
$resp = $app->input->get('responsable', '');
$prod = $app->input->get('produit', '');
if (!empty($resp) && !empty($prod)) {
echo "{fabrik view=form id=70 ___produit_raw=$prod ___responsable_raw=$resp}"
echo "[[h3]]Produit Cosm?tique[[br /]]{fabrik view=element list=2 rowid=$prod element=produit___nom}[[/h3]]";
echo "{fabrik view=details id=10 rowid=$resp}";
echo "{fabrik view=list id=63 formule_view___personne_responsable=$resp formule_view___produit=$prod}";
echo "{fabrik view=details id=71 usekey=personne_responsable|produit rowid=$resp|$prod}";
}
else {
echo "fabrik view=form id=70";
}
?]]
{/source}

The only differences from your suggested code are the PHP tags and the double bracket tags added by Sourcerer (since I'm using TinyMCE as an editor).

cheesegrits · Jan 20, 2016

Missing semi colon at end of first echo.

Sent from my Nexus 7 using Tapatalk

mbeley · Jan 20, 2016

Working now!
Thanks for your great support.

mbeley · Jan 21, 2016

I'm using the following element view:
{fabrik view=element list=72 rowid=1 element=toxico___ingredient}
Getting Error: O You are trying to embed an element called toxico___ingredient which is not present in the list or has been unpublished
But the element exists and is published.
Any ideas?

cheesegrits · Jan 21, 2016

Nope, that's not what you have in the article, you have ...

Code:

echo "[[h5]]{fabrik view=element list=72 rowid=3 element=</span><span style="font-family: courier new, courier, monospace;"><span style="font-family: courier new, courier, monospace;">toxico___</span>ingredient}[[/h5]]";

When embedding PHP in articles, you need to turn off WYSIWYG for that user (select "None" as the editor in preferences). It used to be you could just toggle the editor into plain text mode, but at some point the J! editor got really aggressive about adding markup even when saving with the view toggled. I've found the only reliable way is to just turn off WYSIWYG entirely.

-- hugh

mbeley · Jan 21, 2016

Sourcerer was doing ok in wysiwyg (using the sourcerer code editor and replacing the tags).
And suddenly it started acting up.
I turned off wysiwyg but same error is happening.

mbeley · Jan 22, 2016

I now have wysiwyg turned off and using

Code:

{source 0}code in an html editor{/source}

as described in https://www.nonumber.nl/extensions/sourcerer#not-using-a-wysiwyg-editor

I have created several articles for the sake of testing, now this issue is in article 7.
Can be tested at: http://www.securite-cosmetique.fr/i...ent&view=article&id=7&produit=1&responsable=3

I checked the list number and it corresponds to the name toxico:
id,label,introduction,form_id,db_table_name,db_primary_key
72,Profils toxicologiques,"",69,toxico,toxico.id

I'm still using the same code below:

Code:

{fabrik view=element list=72 rowid=1 element=toxico_ingredient}

I tried using the list id instead of ingredient, but the result is the same: Error : 0

troester · Jan 22, 2016

I can't opne the article (it's checked out).
But it must be 3 underscores
toxico___ingredient

mbeley · Jan 22, 2016

Thanks troester.
Yesterday I had 3 underscores, today only 1.
Yesterday it did not work, now it's finally working!
Go figure...

mbeley · Jan 22, 2016

Now I would like to show the element details of all the values of an array based on a SQL query.

My code is:

Code:

$query = $db->getQuery(true);
$query = "SELECT toxico FROM formule_tox_view WHERE personne_responsable=$resp AND produit=$prod";
$db->setQuery($query);
$rows = $db->loadObjectList();
foreach ($rows as $value) {
echo "{fabrik view=element list=72 rowid=$value element=toxico___ingredient}";
    echo "{fabrik view=details id=69 rowid=$value}";
}

I'm getting this:
Catchable fatal error: Object of class stdClass could not be converted to string in /home/boagente/public_html/rspc/plugins/system/sourcerer/helper.php(635) : runtime-created function on line 25

So I looked up this error and I thought I would change one line as below. But I have no idea what I'm doing here...

Code:

$rows->rows = $db->loadObjectList();

Now I have different errors:

Warning: Creating default object from empty value in /home/boagente/public_html/rspc/plugins/system/sourcerer/helper.php(635) : runtime-created function on line 23

Notice: Array to string conversion in /home/boagente/public_html/rspc/plugins/system/sourcerer/helper.php(635) : runtime-created function on line 25

Notice: Array to string conversion in /home/boagente/public_html/rspc/plugins/system/sourcerer/helper.php(635) : runtime-created function on line 26

troester · Jan 22, 2016

In general:better don't use common variable names like $row, $value (although this is the example code in the WIKI), this may override variables used in Fabrik or sourcerer.
Use e.g $my_rows, $my_value...
$value is an object (holding all columns from your SELECT), the rowid you need is in $value->toxico
So try

Code:

$query = $db->getQuery(true);
$query = "SELECT toxico FROM formule_tox_view WHERE personne_responsable=$resp AND produit=$prod";
$db->setQuery($query);
$rows = $db->loadObjectList();
foreach ($rows as $value) {
$my_rowid = $value->toxico;
echo "{fabrik view=element list=72 rowid=$my_rowid element=toxico___ingredient}";
    echo "{fabrik view=details id=69 rowid=$my_rowid}";
}

mbeley · Jan 22, 2016

Thank you so much Troester, the loop is now working!

But i'm getting the previous error message...

This code is working (taking out the element view)

Code:

$query = $db->getQuery(true);
$query = "SELECT toxico FROM formule_tox_view WHERE personne_responsable=$resp AND produit=$prod";
$db->setQuery($query);
$rows = $db->loadObjectList();
foreach ($rows as $value) {
$my_rowid = $value->toxico;
echo "{fabrik view=details id=69 rowid=$my_rowid}";
}

When adding the element view, back to the same Error: O You are trying to embed an element called toxico___ingredient which is not present in the list or has been unpublished
And notice how it now has 3 underscores!

Code:

$query = $db->getQuery(true);
$query = "SELECT toxico FROM formule_tox_view WHERE personne_responsable=$resp AND produit=$prod";
$db->setQuery($query);
$rows = $db->loadObjectList();
foreach ($rows as $value) {
$my_rowid = $value->toxico;
echo "{fabrik view=details id=69 rowid=$my_rowid}";
echo "{fabrik view=element list=72 rowid=$my_rowid element=toxico___ingredient}";
}

mbeley · Jan 22, 2016

I found a workaround!
I was thinking: If Fabrik does not give the expected result, maybe SQL will! And it does.

Code:

$query = $db->getQuery(true);
$query = "SELECT toxico FROM formule_tox_view WHERE personne_responsable=$resp AND produit=$prod";
$db->setQuery($query);
$tox = $db->loadObjectList();
foreach ($tox as $value) {
$tox_rowid = $value->toxico;
$query2 = $db->getQuery(true);
$query2 = "SELECT cosing.INCI_Name FROM cosing JOIN toxico ON toxico.ingredient = cosing.id WHERE toxico.id=$tox_rowid";
$db->setQuery($query2);
$title = $db->loadResult();
echo "$title";
echo "{fabrik view=details id=69 rowid=$tox_rowid}";
}

mbeley · Jan 22, 2016

Thank you Hugh and Troester.
I really enjoy working with Fabrik, and I could not do it without your help!

(SOLVED) Generate report from database

mbeley

Member

cheesegrits

Support Gopher

mbeley

Member

cheesegrits

Support Gopher

mbeley

Member

cheesegrits

Support Gopher

mbeley

Member

mbeley

Member

cheesegrits

Support Gopher

mbeley

Member

mbeley

Member

troester

Administrator

mbeley

Member

mbeley

Member

troester

Administrator

mbeley

Member

mbeley

Member

mbeley

Member

Useful links

Members online