Form not updating mandatory fields when changes applied directly to database record

[drareve]

My members normally only use the system when they join, renew, or need to update personal information but I require them to complete particular fields and periodically review their information. I achieve this by making certain fields mandatory, including a checkbox where they must tick the statement, "I accept EUA and confirm the data in this record is correct". The mandatory setting ensures they must be compliant before saving their record.

At renewal I update their record so the checkbox is un-ticked, meaning they must re-tick it to save their record, and I use the change history data to report if any record has not been re-ticked in last 365 days.

Unfortunately because the mandatory fields apply to both the user and the administrator, when and admin edits the member's profile (or approves a new registrants), they have to remove the mandatory flag against the profile fields or they are forced to fill in the member's fields.

It would be convenient if functionality could be added so administrators could be edit profiles and be immune from mandatory fields, but meanwhile as there are several mandatory fields, it is inevitable that administrators will sometimes forget to restore the mandatory flag.

To make is simpler I have a two SQL statements that
1- updates the value of the usf_mandatory column in the relevant records in the table _user_fields, to 0 before the admin makes profiles changes
2- updates the value of the usf_mandatory column in the relevant records in the table _user_fields, back to 1 after the admin makes profiles changes.
example.

Code: Alles auswählen

UPDATE admnh_user_fields SET usf_mandatory = CASE WHEN admnh_user_fields.usf_id BETWEEN 3 AND 9 THEN 0 WHEN admnh_user_fields.usf_id =10 THEN 0 WHEN admnh_user_fields.usf_id =19 THEN 0 WHEN admnh_user_fields.usf_id BETWEEN 21 AND 26 THEN 0 ELSE usf_mandatory END

However, whilst I see the SQL updates the records, the form an admin uses to update the profile (adm_program/modules/profile/profile_new.php) does not recognise the change unless they either (a) clear cookies from the browser and log back on or (b) save any record within the Profile fields form (modules/preferences/fields_new.php)

I currently execute the SQL from the phpMyAdmin console as my attempt to create a php file failed.

Any suggestions on the PHP I could use so I could execute the SQL changes via a browser and can anyone explain why the changes are not seen in the forms until after the cookies are cleared.

Thanks in advance.

[fasse]

Hi drareve,

we work at this point with a cache. Every user data will be loaded once and only be reloaded if user data was changed through program functions.

If you update user data without program functions than you must call the following function afterwards:

Code: Alles auswählen

$gCurrentSession->renewUserObject();

Fasse

[drareve]

Thanks for the info and letting me know the name of the function

I realised it was related to sessions and didnt known the name of the function so achieve the same with
SQL statement "UPDATE mem_sessions SET ses_renew=1";

If it helps anyone like me I use the following php file saved into ..\adm_program\modules\members and the admins use it to enable mandatory fields. There is a similar one that set the values to "0" to disable.

I am not a system prog so I'm sure there are better ways, but it works for my scenario

Code: Alles auswählen

<?php
/**
 ***********************************************************************************************
 * Set mandatory field to 1 (enabled)
 *
 * created by admin@sasac.co.uk
 *
 * Parameters:
 *
 * $sql : Modify the statement to select the mem_user_fields.usf_id that should be included.
 * user must be an administrator to execute this script.
 ***********************************************************************************************
 */



require_once(__DIR__ . '/../../system/common.php'); // loads common parameters including database objects and variables
require(__DIR__ . '/../../system/login_valid.php'); // verifies that a current user is executing the script

if($gCurrentUser->isAdministrator()) //check TRUE OR FALSE to see if current user has administrator privs
{   // start of if TRUE (is an administrator)

    //define the SQL statement to set required profile fields to manadatory (1) using usf_id values

    $sql = "UPDATE mem_user_fields SET usf_mandatory = CASE
WHEN mem_user_fields.usf_id BETWEEN 3 AND 6 THEN 0
WHEN mem_user_fields.usf_id =8 THEN 0
WHEN mem_user_fields.usf_id =10 THEN 0
WHEN mem_user_fields.usf_id =12 THEN 0
WHEN mem_user_fields.usf_id BETWEEN 21 AND 25 THEN 0
ELSE usf_mandatory END";

// run SQL query to update usf_id
    $result = $gDb->query($sql); // $gDb is the PDO database connection defined /../../system/classes/Database.php:26:

    $sql = "UPDATE mem_sessions SET ses_renew=1";
    $result = $gDb->query($sql);

//	some text to show feedback to user

    echo <<<HTML
<h3>Mandatory Fields have been disabled (Exception)<br></h3>
<p>When mandatory fields are disabled you can edit and save a user's profile without being required to input data into fields that are usually mandatory<br></p>
<p><a href="man_on.php">Click here to enable mandatory fields</a><br>
<p>When mandatory fields are enabled, the <b>system requires mandatory fields to be completed (normal) </b>when editing a user's profile.<br> </p>
<p><a href="members.php">Click here to return to User Management</a></p>

HTML;


    $sql = 'SELECT `usf_name`,`usf_mandatory` FROM mem_user_fields';//define the SQL statement to show user fields

    $stmt = $gDb->query($sql); // executes query and defines $stmt as output array
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

//The $result is a multidimensional (array of arrays),with containing info from SQL query.

//echo '<pre>';var_dump($result);echo'</pre>'; // output $result array to screen

// output field name details as HTML table
echo <<<HTML
                <head>
                <style>
                table {
                    font-family: arial, sans-serif;
                    border-collapse: collapse;
                    width: 50%;
                }
                th {
                border: 1px solid #dddddd;
                text-align: center;
                padding: 8px;
                }
                td {
                border: 1px solid #dddddd;
                text-align: left;
                padding: 1px;
                }
                tr:nth-child(even) {
                    background-color: #dddddd;
                }
                </style>
                </head>
                <body>
                <h3>Status of Field Names</h3>
    HTML;

    echo '<table><tbody><th>Field Name</th><th>Mandatory Field Status</th>';
    foreach ($result as $mem_user_fields) {
        echo '<tr><td>' . $mem_user_fields ['usf_name'] . '<td>' . $mem_user_fields ['usf_mandatory'];
    }

}   // end of if TRUE (is an administrator)


else
{   // start of if FALSE (is an administrator)
$userId = (string) $gCurrentUser->getValue('usr_login_name'); //retieves user_login_name from $gCurrentUser

echo <<<HTML
<h3>The user $userId is not an administrator<br></h3>
<p><a href="/../../system/logout.php">Log off
</a> and reconnect with an administrator account if you want to change mandatory settings</p>

HTML;
}   // end of if False (is an administrator)

?>