• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

JOIN Bug?

rdiana

Member
Dear friends,
I've a form in which a php form plugin is activated. The PHP plugin should store data, from table two-joined tables, into table TEMP_REPORT. The simple code is the following:

$db =& JFactory::getDBO();
$query="INSERT INTO temp_report (user_id, lab_group, act_id) SELECT user_id, lab_group, lesson_days.id FROM `fab_laboptions` LEFT JOIN lesson_days ON fab_laboptions.lab_id = lesson_days.lab_id WHERE lesson_days.id ='1' ";
$db->setQuery($query);
$db->query();

But it desn't work, and no records are copied into TEMP_REPORT List. A problem with the LEFT Join? How to fix?

Thank you.
Cheers,

Roberto
 
Dear Hugh,
yes, of course. And it works in phpmyadmin. In Fabrik, I've used the code as PHP Form plugin setted as OnBeforeProcess.
The code runs correctly in Fabrik if I remove the LEFT Join part, but it is necessary.
 
I'm a little confused as to what you are doing.

The PHP plugin should store data, from table two-joined tables, into table TEMP_REPORT. The simple code is the following:
I've used the code as PHP Form plugin setted as OnBeforeProcess.
So at the start of the form process your PHP code is supposed to insert records, user_id, lab_group, act_id into TEMP_REPORT?

If this works with PHPMYADMIN then I'm not sure why it wouldn't work as a php plugin.

All Fabrik is doing is connecting to the database and executing your code, so the joins shouldn't be a problem otherwise they would fail in phpmyadmin as well.

Maybe try the following code for debugging?


Code:
$db =& JFactory::getDBO();
$query="INSERT INTO temp_report (user_id, lab_group, act_id) SELECT  user_id, lab_group, lesson_days.id FROM `fab_laboptions` LEFT JOIN  lesson_days ON fab_laboptions.lab_id = lesson_days.lab_id WHERE  lesson_days.id ='1' ";
$db->setQuery($query);
[COLOR=#000000][COLOR=#0000BB]$temp [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]db[/COLOR][COLOR=#007700]->[/COLOR][COLOR=#0000BB]loadObjectList[/COLOR][COLOR=#007700]();[/COLOR][/COLOR]
// $db->query(); 
[COLOR=#000000][COLOR=#0000BB]var_dump[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]$temp[/COLOR][COLOR=#007700]);[/COLOR][/COLOR]
exit;

 
Dear Felixhat,
thank you for your reply. The issue: I want to realize an attendance register, to be used in a university classroom. Then I create an event (a lesson and its date) in a suitable form and, on submission, a php plugin copies all the registred student-id and the lesson-id from two joined tables in a new list. In this list the new yesno field ATTENDANCE is present. The reason of this choice is related to the use of the list-plugin inlineedit, which is very useful to upgrade quickly a large number of records (the students).

The insert mysql command works fine also with a subselect but, under Fabrik, it doesn't if the LEFT Join is present. I emphasize the query has already been tried under phpmyadmin.

I've modified the code as suggested, but Fabrik doesn't allow to view the php output: how can I proceeed to view the debug?

Cheers,
Roberto
 
To view the output, you need to put an 'exit' after the var_dump(), to halt script processing at that point, otherwise Fabrik does an automatic redirect at the end of form processing, so your browser never sees any output.

You may have to put some error handling in to see if the query is generating an error for some reason.

One question - are all the tables involved in the same database as Joomla?

-- hugh
 
Dear Hugh,
yes, all the tables are in the same database. I had already tried to put 'exit' at the end of the script but nothing happened. I'll try again now before sleeping... :)
 
Hi Hugh,
finally, I've modified the query code in the following way:

$db =& JFactory::getDBO();
$query="INSERT INTO temp_report (user_id, lab_group, act_id) SELECT fab_laboptions.user_id, fab_laboptions.lab_group, lesson_days.id FROM fab_laboptions LEFT JOIN lesson_days ON fab_laboptions.lab_id = lesson_days.lab_id WHERE lesson_days.id ='16'";
$db->setQuery($query);
$db->query();

Practically, I've used a full name (table.field) and now the query works fine ;), perhaps in case of joined tables a more explicit syntax is required.

Thank you again.

Roberto
 
OK, glad you got it sorted.

It's just strange the exact same query would work in phpMyAdmin, but not through J!'s query API.

-- hugh
 
Hugh, another question: the original query has a placeholder related to an element of the same form. In fact this placeholder is evaluated only if I active the php form plugin onAfterProcess, and not onBeforeProcess (in this case it remains blank). Is it a normal behaviour? In which step a placeholder is evaluated?
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top