Getting data from another table, and set by set_value

Suite and Optimus Suite 5
#1

Dear Friends,

Is any possibility to get some data from another table, and use it to set_value in beforeInsert option?

This my code :

require '../codebase/connector/grid_connector.php';
require_once './connection.php';
$res=mysql_connect($host,$dbUsrName,$dbPass);
mysql_query("SET NAMES UTF8");
mysql_select_db($dbName);
$conn= new GridConnector($res);
$config = new GridConfiguration();
$conn->enable_log("log.txt",true);
$config->setColTypes(array("ro","ro","ro","ro","ro"));
$config->setInitWidths(array("700","*","*","*","*"));
$config->setColIds(array("name","postcode","town","street","company_id"));
$conn->set_config($config);
$company_name;
function getIdValue($data){
    global $company_name;
    $company_name = $data->get_value("company_id");
    LogMaster::log("name: ".$company_name);
}


if($conn->is_select_mode()){
   $conn->render_table("Localisations","id","name,postcode,town,street,company_id","","company_id"); 
}
else{
    
    $conn->event->attach("beforeInsert","getIdValue");
   $conn->render_table("Localisations","id","name,postcode,town,street,company_id","","");
    

}

Now, when it fires up, my $company_name equals clicked tree item name(which was typed in form field). What I need to do, is change this element by set_value method to id number which is in another “Companies” table. How get this value from another table and use it in beforeInsert method in this context?

This is log.txt:

====================================
Log started, 18/10/2016 08:10:42
====================================

DataProcessor object initialized
1476816938277_name => ccc
1476816938277_postcode => cc
1476816938277_town => c
1476816938277_street => c
1476816938277_company_id => DELETE
1476816938277_!nativeeditor_status => inserted
ids => 1476816938277

Row data [1476816938277]
name => ccc
postcode => cc
town => c
street => c
company_id => DELETE
!nativeeditor_status => inserted

name : DELETE

INSERT INTO Localisations(`name`,`postcode`,`town`,`street`,`company_id`) VALUES ('ccc','cc','c','c','DELETE')

exception 'Exception' with message 'MySQL operation failed
Cannot add or update a child row: a foreign key constraint fails (`bv`.`Localisations`, CONSTRAINT `Localisations_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `Companies` (`id`))' in gg/file_manager/codebase/connector/db_common.php:965

Now it working if I use set_value method with hardcoding (f.e. if I set like that: $data->set_value(“company_id”,14); ).
I’m using foreign keys because I have TreeView based on TreemultitableConnector.

Thanks for any advice.

#2

The Connector doesn’t provide a ready to use utility for such functionality. Still, you can have any code in the event, handler, including to call some external function, which will do the necessary conversion ( external function can use its own DB connector to retrieve the necessary info )