2. PHPRunner General questions
  3. [SOLVED]  Execute PHP/DB Query from JS
This topic is locked
[SOLVED]

 Execute PHP/DB Query from JS

 		11/20/2014 12:30:11 AM
PHPRunner General questions
S
 Stucco author

Hi,
I need to make Field B required depending on the value of Field A. Field A is a lookup table. The trigger for whether or not it should be required is a checkbox within the Field A table. I need to perform a query to the database either on load or on value change to see which possible values of Field A would set the requirement to complete Field B.
For the conditional requirement I have the technique from this old post working: Old Post.
However, this uses hard coded values to determine for which values to make Field B required. Instead I need to use a DBLookup or something else to find the list of values from the database that should trigger the conditional requirement. I can't seem to find a way to execute the query within the JS event.
My only idea at this point is to create a custom PHP page that generates JS, and include that page as a JS include from the custom JS event. I don't even know if this would work.
Thanks!
S
 		Stucco author 11/20/2014

I'm sure this isn't the best way, but I forced it to work.
I created a JavaScript OnLoad event with the following code:

Runner.util.ScriptLoader.addJS(['container_type.php?pageid='+pageid]);

Runner.util.ScriptLoader.load();


Then I created a custom page under editor called container_type.php that generates JS with the following code:

<?PHP

include("include/dbcommon.php");

$sql="SELECT id FROM customers WHERE require_container_type = 1;";

$rs=CustomQuery($sql);

$customer_ids = "";

while($row=db_fetch_array($rs)){

$customer_ids .= '"' . $row["id"] . '",';

}

$customer_ids = rtrim($customer_ids,",");

?>
var customer_field = Runner.getControl(<?PHP echo $_GET['pageid'];?>, 'customer_id');

var container_type_field = Runner.getControl(<?PHP echo $_GET['pageid'];?>, 'container_type_id');

var customers_with_required_container_type = [<?PHP echo $customer_ids; ?>];
//-----ON CHANGE CODE

customer_field.on('change', function(e) {

if (customers_with_required_container_type.indexOf(this.getValue()) == -1) {

container_type_field.removeValidation("IsRequired");

} else {

container_type_field.addValidation("IsRequired");

}

});


After generated it ends up looking something like this:

var customer_field = Runner.getControl(1, 'customer_id');

var container_type_field = Runner.getControl(1, 'container_type_id');

var customers_with_required_container_type = ["6","11"];
//-----ON CHANGE CODE

customer_field.on('change', function(e) {

if (customers_with_required_container_type.indexOf(this.getValue()) == -1) {

container_type_field.removeValidation("IsRequired");

} else {

container_type_field.addValidation("IsRequired");

}

});


It isn't pretty but it works.
There is one strange behavior that I remember was a PHPRunner bug from a while ago that I thought was fixed. If I pick a customer for which the container type is required, and do not provide a value, and attempt to save, I get the required error, which is correct. But if after that attempt I switch to a customer that does NOT require the container type, the field still shows as required and I am not able to save.
Admin 11/21/2014

I believe that this approach is too complicated. My guess is that you do not need to create an extra PHP file for this task.
What you need to do is to execute SQL query in event like BeforeDisplay and then pass query results to Javascript OnLoad event using technique described in this article:

http://xlinesoft.com/phprunner/docs/how_to_access_php_variables.htm