table prefilter error?

[tkj]

Hi making a new table goes well, however when i add a prefilter

Where Currencylink Greater than 1

I tried putting the 1 like 1 and '1' however i keep getting the error:

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 \'where ( jos_fabrik_formdata_2.Currencylink > '1' ) SQL=SELECT jos_fabrik_formdata_2.fabrik_internal_id AS `jos_fabrik_formdata_2.fabrik_internal_id`,
jos_fabrik_formdata_2.time_date AS `jos_fabrik_formdata_2.time_date`,
jos_fabrik_formdata_2.userID AS `jos_fabrik_formdata_2.userID`,
jos_fabrik_formdata_2.Funding AS `jos_fabrik_formdata_2.Funding`,
jos_fabrik_formdata_2.Currencylink AS `jos_fabrik_formdata_2.Currencylink`,
jos_fabrik_formdata_2.transactiontypelink AS `jos_fabrik_formdata_2.transactiontypelink`,
jos_fabrik_formdata_2.fabrik_internal_id AS `jos_fabrik_formdata_2.fabrik_internal_id`,
jos_fabrik_formdata_2.fabrik_internal_id AS __pk_val
FROM jos_fabrik_formdata_2
WHERE (`userID` = \'63\' )
where ( jos_fabrik_formdata_2.Currencylink > '1' )
ORDER BY `jos_fabrik_formdata_2.Currencylink` DESC LIMIT 0, 10You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \''1' )\' at line 2 SQL=SELECT COUNT(*) FROM jos_fabrik_formdata_2 WHERE (userID = \'63\' ) \n and ( jos_fabrik_formdata_2.Currencylink > '1' )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 \'where ( jos_fabrik_formdata_2.Currencylink > '1' ) SQL=SELECT jos_fabrik_formdata_2.fabrik_internal_id AS `jos_fabrik_formdata_2.fabrik_internal_id`,
jos_fabrik_formdata_2.time_date AS `jos_fabrik_formdata_2.time_date`,
jos_fabrik_formdata_2.userID AS `jos_fabrik_formdata_2.userID`,
jos_fabrik_formdata_2.Funding AS `jos_fabrik_formdata_2.Funding`,
jos_fabrik_formdata_2.Currencylink AS `jos_fabrik_formdata_2.Currencylink`,
jos_fabrik_formdata_2.transactiontypelink AS `jos_fabrik_formdata_2.transactiontypelink`,
jos_fabrik_formdata_2.fabrik_internal_id AS `jos_fabrik_formdata_2.fabrik_internal_id`,
jos_fabrik_formdata_2.fabrik_internal_id AS __pk_val
FROM jos_fabrik_formdata_2
WHERE (`userID` = \'63\' )
where ( jos_fabrik_formdata_2.Currencylink > '1' )
ORDER BY `jos_fabrik_formdata_2.Currencylink` DESC

[rob]

Hi

Do you have "users view the own details" turned on as well? If so then it looks like a bug in the construction of the SQL statement

[tkj]

Yes user "view own details" has been turned on, this function works fine in another form i have, but with prefilter turned on, i get same error as above. any idea's?

[rob]

ok think i've found the problem, to fix it :

open components/com_fabrik/fabrik_table.class.php

in the function getPrefilterSql, add after these lines (around line 1147)


if( $appliedPrefilters == 0 and $cond == '' ){
$selJoin = 'where';
}




//make sure we dont duplicate the where statement
if(stristr($sql, 'where' && $selJoin == 'where')){
$selJoin = 'and';
}

[tkj]

Hi unfortunate im having the same error after adding the lines you asked me too

The new sql error:
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 \'where ( jos_fabrik_formdata_2.transactiontypelink > 1 )
ORD SQL=SELECT jos_fabrik_formdata_2.fabrik_internal_id AS `jos_fabrik_formdata_2.fabrik_internal_id`,
jos_fabrik_formdata_2.time_date AS `jos_fabrik_formdata_2.time_date`,
jos_fabrik_formdata_2.userID AS `jos_fabrik_formdata_2.userID`,
jos_fabrik_formdata_2.Funding AS `jos_fabrik_formdata_2.Funding`,
jos_fabrik_formdata_2.Currencylink AS `jos_fabrik_formdata_2.Currencylink`,
jos_fabrik_formdata_2.transactiontypelink AS `jos_fabrik_formdata_2.transactiontypelink`,
jos_fabrik_formdata_2.fabrik_internal_id AS `jos_fabrik_formdata_2.fabrik_internal_id`,
jos_fabrik_formdata_2.fabrik_internal_id AS __pk_val
FROM jos_fabrik_formdata_2
WHERE (`userID` = \'63\' )
where ( jos_fabrik_formdata_2.transactiontypelink > 1 )
ORDER BY `jos_fabrik_formdata_2.Currencylink` DESC LIMIT 0, 10You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'where ( jos_fabrik_formdata_2.transactiontypelink > 1 )
ORD SQL=SELECT jos_fabrik_formdata_2.fabrik_internal_id AS `jos_fabrik_formdata_2.fabrik_internal_id`,
jos_fabrik_formdata_2.time_date AS `jos_fabrik_formdata_2.time_date`,
jos_fabrik_formdata_2.userID AS `jos_fabrik_formdata_2.userID`,
jos_fabrik_formdata_2.Funding AS `jos_fabrik_formdata_2.Funding`,
jos_fabrik_formdata_2.Currencylink AS `jos_fabrik_formdata_2.Currencylink`,
jos_fabrik_formdata_2.transactiontypelink AS `jos_fabrik_formdata_2.transactiontypelink`,
jos_fabrik_formdata_2.fabrik_internal_id AS `jos_fabrik_formdata_2.fabrik_internal_id`,
jos_fabrik_formdata_2.fabrik_internal_id AS __pk_val
FROM jos_fabrik_formdata_2
WHERE (`userID` = \'63\' )
where ( jos_fabrik_formdata_2.transactiontypelink > 1 )
ORDER BY `jos_fabrik_formdata_2.Currencylink` DESC

cheers
Troels

[rob]

Hi

Any chance that you could export the tables into a fabrik package for me, so I can install it locally and test things myself?
To export the tables, select components->fabrik->tables
select the tables you want to export
press export
give the package a label and check all the boxes
save the .tgz file that is made

Cheers
Rob

[tkj]

Hi Rob,

sure file has been emailed to info@pollen-8.co.uk dont think i can attach a file here in the forum?
cheers
T

[rob]

hi

Thanks for emailing the tables, the problem was easy to fix:

open components/com_fabrik/fabrik_table.class.php

in the function getPrefilterSql, add after these lines (around line 1147) , replace:

//make sure we dont duplicate the where statement
if(stristr($sql, 'where' && $selJoin == 'where')){
$selJoin = 'and';
}


with


//make sure we dont duplicate the where statement
if(stristr($sql, 'where') && $selJoin == 'where'){
$selJoin = 'and';
}

[tkj]

excellent :-) thanks.
T

[cchiacchierini]

I am having the same difficulty; however the code fix above doesn't work for me.

[rob]

what error do you get?

[cchiacchierini]

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 \'where ( jos_fabrik_formdata_1.status = Open ) LIMIT 0, 30\' at line 15 SQL=SELECT jos_fabrik_formdata_1.time_date AS `jos_fabrik_formdata_1.time_date`,
jos_fabrik_formdata_1.userid AS `jos_fabrik_formdata_1.userid`,
jos_fabrik_formdata_1.status AS `jos_fabrik_formdata_1.status`,
jos_fabrik_formdata_1.User_Name AS `jos_fabrik_formdata_1.User_Name`,
jos_fabrik_formdata_1.Assigned_To AS `jos_fabrik_formdata_1.Assigned_To`,
jos_fabrik_formdata_1.Issue AS `jos_fabrik_formdata_1.Issue`,
jos_fabrik_formdata_1.Issue_Detail AS `jos_fabrik_formdata_1.Issue_Detail`,
jos_fabrik_formdata_1.priority AS `jos_fabrik_formdata_1.priority`,
jos_fabrik_formdata_1.category AS `jos_fabrik_formdata_1.category`,
jos_fabrik_formdata_1.Close_Date AS `jos_fabrik_formdata_1.Close_Date`,
jos_fabrik_formdata_1.fabrik_internal_id AS `jos_fabrik_formdata_1.fabrik_internal_id`,
jos_fabrik_formdata_1.fabrik_internal_id AS __pk_val
FROM jos_fabrik_formdata_1
WHERE (`userid` = \'63\' )
where ( jos_fabrik_formdata_1.status = Open ) LIMIT 0, 30You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'where ( jos_fabrik_formdata_1.status = Open ) LIMIT 0, 30\' at line 15 SQL=SELECT jos_fabrik_formdata_1.time_date AS `jos_fabrik_formdata_1.time_date`, \n jos_fabrik_formdata_1.userid AS `jos_fabrik_formdata_1.userid`, \n jos_fabrik_formdata_1.status AS `jos_fabrik_formdata_1.status`, \n jos_fabrik_formdata_1.User_Name AS `jos_fabrik_formdata_1.User_Name`, \n jos_fabrik_formdata_1.Assigned_To AS `jos_fabrik_formdata_1.Assigned_To`, \n jos_fabrik_formdata_1.Issue AS `jos_fabrik_formdata_1.Issue`, \n jos_fabrik_formdata_1.Issue_Detail AS `jos_fabrik_formdata_1.Issue_Detail`, \n jos_fabrik_formdata_1.priority AS `jos_fabrik_formdata_1.priority`, \n jos_fabrik_formdata_1.category AS `jos_fabrik_formdata_1.category`, \n jos_fabrik_formdata_1.Close_Date AS `jos_fabrik_formdata_1.Close_Date`,\n jos_fabrik_formdata_1.fabrik_internal_id AS `jos_fabrik_formdata_1.fabrik_internal_id`,\n jos_fabrik_formdata_1.fabrik_internal_id AS __pk_val\n FROM jos_fabrik_formdata_1 \n WHERE (`userid` = \'63\' ) \n where ( jos_fabrik_formdata_1.status = Open ) LIMIT 0, 30