FEATURE REQUEST field default values not currently set at DB level
I just realized that the default values for fields are not set at the database table level.
When a record is added via a form outside the Admin, the default values should be honoured automatically. This would be the case if the default value for the column was set at table create/alter.
I've passed this request on to our senior developer as something to look into adding in the future.
For now the best way to ensure that blank values are added to columns that you don't have data for is to use the mysql_insert function that comes with CMS Builder. Here is a example of how to use it:
$colsToValues = array(
'title' => 'Escape',
'content' => '<p>An escape is a car made by Renault.</p>',
'brand' => 'Renault'
mysql_insert('cars', $colsToValues, true);
Although this method won't insert the default values, it will enter empty content into the field, and no errors will be thrown if there isn't a default value for the field.
The first value in mysql_insert is the table name, the second is an array of columns and values (these will be automatically validated to be mysql safe), the final option is if you want blank values to be used if no value was provided for a field.
PHP Programmer - interactivetools.com
Here's some background on where we're at with that feature request, it's come up a few times:
I really like the idea of having the default fields set automatically at the DB layer, but some of the default values have some edge cases where that won't work such as when they are calculated (+5 days for a date field) or allow <?php ?> tags in the "Default Value" field, or are used for user instructions that are intended to be replaced on data entry: "Enter PO# Here".
We've considered building that functionality into our mysql_* convenience functions, but I'm a bit on the fence about that as I like them to be independent of cmsb structures,
Another approach we could have is to have a function that returned all the defaults for a particular schema. eg: $fieldsToValues = getSchemaDefaults($schemaName); or something like that.
We're in the middle of re-writing the field library to work like the plugins systems (and potentially be extensible allowing users to add field types as easily as they add plugins), so hopefully that will make it easier as right now the default values are all being calculated in /lib/menus/default/edit_functions.php as each field is displayed.
In any case, I'll put it on the feature request list and we'll can revisit it once we get back on the field library (or if you need something sooner you could hack together a getSchemaDefaults type function manually).
Hope that helps!
Understand the edge cases can't be done, but any others could and should be.
So if it is an edge case do what you do now, if a static value then set the default at the database level. OR maybe a checkbox for us to control what happens with static values.
MYSQL Default Rules:
If the column can take
NULL as a value, the column is defined with an explicit
DEFAULT NULL clause.
If the column cannot take
NULL as the value, MySQL defines the column with an explicit
DEFAULT clause, using the implicit default value for the column data type. Implicit defaults are defined as follows:
For numeric types, the default is
0, with the exception that for integer or floating-point types declared with the
AUTO_INCREMENTattribute, the default is the next value in the sequence.
For date and time types other than
TIMESTAMP, the default is the appropriate “zero” value for the type. For the first
TIMESTAMPcolumn in a table, the default value is the current date and time. See Section 11.3, “Date and Time Types”.
For string types other than
ENUM, the default value is the empty string. For
ENUM, the default is the first enumeration value.
TEXT columns cannot be assigned a default value.