ORDER BY weird behaviour

Hi @fecke9296,

How are you displaying your project? On the back-end? On the front-end with wpdadiehard? Or on the front-end with Data Forms? Can you add some screenshots? You can use the contactform on the plugin website if you prefer to send them in private.

>>> OT: is there a way to make projects public?

Yes, you can! Make sure you do not select any roles in your project page and add your project page to a web page using one of the shortcodes. It only works if you explicitly remove all the roles from a page.

Hope this helps,
Peter

Hi Peter,

Thanks for your quick reply ??
Both back-end and front-end display the problem. It seems that the ordering is done not properly from the import of the table. On the other hand, ordering on MySQL workbench works fine. The table I want to display is linked to WP Data ACCESS through server import. Among all the table presented in the db imported, this is the only one which display this behaviour. You can find the screenshot from the front-end here.

• This reply was modified 3 years, 6 months ago by fecke9296.

In addition, I found another odd behaviour when managing the Project template Utility. When I change something on either the Data entry tab or the List Table tab on one of the column where the SORTBY works, automatically the tick on the seventh column (the one displaying odd behaviour) is taken off, and I cannot manage to restore it. Here the video

Hi @fecke9296,

Thanks for the video! It clarifying the issues. ??

The problem is in the clinical phase column name. The column name contains a white space and the plugin does not handle white spaces in column names correctly. This issue is on my to do list. Hope to fix it soon. As a work around you can replace the white space with an underscore (clinical_phase instead of clinical phase). That should it.

Please let me know if this work around works for you.

Thanks,
Peter

Wooo! So easy to solve! Thanks a lot ??

I want to add that I have encountered this and other problems with both table names and column names. This is not unique to WPDA, other tools have similar problems.

I advise all my users to stick to the 26 letters of the alphabet, either upper or lower case, digits 0 to 9, and the underscore ‘_’ character, do not use anything else for names.

Remember that you can, in WPDA, define the presentation name of the column in explorer->settings and in publications. This can have anything you want.

Great advice, @charlesgodwin :). Indeed I usually name everything with underscores or dashes, I don’t know how this time it slipped. Another reason to remember! ??

Great you solved it @fecke9296! ??

Your post made me think Charles! The problems you are referring to are not only applicable to database issues. To same problems appear when table or column names are used in html element id’s or names. You know this is on my to do list, but I have been pushing this ahead of me as I know the consequences of the necessary changes might make things even worse. What if I would just add a warning? It would not solve the problem, but would it be enough to help users? At least it would not make things worse. ?? What do you think?

@peterschulznl A warning would be useful but where do you warn? The problem is pervasive.

Perhaps the best short term solution is two fold.
1. Add a troubleshooting section to the documentation or add some text to ‘known limitations’
2. Add warnings in the explorer->settings area

To all reading this, I’m serious about my earlier warning about safe characters. Even the hyphen “-” can cause problems in some contexts.

Re the fix.
Using the MySQL convention of a back-tick “`” as a convention is a problem as it’s also used, for other purposes, in Javascript. I’m not sure you can devise a 100% solution.

Hi Charles,

Good idea! Here is a start:
https://ibb.co/ZS6jC1j

The validation will be added to the other tools as well. And of course I will add a section to the documentation. I think this is much safer than rewriting names for html and jquery.

Thank you for your contribution Charles! And thank you @fecke9296 for reporting! I’ll try to add this to the next update.

Best regards,
Peter

Looks OK.

I think keeping it at the “we warned you!” level is better than trying to code over every possible problem. I expect your users to realize that this product is more than a simple database tool and that integrating many technologies requires compromise. If changing a column name would break other applications, the user could consider using generated virtual columns as a sort of ‘alias’. https://mariadb.com/kb/en/generated-columns/