JOIN Bug?

Discussion in 'Fabrik 3.x Testing' started by rdiana, Apr 28, 2012.

  1. rdiana Member

    Level: Community
    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
  2. cheesegrits Support Gopher

    Level: Community
    Have you tried running that query by hand in something like phpMyAdmin?

    -- hugh
  3. rdiana Member

    Level: Community
    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.
  4. felixkat Senior Member

    Level: Community
    I'm a little confused as to what you are doing.

    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 (text):
    $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;
     
  5. rdiana Member

    Level: Community
    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
  6. cheesegrits Support Gopher

    Level: Community
    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
  7. rdiana Member

    Level: Community
    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... :)
  8. rdiana Member

    Level: Community
    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
  9. cheesegrits Support Gopher

    Level: Community
    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
  10. rdiana Member

    Level: Community
    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?

Share This Page