2 Database Joins on 1 Form, 2nd Join Fails to Load with MySQL Where statement?

J

JoomlaAddict

Member
I have a form set up with several groups of cascading dropdowns. The overall structure looks like this:

Group 1:
- Element 1 = DB Join
- Element 2 = Cascading Drop Down, watches Element 1
- Element 3 = Cascading Drop Down, watches Element 2

Group 2:
- Element 4 = Cascading Drop Down, watches Element 3
- Element 5 = Cascading Drop Down, watches Element 3
- Element 6 = Cascading Drop Down, watches Element 3 (hidden)
- Element 7 = DB Join

I want the options presented in element 7 to be refined by the data from Element 6. The data scheme is set up in a way that there will be only one available option in element 6, so it should auto select.

I think I've got all this set up correctly, but Element 7 never populates any data with the following Where clause: thistable.{table's field} = '{form___element_name}'

If I remove the where clause, Element 7 populates with unfiltered data. I checked the DB and it looks like Fabrik made a new table when I created the second DB Join, but I'm not sure what it's used for. The naming convention for this new table looks like this: "formname___repeat__element_name"

Any idea on what's going on? I'm not sure how to trouble shoot this further.
 
I think I've got all this set up correctly, but Element 7 never populates any data with the following Where clause: thistable.{table's field} = '{form___element_name}'
Click to expand...

Did you make a typo in your post, or did you really use thistable.{table's field}?

It should be {thistable}.table_field

How is your second join element set to render? if you set it to oen of the types that allows for multiple selections, like checkbox, then we automatically create a one-to-many table to hold the results, as we obviously can't store multiple foreign keys to multiple rows in the "other" table, in a single field on the main table. So it has to use a one-to-many linkage table.

However, that shouldn't affect the way you are trying to use the WHERE filter.

-- hugh
 
Sorry, that was a typo, I was typing too fast. "table's field" was supposed to be table_field.

I have tried different renderings for the 2nd DB Join, including check boxes, so that explains the new database table. Thanks for that info! :) But I'm not sure why the WHERE query isn't refining the data. If I remove the WHERE query, I get all options in the 2nd DB join. I would think this implies a problem with the syntax of the WHERE clause, but I don't see what the problem is, its a fairly simple query.
 
I turned on Joomla debug, here is the error that pops up under mysql queries. In the database join (Element 7), I'm trying to show "attributes" that match the "type":


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'thistable.type = '' ORDER BY text ASC' at line 1 SQL=SELECT DISTINCT(`test_repeat_Attributes`.`attribute`) AS value, attribute AS text FROM `vehicle_attributes` AS `test_repeat_Attributes` thistable.type = '' ORDER BY text ASC
Click to expand...

Any thoughts on where the error is?
 
JoomlaAddict said:
I turned on Joomla debug, here is the error that pops up under mysql queries. In the database join (Element 7), I'm trying to show "attributes" that match the "type":




Any thoughts on where the error is?
Click to expand...

I am having the same problem. It appears to be the way in which Fabrik is rendering the second db join, as I too have 2 multi-select fields. The problem really lies with the query itself and MySql. MySql switched the way they handled certain statements, and the way Fabrik puts together its SQL query creates an implied join, which as of 5.0.12 is now interpreted differently.

You can see the MySql dev notes here: http://bugs.mysql.com/bug.php?id=14047
 
Have you tried putting a WHERE in front of you clause?

WHERE {thistable}.whatever = '{table___element}'

-- hugh
 
Thanks Cheesegrits! I'm used to working in CCD where the WHERE clause is included, so I overlooked this step. :)

But unfortunately, this did not fix my problem. :( Two questions...

1. In the second DB Join, which column/field should the WHERE statement be referencing? Should it be asking for the corresponding field found on the "Repeat" table that was created by the second DB Join, or should it be asking for the field found in the table I chose to join (the one selected in the Data area)?

2. I tried referencing both fields in the question above, and both times I got the "Unknown Column" error when I turned on Joomla debug. The 2nd DB Join doesn't seem to try to load the data (no ajax spinner) after I select Element 3, which causes Element 6 to populate with data. Is the mysql error causing this to happen, or do I need to set up Element 7 to "Watch" Element 6?

Here are the mySQL errors...

Code: 
Unknown column 'thistable.type' in 'where clause' SQL=SELECT DISTINCT(`test_repeat_Additional_Attributes`.`attribute`) AS value, attribute AS text FROM `vehicle_attributes` AS `test_repeat_Additional_Attributes` WHERE thistable.type = '' ORDER BY text ASC


Unknown column 'thistable.Additional_Attributes' in 'where clause' SQL=SELECT DISTINCT(`test_repeat_Additional_Attributes`.`attribute`) AS value, attribute AS text FROM `vehicle_attributes` AS `test_repeat_Additional_Attributes` WHERE thistable.Additional_Attributes = '' ORDER BY text ASC
 
Thanks again cheesegrits, I had overlooked simple syntax. :-[

So this is sort of working now. I'm not getting any mysql errors, but the element isn't populating data because it's not watching any elements.

Element 7 = DB Join, but it won't know what data to populate until Element 6 populates. Element 6 populates as soon as Element 3 is selected by the user.

Is it possible to have element 7 watch element 6?
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 13 (members: 1, guests: 12)
    Back
    Top