Formalicious Form published in database and in form, but showing unpublished in list and won't display

Hi,
I have a new site, using latest MODX 3.0.5 - have created a form in Formalicious, the same way I always do on other sites which work, but on this site despite the form showing published when you go into edit it, and in the database form published = 1, no expiry date set, and publish date in the past, it will not update to show published in the list of forms, and does not display when called. Have cleared cache but still no joy.
My previous sites are on older versions of MODX so I wondered if there was a bug or issue with latest version of MODX and current Formalicious distribution.
Any pointers would be gratefully received.
thanks
Sam

Does it work if you set a “Un-publication Date”?

Are there any (related) errors in the MODX error log?

I set that and save, it defaults to 1970 date.
I changed it manually in PHPmyadmin, and the form now shows as published in the backend, but when viewed on the front end none of the fields show, only the submit button and the title.
I am using latest modx and Formalicious 3.0

error log shows: /public_html/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) Error 42000 executing statement:
Array
(
[0] => 42000
[1] => 1064
[2] => 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 ‘ASC’ at line 1
)
I saw a post about this and it was an issue with a reserved key in mySQL8 for RANK - but I am using mySQL 7.4x

The fixes suggest update files in core/components/formalicious/model/ but I do not have this folder despite reinstalling formalicious twice now

There is no MySQL 7! (I think you confuse it with the PHP version).


It looks like this pull request if for the MODX 2.x version of the “Formalicious” extra.
You’d have to apply similar changes to the “3.x” branch of the extra. (The corresponding files are now located in core/components/formalicious/src/Formalicious/Model and core/components/formalicious/src/Formalicious/Processors/Mgr/...)

I did see changes on Github to fix the problem with “dates beyond 2038”. So hopefully this issue is fixed in the next version.

My apologies you are correct - it is MySQL 8 - I was looking at the server by mistake.

I have updated all the files in core/components/formalicious where I could find rank to FormaliciousAnswer_rank, including /src/ but I still have the same issue.
When I reran the install it did appear to install ok though which is better than previously.
Not sure what to do now

FormaliciousAnswer_rank isn’t the right value for every class.
Try instead adding backticks (`) around rank every time it is used to sort the query:

// For example: Replace
$criteria->sortby('rank', 'ASC');
// with
$criteria->sortby('`rank`', 'ASC');

Nothing is working :frowning:
I have tried just change the files mentioned in the fix and this does not fix it.
Are you saying that all instances of rank should now be (backtick) rank (backtick) everywhere in the package where ‘rank’ is mentioned?
Also not sure how to fix the default date other than manually changing the unpublish date in mysql.
I would not normally fret and would just wait for the updated release to come out but given this issue has been known for almost 6 months, it looks like there is no fixed release coming :frowning:

I did a test on a new installation (MODX 3.0.5, Formalicious 3.0.0-pl, MySQL 8).
The form fields were displayed correctly when I made these two changes:

changed to $criteria->sortby('`rank`', 'ASC');

also changed to $criteria->sortby('`rank`', 'ASC');.

(Additional code changes might be necessary for other functionality of Formalicious.)


I’m sure there will be a new release eventually.

Sorry for my ignorance but are you installing first and then making these changes?

Yes, otherwise the changes you make to the code get overwritten again.

No that hasn’t worked either. Ugh so frustrating but thank you so much for all your help. I appreciate your time.

I have a site on the same server running an older version of Formalicious - v 2.0.3 - is there a way I can get the older version on to this new site? Clearly the 3.0 version is not going to work and I have spent so much time on it now, I am needing to give up and revert I think

The older version of Formalicious won’t run on MODX 3.


Do you still get the same error in the MODX error log
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 'ASC' at line 1
even after the changes you made to the code?

Yes (excuse hour incorrect due to British Summer Time)
I think I have uninstalled and reinstalled and edited so many times I am now getting extra errors too! :slightly_frowning_face:

[2024-06-18 14:09:15] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) Error 42000 executing statement:
Array
(
[0] => 42000
[1] => 1064
[2] => 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 ‘ASC’ at line 1
)

[2024-06-18 14:09:18] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) Error 42000 executing statement:
Array
(
[0] => 42000
[1] => 1064
[2] => 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 ‘ASC’ at line 1
)

[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/public_html/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/public_html/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/public_html/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxxl/core/components/formalicious/model/

a quick reinstall of pdotools seems to have cleared the issue with the /model/ directory now.
So now I just have this in my error log:
[2024-06-18 14:17:05] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) Error 42000 executing statement:
Array
(
[0] => 42000
[1] => 1064
[2] => 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 ‘ASC’ at line 1
)

If you add the line

$xpdo->log(xPDO::LOG_LEVEL_ERROR, 'SQL = ' . $criteria->toSql());

to the file core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php after line 227

the SQL query that generates the error should get logged to the MODX error log as well. What is this SQL query?


And please use a code-block to enter the query into this forum, so that the backticks are preserved:

```// create a line with 3 back-ticks to start a code-block
// SQL query here
```// create a line with 3 back-ticks to end the code-block
[2024-06-18 14:37:12] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) Error 42000 executing statement: 
Array
(
    [0] => 42000
    [1] => 1064
    [2] => 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 'ASC' at line 1
)

[2024-06-18 14:37:12] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 228) SQL = SELECT `FormaliciousField`.`id` AS `FormaliciousField_id`, `FormaliciousField`.`step_id` AS `FormaliciousField_step_id`, `FormaliciousField`.`title` AS `FormaliciousField_title`, `FormaliciousField`.`placeholder` AS `FormaliciousField_placeholder`, `FormaliciousField`.`description` AS `FormaliciousField_description`, `FormaliciousField`.`directional` AS `FormaliciousField_directional`, `FormaliciousField`.`type` AS `FormaliciousField_type`, `FormaliciousField`.`required` AS `FormaliciousField_required`, `FormaliciousField`.`published` AS `FormaliciousField_published`, `FormaliciousField`.`rank` AS `FormaliciousField_rank`, `FormaliciousField`.`property` AS `FormaliciousField_property` FROM `modx_formalicious_fields` AS `FormaliciousField` WHERE  ( `FormaliciousField`.`published` = 1 AND `FormaliciousField`.`step_id` = 1 )  ORDER BY rank ASC 
[2024-06-18 14:37:13] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) Error 42000 executing statement: 
Array
(
    [0] => 42000
    [1] => 1064
    [2] => 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 'ASC' at line 1
)

[2024-06-18 14:37:13] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 228) SQL = SELECT `FormaliciousField`.`id` AS `FormaliciousField_id`, `FormaliciousField`.`step_id` AS `FormaliciousField_step_id`, `FormaliciousField`.`title` AS `FormaliciousField_title`, `FormaliciousField`.`placeholder` AS `FormaliciousField_placeholder`, `FormaliciousField`.`description` AS `FormaliciousField_description`, `FormaliciousField`.`directional` AS `FormaliciousField_directional`, `FormaliciousField`.`type` AS `FormaliciousField_type`, `FormaliciousField`.`required` AS `FormaliciousField_required`, `FormaliciousField`.`published` AS `FormaliciousField_published`, `FormaliciousField`.`rank` AS `FormaliciousField_rank`, `FormaliciousField`.`property` AS `FormaliciousField_property` FROM `modx_formalicious_fields` AS `FormaliciousField` WHERE  ( `FormaliciousField`.`published` = 1 AND `FormaliciousField`.`step_id` = 1 )  ORDER BY rank ASC 

```//

The back-ticks around rank are missing.


Are you really sure you changed line 34 in the file core/components/formalicious/src/Formalicious/Model/FormaliciousStep.php to $criteria->sortby('`rank`', 'ASC');?