MySQL Errors with Cascading Dropdowns + WHERE Statement

[JoomlaAddict]

Hey everyone,

I have set up a form that uses 1 DB Join element and 2 Cascading Dropdowns. This is intended to work as a vehicle selector, so a user can select Year Make Model using these three dropdowns.

I have it working using a source data table that contains the vehicle records (Year Make Model), but I want to constrain the Model selection (the 3rd dropdown) so it only displays options for vehicle records that match Year AND Make. Currently, it is only constrained by Make, so a user is able to select 2016 Ford Model-T. Ford hasn't made a Model-T since 1931, so Model-T shouldn't show in this example.

Here is what the data structure looks like on the source table:

{index} - {vehicle_year_id} - {vehicle_make_name} - {vehicle_model_name}​

I added the following statement the WHERE query in Fabrik:

{element___vehicle_year} = {thistable}.vehicle_year_id​

The token {element___vehicle_year} references the DB Join, which is the first element in the series. Adding this WHERE statement causes the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '} = `vehicle_ymm_1`.vehicle_year_id ORDER BY text ASC' at line 3

SQL=
SELECT DISTINCT(`vehicle_ymm_1`.`vehicle_model_name`) AS value, `vehicle_ymm_1`.`vehicle_model_name`AS text
FROM `vehicle_ymm` AS `vehicle_ymm_1`
WHERE 4 = -4 AND {element___vehicle_year} = `vehicle_ymm_1`.vehicle_year_id
ORDER BY text ASC​

From what I can tell, all of this looks correct. Can anybody tell me if they see something wrong with the syntax?

Thanks in advance!

 

[cheesegrits]

The {element___vehicle_year} hasn't been replaced, which means it doesn't exist as an element name. Should it be {element___vehicle_year_id}?

-- hugh

 

[JoomlaAddict]

Unfortunately no, I copy/pasted from the "Full Element Name" column into the WHERE statement, and the name of that element doesn't include "_id". To double check, I just copy/pasted it again and I am still getting the same error.

So for some reason, the token isn't getting replaced?

 

[cheesegrits]

Ah, I was going by what you said in your post ...

Here is what the data structure looks like on the source table:

{index} - {vehicle_year_id} - {vehicle_make_name} - {vehicle_model_name}

To debug this, you'll have to put a line of debug code in. In ./plugins/fabrik_element/cascadingdropdown/cacadingdropdown.php, around line 878 should be ...

        $where = $w->parseMessageForPlaceHolder($where, $data);

Before that line, put ...

var_dump($where, $data);exit;

... and load the page again. It should just dump out some data. Paste that here.

If it's not at line 878, update from github,.

-- hugh

 

[JoomlaAddict]

Here's what I got after making that update:

string(6) "4 = -4" array(7) { ["Itemid"]=> string(3) "197" ["option"]=> string(10) "com_fabrik" ["view"]=> string(4) "form" ["formid"]=> string(1) "4" ["customer_vehicle_build_submissions___vehicle_year_raw"]=> array(1) { [0]=> string(0) "" } ["whereval"]=> array(1) { [0]=> &string(2) "''" } ["wherekey"]=> string(15) "vehicle_year_id" }

 

[cheesegrits]

OK, so try appending _raw to the placeholder you are using.

-- hugh

 

[JoomlaAddict]

Thanks Hugh! I gave that a shot, but I'm still getting errors. Here is the fabrik error that displays on the front-end after applying "_raw". Looks like the code is making it slightly farther, there is no closing curly brace "}" at the start of the syntax error (bolded):

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= `vehicle_ymm_1`.vehicle_year_id ORDER BY text ASC' at line 3 SQL=

SELECT DISTINCT(`vehicle_ymm_1`.`vehicle_model_name`) AS value, `vehicle_ymm_1`.`vehicle_model_name`AS text
FROM `vehicle_ymm` AS `vehicle_ymm_1`
WHERE 4 = -4 AND = `vehicle_ymm_1`.vehicle_year_id
ORDER BY text ASC​

And here is the var_dump:

string(6) "4 = -4" array(7) { ["Itemid"]=> string(3) "197" ["option"]=> string(10) "com_fabrik" ["view"]=> string(4) "form" ["formid"]=> string(1) "4" ["customer_vehicle_build_submissions___vehicle_year_raw"]=> array(1) { [0]=> string(0) "" } ["whereval"]=> array(1) { [0]=> &string(2) "''" } ["wherekey"]=> string(15) "vehicle_year_id" }​

 

[cheesegrits]

Put single quotes around your placeholder so it still generates a valid query even when the value is empty, like '' = foo, instead of = foo.

-- hugh

 

[JoomlaAddict]

That did the trick, thanks for the help troubleshooting this!

There aren't any records where any of the Year, Make or Model columns are NULL, so does that mean the error was only being triggered on page load since the vehicle_year value hadn't been set yet?

 

[cheesegrits]

Correct. You wouldn't see the error on the page when it updates (which would happen if your set the watched element back to "no selection", you'd just see a Javascript error complaining about "unexpect entity <", because of the PHP error tossed into the structure JSON response from the AJAX call that updates the CDD, which then blows up the automatic JSON parsing of the response in the CDD's JavaScript.

-- hugh