if ( empty( $modeldata['Teams_teamId'] ) ) $modeldata['Teams_teamId'] = null;
PDO is smart enough to know the difference between an empty string ('') and a null string and generates the correct database insert/update when the value is null.
This is still a workaround and hopefully will not be accepted as a solution. This creates too much extra/unnecessary work for the developer. IMO, since PHPRad already knows that the field is NULLable and that it is not required, this should be the default action taken by PHPRad for any NULLable column.
Here's what I would like to see PHPRad do:
When retrieving information from the database catalog:
Set the field's default value to the database default for the field
If there is no default value in the catalog and the field is NULLable, set the field's default value to NULL
If the field is NULLable, set the field's default field-required to false
Provide a NULL default option (checkbox) on the Manage Database dialog that, when checked, would cause the default value to be set to NULL (not the literal 'NULL'). This cannot be a literal in the DefaultValue field because it would not allow for the literal 'NULL' to be used as a default value.
Modify the controller to set the value to NULL when the field has the NULL default option checked and the field is empty
@willvin Thanks for the suggestion. I appreciate you trying to provide solutions, but this still doesn't solve the problem. It just keeps the column from being updated if nothing is selected. The problem with this is that when Schedules.Teams_teamId is not NULL (contains a Teams.teamId value) going into the Edit page and the value is removed during the edit so that no Team is selected when the update occurs, removing the field from the array will cause the field to NOT be updated. The end result is that the Schedules.Teams_teamId will continue to contain the old Schedules.Teams_teamId value instead of being set to NULL.
IMO, the right solution is for PHPRad to properly handle setting NULLable columns to NULL when no value is selected. I realize that this will require code changes and won't immediately solve this problem. But, knowing that the problem has been acknowledged and is being addressed makes my decision to purchase PHPRad much easier.
@willvin This is NOT a database design issue. I've been programming for over 40 years and designing databases for the past 25 years. This is a COMMON scenario.
The column Teams_teamId in the Schedules table is a foreign key into the Teams table. The teamId column in the Teams table is a typical ID column which is defined as INT(11). Schedules.Teams_teamId is not required and thus is NULLable. I am not trying to set the value of Schedules.Teams_teamId to ''. Instead, PHPRad is attempting to set the value of the INT column to '' when no team was selected on the Add/Edit page. Because the column is NULLable, PHPRad should be setting the column to NULL instead of '' when no value is supplied.
As for how the data is entered on the Add/Edit page, the field is setup as a SELECT field where the value is being selected from the teamId column in the Teams table. When I select a team on the Schedules Add/Edit page, the Schedules.Teams_teamId value is populated correctly by PHPRad. But when no team is selected, PHPRad attempts to set the value of Schedules.Teams_teamId to '' even though PHPRad knows that Schedules.Teams_teamId is an INT and that it is NULLable.
Add to this that PHPRad will not allow me to set the default value for Schedules.Teams_teamId to NULL. I must enter a numeric value in the default value field. But there is no legitimate number that can be used because it is a foreign key and any value that is not in the Teams table would violate referential integrity rules.
@willvin What do you want an image of? I manually typed in the error message since I couldn't upload an image here. An image will show you the same error:
Quote
Error Executing Query :- 'ALTER TABLE `roles` MODIFY COLUMN `roleId` VARCHAR(255) auto_increment; ALTER TABLE `roles` MODIFY COLUMN `roleId` INTEGER(11); ALTER TABLE `role_permissions` MODIFY COLUMN `roleId` INTEGER(11); ALTER TABLE `staff` MODIFY COLUMN `Roles_roleId` INTEGER(11); ALTER TABLE `roles` ADD UNIQUE( `roleId`);' Error message: Incorrect column specifier for column `roleId`
Exception Traces This will only be displayed in DEVELOPMENT_MODE. Error Message SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'Teams_teamId' at row 1 File D:\Websites\wp247shiftpress_phpRad\app\models\PDODb.php On Line 1861 Stack Trace 1 D:\Websites\wp247shiftpress_phpRad\app\models\PDODb.php(1861): PDOStatement->execute() 2 D:\Websites\wp247shiftpress_phpRad\app\controllers\SchedulesController.php(218): PDODb->update('schedules', Array) 3 D:\Websites\wp247shiftpress_phpRad\system\Router.php(196): SchedulesController->edit('1', Array) 4 D:\Websites\wp247shiftpress_phpRad\system\Router.php(109): Router->run('schedules/edit/1/') 5 D:\Websites\wp247shiftpress_phpRad\index.php(106): Router->init()
I have the same problem with PHPRad Studio Classic Edition Version 2.7.3 but in my case the column is a foreign key to another table defined as INT(11) NULL. The column is not required and must be set to NULL when no value is supplied. When I attempt to set the default value to NULL, PHPRad tells me:
Quote
Value Not a Numeric Value. Table Field Accept Only Numeric Values
Setting the value to a numeric value that does not exist in the foreign table would result in a foreign key referential integrity violation.
The bottom line is: NULL needs to be the default value for fields that are nullable - regardless of the data type.
I am attempting to implement Role-Based Access Controls and get the following error when I click "Okay" on the "Role Base Access Control > Dynamic Role Permissions > Manage user Roles Permission Tables" dialog.
Quote
Error Executing Query :- 'ALTER TABLE `roles` MODIFY COLUMN `roleId` VARCHAR(255) auto_increment; ALTER TABLE `roles` MODIFY COLUMN `roleId` INTEGER(11); ALTER TABLE `role_permissions` MODIFY COLUMN `roleId` INTEGER(11); ALTER TABLE `staff` MODIFY COLUMN `Roles_roleId` INTEGER(11); ALTER TABLE `roles` ADD UNIQUE( `roleId`);' Error message: Incorrect column specifier for column `roleId`
I believe the error is related to the ALTER TABLE `roles` MODIFY COLUMN `roleId` VARCHAR(255) auto_increment; statement.
I have an image but the upload directory is still full.
Thanks for the reply Willvin. I must admit that I am surprised that phpRad doesn't support self-referencing relationships. This is a common database design technique. I'm not sure what you mean by "use the current record value". The data in the current record is not the data for the referenced record. Here's an example:
I have 2 records in my Staff table:
Record 1:
Id: 1
Name: Person 1
Manager Id: Null
Record 2:
Id: 2
Name: Person 2
Manager Id: 1
When displaying Record 2, then name of the Manager should be "Person 1" but the name on the current record is "Person 2". "Person 2" would not be the correct value.
For those interested, I did find a workaround by creating a view that selects the Id and Name from the Staff table and then referencing the view for the lookup instead of the directly referencing the table.
I am using PHPRad Classic version 2.7.3. I have a self-referencing table (Staff) that doesn't show any of the Staff fields in the Record Display Field select list on the Master Detail Relation dialog. In this case, staff.manager_Staff_staffId in the Staff table references staffId in the same table. My expectation is to be able to pick from the fields that are in the Staff table.
Note that the Record Display Field shows the detail fields just fine on non-self-referencing tables.
I tried to upload a picture but got "The upload directory is full. Please contact an administrator about this problem."
