Changing values before insert or update

[bdurfee]

Here's the situation.

Record X in Table Y has a status set to 'Approved'.
User Z in Group A (low permissions) logs in and goes to Table Y to edit Record X.
User Z can see the status field of Record X.
User Z makes changes to Record X.
No matter what User Z sets for the status, the status needs to be set to 'Pending' because User Z cannot make that decision.

User Admin logs into the same Table Y, reads the record and can set the status to 'Approved' or 'Disapproved' accordingly. User Admin's status change stays with whatever they choose for the status.

I have three ideas that might solve the problem.
1. Set the status in the detail view so that it is only viewable to certain groups. (Setting it as 'not viewable in detail view' won't work since the admins need to be able to use that same table to update the status.)
2. Use the before_update and before_insert hooks to change the value of that field before it is updated or inserted.
3. Use the after_update and after_insert to run an update query setting the status field to pending except for groups that are supposed to see the field.

I don't know how to do #1 or #2.
I have tried #3, but it will not connect to the database. #3 will work if I know where the database connect function is in AppGini.

Suggestions? Solutions?

[shasta59]

bdurfee

It may be an idea to post the code here which you are inserting into the hook for #3. This may make it easier for someone to suggest a solution.

Alan

[bdurfee]

I've found that when I have extensive hooks, it's easier to create an additional page with functions. I include the additional page at the top of the hooks page and then reference it in the hook.

Code: Select all

	include('__func_hooks.php');   // the page I created that holds the functions.

	function cust_school_teachers_after_insert($data, $memberInfo, &$args){
	
		$record_id = $data['selectedID'];
		$group = $memberInfo['group'];
		
		$updatestatus = status_updates($group, $record_id, 'teachers');
		
		return TRUE;
	}

Here are the functions:

Code: Select all

	// ------------------------------------------------
	// CONNECT TO DATABASE
	// ------------------------------------------------
	function dbconnect_1(){
		$server	=	'server.address';
		$db_user =	'username';
		$db_pass =	'password';
		$dbname =	'dbname';
	
		$db_conn = mysql_connect($server, $db_user, $db_pass) or die ("Database CONNECT Error");
		mysql_select_db($dbname, $db_conn);
		
		return $dbname;
	}
	
	// ------------------------------------------------
	// RUN THE QUERY STATEMENT
	// ------------------------------------------------
	function runthequery($statement, $db){
		$dbname = dbconnect_1();
		
		$results = mysql_query($statement);
		mysql_close();
		
		return $results;
	}

	// ------------------------------------------------
	// STATUS UPDATES
	// ------------------------------------------------
	function status_updates($group, $record_id, $table){
	
		if($table == 'teachers'){
			$updTable = 'cust_school_teachers';
			$updField = 'sch_teach_status';
			$updStatus = 'Pending';
			$updIDField = 'sch_teach_id';
		}elseif($table == 'classes'){
			$updTable = 'cust_school_classes';
			$updField = 'sch_class_status';
			$updStatus = 'Pending';
			$updIDField = 'sch_class_id';
		}
	
		$agCtr = 0;
		$approved_groups = array();
		$approved_groups[$agCtr] = 2;	$agCtr++;
		$approved_groups[$agCtr] = 4;	$agCtr++;
		$approved_groups[$agCtr] = 5;	$agCtr++;
		
		$DoUpdate = 'Yes';
		foreach($approved_groups AS $ag){
			if($group == $ag){
				$DoUpdate = 'No';
			}
		}

		if($DoUpdate == 'Yes'){
			$updStatus = 'UPDATE '.$updTable.' SET '.$updField.' = "'.$updStatus.'" WHERE '.$updIDField.' = '.$record_id;
			$success = runthequery($updStatus, 1);
		}
	}

The problem is that I get a database connect error. Therefore, if I can use AppGini's database connect, I can run the query and it solves the issue.

I'm open to any code improvements you think of.

[shasta59]

dburfee

Here is some info on variables you may wish to consider. If you are calling a function within a function then the variable is, or may be, not persisting once the function is done and therefore giving you the result you are having.

MySQL has three different kinds of variables: (this is a quick - read cheap and dirty - overview only)

Local variables
Local variables are set in the scope of a statement or block of statements.
Once that statement or block of statements has completed, the variable goes out of scope.

Session variables
Session variables are set in the scope of your session with the MySQL server.
A session starts with a connection to the server and ends when the connection is closed.
Variables go out of scope once the connection is terminated.
Variables created during your connection cannot be referenced from other sessions.
To declare or reference a session variable, prefix the variable name with an @ symbol:

SET @count = 100;.

Global variables
Global variables exist across connections.
They are set using the GLOBAL keyword: SET GLOBAL max_connections = 300;.
Global variables are not self-defined, but are tied to the configuration of the running server.

While this does not solve the issue it may give you a clue as to what is happening in your code. As far as I can tell, by a quick look at your code, your variables are working only within their function and then they 'go away' after it is done.

Hope this helps in some way. (Or it might just muddy the waters even more. - grin)

Alan