2. PHPRunner General questions
  3. NULL instead of empty string
This topic is locked

NULL instead of empty string

 		2/19/2019 4:18:25 PM
PHPRunner General questions
P
 Penultimatum author

Hi,
I have an add & edit page for a table that has a field which is a Foreign Key in another table. The value of this column can be either the key in the master table, or it could be NULL when the entry is not relevant.
The problem is the ADD & EDIT pages appear to be setting this field to an empty string rather than NULL when no value is specified (via dropdown selection). This results in the Insert and Update failing with a "Cannot add or update a child row: a foreign key constraint fails"
For the ADD page I have added a line to the "Before record added" to unset the variable if empty:

if ($values["Name"]=="") { unset($values["Name"]); }


However, I am unable to get the Update page to work so its not possible to change an existing row. Doing a dump of $values in 'Before record updated' I can see:

["Name"]=> string(0) ""


I have tried changing this to NULL with:

$values["Name"] = NULL;


I can see this appears to have changed in a further dump of $values, but the update still fails with a key constraint so I dont believe this is working.
I have verified that a manual MySQL insert works with the following:

update Table SET Name = NULL WHERE ID = "1234";


How do I get PHPRunner to set NULL values rather than empty ones in this scenario?
Thanks!!
Jim.
jadachDevClub member 2/19/2019

I just checked this out on one of my test apps. If I do not make a selection, my database value remains null. If I make a selection and save, the database field has a numeric value. If I edit the selection to "Please Select" (no value), the database field returns to null. I am using MSSQL Server if that makes any difference.
P
 		Penultimatum author 2/20/2019



I just checked this out on one of my test apps. If I do not make a selection, my database value remains null. If I make a selection and save, the database field has a numeric value. If I edit the selection to "Please Select" (no value), the database field returns to null. I am using MSSQL Server if that makes any difference.


I'm using MySQL and enabling query logging I can see that when the drop down is set to "Please Select" I get the equivalent of the following:
Add page:

insert into Table (`id`, `var1`, `var2`, `Name`) values ('abc', 'def', 'xyz', '')


Edit Page

update `Table` set `id`='abc', `var1`='def', `var2`='def', `Name`='' where id='abc'


I have the MySQL column set to allow NULL and default value to be NULL, but it looks like PHPRunner is trying to set an empty string rather than NULL in this scenario.
Jim.
M
 		MikeT 2/20/2019

Maybe you could use 'Custom record update' event with your own SQL, but this would be somewhat cumbersome solution.
Admin 2/20/2019

For your scenario using CustomEdit event will be a solution.
P
 		Penultimatum author 2/22/2019

I managed to achieve what I need using the 'Custom Record Update' with something like this:

## Build the Set value element of update ###

$set_string = '';

foreach ($values as $key => $value) {

if($set_string != "") { $set_string .= " , "; }

if($key == 'Name' && $value == '') {

$set_string .= " Name = NULL ";

} else {

$set_string .= " $key = '" . $value ."'";

}

}
### Set the Where clause from keys array #####

$where_string = '';

foreach ($keys as $key => $value) {

if($where_string != "") { $set_string .= " , "; }

$where_string .= " $key = '" . $value ."'";

}
### Create and execute the query ###

if($set_string != '' && $where_string != '') {

$sql_string = "UPDATE MYTABLE SET $set_string WHERE $where_string ;";

$sql = DB::prepareSQL($sql_string);

if (! DB::Exec( $sql ) ) {

echo "Error updating DB:" . DB::LastError() . "<BR>";

exit;

}

}
return false;


Thanks.
M
 		MikeT 2/22/2019

Just another thought: maybe you could achieve the same with triggers in the db, maybe less overhead and stuff to manage in your code. But probably also a question of "taste".

(Personally I'd prefer to have this handled right at the storage/db level.)