Saving nothing + filter bug

[Amema]

I already have three lists that work more or less perfectly. Trying to add a fourth ends in disaster, even though it's only one more of the same. No extra frills added; I use exactly the same fields and functions as before...

A First of all, the Save button only saves the id, but not the rest of the entered info into fields, textareas, date, user and dropdown. If I try to add it by editing afterwards I get the error:

Store row failed:
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' at line 1 SQL=UPDATE `libro_` SET WHERE

together with a blue line saying that saving/updating the line was successful, even though nothing was updated.


B At first I had filter ON for the dropdowns, but filter them as dropdowns gave these errors and warnings. (It says "Unknown column 'libro._". But the database table is libro_ without the period.) Here is the error for the User Dropdown filtered as Dropdown:

• filter query error: Unknown column 'libro._respondecas' in 'field list' SQL=SELECT DISTINCT(`j25_users`.name) AS `text`, `libro`.`_respondecas` AS `value` FROM `libro_`INNER JOIN `j25_users` AS `j25_users` ON `j25_users`.id = `libro`.`_respondecas` WHERE `libro`.`_respondecas` IN ('') GROUP BY text ASC

Warning: Invalid argument supplied for foreach() in /storage/.../components/com_fabrik/models/element.php on line 2165

Warning: Invalid argument supplied for foreach() in /storage/.../components/com_fabrik/models/element.php on line 2214

Warning: array_values() [function.array-values]: The argument should be an array in /storage/.../components/com_fabrik/models/element.php on line 2222





C The next dropdown field is a Dropdown with individual filter as Dropdown, which returns other errors:

• filter query error: Unknown column 'libro._stato' in 'field list' SQL=SELECT DISTINCT(`libro`.`_stato`) AS `text`, `libro`.`_stato` AS `value` FROM `libro_`LEFT JOIN `j25_users` AS `j25_users` ON `j25_users`.`id` = `libro_`.`respondecas` WHERE `libro`.`_stato` IN ('') GROUP BY text ASC

Warning: Invalid argument supplied for foreach() in /storage/content/72/106572/lek.bonadomo.se/public_html/j25eo/components/com_fabrik/models/element.php on line 2165

Warning: Invalid argument supplied for foreach() in /storage/content/72/106572/lek.bonadomo.se/public_html/j25eo/components/com_fabrik/models/element.php on line 2214

Warning: array_values() [function.array-values]: The argument should be an array in /storage/content/72/106572/lek.bonadomo.se/public_html/j25eo/components/com_fabrik/models/element.php on line 2222

Warning: usort() [function.usort]: The argument should be an array in /storage/content/72/106572/lek.bonadomo.se/public_html/j25eo/libraries/joomla/utilities/arrayhelper.php on line 463

Warning: array_unshift() [function.array-unshift]: The first argument should be an array in /storage/content/72/106572/lek.bonadomo.se/public_html/j25eo/components/com_fabrik/models/elementlist.php on line 125

Warning: Invalid argument supplied for foreach() in /storage/content/72/106572/lek.bonadomo.se/public_html/j25eo/libraries/joomla/html/html/select.php on line 504





Changing both dropdowns individual filters to 'field' makes the errors go away.

 

[troester]

Do you have any special characters in your table (libro?) name? (you had a post about diacritic letters)
Fabrik will replace such characters with _ which may result in issues if it's at the beginning or end of a string.

 

[cheesegrits]

OK, I think I see where the issue might be.

Is your 'stato' element called 'stato' or '_stato'?

I don't see an easy way round this one. Looks like you just can't have a table name ending in _. And preferably, no elements starting with _, although that probably wouldn't generate this error.

The problem being, in "full" element names, we use ___ (triple underscore) as the separator between the table and element name, to create the unique table___element name.

And one of the very frequently used functions we have in our code is safeColName(), which takes a full element name, and turns it into a quoted MySQL-safe column name, so ... safeColName('table___element') returns `table`.`element`.

The problem being, if your table ends in _ or your element starts with _, the full name will be table____element. And when we run that through safeColName(), that's going to return `table`.`_element`.

We could modify that so it returns `table_`.`element`, but that will simply break in a different way, for elements that start with _ instead of tables that end with _. That safeColName() function has no way of knowing whether the _ should be on the end of the table name or the start of the element name. Not without making a change to the way it gets called, which would a) require massive amounts of code changing, and b) add expensive processing to every safeColName() call, as it would have to load the element model itself (i.e. the full data structure for that element), rather than just manipulating strings.

So ... your only fix (at least for now) is to blow that List away, including deleting all the elements, and recreate it using a table name that doesn't end in _.

Sorry about that. One of those things we didn't think about when writing that code, and you happen to be the first person (in 7 years) who has tried using a table ending in an underscore!

-- hugh

 

[cheesegrits]

OK, I think I see where the issue might be.

Is your 'stato' element called 'stato' or '_stato'?

I don't see an easy way round this one. Looks like you just can't have a table name ending in _. And preferably, no elements starting with _, although that probably wouldn't generate this error.

The problem being, in "full" element names, we use ___ (triple underscore) as the separator between the table and element name, to create the unique table___element name.

And one of the very frequently used functions we have in our code is safeColName(), which takes a full element name, and turns it into a quoted MySQL-safe column name, so ... safeColName('table___element') returns `table`.`element`.

The problem being, if your table ends in _ or your element starts with _, the full name will be table____element. And when we run that through safeColName(), that's going to return `table`.`_element`.

We could modify that so it returns `table_`.`element`, but that will simply break in a different way, for elements that start with _ instead of tables that end with _. That safeColName() function has no way of knowing whether the _ should be on the end of the table name or the start of the element name. Not without making a change to the way it gets called, which would a) require massive amounts of code changing, and b) add expensive processing to every safeColName() call, as it would have to load the element model itself (i.e. the full data structure for that element), rather than just manipulating strings.

So ... your only fix (at least for now) is to blow that List away, including deleting all the elements, and recreate it using a table name that doesn't end in _.

Sorry about that. One of those things we didn't think about when writing that code, and you happen to be the first person (in 7 years) who has tried using a table ending in an underscore!

-- hugh

 

[Amema]

Yeah, I just ended testing. After the first answer I went back and checked and I had written libro_ as name of the table in the list settings. (Aren't we always bugged about to not forget the underscore in other places? It's become habit...)
I don't remember about my first attempts, but might have been the same thing. Now I did make a new list and as the only thing I could find that was not looking the same as the other three lists were the underscore, I tested that. And I now have a working table!
Thanks for making me aware of what I happened to do right this time!

 

[felixkat]

Thanks for posting back the good news.

Isn't it amazing how one small character can cause so much havoc!


To be honest we rarely see things like that, we used to have a few posts if somebody put a space in a table name, for example naming it 'A Table' but Fabrik now replaces the space with, ironically enough, an underscore.


Hugh,

Maybe we could add something in to prevent a underscore on at least the creation of a new list?

Even an additional trim?

$newtable = trim(JArrayHelper::getValue($data, '_database_name'));
            // mysql will force db table names to lower case even if you set the db name to upper case - so use clean()
            $newtable = FabrikString::clean($newtable);