Good morning, I would need to know if, by making an insertion in a table, it is possible to automatically set empty fields to NULL. Obviously assuming that in MySql the field is set to NULL as default value. This is necessary to allow the proper functioning of the unique indexes on MySql (empty fields are considered duplicates while NULL fields are treated well).
In your sandbox I was unable to do this, as you can see from the attached image. Thanks in advance for your reply
Have you tried with a varchar field? I believe there is a problem with them. On the date or number fields the NULL value is entered correctly but on the character fields the value on MySql is empty.
It does work with VARCHAR but it is only editable from the back-end, it will not work when edited from the front-end. If you set the column not to be editable then the NULL value will be added when you enter a new row.
If "Editing" is enabled in the editing tab above the table then it will add an empty row.
I hope this helps, do let me know if you need any further assistance.
then the plugin arbitrarily inserts the empty value into a modified (or added) field in the frontend. however, this creates a serious problem with the MySQL logic for unique index pairs. I read in your changelog that this issue has been solved for dates and numbers, it would be necessary to solve it for characters as well.
Do you have any news if this is in your upgrade plans?
As a workaround, do you think it could use a plugin hook or trigger on mysql?
I spoke to our developers and they are planning to add this to one of our future updates but they are not able to give us an exact ETA.
You can use the hook below.
Please do let us know if there is anything else we can assist you with.
// This filter is applied to the form data before is save in database.
//
// $formData contains the keys and values that will be inserted or updated in the table
//
// $table_id is the table identifier from the MySQL table (wp_wpdatatables).
function updateValuesBeforeSave($formData,$tableID){
//Helper method that load columns config data from DB
// $columnsData is array of objects that contain data from column settings
$columnsData = \WDTConfigController::loadColumnsFromDB($tableID);
foreach ($columnsData as $column){
// check if column type is string
if ($column->column_type == 'string') {
//check if data from that string column is empty string
if($formData[$column->orig_header] == '') {
// set NULL value for string column that have empty data
$formData[$column->orig_header] = NULL;
}
}
}
return $formData;
}
add_filter('wpdatatables_filter_formdata_before_save','updateValuesBeforeSave', 10, 2);
As an alternative I had thought of a trigger but I think that, with your code, I could get the result by writing it once for all the tables. Can you confirm that, by not setting $ formData and $ tableID, the function will be applied to all the tables being edited?
Yes, for all tables that have a string column that is empty, when you submit the data through the editing module, they must be forwarded to the function as parameters, so you don't have to use them in the function itself.
I hope this helps, please don't hesitate to contact us if there is anything else we can assist you with.
Good morning,
I would need to know if, by making an insertion in a table, it is possible to automatically set empty fields to NULL.
Obviously assuming that in MySql the field is set to NULL as default value.
This is necessary to allow the proper functioning of the unique indexes on MySql (empty fields are considered duplicates while NULL fields are treated well).
In your sandbox I was unable to do this, as you can see from the attached image.
Thanks in advance for your reply
Hi Axel
Thank you for reaching out to us.
What you would like to achieve is already default behavior for manual tables. If you don't add any values to a cell it will display as an empty cell
but in the database it will be displayed like this
The same will happen if you delete a cell
I hope this helps, please do let us know if there is anything else we can assist you with.
Thanks Blaženka for the very quick reply.
Have you tried with a varchar field?
I believe there is a problem with them.
On the date or number fields the NULL value is entered correctly but on the character fields the value on MySql is empty.
Thanks again
Hi Axel
It does work with VARCHAR but it is only editable from the back-end, it will not work when edited from the front-end. If you set the column not to be editable then the NULL value will be added when you enter a new row.
If "Editing" is enabled in the editing tab above the table then it will add an empty row.
I hope this helps, do let me know if you need any further assistance.
Very clear Blaženka,
then the plugin arbitrarily inserts the empty value into a modified (or added) field in the frontend.
however, this creates a serious problem with the MySQL logic for unique index pairs.
I read in your changelog that this issue has been solved for dates and numbers, it would be necessary to solve it for characters as well.
Do you have any news if this is in your upgrade plans?
As a workaround, do you think it could use a plugin hook or trigger on mysql?
Thanks for your patience
a.
Hi Axel
I spoke to our developers and they are planning to add this to one of our future updates but they are not able to give us an exact ETA.
You can use the hook below.
Please do let us know if there is anything else we can assist you with.
// This filter is applied to the form data before is save in database. // // $formData contains the keys and values that will be inserted or updated in the table // // $table_id is the table identifier from the MySQL table (wp_wpdatatables). function updateValuesBeforeSave($formData,$tableID){ //Helper method that load columns config data from DB // $columnsData is array of objects that contain data from column settings $columnsData = \WDTConfigController::loadColumnsFromDB($tableID); foreach ($columnsData as $column){ // check if column type is string if ($column->column_type == 'string') { //check if data from that string column is empty string if($formData[$column->orig_header] == '') { // set NULL value for string column that have empty data $formData[$column->orig_header] = NULL; } } } return $formData; } add_filter('wpdatatables_filter_formdata_before_save','updateValuesBeforeSave', 10, 2);Thank you so much Blaženka,
As an alternative I had thought of a trigger but I think that, with your code, I could get the result by writing it once for all the tables.
Can you confirm that, by not setting $ formData and $ tableID, the function will be applied to all the tables being edited?
Thanks again for your effort.
a.
You are most welcome
Yes, for all tables that have a string column that is empty, when you submit the data through the editing module, they must be forwarded to the function as parameters, so you don't have to use them in the function itself.
I hope this helps, please don't hesitate to contact us if there is anything else we can assist you with.