A report column can be marked as editable (in-place editing) with the dbr.editable command. This command takes the column number and parameter references as parameters. When a user clicks on the editable column, an in-place editor is shown, allowing them to modify the value. The edited value, along with specified parameters, is then sent to the procedure defined in the command. This procedure can either perform the update operation or reject the update by returning the original value. If the editing procedure returns a value (single row, single column), myDBR will format the cell according to the defined cell formatting.

Sometimes, you may not want all rows in a column or columns in a crosstab to be editable. To prevent editing on selected cells, you can add the show_link parameter to the dbr.editable command. Alternatively, you can apply the 'no_edit' class to specific cells using the dbr.cellclass command.

(For information on editing multiple columns simultaneously, refer to Creating Editable Reports.)

With the options available in myDBR's dbr.editable command, you can:

In the example, we'll define the in-place editing to be available in the following cross-table report. We'll use the dbr.editable command to mark column 4 (Budget) to be updateable with the procedure sp_DBR_update_crosstable.

Let's illustrate in-place editing in a cross-table report. We'll configure column 4 (Budget) to be editable using the dbr.editable command, linking it to the stored procedure sp_DBR_update_crosstable.

Parameters for the command in the example are:

create procedure sp_DBR_InPlaceEditing
begin

select 'dbr.editable', 'Budget', 'sp_DBR_update_crosstable', 'inCategory=cat', 'inWeek=week';

select 'dbr.crosstab', 'Week';
select 'dbr.count', 'cat';
select 'dbr.sum', 'Total';
select 'dbr.hsum', 'Total';
select 'dbr.hidecolumns', 1;

select  category as 'Film category[cat]', 
  payment_week 'Week', 
  amount as 'Total', 
  budget as 'Budget', 
  category_id
from film_budget;

end

The report responsible for updating can either reject the update and revert to the original value or proceed with the new value. It receives all parameters specified in the dbr.editable command, along with an additional parameter for the edited value.

The last parameter of the update procedure is automatically filled with the edited field's value. You can customize the name of this parameter as needed (e.g., inValue in the example).

create procedure sp_DBR_update_crosstable(
inCategory int,
inWeek int,
inValue float
)
begin

/* Check the data */
if (inValue>0) then
  /* Accept the update, no need to return a value */
  update film_budget
  set budget = inValue
  where category_id=inCategory and payment_week=inWeek;
else 
  /* Reject the update, return the original value */
  select budget
  from film_budget
  where category_id=inCategory and payment_week=inWeek;
end if;

end

In this example, we'll enable in-place editing for the Budget column within the following cross-table report (under weeks).

When the editing procedure returns more than one column, the return values are sent back as a JSON string.

create procedure sp_DBR_edit_with_callback_JSON()
BEGIN

select 'dbr.javascript', '
function country_callback(val)
{
/* 
'this' is the edited cell
'val' is a JSON string as the editing report returns multiple columns
The JSON string can be parsed to a JSON object using JSON.parse
*/
var c = JSON.parse(val);
this.textContent = c.Name;
this.nextSibling.textContent = c.Code;
this.nextSibling.nextSibling.textContent = c.Continent;
}
';

select 'dbr.editable', 'Country', 'sp_DBR_country_edit', 'inPerson=Person', 'type=select_find', 'select=select Code, Name from demo_country', "options={'callback':country_callback}";

select p.id as 'PersonID', p.Person, c.Name as 'Country', c.Code as 'Country Code', c.Continent
from Persons p
 join Countries on c.Code=p.CountryCode;

end

The Country column is editable: users select a value from the dropdown list, and the corresponding country code is passed to the editing report.

drop procedure sp_DBR_country_edit
$$
create procedure sp_DBR_country_edit(
inPerson int,
inCode varchar(3)
)
begin

update Persons
set CountryCode=inCode
where id=inPerson;

/*
 Return two columns -> will be sent back as a JSON string
*/

select inCode as Code, Name, Continent
from Countries
where Code=inCode;

end

We will use different input types for the following data: a plain input, a textarea (autosizing), a select list, radio buttons, a datepicker, and a colorpicker. Dates are shown as-is from the database.

For the radio buttons and select list, the textual representation shown to the user will be fetched. myDBR will pass the ID to the editing report.

create procedure sp_DBR_update_inputtype()
begin
select 'dbr.pageview';

select 'dbr.javascript', '
function color_callback(settings, cell)
{
/* 
  Chosen (format #626262) value is in 'this'
  colorpicker.convert(this) will convert the color value into visible color
*/
colorpicker.convert(this);
}';

select 'dbr.editable', 'Basic', 'sp_DBR_input_edit', 'inId=id';
select 'dbr.editable', 'autosize', 'sp_DBR_text_edit', 'inId=id', 'type=autosize';
select 'dbr.editable', 'select', 'sp_DBR_varchar_edit', 'inId=id', 'type=select', "select=select id, value from char_values";
select 'dbr.editable', 'radio', 'sp_DBR_integer_edit', 'inId=id', 'type=radiobutton', "select=sp_radiovalues";
select 'dbr.editable', 'Date', 'sp_DBR_date_edit', 'inId=id', 'type=datepicker';
select 'dbr.editable', 'color', 'sp_DBR_color_edit', 'inId=id', 'type=colorpicker', "options={'callback':color_callback}";

select 'dbr.hidecolumn' 'id';

select  e.plain_column as 'Basic Input[Basic]',
        e.text_column as 'Textarea[autosize]',
        c.value as 'Select list[select]',
        i.value as 'Radio Button[radio]',
        e.date_column as 'Date', 
        e.color as 'Color[color]',  /* color format: #626262 */
        id
from mydb.editable e
  join int_values i on i.id = e.integer_column
  join char_values c on c.id = e.char_column;

end

With 'autocomplete'-editable type, one can search and autocomplete from a large amount of data as suggestions based on the user's input are fetched via Ajax. The user edits the visible value (for example, country name), and the editable passes the corresponding ID (for example, country code for a country) to the editing procedure. If one passes allow_new option to the call ("options={'allow_new': true}"), the user can enter a value other than those fetched via Ajax.

select 'dbr.hidecolumn', 'ID';
select 'dbr.editable', '[autoc]', 'sp_DBR_edit_country_ac', 'in_id=ID', 'type=autocomplete','select=sp_country_ac';

select e.id as 'ID', c.country as 'Country[autoc]'
from mydata e
 left join countries c on c.code=e.country_code
;

Where the editing report is:

create procedure sp_DBR_edit_country_ac(
in_id int,
in_value char(2)
)
begin

update mydata
set country_code = in_value
where id=in_id;

-- Return data for callback handling
select code, country, continent
from countries
where code = in_value;
end

The autocomplete Ajax query returns three columns: the visible value, an optional secondary row (leave empty if not shown), and the ID for the row. Limit the query to 20 rows (maximum shown at a time).

create procedure sp_country_ac(
in_search varchar(255)
)
begin

select country, concat('Capital: ', capital), code
from countries
where country like concat('%', in_search, '%')
limit 20;

end

Table 'int_values' and its associated 'sp_radiovalues' looks like this:

select * from int_values;

id   name
-----------
 1   'Asia'
 2   'Europe'
 3   'North America'
 4   'Africa'
 5   'Oceania'
 6   'South America'
 7   'Antartica'

create procedure sp_radiovalues()
begin

select id, name
from int_values;

end

Updating procedures receive two parameters: the supplied 'id' parameter from the first column of the result set, and the user-supplied value with the specific data type. The update procedures can include checks for the input, update the data, and optionally return a value that will replace the user input.

For the date column, we'll do a direct update

create procedure sp_DBR_date_edit (
inId int,
inUservalue date
)
begin

update mydb.editable
set date_column = inUservalue
where id=inId;

end

For the datetime, we can, for example, check if the date belongs to the current year and accept or reject the update based on that.

create procedure sp_DBR_datetime_edit (
inId int,
inUservalue datetime
)
begin

/* Does the user date belong to current year? */
if ( year(inUservalue) = year(now()) ) then
  update mydb.editable
  set datetime_column = inUservalue
  where id=inId;
else
  /* Do not update and return the original data back to report */
  select datetime_column
  from mydb.editable
  where id=inId;
end if;

end

myDBR uses the IDs of the user-selectable radio buttons. If this update were to be rejected and the ID converted, myDBR will automatically convert the ID back into a user-visible value.

create procedure sp_DBR_integer_edit (
inId int,
inUservalue int
)
begin

update mydb.editable
set integer_column = inUservalue
where id=inId;

end

Select works just like radio buttons, automating the conversion between IDs and user-visible values.

create procedure sp_DBR_varchar_edit (
inId int,
inUservalue varchar(5)
)
begin

update mydb.editable
set varchar_column = inUservalue
where id=inId;

end

When using checkbox-type, one places the IDs (numeric) and the user-visible values in separate columns. The ID column is specified with the 'checkbox_id' option. myDBR will return the selected values wrapped in a DIV with class editable_cb_item and separated by a DIV with class editable_cb_item_comma. This allows formatting the visible values either on the same line separated by a comma or on separate lines.

Checkbox-editing also has an option that allows using find/scroll/collapse options (similar to checkbox parameters). The html-option preserves the HTML in the cell. The find option allows searching for the choices, the scroll option shows the list as a scrollable list, and the collapse option (rarely used) allows collapsing the selection.

create procedure sp_DBR_editable_checkbox()
begin

SET @@group_concat_max_len = 100000; /* For MySQL group_concat_max_len has a default value of 1024, we'll prepare for more */ 

select 'dbr.editable', '[Categories]', 'sp_DBR_edit_film_category', 'in_film_id=film_id','checkbox_id=ids', 'type=checkbox', 'select=select category_id, name from category';


select fi.film_id, fi.title, ca.cat_ids as 'ids', ca.cat_names as 'Categories'
from film_samples fi
join (
  select f.film_id, group_concat(c.category_id) as cat_ids, group_concat(c.name) as cat_names
  from film_samples f
    left join film_category fc on fc.film_id=f.film_id
    left join category c on c.category_id=fc.category_id
  group by f.film_id
) ca on ca.film_id = fi.film_id;


select 'dbr.editable', '[Countries]', 'sp_DBR_edit_selected_demo_countries', 'in_id=id','checkbox_id=codes', 'type=checkbox', 
  'select=select c.Code, concat( c.flag , ' ',c.Name), c.Continent from country c order by c.Continent, c.Name',"options={'html':1, 'scroll':1, 'find':1}";

select 
  d.id, 
  group_concat(s.country_code) as 'codes,
  concat('dbr.html:',group_concat(concat('<div class="editable_cb_item">',c.flag, ' ',c.name,'</div>') SEPARATOR '<div class="editable_cb_item_comma">,</div>')) as Countries
from data d
  left join selected_demo_countries s on s.id=d.id
  left join country c on c.Code=s.country_code
group by d.id;


end

When stored procedures are used in dbr.editable select parameters, you can pass parameters to them using normal column references.

create procedure sp_DBR_update_inputtype_parameter()
begin

select 'dbr.pageview';

select 'dbr.editable', 'Select', 'sp_DBR_integer_edit', 'inId=id', 'type=select', "select=sp_select_with_params [id]";

select  id, 
        date_column as 'Date', 
        datetime_column as 'Datetime', 
        i.value as 'Radio Button[radio]',
        c.value as 'Select'
from mydb.editable e, int_values i, char_values c
where e.integer_column = i.id and e.char_column = c.id;

end

The file_rename editable type is designed for situations where there is a table in the database containing information about files that users can edit. This routine updates both the actual file name and its corresponding reference in the database.

The actual path to the file is now displayed to the user, with the routine passing the ID of the table containing the actual filename.

create procedure sp_DBR_editable_rename()
begin

select 'dbr.editable', 'file', 'sp_DBR_editable_rename_do', 'type=file_rename', 'in_id=id';

select id, substring_index(file, '/', -1) as 'file'
from editable_rename
where id=1;

end;

The actual path to the file is now displayed to the user. The routine passes the ID of the table containing the actual filename. The file rename editable procedure has two automatically filled parameters (the last two). The penultimate parameter takes values 'get' or 'set', and the last one represents the user-edited file name. The routine is initially called with the parameter 'get' to retrieve the full filename based on the ID. At this stage, the routine returns the full filename. After a successful rename, the routine is called a second time to update the file reference. The file renaming process will fail if: a) the file does not exist in the filesystem, b) a file with the new name already exists, or c) there are insufficient filesystem permissions for the rename action.

create procedure sp_DBR_editable_rename_do(
in_id int,
in_action varchar(10),
in_file varchar(255)
)
begin

if (in_action='get') then
  select file
  from editable_rename
  where id=in_id;
else
  update editable_rename
  set file = in_file
  where id=in_id;
end if;

end

Auto-updating the summary columns with the supplied autosum_int / autosum_float requires you to simply define the predefined callback function. After editing is completed, a new summary value is calculated.

Setting the onblur option to empty causes the 'OK' and 'Cancel' buttons to appear below the editable field.

select 'dbr.editable', 'value', 'sp_DBR_budget_edit', 'inAccount=account', "options={'onblur':'', 'callback':autosum_int}";
select 'dbr.sum', 2;

select account, value
from budget;

In this example, we have an editable column and another column that holds a calculated value based on other columns. When a user updates the editable column, we need to recalculate the calculated value and update the summary columns accordingly. For this purpose, we'll use myDBR's built-in functions col_value_get and col_value_set. These functions retrieve and set table cell values, respectively.

Additionally, the example includes a validation check: if the entered value is greater than 100, it prompts the user for confirmation. This functionality is implemented by setting a custom handler for the onsubmit event.

The col_value_get function follows this format: col_value_get(obj, col), where obj is the reference to the cell and col is the column class in the row if referencing a column other than the object's own.

The col_value_set function has the following format: col_value_set(obj, col, decimals, val, prefix, suffix). Here, obj is the reference to the cell, col is the column class in the row if referencing a column other than the object's own, val is the value to be inserted, and prefix and suffix are additional text attached to the cell value.

The autosum-functions have the following formats:

autosum_int( obj, column, prefix, suffix )

autosum_float( obj, column, decimals, prefix, suffix )

When the B column of the first row is updated, the following callback will calculate the cells marked in red.

select 'dbr.javascript', 'function mycallback()
{

// Value of A in edited row
var A = col_value_get( this, "Acol" );

// Value of B in edited cell
var B = col_value_get( this );

// Set the value of 4th A+B column
col_value_set( this, "ABcol", 0, A+B);

// Calculate sum of edited column
autosum_int(this);

// Calculate sum of A+B column
autosum_int(this,"ABcol");

}
function mysubmit(settings, cell)
{
  var edited_value;

  edited_value = Cell_data.get_value_raw( $(cell).find("input").val(), "int" );

  if ( (edited_value>100) && !confirm("Do you to set the value this high ("+$(cell).find("input").val()+")?")) {
    cell.reset();
    return false;
  }
  return true;
}

';

select 'dbr.sum', 'A', 'B', 'AB';
select 'dbr.editable', 'B', 'sp_DBR_edit_B', 'inKey=[ID]', "options={'callback':mycallback,'onsubmit':mysubmit}";

select 'dbr.colclass', 'A', 'Acol';
select 'dbr.colclass', 'A+B', 'ABcol';

select ID, A, B, A+B as 'A+B'
from mytable;

dbr.selectable converts ID columns of a result set into checkboxes, allowing users to mark rows for processing. The selected row IDs can then be used in a subsequent linked report, typically in conjunction with dbr.button. The result set displayed by dbr.selectable will be styled with the specified dbr.resultclass.

When using dbr.selectable, the selected row IDs can be referenced in the linked report using the syntax 'selectable.cssclass', where cssclass is the CSS class applied with dbr.resultclass. It's important to note that these selected IDs are provided by the user, so it's essential to validate these values in the linked report if necessary.

/* Convert the ID column to checkboxes */
select 'dbr.selectable', 'ID';
/* Give the result set a class which will be used later as parameter reference to the linked report */
select 'dbr.resultclass', 'films';

select  
  f.film_id as 'ID',
  f.title as 'Title',
  c.name as 'Category'
from films f 
  join category c on c.category_id = f.category_id;
  
select 'dbr.button', 'Set category';
select 'dbr.report', 'sp_DBR_film_category_set', 'dynamic_div[]', 'in_ids=selectable.films';

select 'dummy';

The IDs are passed in as a comma-separated string ("1,2,3,4"). At the end, we'll refresh the main report with dbr.refresh

create procedure sp_DBR_film_category_set(
in_ids text,
in_category_id int
)
begin

update films 
set category_id = in_category_id
where find_in_set(films.film_id, in_ids);

select 'dbr.refresh';

end

To refresh the list with selected values, you have multiple choices:

To use direct JavaScript to change the visible values

create procedure sp_DBR_film_category_set(
in_ids text,
in_category_id int
)
begin
declare v_category_name varchar(50);

update films 
set category_id = in_category_id
where find_in_set(films.film_id, in_ids);

-- Get the name
select name into v_category_name
from category
where category_id = in_category_id;

-- Escape the v_category_name if it can contain backslashes or '-characters, if not, you can skip this
set v_category_name = replace(v_category_name, "\\", "\\\\");
set v_category_name = replace(v_category_name, "'", "\\'");

select 'dbr.javascript', concat("selected.set_col_value('films', '",in_ids,"', 'category', '",v_category_name,"')");

end

The selected.set_col_value is a helper function which has a format of

selected.set_col_value(table_class, comma_separated_id_string, column_class, visible_value);.

It changes the value for column_class-columm for all rows whose ID matches with the supplied IDs and the table is identified by the table_class.

By enabling the 'Rich text' extension in the report parameters, you can utilize the 'richtext' editing type, which provides a comprehensive editor interface. This editor can be invoked either directly through HTML code or by using HTML code displayed with dbr.html:.

myDBR utilizes TinyMCE as the Rich Text editor. Options for TinyMCE can be specified using the 'richtext' option.

To display HTML code as rendered HTML, use dbr.html: in the query. For enabling rich text editing, set 'type=richtext' and use the option "'html':1" to indicate conversion of HTML into editable content.

select 'dbr.editable', 'rich_text',  'sp_DBR_edit_rich_text', 'in_id=id', 'type=richtext', "options={'html':1,'richtext':{'width': 600, 'height': 350}}";
select 'dbr.hidecolumn', 'id';
select 'dbr.colstyle', 'rich_text', '[white-space: pre;]';

select id, concat('dbr.html:', rich_text) as rich_text
from rich_text;

Alternatively, you can directly embed HTML code to invoke the editor. In this scenario, the HTML code is displayed as-is within the query, and no 'html' option needs to be passed.

select 'dbr.colstyle', 'rich_text', '[white-space:pre]';
select 'dbr.hidecolumn', 'id';
select 'dbr.editable', 'rich_text',  'sp_DBR_edit_rich_text', 'in_id=id', 'type=richtext', "options={'richtext':{'width': 600, 'height': 350}}";

select id, rich_text
from rich_text;

By enabling the 'Code editor' extension in the report parameters, you can utilize code editing for languages such as HTML, PHP, JavaScript, CSS, and SQL.

select 'dbr.hidecolumn', 'id';
select 'dbr.editable', 'code', 'sp_DBR_edit_code', 'inA=id', 'type=php';
select 'dbr.colstyle', 'code', '[white-space:pre;font-family:Courier]';

select id, code
from code_editing;

Using dbr.upload allows you to provide file upload capability to the server.

select 'dbr.upload.options', 'noreplace', 1;
select 'dbr.upload.options', 'accepted_files', 'jpg,png';
select 'dbr.upload.options', 'maxheight', 300;
select 'dbr.upload.options', 'maxwidth', 200;
select 'dbr.upload.options', 'quality', 0.6;

select 'dbr.upload', '/var/www/upload', 'sp_DBR_upload_file', v_client_id;

select 'dummy';

The upload procedure requires three fixed parameters: path, filename, and file size. Additional parameters can also be passed to the procedure. Upon successful upload, the procedure returns the text that is shown to the user.

create procedure sp_DBR_upload_file(
in_path varchar(255),
in_file  varchar(255),
in_size int,
in_id int,
inLogin varchar(128)
)
begin


insert into uploads 
values (inLogin, in_path, in_file, in_size, in_id, now());

select concat('File ', in_file, ' uploaded (',format_bytes(in_size),')');

end