Error when creating a List Page with Code Generator

By andreasml - October 8, 2022

Hi 

I am trying to create a List Page with the Code Generator, and I get the following message when I run the URL of the page (../section-list.php):

Fatal error: There was an error creating the list field 'new_owner'. 
MySQL Error: You have an error in your SQL syntax; 
check the manual that corresponds to your MariaDB server version 
for the right syntax to use near 'd code on line 3 ' ORDER BY fullname' 
at line 3 in /var/www/vhosts/vascularregistry.gr/httpdocs/registry/lib/schema_functions.php 
on line 410

However, if I login into the CMSB, it works fine, and the content of the list page appears. 

Any ideas on how to carry on?

Kind regards, 

Andreas Lazaris

By daniel - October 10, 2022

Hi Andreas,

It looks like this is being triggered by an advanced list field, possibly due to an incorrect MySQL query. Can you check the list options for the "new_owner" field? If it's set up to generate the list from a MySQL query, that is probably generating the error. Feel free to copy the query here and I can try to troubleshoot the issue.

Thanks,

Daniel
Technical Lead
interactivetools.com

By andreasml - October 10, 2022

Hi Daniel

The list options for the "new_owner_ field are:

SELECT num, CONCAT_WS(", ", fullname, firstname)
FROM `<?php echo $TABLE_PREFIX ?>accounts`
WHERE hospital='<?php echo $CURRENT_USER['hospital'] ; ?>'
ORDER BY fullname

I changed that to a simple list, but the problem still exists. The new_owner field Let me note again that the problem disappears when I have logged into the CMSB and run the URL. 

Thanks for your assistance. Regards, 

Andreas

By daniel - October 10, 2022

Hi Andreas,

Try this:

global $CURRENT_USER;
SELECT num, CONCAT_WS(", ", fullname, firstname)
FROM `<?php echo $TABLE_PREFIX ?>accounts`
WHERE hospital='<?php echo $CURRENT_USER['hospital'] ; ?>'
ORDER BY fullname

Note that this needs to be on a page where the user is required to be logged in, since it's using details from the current user's record.

Let me know if that works!

Thanks,

Daniel
Technical Lead
interactivetools.com

By andreasml - October 10, 2022

Hi Daniel

It does not work both in logged in and logged out users. The following message appears.

Fatal error: There was an error creating the list field 'new_owner'. 
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to
your MariaDB server version for the right syntax to use 
near 'global $CURRENT_USER; SELECT num, CONCAT_WS(", ", fullname, firstname) FROM `...' 
at line 1 in /var/www/vhosts/vascularregistry.gr/httpdocs/registry/lib/schema_functions.php on line 410

Also, I cannot open the section itself. The above fatal error also appears.

Regards, 

Andreas

By daniel - October 11, 2022

Hi Andreas,

Apologies, try this instead:

<?php global $CURRENT_USER; ?>
SELECT num, CONCAT_WS(", ", fullname, firstname)
FROM `<?php echo $TABLE_PREFIX ?>accounts`
WHERE hospital='<?php echo $CURRENT_USER['hospital'] ; ?>'
ORDER BY fullname

Thanks,

Daniel
Technical Lead
interactivetools.com

By Dave - October 12, 2022

Hi Andreas, 

It looks like what is happening is that something in the PHP code is throwing an error which is interfering with the SQL query.

If you like you can fill out a 2nd level support request here and we can take a look and debug that for you:
https://www.interactivetools.com/support/request/

Hope that helps!

Dave Edis - Senior Developer

interactivetools.com

By andreasml - October 15, 2022

Hi David

Thank you very much for your help

In order to run properly I had to downgrade the PHP version from 7.4 to 7.3

(https://www.interactivetools.com/forum/forum-posts.php?postNum=2244577#post2244577)

Kind regards,

Andreas

By Dave - October 17, 2022

Hi Andreas, 

Ok, great.  Glad it's working! :-)

Dave Edis - Senior Developer

interactivetools.com