Error when creating a List Page with Code Generator

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,

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,

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

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,

Hi Daniel

Sorry to say, but there still come up with the same message

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 4 ' ORDER BY fullname' at line 3 in 
/var/www/vhosts/vascularregistry.gr/httpdocs/registry/lib/schema_functions.php on line 410

Regards, 

Andreas

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!

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

Hi Andreas, 

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