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 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

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 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! :-)