Does FormConnector implement complex sql or even the beforeRender event???
Yep, all connectors support all documented events and API methods.
so i can use SQL joins and subqueries to load data on a form??
Yep, you can use any valid SQL to get the data.
Beware that while any SQL can be used for data loading, connector will not be able to generate update commands correctly for complex SQL
session_start();
require("../config.php");
require("./connector/db_mysqli.php");
require("./connector/grid_connector.php");
$dbc= new mysqli(DB_SERVER ,DB_USER,DB_PASS, DB_NAME);
if($dbc){
$grid = new GridConnector($dbc, "MySQLi");
$grid->dynamic_loading(100);
//$config = new GridConfiguration();
//$config->setHeader(" ","First Name","Last Name","Email Address","Department","Privileges On","#cspan","Account Activation");
//$config->attachHeader("#rspan","#text_filter","#text_filter","#text_filter","","Employees","Payrolls","");
//$config->attachFooter("Item Name,Item CD", Array("background: #ff0000;", "background: #00ff00;"));
//$config->setInitWidths("30,150,150,*,150,100,100,100");
//$config->setColSorting('na,str,str,str,str,str,str,str');
//$config->setColColor("#CCE2FE");
//$config->setColHidden("false,false");
//$config->setColTypes("cntr,ro,ro,ro,ro,coro,coro,coro");
//$config->setColAlign('right,left,left,left,left,center,center,left');
//$grid->set_config($config);
/*CRUD*/
$grid->event->attach("beforeUpdate","update");
//$grid->event->attach("beforeIsert", insert);
//$grid->event->attach("beforeDelete",delete);
//$grid->event->attach("beforeProcessing", function validate($data){
// if ($data->get_value("some")=="")
// $data->invalid();
// });
//$grid->event->attach("beforeRender",set_userData);
//$grid->enable_log("./errors.txt");
$loadSQL = "SELECT * FROM department INNER JOIN admin_priv USING(departmentid) INNER JOIN login USING(loginid) WHERE login.level='A' ORDER BY fname ASC";
$grid->set_options("employee_priv",array("n" => "No Privileges","e"=>"Edit Only", "c"=>"Create & Edit","a" => "Full Access"));
$grid->set_options("payroll_priv",array("n" => "No Privileges","e"=>"Edit Only", "c"=>"Create & Edit","a" => "Full Access"));
$grid->set_options("active",array("Activated" => "Active","Deactivated"=>"Inactive"));
$grid->render_sql($loadSQL,"loginid",",fname,lname,email,national_id,department_name,employee_priv,payroll_priv,active","department_name,level,password");
function set_userData($row){
$row->set_userdata("password",$row->get_value("password"));
}
/*
function update_me($data){
$fname = trim(strval($data->get_value('fname')));
$lname = trim(strval($data->get_value('lname')));
$email = trim(strval($data->get_value('email')));
$nationalID = trim(strval($data->get_value('national_id')));
$departmentID = trim(intval($data->get_value('departmentid')));
$nationalID = trim(intval($data->get_value('national_id')));
$emp_priv = trim(strval($data->get_value('employee_priv')));$active
// $pay_priv = trim(strval($data->get_value('payroll_priv')));
// $active = trim(strval($data->get_value('active')));
//$password = trim(strval($data->get_value('password')));
//if(!isset($fname)||!isset($lname)||!isset($email)||!isset($nationalID)||!isset($departmentID)||!isset($nationalID)||!isset($emp_priv)||!isset($active)||!isset($pay_priv))$data->invalid();
//$updateadmin_priv ="UPDATE admin_priv SET fname='$fname',lname='$lname',national_id='$nationalID',departmentid='$departmentID',employee_priv='$emp_priv',payroll_priv='$pay_priv' WHERE loginid='{$data->get_id()}' LIMIT 1";
// $adminset = "UPDATE login SET email='$email,password= sha1('$password'),active='$active' WHERE loginid='{$data->get_id()}' LIMIT 1";
//$loginset = @mysqli_query ($dbc, $updateLogin);
if (mysqli_affected_rows($dbc) == 1) {
$loginset = @mysqli_query ($dbc, $updateLogin);
if (mysqli_affected_rows($dbc) == 1)
$data->success();
else $data->invalid();
}
else $data->invalid();
}//END update
*/
//UPDATE existing records
function update_me($data){
$fname = $data->get_value('fname');
$lname = $data->get_value('lname');
$email = $data->get_value('email');
$nationalID = $data->get_value('national_id');
$departmentID = $data->get_value('departmentid');
$nationalID = $data->get_value('national_id');
$emp_priv = $data->get_value('employee_priv');
$pay_priv = $data->get_value('payroll_priv');
$active = $data->get_value('active');
$password = $data->get_value('password');
$updateadmin_priv ="UPDATE admin_priv SET ";
$updateadmin_priv .= isset($fname)?" fname='$fname',"."" :" ";
$updateadmin_priv .= isset($lname)? "lname='$lname',"."": " ";
$updateadmin_priv .= isset($nationalID)? "national_id='$nationalID',"."": " ";
$updateadmin_priv .= isset($departmentID)? "departmentid='$departmentID',"."": " ";
$updateadmin_priv .= isset($emp_priv)? "employee_priv='$emp_priv',"."": " ";
$updateadmin_priv .= isset($pay_priv)? "payroll_priv='$pay_priv',"."": " ";
//$updateadmin_priv .= isset($data->get_id()? "WHERE loginid='{$data->get_id()}' LIMIT 1" :"";
$updateadmin_priv = trim($updateadmin_priv);
$grid->sql->query($updateadmin_priv);
}//END update
function update($data){
$grid->sql->query("UPDATE admin_priv SET fname='{$data->get_value('fname')}',$lname='{$data->get_value('lname')}',departmentid='{$data->get_value('departmentid')}',national_id='{$data->get_value('national_id')}',employee_priv='{$data->get_value('employee_priv')}',payroll_priv='{$data->get_value('payroll_priv')}' WHERE loginid='{$data->get_id()}' LIMIT 1");
$grid->sql->query("UPDATE login SET email='{$data->get_value('email')}',password=sha1('{$data->get_value('password')}'),active='{$data->get_value('active')}' WHERE loginid='{$data->get_id()}'");
$_SESSION =$data;
}//END delete
//INSERT new records
function insert($data){
}//END isert()
//DELETE existing records
function delete($data){
}//END delete
}//END if($dbc)
?>
Please help me with the custom update for complex sqlTry to move custom functions BEFORE the code which attaches them as event handlers.
thank you…i have another issue,form items of type select (options) are not sending data to the GridConnector when i bind the form as shown[code]
//JS CODE
var str = [
{ type:“block” , name:“block_one”, offsetLeft:“30”, list:[
{ type:"select" , name:"departmentid", label:"Department", validate:"ValidInteger", inputWidth:143, required:true, offsetTop:"10", position:"label-top",connector:"./../php/servers.php?options=true&department=2" },
{ type:"select" , name:"acctive", label:"Account Activation", labelWidth:170, inputWidth:143, required:true, offsetTop:"15", position:"label-top", options:[
{ value:"Activated", text:"Account Activated" },
{ value:"Deactivated", text:"Deactivate Account" }
] },
{ type:"label" , name:"lab2", label:"Account Access Rights" },
{ type:"select" , name:"payroll_priv", label:"Privileges On Payrolls", validate:"ValidAplhaNumeric", labelWidth:170, inputWidth:143, required:true, offsetTop:"10", position:"label-top", options:[
{ value:"n", text:"No Privileges" },
{ value:"e", text:"Edit Only" },
{ value:"c", text:"Create & Edit" },
{ value:"a", text:"Full Control" }
] },
{ type:"select" , name:"employee_priv", label:"Privileges On Employees", validate:"ValidAplhaNumeric", labelWidth:170, inputWidth:143, required:true, offsetTop:"10", position:"label-top", options:[
{ value:"n", text:"No Privileges" },
{ value:"e", text:"Edit Only" },
{ value:"c", text:"Create & Edit" },
{ value:"a", text:"Full Control" }
] }
] }
];
var admins_form = admins_form_cell.attachForm(str);
admins_form.enableLiveValidation(true);
admins_form.attachEvent("onValidateError", function (input, value, result){
dhtmlx.message({type:"error", text:admins_form.getItemLabel(input).replace(/\*+/g,'')+" is missing!"});
});
admins_form.bind(admins_grid);
[/code]
#
#PHP CODE
#
<?php
session_start();
require("../config.php");
require("./connector/db_mysqli.php");
require("./connector/grid_connector.php");
$dbc= new mysqli(DB_SERVER ,DB_USER,DB_PASS, DB_NAME);
function update($data){
$departmentID = trim(intval($data->get_value('departmentid')));
$emp_priv = trim(strval($data->get_value('employee_priv')));
$pay_priv = trim(strval($data->get_value('payroll_priv')));
$active = trim(strval($data->get_value('active')));
$lid = $data->get_id();
global $grid;
$grid->sql->query("UPDATE admin_priv SET departmentid=".$departmentID.",employee_priv='".$emp_priv."',payroll_priv='".$pay_priv."' WHERE loginid=".$lid." LIMIT 1");
$grid->sql->query("UPDATE login SET email='".$email."',password=SHA1('".$password."'),active='".$active."' WHERE loginid=".$lid." LIMIT 1");
$data->success();
}//END update
/*CRUD processing triggers*/
$grid->event->attach("beforeUpdate", "update");
$grid->enable_log("./admins_grid.txt",true);
$loadSQL = "SELECT * FROM department INNER JOIN admin_priv USING(departmentid) INNER JOIN login USING(loginid) WHERE login.level='A' ";
$grid->render_sql($loadSQL,"loginid",",fname,lname,email,national_id,department_name,employee_priv,payroll_priv,active","password,level,departmentid,loginid");
PS: I have dhtmlxprocessor.js and the debug.js included, this code has been shortened for other updates which are working