2. PHPRunner General questions
  3. Problem with default drop-down value in 4.1 with MySQL Strict Mode
This topic is locked

Problem with default drop-down value in 4.1 with MySQL Strict Mode

 		8/19/2009 12:02:39 PM
PHPRunner General questions
J
 jsuisman author

Hi all,
I have older project that was written using phprunner 4.1 Build 377. It uses a mysql 5.0.4 database in non-strict mode. On a test machine I enabled mysql Strict mode and this project is now broken.
This is what is happening. ex. field1 in database is an enum with values 'true' and 'false' it allows null and default is NULL. It's not required in phprunner project. The look-up wizard values in project are true and false with default blank. When phprunner generates the htmlp it generates a select with the first option having a blank value. When I go to save changes to record, I get back the dreaded Data truncated for column 'whatever' at row 1 mysql error. The problem is that using strict mode in MySql gives this error if you try to insert a blank value for an enum. It doesn't just grab the default field value like using non-strict mode.
Ideally, I think the fix for this is to have the first option in the select to be <option id=whatever value="NULL">-SELECT-</option>. I've tried to set the default value in the look-up wizard to be both NULL and "NULL" and neither one of them works either. Any ideas on this?
Thank you,
Jarred
EDIT - Actually project was created using phprunner 4.2 Build 377, NOT 4.1
J
 		Jane 8/20/2009

Hi,
you can change option value in the BuildSelectControl function in the generated include/commonfunctions.php file.
J
 		jsuisman author 8/20/2009

Hi,

you can change option value in the BuildSelectControl function in the generated include/commonfunctions.php file.


Thanks Jane,
But...will those changes to commonfunctions.php get overwritten every time the project is edited and built?
Jarred
J
 		Jane 8/20/2009

Jarred,
Yes.

You can also add your changes to the PHPRunner4.2/source/include/commonfunctions.php file.
J
 		jsuisman author 8/21/2009



You can also add your changes to the PHPRunner4.2/source/include/commonfunctions.php file.


Jane,
This didn't really work...phprunner automatically wraps these values with an apostrophe and MySQL treats 'NULL' differently than NULL.
Currently the default <option> value of every select is "". This relies on the fact that MySQL strict mode isn't enabled and by saying "UPDATE whatever SET enum_field = ''" MySQL reverts to the default value. Technically this statement should read "UPDATE whatever SET enum_field = DEFAULT". This also works when the user hasn't explicitly defined a default value for the field. It goes to MySQL's default which is the first element in the enum. The same thing that happens currently.
I'm wondering if I can make a feature request for possible inclusion in next release of phprunner. Since every field on the edit page of the project is included in the update SQL statement, perhaps you could give the user an option of what to set as the value of the first (default) option in a lookup/select. Ideally this would also include the ability to use SQL keywords by turning off apostrophe wrapping.
It would also require a minor change to validation.js for required fields. Where you check for cbo, you would just check for selected index being 0 and if it is set validateObject.val = ''.
But, this all hinges on whether or not you could change the query builder to turn apostrophe wrap on or off. (back to original problem)
Thanks,
Jarred
J
 		Jane 8/24/2009

Hi,
in the latest version you can remove field from update query in the Before record added event.

Here is a sample:

if ($values["FieldName"]=="")

unset($values["FieldName"]);
J
 		jsuisman author 8/24/2009



Hi,
in the latest version you can remove field from update query in the Before record added event.

Here is a sample:

if ($values["FieldName"]=="")

unset($values["FieldName"]);



Jane,
First of all, thanks for your help.
This doesn't really work either though. It works great if the drop-down is set to blank and they want to leave the drop-down as blank, but if the drop is set to something and the user wants to make it blank, this statement will remove it from the SQL query and the value won't change in the db.
I guess what I'm really looking for is a way to explicitly set a field to null or default, instead of relying on the non-strict mode of the db to make it null when it gets a invalid value. The SQL statement would look something like

UPDATE someTable SET `someField` = NULL



or even better

UPDATE someTable SET `someField` = DEFAULT


Thanks again,
Jarred