Weird database join issue...[Solved]

[m6xmed5]

Hi guys,
I'm having a bit of a strange issue with a database join element with a where clause.
I'll do my best to explain whats happening.

I have 2 tables;
table 'manual_transmission_jobs' where the database join element is being used
and
table 'dyno_setups' where the database join element looks up.

I want the database join element to return only one result based on a calc element 'setup' in the 'manual_transmission_jobs' table - the calc element returns the id of the desired row in 'dyno_setups' depending on the value of a dropdown element in the 'manual_transmission_jobs' table.

The issue I'm having is that when I try to add a new record I get a mysql syntax error.

1064 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 'ORDER BY text ASC' at line 5

If access level on the database join element is set to anything other that 'public' you I get the same syntax error.

I think I'm getting the error on the add form because the calc element hasn't yet returned a row id.

I'm using this where clause to look up the record I want to link to:

WHERE {thistable}.id = {manual_transmission_jobs___setup_raw}

have also tried

WHERE {thistable}.id = {manual_transmission_jobs___setup}

Does anyone know why it's throwing the error?

I assumed that fabrik would have a build in way to deal with a no results scenario.

Any pointers would be much appreciated.

 

[m6xmed5]

Thanks to startpoint for pointing this out...

instead of

WHERE {thistable}.id = {manual_transmission_jobs___setup_raw}

do

{thistable}.id = '{manual_transmission_jobs___setup_raw}'

so drop the WHERE and single quote the placeholders for the fk.

 

[cheesegrits]

The "WHERE" is optional, we insert it if you don't.

And yes, you always need to quote any placeholders that might return an empty value, such that the resulting SQL is valid if the placeholder is empty. Without the quotes, if your placeholder has no value, the query becomes ...

[...] WHERE tablename.id = ORDER BY text ASC

... which is obviously syntactically incorrect. With the quotes, it becomes ...

[...] WHERE tablename.id = '' ORDER BY text ASC

... which is correct (and returns the correct result, an empty set).

I assumed that fabrik would have a build in way to deal with a no results scenario.

Anywhere in Fabrik where you enter SQL or PHP directly, you have to ensure that the resulting code will be syntactically correct, for any value that your placeholder may have (including being empty).

For example, in a PHP fragment, if you have a placeholder which will either be an integer, or empty, and you need to treat it as an integer ...

$myValue = (int)'{mytable___myelement}';

... ensures that $myValue will always be an integer, and it'll be 0 if the placeholder was empty.

-- hugh