Manual

Introduction

Latest release: 11.12 Elba (released November 30, 2023). This manual is about V. 11.12 Elba, if you need the documentation for a previous release please read the documentation file included in the package you downloaded; the features described in this file refers to DaDaBIK Platinum Version, some of these features might be not available for DaDaBIK Pro or Enterprise. Check the features comparison for further details.

Table of contents

1. What is DaDaBIK?
2. Requirements
3. License
4. Video tutorials
5. Installation & upgrade
6. The config.php and config_custom.php files
7. How to develop a DaDaBIK application
    7.1. Introduction
    7.2. How to configure a drop-down lookup field
    7.3. How to hide a field from a form
    7.4. How to manage uploaded file (images and other files) fields
    7.5. How to create a Master/Details view (subforms)
    7.6. How to create a pre-filtered results grid using VIEWS
    7.7. How to create graphic reports and pivot tables
    7.8. How to create PDF reports / documents
    7.9. How to share the same database between two or more DaDaBIK applications
8. Authentication
    8.1. Standard Authentication
    8.2. LDAP Authentication
    8.3. Wordpress Authentication
9. Permissions
10. How to add your custom code to DaDaBIK (low-coding)
    10.1. Calculated fields
    10.2. Custom validation functions
    10.3. Custom formatting functions
    10.4. Custom required functions / conditional fields
    10.5. Custom default value functions
    10.6. The custom startup function
    10.7. Custom buttons
    10.8. Hooks
    10.9. Row-level Custom Filters
    10.10. Custom pages
    10.11. Custom pages and dashboards
11. Import from MS Excel / CSV / ODS
12. DaDaBIK Wordpress Integration
13. HTTP API
14. Audit/Revisions
15. Live edit
16. Language translations
17. Performances
18. Security
19. Backup
20. Best Practices
21. FAQs
22. Known bugs, limitations and unexpected behaviors
23. Previous/Next buttons and page navigation bar, unexpected behavior

1. What is DaDaBIK?


DaDaBIK is a no-code / low-code development platform and a Web application builder written in PHP. It allows you to easily create a highly customizable database front-end or a database-based Web application without coding. You can use it to create in a few minutes a very basic CRUD (Create, Read, Update, Delete) database front-end or to develop more sophisticated database Web applications, for example Warehouse Management Applications, CRM applications, ERP Applications, HelpDesk Applications and others.

DaDaBIK was one of the very first RAD PHP tools focused on database applications (first version released in 2001) and it is still one of the most popular.

Features include (check the features comparison for further details):

  • Forms generation/customization (also according to rules: e.g. show field "State" only if country is "USA")
  • Datagrid generation/customization
  • Chart and Tabular reports generation:
    • simple reports, generated in a few clicks (choose chart type, how to aggregate data, what data to display )
    • advanced reports, using custom SQL code
  • 15 field types, including lookup fields
  • Calculated fields
  • Responsive front-end
  • File uploading
  • Master/detail views
  • Duplication checking
  • Authentication (also with Wordpress and LDAP)
  • Granular permissions
  • Integration with external user/group/password lists
  • Record locking
  • Record ownership authorizations
  • CSV export
  • PDF generation, also using custom templates
  • Email alerts
  • SQL logging
  • Revision/Audit
  • HTTP API for authentication and record selections
  • Pre-defined/custom formatting rules
  • Pre-defined/custom validation rules
  • Custom pages in PHP or simple HTML
  • Three graphic themes, two page layouts (left menu, top menu)
  • Graphic layout customizable through a documented CSS file
  • Front-end in 22 languages (English, Italian, Catalan, Chinese, Croatian, Czech, Danish, Dutch, Estonian, Finnish, French, German, Hungarian, Norwegian, Polish, Portuguese, Rumanian, Russian, Slovak, Spanish, Swedish, Telugu).

[back to top]

2. Requirements


  • Short answer:
    • DaDaBIK is a Web application written in PHP so you need a Web server (for example Apache), you need PHP and you need a DBMS (MySQL, MariaDB, PostgreSQL , SQLite or Microsoft SQL Server are supported). Since some of the PHP files are encrypted with IonCube, you also need a PHP extension, the Free IonCube loader.
    • If you want to use DaDaBIK on your local computer and you don't know what a Web server is or you don't know how to install it, you can download and install an application like MAMP, which automatically installs on your computer Apache, PHP and MySQL.
    • If you are building your Web application "in the cloud", your hosting provider has probably everything you need already set up. Please check, at least, the availability of the IonCube loaders: some hosting providers already have it installed, some others allow you to install it but some providers could prevent the installation of PHP extensions.

      The requirements, as you can see from the next section, are low; the vast majority of hosting services meet such requirements.
      We suggest, among the others, A2HOSTING, which offers a good shared hosting service at $6.99/mo (billed annually), 100% compatible with DaDaBIK (we tested it), with IonCube already installed, 30-days full refund money back guarantee and four options for the location of the server: USA (Michigan), USA (Arizona), Europe (Amsterdam) or Asia (Singapore).

      Disclosure: we may receive commissions from the A2Hosting sales made starting from the link above, however, it's a service we tested with DaDaBIK and we highly recommend!
  • Some more details:
    • PHP
      • PHP 7: any version >= 7.2 is supported. PHP 8: version 8.1 is supported (version 8.0 is not).
        • with the mbstring extension enabled if you need to handle Multibyte character
        • with LDAP support enabled if you need LDAP authentication
        • with the ext-xmlreader extension enabled if you need to import XLS/CSV/ODS files
    • The IonCube loader (version 10.1.x or higher is required if you use PHP 7, version 12 if you use PHP 8.1, version 13 for PHP 8.2).
    • MySQL or PostgreSQL or SQLite or Microsoft SQL Server :
      • MySQL version >= 5.0 (PDO_MYSQL PHP driver required, MariaDB is supported as well)
      • PostgreSQL version >= 8.2 (PDO_PGSQL PHP driver required)
      • SQLite version >= 3 (PDO_SQLITE PHP driver required)
      • MS SQL Server version >= 2012 (MS SQL Server on Azure is also OK!) (PDO_SQLSRV PHP driver required)
      • Since we often receive questions about this: yes, a database instance on Amazon AWS or Microsoft Azure is also fine
    • For MySQL, all the database tables must be InnoDB (this is the default MySQL engine) if you want to correctly handle transactions; DaDaBIK can also work with MyISAM tables but it cannot guarantee that transactions are processed correctly and some features won't work as expected.

      For MySQL, running MySQL with the NO_BACKSLASH_ESCAPES SQL mode enabled is not compatible with DaDaBIK (by default it is NOT enabled but you can check if it is by executing: SELECT @@SQL_MODE).

      To prevent SQL injection vulnerabilities you have to follow these rules:
      • for PostgreSQL, standard_conforming_strings must be ON (if you have a recent PostgreSQL, it should be ON by default)
      • for SQLite, the database must be in UTF8.

      UTF8 is the character encoding strongly recommended also for MySQL, PostgreSQL and MS SQL Server.

      In particular for MS SQL Server if you need to represent Unicode characters other than ASCII, your collation must be one of the collations ending with _utf8, introduced with SQL Server 2019 (and available on Azure as well). nchar, nvarchar and ntext fields are not supported by DaDaBIK: you can use them but DaDaBIK won't add the "N" prefix to the queries so you can't represent Unicode characters other than ASCII. Text fields are also not supported to represent Unicode characters other than ASCII (and they are deprecated in SQL Server anyway), you should use varchar(max) (+ _utf8 collation) instead.

    • DaDaBIK works with any modern browser: Firefox, Google Chrome, Safari, Edge
    • Your Web server must be connected to the Internet (for serial number validation).

    Not sure if your system matches the requirements? Most probably it does, but if you want to be sure before buying contact us and we will check it for you.

[back to top]

3. License


DaDaBIK is released under the DaDaBIK license and its use requires to purchase a regular license starting the purchase from the Website www.dadabik.com and completing it through the reseller Fastspring.com. After having purchased a license, you can find the text of the license in the file dadabik_license.txt. For some of the files that compose the DaDaBIK package, you get the full source code and you can modify it

[back to top]

4. Video tutorials


On our YouTube channel you can find a beginner video tutorial and some more advanced tutorials in the series "How to create a Web Application with DaDaBIK"


[back to top]

5. Installation & upgrade


Installation

  1. READ the requirements
  2. Copy all the files contained in the program_files folder in a directory of your choice under your Web server, e.g. "my_dadabik_directory"
  3. Open the file /include/config_custom.php with a plain text editor and set the value of the required parameters (see here for further details)
  4. Run the file http://yoursite.com/my_dadabik_directory/install.php to install DaDaBIK
  5. Have fun! :)

In this tutorial (installation chapter), you can see a step-by-step installation of DaDaBIK.

Upgrade from previous versions

New, official, upgrade procedure

You can easily upgrade to the latest version without losing your application's configuration if you are running at least version 5.0:

  1. Unzip your new DaDaBIK zip package, you will find, among the other, two files: up.php and up2.php
  2. Copy those two files in the main folder of your old DaDaBIK installation (the one you want to upgrade)
  3. run the file http://yoursite.com/my_dadabik_directory/up.php and follow the instructions.

After the execution of the upgrade procedure or in case you decide to cancel it, for security reason you have to delete the files up.php and up2.php from your DaDaBIK folder.

In case you have also changed the edition (e.g. from DaDaBIK Pro to DaDaBIK Platinum), after the execution of the upgrade procedure you must change the serial number in config_custom.php using the new serial number you got.

In case you have installed a prepackaged app (Dada HelpDesk, Dada sales) you can upgrade it using the same procedure, however only the DaDaBIK engine will be upgraded: if the app itself has been improved you won't see those improvements/fixes after a simple upgrade.

Here is a video where you can see the upgrade process in action:

The one just described is the new easy upgrade procedure; below we'll describe the old upgrade procedure, that is still available in case you prefer it.

Old upgrade procedure (in use before V 10.4 and still available if you prefer it)

You can upgrade to the latest version without losing your application's configuration if you are running at least version 5.0:

  • Replace all the old files with the new ones but:
    • keep your upload folder if you want to save the uploaded files, but BE SURE the folder is protected, see Security section
    • custom_functions.php and custom_functions.js (and, starting from DaDaBIK 10, all the other custom function files: custom_required_functions.php, custom_validation_functions.php, ....): IF you have edited the files you can keep your version; however, since the default custom_functions.php and custom_functions.js files may change when a new version comes out (for example, more documentation in form of comments can be available, and this may affect your functions), you should, instead, read the new custom_functions.php and custom_functions.js files and then copy your custom functions in the new files. Normally, if there are changes in the files that you should be aware of, this is also mentioned in the changelog file.
    • keep your custom language files if you have created any
    • keep your config_custom.php file (if any, the file is available only starting from DaDaBIK 9.3)
    • keep your styles_screen_custom.css file (if any, the file is available only starting from DaDaBIK 9.0)
    • keep your custom PDF template files
    • if you modified any of the other DaDaBIK files (including /templates/default_header_no_template.html and /templates/default_footer_no_template.html) you have to re-apply the modifications to the new files
  • Upgrade the Wordpress plugin in the Wordpress installations where you have used it (if any)
  • If you set your configuration parameters in config_custom.php and you kept config.php untouched (you can do that starting from DaDaBIK 9.3), skip to the next point; otherwise, if you edited config.php, update your new /include/config.php file using your old configuration settings; don't substitute the new file with the old one but MANUALLY edit the new file according to your old configuration.
  • Run the file http://your_host/your_dir/upgrade.php and follow the instructions to upgrade DaDaBIK
  • After having upgraded, it's a good security practice to change the parameter $dadabik_session_name in config.php or config_custom.php (and in the Wordpress DaDaBIK Wrapper if you use it) using a name you haven't used in the past; this allows to make invalid all the current sessions, that might have potentially dangerous content (think about a security fix) or content not compatible with the new version.

After the execution of the upgrade procedure or in case you decide to cancel it, for security reason you have to delete the files install.php, upgrade.php, upgrade2.php, up.php and up2.php from your DaDaBIK folder.

In case you have installed a prepackaged app (Dada HelpDesk, Dada sales) you can upgrade it using the same procedure, however only the DaDaBIK engine will be upgraded: if the app itself has been improved you won't see those improvements/fixes after a simple upgrade.

Please note that, due to SQLite limitations, the upgrade script (both new and old procedure) is available just for MySQL, PostgreSQL and MS SQL Server.

Upgrade from DaDaBIK PRO to DaDaBIK ENTERPRISE or PLATINUM (or any other edition's change)

You can upgrade from DaDaBIK PRO to ENTERPRISE or PLATINUM following the same instructions of the previous paragraph ("Upgrade from previous version"); this also applies if the version number is the same (e.g. you want to upgrade from DaDaBIK 11.12 Pro to DaDaBIK 11.12 ENTERPRISE).

[back to top]


6. The config.php and config_custom.php files


The folder /include contains two important files: config.php and config_custom.php. The config.php file contains some parameters that DaDaBIK requires before its installation and other, optional, configuration parameters.

The required parameters that you must specify are:

  • Your serial number: $serial_number
  • Some database connection parameters: $dbms_type, $host, $db_name, $user, $pass, $db_schema (the last one for PostgreSQL only)
  • Your Time Zone: $timezone
  • Some security parameters: $secret_key and $dadabik_session_name

Starting from version 10.4, you can no longer edit the config.php file directly, you must specify the value of all your config parameters (required and optionals) in config_custom.php. You can use the config.php file just as a (read-only) documentation file, where you can see all the parameters available and their possible values.

For example, let's say your timezone is "Europe/Rome", you are using MySQL on localhost, user "silvia" and password "98u$78jjh" and you want to install DaDaBIK in the database "test_db"; this should be (at least) the content of your config_custom.php file, before installing DaDaBIK:

<?php
$serial_number = '... your serial number here ...';
$dbms_type = 'mysql';
$host = 'localhost';
$db_name = 'test_db';
$user = 'silvia';
$pass = '98u$78jjh';
$secret_key = '... your secret key here ...';
$dadabik_session_name = '... your session name here ...';
$timezone = 'Europe/Rome';

If you are new to DaDaBIK, we recommend to also add these two additional parameters:

$default_install_enable_tables_on_creation = 'yes';
$grant_permissions_after_table_installation = 1;

... and also these two ones (they allow to create/alter/drop tables directly from DaDaBIK, so first read the security implications in config.php):

$enable_data_tab_operations = 1;
$enable_create_view = 1;

To summarize, the easiest thing you can do is:

  1. Copy the content from the three boxes above
  2. Open config_custom.php
  3. Replace all its content with the content you copied at point 1.
  4. Edit each of the 9 required parameters according to the instructions you have in config.php

Some additional notes or common mistakes:

  • NEVER change config.php, you should only edit config_custom.php
  • Use a plain text editor (a Word processor such as MS Word can cause problems)
  • Don't change the quote characters used to delimit the parameter values e.g. $host = 'localhost' IS FINE, $host = ‘localhost’ IS NOT. In the second example we replaced the straight single quotes with opening and closing quotes.
  • If you use a DBMS different than MySQL, the required parameters you have to specify may differ from the nine mentioned above, check the config.php file for details (for example PostgreSQL needs an additional parameter).
  • config.php contains ALL the configuration parameters you can set, together with their default value, but you don't need to specify all of them in config_custom.php. In config_custom.php you need the nine required parameters + any additional parameter whose value you want to change from the default. If you are new to DaDaBIK, probably you want to install it by setting only the nine required parameters (plus the four, additional, suggested parameters), then, in case you need it, you can add additional parameters later.
[back to top]

7. How to develop a DaDaBIK application

7.1. Introduction


After having installed DaDaBIK, you can set some very general configuration parameters from the file /include/config.php, just by opening it with a plain text editor and editing it. The file is self-explanatory. At that level you can, for example configure some DaDaBIK features (e.g. authentication, upload, email notices, language, ...).

For a more analytic tuning, however, you will use the admin area ("EDIT this app" button on the top right corner), where you can:

  • Define your data structures (tables and views)
  • Choose the pages that compose your applications; a page in DaDaBIK application can consist of:
    • a set of search/insert/edit forms, results grid and reporting tools related to a database table or a database view; DaDaBIK automatically creates this set for each table/view you have in your database, trying to guess the correct settings according to your schema but you can customize it (see "Configure your datagrids" and "Configure your forms" later)
    • a custom HTML page (you write your custom HTML code)
    • a custom PHP page (you write your custom PHP code) (DaDaBIK Enterprise/Platinum only)
    • a link to an internal or external URL
  • Configure your data grids, choosing between a classic tabular data grid view (default) or a custom results page completely customizable using HTML templates.
  • Configure your forms: this is the core of a DaDaBIK application configuration; here, for each field, you can set
    • if the field must be included or not in search/insert/update forms and data grids
    • its label
    • its content (e.g. numeric, alphabetic, e-mail, url...)
    • the HTML input type (e.g. textbox, dropdown menu...)
    • the possible values, also driven from another table ("foreign key" lookup support)
    • how the fields are arranged in each form
    • custom formatting and validation functions, calculated field functions
    • requiredness
    • search operators
    • and more...
  • Set the permissions (see the Permissions sections for more details)
  • Synchronize your applications if you change the database schema from outside DaDaBIK

The admin area is also self-explanatory, so you can read most of the instructions on how to configure DaDaBIK by clicking on the help tooltips help tooltip available in the admin area itself. That's the reason why we kept this manual short, you will find everything you need about configuration in the admin section. Here we will only focus on documenting a few typical use cases:

  • How to configure a drop-down lookup field
  • How to hide a field from a form
  • How to manage uploaded file (images and other files) fields
  • How to configure a master/details view
  • How to create a pre-filtered results grid
  • How to create graphic reports
  • How to create a completely custom page
  • What is a hook and how to add some PHP custom codes to the DaDaBIK's workflow
  • How to use calculated fields
  • How to share the same database between two or more DaDaBIK applications

See the related following paragraphs for further details.

If you are configuring a DaDaBIK application that is already publicly accessible, it's a good practice to set $maintenance_mode = 1 in config.php, in this way only administrator users can access the application.

Some graphic customization can be done by changing the files /include/header.php and /include/footer.php and by editing the CSS file /css/styles_screen.css. In addition, the datagrid HTML templates feature allows you to deeply customize the appearance of your item results page.

If you want to customize the sentences used by DaDaBIK you can create your own custom language file, see Language translations later

7.2. How to configure a drop-down lookup field


A lookup field is a field whose value is bound to another value in a source data table. For example let's say you have a table customers having, among the others, the fields address and city. Instead of setting the the field city as textbox, you want to provide the user a drop-down list which takes its values from a table cities having as fields ID and name. All you have to do is setting the following values (in the forms configurator), for the field city in the table customers:

  • Field type: select_single
  • Lookup table name: cities
  • Lookup table primary key field: ID
  • Lookup table linked fields: name

Configuring the field in this way, for the field customers.city you save in your database the ID of the city but you show to your users the name of the city. Also, you limit the options available to the cities you have in the table cities. The field customers.city needs to have the same database type the field cities.ID has, so if the ID is INTEGER, customers.city needs to be integer as well.

The select_single (drop-down list) is not the only DaDaBIK field compatible with the lookup approach: you can also use radio buttons, a list of checkboxes or a drop-down multiple select: all you have to do is selecting the right DaDaBIK field type: instead of select_single you need, respectively, select_single_radio, select_multiple_checkbox or select_multiple_menu.

It's worth mentioning that you are not limited to one linked field, you can add as many linked fields as you want. Let's say the table cities contains also the name of the region each city belongs to: cities (ID, name, region_name); you can add region_name as an additional linked field so that users will see in the drop-down menu not only the name of the city but also the name of the relative region.

If the user has insert permissions on the lookup table, a + button is displayed close to the dropdown menu, allowing the user to add a new option to the menu without leaving the current form. If you have, in the same form, more than one lookup field referring to the same table and you have used the + button to add an option to one of them, the other ones are not updated with the new option (they will be when you reload the form, though).

For additional options related to lookup fields, including cascade fields, filtered lookup and the possibility to add a new item to a list on the fly, please refer to the in-line helps available for each parameter in forms configurator.

Related videos:

7.3. How to hide a field from a form


Hiding a field from a form or from the results grid is very simple: let's say you have a field salary in a table employees and you want to show the field only to some of your users, the users belonging to the group human resources. From the permissions menu of the admin section, choose employees as a table and, for each group, set the permissions: you probably want to set, for all groups except from admin and human resources, the permissions to NO for all the operations: RESULTS, EDIT, CREATE, DETAILS, QUICK SEARCH and SEARCH; the effect is that DaDaBIK will never show the field, the field will be excluded from the results grid, from the edit form, from the "create new" form, from the details page, from the quick search form (the boxes on the top of the results grid) and from the search form.

Please note that normally if you have a field which is auto-increment, you need to hide it form the CREATE form since the value needs to be assigned automatically from your DBMS (MySQL, PostrgreSQL, SQLite, MS SQL Server) and not entered by the user.

The permissions menu is used to set the presence of fields, as explained above, only if granular permissions are enabled (parameter $enable_granular_permissions in config.php), which is the default settings. If granular permissions are disabled, you can choose to hide a field directly from the forms configurator, in this case you set the presence of a field globally, for all the users.

7.4. How to manage uploaded file (images and other files) fields


DaDaBIK has two file field types: generic_file and image_file; both the field types generate in insert/edit forms an upload control that allows users to upload files. The difference between the generic_file and the image_file lies in how the file is displayed in the results grid and in the details page: for image_file fields, the actual image is displayed (using the img html tag), for generic_file fields, a link to the file is displayed.

To use the upload function you have to set $enable_uploads = 1 and set the proper $upload_directory in config.php; the files uploaded, in fact, are not stored directly in the database but in the file system, the database stores just a reference to the file. From config.php you can also set the upload allowed file extensions and the size of the image thumbnails to show in results grid and details page. Please note that DaDaBIK automatically assigns a unique file name to the file the user uploads, that can be different to the original one. You should also consider that some particular characters (that have a special meaning) in a file name can make PHP misinterprets the file name, e.g. the filename "my\test.txt" will be seen as just "test.txt"

You should protect the upload directory from public access, read the security chapter of this documentation file for further information.

In this video you will see, step by step, how to manage files upload in DaDaBIK.



7.5. How to create a Master/Details view (subforms)


A Master/Details view (also called subform) is an edit form (or a details page) that shows not only the details of a records but also one or more result grids displaying related records from other tables.

A typical example is the representation of an invoice (or an order):

Invoice n. 1 date 08/04/2017

Customer Bob Smith

Description

Product            Quantity        Price

iPhone 7           1                    700

USB Key          2                    20

Total: 740

You probably represent the information using two tables:

invoices

id_invoice
number_invoice
customer_invoice
date_invoice
1 1 Bob Smith 08/04/2017

invoice_items

id_invoice_item
id_invoice
product_invoice_item
quantity_invoice_item
price_invoice_item
1
1
iPhone7
1
700
2 1 USB Key 2 20

(you probably want to store information related to products in a third table but let's keep the example simple)

The best way to represent this data in a DaDaBIK application is to create a master/details view with invoices and invoice_items: from the form configurator select id_invoice (the primary key of the invoices table) and add invoice_items as master/details, selecting id_invoice as foreign key:

You can add as many details tables as you want to the same master.

Related video, master/details chapter :



7.6. How to create a pre-filtered results grid using VIEWS


Sometimes you want to show to your users a pre-filtered version of your data. Let's say for example you have a table products containing a field quantity_product, which stores, for each product, the quantity you have in your warehouse. If the table products is installed in DaDaBIK, it can be accessed from a menu item products and users can also execute search operations over it, for example, through the search form, they can ask to see only the products having a quantity value less than 10. If you realize that such query is quite common, you might want to offer to your users a direct access to the products having a quantity value less than 10, without forcing them to perform a search operation every time. You can do this using VIEWS.

A database view, informally, can be defined as a "virtual table" which is based on a real table; for example we can create a view products_low_stock based on the table products with this simple SQL statement:

CREATE VIEW products_low_stock AS SELECT * FROM products where quantity_product < 10

The view products_low_stock can be installed in DaDaBIK as it was a normal table and therefore you can assign to it a menu item that allows users to directly access the products with low stock level in one click, without performing a search operation. Every time you access the view products_low_stock, your DBMS implicitly execute the query "SELECT * FROM products where quantity_product < 10" and provides you with the up-to-date results.

You can create a database view directly in DaDaBIK, admin section, data tab:

create view screenshot

For additional information about databaseviews, the Wikipedia entry is simple and quite informative: https://en.wikipedia.org/wiki/View_(SQL). A creative use of database views can help you in the process of creating very complex DaDaBIK applications.

In this video chapter, you will learn, step by step, how to use VIEWS in DaDaBIK.


7.7. How to create graphic reports and pivot tables

(DaDaBIK Enterprise/Platinum only)


Starting from the results grid of any page based on tables/views you can create a graphic report; several chart types are available, including bar charts, line charts, pie charts and doughnut charts.

DaDaBIK provides two types of reports: simple reports and advanced SQL reports. You can create a simple report in a couple of clicks by selecting the field you want to put on your X-axis and the field you want to put on your Y-axis. Normally you want to aggregate data according to a field, show each group of data on your X-axis and and show some information related to each group on your Y-axis.

For example you might want to see the age composition of your customers: in this case you would choose to aggregate data by age and to show, for each group, the number of customers having that age (selecting the count option).

A slightly more complicated example: you have a table sales containing, among the other information, the date of the order and the amount. You want to see your revenues, month by month. In this case you need to choose month month - date_order for the X-axis and sum - amount for the Y-axis.

If you need more freedom, you can use the advanced SQL reports: you can specify your own SQL query, include a where clause, joining several tables and so on. The only constraints you have is: the query must be a SELECT statement where  the first element you select will be used for the X-axis of the graph, the second element for the Y-axis.

Here is an example: let's say we have the following tables:

sales (id_sale, date_sale, customer_sale) - each sale can have several sale items:

sale_items (id_sale_item, id_sale (FK), id_product FK), quantity_sale_item) - each sale item refers a sale and a product

products (id_product, desc_product, price_product)

we want to show, for each month, the total revenues; here is an example of custom SQL report for MySQL:

select date_format(date_sale, "%b"), round(sum(quantity_sale_item*price_product)) from sales inner join sale_items on sales.id_sale = sale_items.id_sale inner join products on sale_items.id_product = products.id_product group by month(date_sale)

If you have created a report and you want to easy access it later from the menu in one click, you can simply copy the URL you see in your browser when the report is displayed and create a custom page (having type = link) that points to this URL. Please note that, if the report has been generated after a search, the search filter is not saved together with the link. If you need to store a report based on a stable search filter, the best way is to create a VIEW and generate the report starting from that view.

What has been explained for graphic reports is also valid for pivot tables: starting from the results grid of any page based on tables/views you can create a pivot table where you can represent, in a tabular fashion, your data, aggregated according to a criteria.

7.8. How to create PDF reports / documents

(DaDaBIK Enterprise/Platinum only)


Introduction

Starting from the details page or from the results grid of any page based on tables/views you can create a a PDF report/document by clicking  the PDF button. If you are in a details page, DaDaBIK will create a PDF document containing the information related to the record you are viewing; if you are in the results grid, DaDaBIK will create a PDF document containing the information related to all the records included in the current results set.

Please note that, in order to use this feature, the parameter $export_to_pdf_feature needs to be set to 1 in config.php (by default, it is set to 1); furthermore, the parameter $pdf_max_number_records allows you to control the maximum  number of records you want to export: since the PDF export is a resource-consuming activity, if you have a table with a huge number of records you might want to limit the export to a specific number of records: users could launch the export on the entire table (without filtering it) and cause a system freeze.

If you launch the PDF generation without selecting a template (or selecting "No PDF template") from the template dropdown menu, DaDaBIK will apply a standard template: for each record, a row containing the field label (in bold) and the field value will be displayed for each field you have in your details page or results grid.

Your custom PDF template

You can, however, create your own template, choosing the position of the fields, adding graphic elements, text or any other content you might want to add to your PDF.

In DaDaBIK, the generation of a PDF document is handled by the well known TCPDF library; in particular by using the writeHTML method (see the documentation of the TCPDF class for additional details on the method), that converts an HTML page to a PDF document trying to preserve the graphic layout.

If you want to create a custom template, you have to create a file having extension .html and save it in the /templates folder: the file is just a standard HTML page that reproduces, in HTML, the PDF document you would like to get. A DaDaBIK template has two peculiarities respect to a normal HTML page:

  • it always starts with the line <!-- DaDaBIK Template -->
  • for each of the field you want to include in the template, you can add the value of the field by using a placeholder, it is something like:
    dadabik_field field_name dadabik_field

For example, let's say we have an IT Help Requests application where users can add their help requests and we want to provide a printed version of each request, which includes our logo, some of the fields displayed in a tabular fashion and a signature request for the manager: here is a very simple template that could satisfy our needs:

<!-- DaDaBIK Template -->
<html lang="en">
<head>
    <meta charset="utf-8" />
</head>

<body>
<img src="images/logo.png">

<h1>IT Help Request Form</h1>

<table>
<tr><td>ID</td><td>dadabik_field id_request dadabik_field</td></tr>
<tr><td>Subject</td><td>dadabik_field subject_request dadabik_field</td></tr>
<tr><td>Details</td><td>dadabik_field details_request dadabik_field</td></tr>
<tr><td>Picture</td><td>dadabik_field picture_request dadabik_field</td></tr>
<tr><td>Assigned to</td><td>dadabik_field it_staff_member_request dadabik_field</td></tr>
<tr><td>Date time</td><td>dadabik_field date_time_request dadabik_field</td></tr>
<tr><td>I need this by </td><td>dadabik_field expected_solution_date_request dadabik_field</td></tr>
<tr><td>Created by </td><td>dadabik_field created_by_request dadabik_field</td></tr>
<tr><td>Status </td><td>dadabik_field status_request dadabik_field</td></tr>
</table>

<br><br><br><br><br>
<div align="right">This form must be signed by the IT Manager for approval</div>

<br><br>
<div align="right">Manager's Signature</div>
<div align="right">____________________</div>

</body>
</html>

A template can be much more sophisticated and it can include (internal) CSS as well. While the final result is not guaranteed (some HTML tags or CSS attributes might be not supported by TCPDF), the TCPDF library tries to preserve your HTML layout as much as possible and does a very good job.

Once you have created your template and saved in the /templates folder, the template name (the part of the filename before .html) will appear as an option to choose in the template dropdown menu.

Please note that, if you are producing a PDF document starting from a master / details view, only the master part of the view is converted in PDF.

If one of the fields you use in your template has content type: HTML or field type: rich editor, you can experience unpredictable results in the PDF produced. TCPDF might in fact misunderstand some characters, such as < or >, considering them as part of a tag; for all the other field types/contents DaDaBIK converts those characters in the corresponding HTML entities so you won't experience such problem.

"Mail Merge" feature

The export to PDF feature applied to a record results set, in combination with the use of custom templates, guarantees results similar to the ones you can get using a typical "Mail Merge" feature available in Word Processors. Let's say for example that you want to send a (paper) mail to all your customers who live in a particular city: you can do it by searching the customers by city and export as PDF the results set, using a template that contains the letter and a name placeholder that will be automatically filled with the name of each of the customer.

Advanced PDF customization

There are still a few things you can do to customize your PDF reports / documents.

Templates in PHP

Instead of using just HTML, you can also use some PHP in your templates. Adding PHP code to your templates opens to many new possibilities, for example part of your template can change according to some conditions (e.g. according to the user currently logged in).

Here is an example of template with some PHP code. The first piece of code just adds the current date to the PDF, the second one adds a whole part of the template ONLY if the current logged user is part of the group having ID 2.

<!-- DaDaBIK Template -->
<html lang="en">
<head>
    <meta charset="utf-8" />
</head>

<body>
<img src="images/logo.png">

<h1>IT Help Request Form</h1>

<?php echo 'Date: '.date('Y-m-d'); ?><br>
<table>
<tr><td>ID</td><td>dadabik_field id_request dadabik_field</td></tr>
<tr><td>Subject</td><td>dadabik_field subject_request dadabik_field</td></tr>
<tr><td>Details</td><td>dadabik_field details_request dadabik_field</td></tr>
<tr><td>Picture</td><td>dadabik_field picture_request dadabik_field</td></tr>
<tr><td>Assigned to</td><td>dadabik_field it_staff_member_request dadabik_field</td></tr>
<tr><td>Date time</td><td>dadabik_field date_time_request dadabik_field</td></tr>
<tr><td>I need this by </td><td>dadabik_field expected_solution_date_request dadabik_field</td></tr>
<tr><td>Created by </td><td>dadabik_field created_by_request dadabik_field</td></tr>

<?php if ($current_id_group == 2){ ?>
<tr><td>Status </td><td>dadabik_field status_request dadabik_field</td></tr>
<?php } ?>

</table>

<br><br><br><br><br>
<div align="right">This form must be signed by the IT Manager for approval</div>

<br><br>
<div align="right">Manager's Signature</div>
<div align="right">____________________</div>

</body>
</html>

If you need PHP templates, remember to save your template file with a .php extension. If DaDaBIK finds in the templates folder two templates having the same name and different extensions (one with .html extension, the other with .php extension), the HTML template will be used.

If you need to display content without using dadabik_field pairs (e.g. you want to display the content of a $myvar variable) and you don't want your content to be interpreted as HTML (probably, in most of the cases, you don't), you must pass your content through htmlspecialchars (e.g. echo htmlspecialchars($myvar)), otherwise you could have unexpected results: if for example you have a < sign in your content, TCPDF might interpret it as HTML and silently stop the rendering of the page because of a rendering error, giving you the false impression that, for example, some fields are not available or the records related to a search are less than they actually are.

Customize the default template

As we mentioned earlier, if you don't choose a template from the dropdown menu, DaDaBIK will use a default standard template; you can change the default header and footer of that template by editing the files default_header_no_template.html and default_footer_no_template.html (/templates folder): this allows, for example, to modify the CSS used by the default template.

Additional customization, the _settings.php template

As we mention earlier, for each custom template you have to create a TemplateName.html (or TemplateName.php) file in the /templates folder. In addition, you can create an (optional) TemplateName_settings.php file that might contain the following items:

  • the set_standard_pdf_parameters_custom() function
  • the pdf_custom_code_after_addpage() function
  • the MYPDF class, that extends the TCPDF class
  • template-specific settings for PDF parameters you have in config.php

The set_standard_pdf_parameters_custom() function overrides the set_standard_pdf_parameters() function, that DaDaBIK calls after the creation of a TCPDF object; in such function, some general characteristics of the document are set, for example margins and font (see set_standard_pdf_parameters() in /include/pdf_functions.php for details).

The pdf_custom_code_after_addpage() function is an optional function executed by DaDaBIK after the execution of the AddPage() method of the TCPDF class. In the following example the function adds a custom logo in the top left corner (coordinates 10,10), after the execution of AddPage():

function pdf_custom_code_after_addpage($pdf){
    $pdf->Image('images/mylogo.png', 10, 10);
}

The MYPDF class allows to customize even more a template, overriding some of the methods of the TCPDF class, for example the Header() and Footer() methods. In the following example the class overrides the Footer method, displaying - for the footer of every PDF page produced - a custom text in a 40x10 cell:

class MYPDF extends TCPDF {

    public function Footer() {

        ... your additional code here ...

        $this->Cell(40, 10, 'My footer text ');

        ... your additional code here ...

    }
}


Finally you can set here the config.php PDF parameters when you need to override the corresponding config.php value; for example let's say that you normally need portrait orientation, so you have $pdf_page_orientation = 'P' in config.php; for one or more templates, however, you might need landscape orientation, in this case you can simply set $pdf_page_orientation = 'L'; in the related _settings.php template file.

You can even override $export_to_pdf_feature, for example because you want to enable the PDF export ONLY for one or some tables/views: in this case you can keep $export_to_pdf_feature = 0 in config.php and set $export_to_pdf_feature = 1 in your _settings.php template files. DaDaBIK won't create the standard "produce PDF" button if $export_to_pdf_feature = 0 but you can easily create a custom "CREATE PDF" button for the tables/views you need, a button that just redirects to something like: index.php?tablename=YOURTABLE&function=search&export_to_pdf=1&pdf_template=YOURTEMPLATE
Please note that to make this work you also need to set $parse_pdf_php_template_even_if_export_pdf_disable = 1 in config.php, otherwise DaDaBIK won't read your _settings.php template file.

The name of the PDF file produced is also something you can set in this file, using the variable $pdf_file_name e.g.:

$pdf_file_name = 'customers';

If you don't specify a name, the default one (the name of the corresponding table) will be used.

Produce a PDF with tabular data

You might need to produce a PDF representing some tabular data, for example a list of products, where each product is represented as a row of the table. If you try to create a custom HTML template for this, you will fail: you can create the products.html template for the row, something like :


<!-- DaDaBIK Template -->
<tr><td>dadabik_field name_product dadabik_field</td><td>dadabik_field price_product dadabik_field</td></tr>

but you miss the starting and closing tag of the table. In these cases, when you need a template for each record and two additional templates that work as header and footer, you need to create three HTML templates; if you have a products.html template, you will also need a products_header.html and a products_footer.html template.

Here is the content of these two additional templates, product_header.html

<!-- DaDaBIK Template -->
<html lang="en">
<head>
    <meta charset="utf-8" />
</head>

<body>

<h1>Products list</h1>

<table>
<thead>
<tr><th>Name product</th><th>Price product</th></tr>
</thead>
<tbody>

and products_footer.html

<!-- DaDaBIK Template -->
</tbody>
</table>
</body>
</html> 

If DaDaBIK sees that, for a custom template, the correspondent _header and _footer template files exist, it will use them.

In order for this to work, however, you also need to set $add_pdf_page_for_each_record = 0 in config.php. Why? Because by default, DaDaBIK create a new PDF page for each record, but this is not what you want if you need to produce tabular data. What if you need this behaviour only for some templates? You can just leave $add_pdf_page_for_each_record = 1 in config.php and set $add_pdf_page_for_each_record = 0 in the specific _settings.php template file where you need it, in this case in the products_settings.php template file.

PDF and UTF-8 characters

In case you experience problems with some UTF-8 characters which are not displayed correctly in PDF, you can fix the problems by using a font that is able to display all the UTF-8 characters (the core TCPDF font used by default is not), for example freesans. You can find some instructions here: https://dadabik.com/forum/read.php?1,19987

Additional information about PDF generation

For additional information about TCPDF, please refer to its documentation.

7.9. How to share the same database between two or more DaDaBIK applications


Sometimes you want to build two (or more) different DaDaBIK applications which work on the same data (i.e. on the same database).

For example you want to build:

  1. A backend/data-entry application, from which you modify/enter data, accessible only to some people and
  2. A simpler, read-only, publicly available application that users can access without logging in.

You can do it very easily following these simple rules:

  • the database name (parameter $db_name) must be the same in all the config.php files
  • the $prefix_internal_table parameter needs to be different and UNIQUE for each application
  • use the $prefixes_to_exclude parameter to state, for each application, the prefixes (see previous rule) you have used in the other applications sharing the same database.

For example, if we have two applications sharing the same database, here is an example of config.php files

config.php application A

$db_name = 'my_database';
$prefix_internal_table = 'dadabik_1_';
$prefixes_to_exclude[0]='dadabik_2_';

config.php application B

$db_name = 'my_database';
$prefix_internal_table = 'dadabik_2_';
$prefixes_to_exclude[0]='dadabik_1_';

If you just want to make part of your application public, you should use the parameter $username_public_user (see the Authentication chapter for further details).

8. Authentication


8.1 Standard Authentication

Authentication is enabled by default, which means that, in order to use an application created with DaDaBIK, you have to login. You can disable authentication by setting $enable_authentication = 0 in /include/config.php; while this is not recommended, it can be useful if you want to use DaDaBIK to create a public Web site.

DaDaBIK is released with the following default users:

  • username: root; password: letizia (this is a user belonging to the admin group)
  • username: alfonso; password: letizia (this is a user belonging to the normal group)
It is strongly recommended to change the default passwords for security reasons.

Admin users can not only use a DaDaBIK application but also configure it through the admin area; furthermore, they can add new users/groups and edit/delete existing ones.

If you want make part of your DaDaBIK application public and part private, for example you want to allow unathenticated users to read a public dataset or you want to open to the public a registration form or a contact form, you can still set $enable_authentication = 1 and then define who is the "public" user in config.php (parameter $username_public_user): unathenticated users will be granted with the permissions you assign to the group the public user belongs to.

[back to top]

8.2 LDAP Authentication

(DaDaBIK Enterprise/Platinum only)


Optionally, you can authenticate your users against an LDAP server. DaDaBIK has been tested with both Open LDAP and Microsoft Active Directory.

From /include/config.php you have to enable $ldap_authentication and specify all the parameters required for the connection. Users who authenticate using LDAP are then copied into the DaDaBIK users table and get assigned to a default (configurable) group. The aim of the copy is to allow the permissions setting, LDAP users authentication is always performed through the LDAP server.

Mixed authentication (some users authenticate against DaDaBIK, others against an LDAP server) is also possible: during the log-in procedure, a user can check/uncheck the LDAP checkbox in order to specify if he is going to authenticate through LDAP or not.

[back to top]

8.3 Wordpress Authentication


If DaDaBIK is integrated into a Wordpress site, users authenticated through Wordpress can also be (optionally) automatically authenticated into DaDaBIK too, without doing the log-in again. See DaDaBIK Wordpress Integration for further details.

[back to top]

9. Permissions


Basic permissions

For each table (each form and field), through the permissions manager (EDIT this App -> Permissions), you can set which operations (Read, Create, Edit, Delete and Details) are allowed.

If you disable the read permission for a table, the related item won't be displayed in the menu of the application. Remember, however, that users can still read its records if the table is used as source of a select_single/select_multiple field or as items table in a master/details view.

Granular permissions:

If $enable_granular_permissions is set to 1 (this is the default settings) in /include/config.php you can set, through the permissions manager, which operations (Read, Create, Edit, Delete and Details) are allowed for each users group on each form and field.

For example you can configure your application to allow the group "Managers" to read and modify all the forms while the group "Employees" to read all the forms and modify just some of them, or just some fields of them.

As we said for the basic permissions, if you disable the read permission for a table, the related item won't be displayed in the menu of the application. Remember, however, that users can still read its records if the table is used as source of a select_single/select_multiple field or as items table in a master/details view.

Permissions Templates

(DaDaBIK Enterprise/Platinum only)

As explained in the previous chapter, through the permissions manager you can set your granular permissions for each group / table / field combination. Especially if you are dealing with a complex application having many user groups, setting the permissions manually can be time consuming; if this is the case, you can save time setting Permissions Templates in your config_custom file.

More specifically, you can add a set of permissions you want to grant, automatically, when you add a new table or a new field to your application.

Let's see an example, let's say you have three groups: admin, managers, employees (IDs 1,2,3) and when you install a new table or add a new field you want to assign all the permissions to the admin group, all the permissions except delete to the managers group and only read permissions to employees group.
You can define a template for that, using a sequence of 6 numbers representing the table permissions (READ DELETE EDIT CREATE DETAILS CSV) and a sequence of 7 numbers representing the field permissions (RESULTS EDIT CREATE DETAILS QUICK-SEARCH ADVANCED-SEARCH CSV), where, for each number:

0 means NO
1 means YES
2 means MY (only valid for read, delete, edit, csv permissions on tables)
3 means YES, but disabled (only valid for edit permissions on fields)

Following the example above:

// group 1
$permissions_template['table_default'][1] = '111111'; // all the table permissions set to "1", means YES
$permissions_template['field_default'][1] = '1111111'; // all the field permissions set to "1", means YES

// group 2
$permissions_template['table_default'][2] = '101111'; // all the table permissions set to "1" except from the second (delete)
$permissions_template['field_default'][2] = '1111111'; // all the field permissions set to "1"

// group 3
$permissions_template['table_default'][3] = '100010'; // only read and details table permissions
$permissions_template['field_default'][3] = '1001010'; // only read, details and advanced search field permissions

Every time you add a new table or a new filed, these rules will be used by DaDaBIK to automatically set (granular) permissions.

You can also set specific templates for specific tables, overriding the general rules if there is an overlap, e.g. let's say you want to follow a different rule only for the field you will add to the table "customers" and only for group 2 the edit must be disabled; this is the template rule you should add:

$permissions_template['field']['customers'][2] = '1011111'; // all the field permissions set to "1" except from edit

Also the table permissions can be table specific, so for example:

$permissions_template['table']['products'][1] = '100000';


will assign, to the group 1, only read permissions for the table products, if and when a table products will be installed.

Permissions templates are integrated and complementar to the config parameter $grant_permissions_after_table_installation. If this parameter is set to 1 (the default is 0), all the permissions are set to YES for all the groups when you install a table (and, only starting from V 11.6, also when you add a field); if it is set to 2, all the permissions are set to YES, but only for the admin group.

If you define permissions templates, the rules in your templates overrides the behaviour prescribed by $grant_permissions_after_table_installation but if, for one or more tables/groups, you didn't set any rule, then for those tables/groups $grant_permissions_after_table_installation will be used.

Finally, the config parameter $grant_permissions_autoincrement_after_table_installation (see config.php for details) is also considered: if it is set to 0 (default is 1) and you are using MySQL (at the moment this parameter only works with MySQL) the permissions for an auto-increment field will be set to NO, regardless of your rules.

Integration with existing user and group lists

(DaDaBIK Enterprise/Platinum only)

DaDaBIK uses by default the dadabik_users and dadabik_groups tables to store users information, but you can also use existing user and group tables. This help the integration of DaDaBIK with existing applications.

To use your own users and groups tables, you have to configure the config parameters whose name starts with $users_table_ and $groups_table_; the integration is also possible if your users table doesn't use the same password encryption that DaDaBIK uses, in this case you have to customize the hashing functions as explained in /include/config.php

If you set your own users and groups tables and tell DaDaBIK the names of the fields to use for authentication (username, password, ... ), DaDaBIK will read users and groups from those tables when it needs it (authentication, permissions setting, ...); however you shouldn't manage (add, edit) users and group FROM DaDaBIK: there are additional fields, not useful for authentication/permissions (e.g. first name, last name, ...) that are available in the DaDaBIK standard users table and unless your custom tables don't have exactly the same fields, if you add/edit users from DaDaBIK you can generate errors and unexpected behaviour.

Owner permissions:

(DaDaBIK Enterprise/Platinum only)

An owner permissions model is also available, it allows to set-up the application in a way such as users can:

  • Delete only their own records
  • Modify only their own records
  • View only their own records
The owner of a record is the user who inserted it.

You can enable this feature from the permissions manager; to enable it, authentication must be enabled and each table you want to protect must have a field whose DaDaBIK field type is set to ID_user. When a field is set to ID_user, the username of the current user will be automatically inserted by DaDaBIK into this field when you insert a new record. If you want to base the ownership on the group instead of the user, change $use_id_group_for_ownership in /include/config.php

Please note that you can enable the owner permissions also for VIEWS, but also for VIEWS (as for TABLES) you need a working ID_user field, VIEWS don't automatically inherit form settings from the underlying tables. In some cases, however, you cannot directly insert records into a view (the DBMS won't allow you to do it, to read some more details related to MySQL see https://dev.mysql.com/doc/refman/5.7/en/view-updatability.html) so the correct ID_user value must come from the underlying tables.

Permissions based on multiple groups

(DaDaBIK Enterprise/Platinum only)

If granular permissions are enabled (by default they are), for each user group you can set table, fields and custom pages permissions. The permissions of each user, therefore, are based on the permissions you granted to the group the user belongs to. Until V 11.6, a user could belong to one group only. Starting from V 11.7, each user has a required (main) group and one or more optional groups they belong as well. If $enable_multiple_groups_permissions is set to 1 (by default it is), the user permissions are computed taking into consideration all the groups the user belongs to (main + others) and, in case they are different, using a "most permissive" strategy.

This means that if group "Managers" has read and delete permissions on table customers and group "Directors" has read and edit permissions on table customers, the user "Alice" belonging to both "Directors" and "Mangers" will have Read, Delete and Edit permissions on table customers. The same for fields and pages permissions. For example if group Directors has create permissions on field customers.firstname but not on customers.lastname and, viceversa, group Managers has create permissions on field customers.lasetname but not on customers.firstname, Alice will have create permissions on both fields. You can check the resulting permissions for each user in the new Permissions -> Show user Permissions page.

It's important to point out that the resulting field permissions don't take into consideration table permissions. For example let's say that group "Managers" has edit (table-level) permissions on table customers and group "Directors" has not. Field-level, Managers has edit permissions on fields firstname and lastname but not on field phone_number, while Directors has edit permission only on phone_number (even if it hasn't edit permission on table level, which is an odd situation but it is possible). If Alice belongs to Directors group only, she won't get edit permissions on customers, however if she belongs to Managers group as well, she will get edit permissions on table customers, on firstname, lastname AND phone_number as well.

For the admin group (by default the group having id_group 1) you can assign table/field/pages permission as for the other groups but in addition the members can also edit the application and read/insert/edit/delete other users/groups. A user gets these additional permissions only if their main group is the admin group, if you add the admin group as additional group, only table, field and pages permissions will be inherited.

[back to top]

10. How to add your custom code to DaDaBIK (low-coding)


Almost all the features we have seen so far don't require any coding (no-code), and in fact you can build an entire Web application without any coding. However in some cases you might want to enrich your application by adding your own PHP/HTML/Javascript code. That's what we call low-code: you don't have to code your application from scratch, you can build most of the application without coding, using the form configurator, the permissions manager and the other tools provided by DaDaBIK and then you add your code to implement some peculiar aspect of your application.

In DaDaBIK you can inject your code in several different ways, for example through custom pages, custom buttons, hooks, calculated fields, custom validation functions and others. In the following chapters we'll cover each of them. Here, we'll see just some general guidelines you should use when you add custom PHP code. If you are new to DaDaBIK, it's probably better to read the next chapters first (calculated fields, custom validation functions, ... ) and get a little familiar and then come back here later.

Which coding style should I use?

Feel free to use the style you prefer, you should think about DaDaBIK as a framework that provides you, as a black box, some pre-built typical components; you build on the top of those components.

An important note related to spaces and tabs, though: when you define a custom function, don't add spaces or tabs in front of the definition:

function print_customer_details(
is OK!


    function print_customer_details(
is not OK, the form configurator parser can't read it correctly.

Global variables

DaDaBIK provides a few global variables that you can use in your own custom code:

  • $conn: the PDO connection to the database
  • $current_user: the username of the currently logged user
  • $current_id_group: the id of the group the logged user belongs to
  • $current_user_is_administrator (0|1): if the currently logged user belongs to the administrators group (1) or not (0)
  • $quote: the correct quote character to use for table and field names in your queries.

In case the authentication is disabled, $current_user and $current_id_group are set to 'nobody' and $current_user_is_administrator to 0.

$_POST $_GET and $_COOKIE variables

You might need to use $_POST $_GET and $_COOKIE variables in your custom code. DaDaBIK automatically escapes all the values coming from $_POST $_GET and $_COOKIE (e.g. on MySQL "tes't" will become "tes\'t"), please consider that when you use those variables in your custom code. For databaes queries, in particular, if you use prepared statements (and you should), you should unescape those values (using the DaDaBIK function unescape() ) before using them in your bind_param_db functions. Don't use unescape() on values that have not been escaped.

For historical reasons, however, in custom PHP pages, $_POST, $_GET and $_COOKIES are not automatically "escaped" for your queries (so a string POSTed as "tes't" will remain "tes't", regardless of your magic_quotes_gpc settings), we suggest to use prepared statements which don't require explicit escaping for field values.

So, just to summarize: in custom pages "tes't" coming from $_POST, $_GET or $_COOKIES will remain "tes't"; in all the other cases (custom validation functions, custom formatting functions, calculated fields, ...) the quote (') will be automatically escaped. This is happening regardless of your magic_quotes_gpc settings. Please note that the automatic escape we are talking about here impacts on your code if you directly use the $_POST $_GET and $_COOKIE variables, if you use the parameters provided to your functions by DaDaBIK (see $params later), the value you get is exactly the one the user inserted.

Queries execution

We suggest to use the $conn PDO connection mentioned before and to use prepared statements.

In your queries, each field and table name must be surrounded by quotes (e.g. SELECT * FROM `customers`); you can use the global $quote variable, which contains the correct quote character according to your DBMS.

Queries and records locking

When a user enters a record in edit mode, DaDaBIK "locks" the record so that other users cannot modify it using the DaDaBIK edit feature and when the user clicks on "SAVE", DaDaBIK checks if the lock is still available (in fact after a certain amount of time the lock is releases). This prevents conflicts and data loss. However, if, with your custom queries, you modify a record, there isn't any check related to locking, consider that when you create your custom code. At the moment the only case case where you can easily check the lock status before modifying data with your custom code is a button in the edit form: check, in the custom buttons chapter, the dadabik_change_status function to see an example. We will provide in the future other tools that you can use in your custom code to check the lock status of a record and to check if someone changed a record since the last time the current user saw it.

ONLY function definitions

As you will learn in the next few paragraphs, you can add your own custom functions in custom_functions.php and in all the files contained in the custom_functions folder. For security reasons, it's important that you write there only function definitions. If you write additional code, external to your functions, such code could be executed by an attacker by directing calling the files. This is true for any DaDaBIK file you want to modify adding your code, except from the files you use to build custom PHP pages, for those files, if you edited them following the instructions, DaDaBIK checks if the script is being executed directly, outside DaDaBIK, and in case it stops the execution.

Custom Code API

A complete set of API functions will be released to make your custom code leaner and more secure. At the moment the API consists of three functions: get_record_details(), update_records(), and count_records(). The functions are static methods of the ddb_api class, therefore you must call them using this syntax:

ddb_api::function_name(function parameters)

get_record_details()

In one code line you can get the field values of a record belonging to any of the tables you have in your database, without directly executing SQL queries. Let's say that in one of your custom functions you need first and last name of the customer having ID 2; instead of executing an SQL query, you do:

$customer_info = ddb_api::get_record_details('customers', 'id', 2);

Where the first parameter is the table name, the second the name of a unique field and the third the value of such field. You will get an associative array containing the value of all the fields, so for example you'll find first and last name in $customer_info['first_name'] and $customer_info['last_name']. In case more than one record matches your query (this should never happen if you use a unique field) an error is triggered.

Please note that a preliminary version of get_record_details() was released with DaDaBIK V 11.7, it was not a method of the ddb_api class and therefore you could use it by simply calling get_record_details(); to guarantee backward compatibility, only for this API function, it is still possible to use it that way.

update_records()

Update one or more records of a table (matching a field name / field value pair) assigning new values to one or more fields. In the following example, we update the record having id_customer 10 in the table customers, assigning '2299 Piedmont Ave.' and 'Berkeley' to the fields address_customer and city_customer, respectively.

ddb_api::update_record('customers', 'id_customer', 10, ['address_customer', 'city_customer'], ['2299 Piedmont Ave.', 'Berkeley']);

Since id_customer is a unique field here, you will update max one record, if your condition matches more than one record, you will update all the matching records.

count_records()

Returns the number of records matching a specified field name / field value pair. In the following example, we count the number of customers whose city is "Bologna".

ddb_api::count_records('customers','city_customer','Bologna')

Stringify Fetches

DaDaBIK set the PDO ATTR_STRINGIFY_FETCHES attribute to TRUE:

setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);

this means that if you use the main DaDaBIK PDO connection, you will always get PHP strings when you fetch records from the database, even for numeric database fields.

10.1. Calculated fields

(DaDaBIK Enterprise/Platinum only)


A calculated  field is a form field whose value is not directly entered by the user but is calculated according to a function.

For example you might have a table products containing the fields price, tax, total_price and you want the field total_price to be computed as price + tax. To get this results, you just need to fill  - for the field total_price in forms configurator - the parameter "Calculated field function", setting the name of a custom function that will handle the calculation, for example dadabik_get_total_price. Then you need to write the function's code in /include/custom_functions.php. Here is an example of the simple dadabik_get_total_price:

function dadabik_get_total_price ($params){
    // first, check if price and tax are not empty and not null
    if ( $params['price'] !== '' && !is_null($params['price']) && $params['tax'] !== '' && !is_null($params['tax'])){
        return ($params['price'] + $params['tax']);
    }
    else{
        return NULL;
    }
    
}

As you can see, we have checked if the input values are NULL or empty ( '' ) before performing the addition. This is particularly useful in the insert form: when you open an insert form, total_price_product is immediately calculated according to the values of price_product and tax_product you have in the form, but if you have just opened the insert form, they would be empty.

Here are some rules you should consider when you write a calculated field function.

  • Each calculated field function receives in input an associative array $params containing all the values coming from the INSERT / UPDATE form.
  • select_multiple and file fields don't work correctly as input for a calculated field function
  • For select_single_radio fields, if the user didn't select any option, the corresponding key in $parameters_ar won't be set
  • If you need to use a field value, the correspondent field cannot be disabled in the edit form (PLEASE NOTE THAT the calculated field, apparently, would work in the form but the value stored would be WRONG)
  • Using as input a field from the form that is also a calculated field can lead to unexpected results.
  • Each calculated field function must return the value to assign to the calculated field and it is automatically executed every time an insert or update operation is executed and every time one of the input value is modified in the INSERT / UPDATE form.
  • If you want to assign a calculated value to a date/date_time field, regardless of format and format_edit you set as a config parameter, you have to set the value in the following format yyyy-mm-dd hh:mm:ss

In this video chapter you will learn, step by step, how to use calculated fields in DaDaBIK.



10.2. Custom validation functions


DaDaBIK, by default, validates input data according to the field type/content. For example, if a field is set as email, DaDaBIK will throw an error if a user tries to insert a non-valid email. However, you can write your own validation function and tell DaDaBIK, in form configurator, to use it for a specific field. You can write your custom validation function in include/custom_functions/custom_validation_functions.php or, if you prefer, in the general include/custom_functions.php file.

In this simple example we just check if the value of the field quantity_product is between 0 and 30:

function dadabik_validate_quantity_product ($params){
    if ( $params['quantity_product'] >= 0 && $params['quantity_product'] <= 30)){
        return true;
    }
    else{
        return false;
    }
    
}

Here are some rules you should consider when you write a custom validation function.

  • Each custom validation function receives in input an array $params containing ALL the values coming from the form posted (the validation rule for a field could depend on the value of other fields) .
  • Only NOT NULL and NOT empty field values are validated, if you are looking for a method to check requiredness, you can simply set a field as required in the form configurator.
  • If you need to use a field value, the correspondent field cannot be disabled in the edit form and cannot be a calculated field (which is always disabled)
  • Each custom validation function must return the boolean true or the boolean false.
  • You also have to specify the error message in your language file (see /include/languages), using as a key nameofthefunction_not_valid; e.g., for the above example, you would add a sentence to the $normal_messages_ar array having key = dadabik_validate_quantity_product_not_valid

In this video chapter, you will see an example of custom validation function.



10.3. Custom formatting functions


DaDaBIK, by default, formats data according to the field type/content. For example, if a field is set as url, DaDaBIK will show, in the datagrid view and details view, its value as a link. However, you can write your own formatting function and tell DaDaBIK, in form configurator, to use it for a specific field. You can write your custom formatting function in include/custom_functions/custom_formatting_functions.php or, if you prefer, in the general include/custom_functions.php file.

In this simple example we format the field last_name_customer to be displayed in red.:

function dadabik_format_last_name_customer ($value, $id){
    return '<span style="color:red">'.htmlspecialchars($value).'</span>';
}

Here are some rules you should consider when you write a custom formatting function.

  • Each custom formatting function receives in input the value to display $value and the value of the unique field of the record processed ($id)
  • If the field to format is a select_single/select_multiple having more than one linked field, $value is an array, where $value[0] is the value of the first linked field, $value[1] the value of the second and so on
  • Each custom validation function must return the formatted value
  • If you don't want your content to be interpreted as HTML (probably, in most of the cases, you don't), you must pass your content through htmlspecialchars (see example above), otherwise you could have unexpected results. This is particularly true if you are using custom formatting functions with PDF custom templates: if for example you have a < sign in your content, TCPDF might interpret it as HTML and silently stop the rendering of the page because of a rendering error, giving you the false impression that, for example, the records related to a search are less than they actually are.

In this video chapter, you will see an example of custom formatting function.



10.4. Custom required functions / conditional fields


You can set a field as required (yes/no) in form configurator, without writing any code. However, sometimes you want to decide if a field is required according to some logic (e.g. according to the value of another field); in these cases, you should use a custom required function. The same function can be also used to specify if a field should be displayed or not in your insert and edit form: again, normally you can do it simply by setting its permissions (e.g. insert = yes) but sometimes you need a more complex logic (e.g. show the field B in the insert form only if the field A is > 50). You can write your custom required function in include/custom_functions/custom_required_functions.php or, if you prefer, in the general include/custom_functions.php file.

In this simple example, we have created a function for the field "state", which is display and required only if the country is "USA".

function dadabik_display_required_state_customer ($params){
    if ( $params['country_customer'] === 'USA'){
        $a['show'] = true;
        $a['required'] = true;
    }
    else{
        $a['show'] = false;
        $a['required'] = false;
    }
    return $a;
    
}

Here are some rules you should consider when you write a custom required function.

  • Each custom required function receives in input an associative array $params containing all the values coming from the INSERT / UPDATE form.
  • select_multiple and file fields don't work correctly as input for a custom required field function
  • For select_single_radio fields, if the user didn't select any option, the corresponding key in $parameters_ar won't be set
  • if you need to use a field value, the correspondent field cannot be disabled in the edit form and cannot be a calculated field (which is always disabled)
  • each custom required function must return an associative array with two elements: 'required' and 'show' and for each element the boolean value true or the boolean value false.
  • About the display (true/false) value: consider that this function just hides/displays the field in the form, but the PERMISSIONS tab in the admin section is still the place where you decide if a field will be considered or not for INSERT and UPDATE operations. For example, in this "state" case, for the field state you should set YES for both CREATE and EDIT so that insert (or update) operations will use the "state" field value (yes, they will use it even if the field is hidden, but this shouldn't be a problem, if the user didn't fill it, it will be empty or null; it can be a problem if the user fills a field with a value and then makes the field disappear: even if it is not in the form, the field will pass its value to the insert or update process).
  • if the field is a calculated field, the "required" element will not be taken into consideration, the "show" elment will be used to show/hide the field in the form, but even if the field is hidden, its value will be computed anyway during insert and update operations.
  • To guarantee backward compatibility (before v. 10 this function only controlled requiredness), this function can also return, instead of an associative array, just a boolean value (true or false): in this case, such value controls the requiredness of a field.

Starting from V. 10.5, the same function can be used to decide if you want to show a field (column) in the results grid. In order for a column to be displayed, the corresponding permissions must always be set to YES (or MY) but you might decide not to show a column even if the permissions are set to YES (or MY) according to custom rules. Here is a situation where you might need it: let's say you have a products table, having a field product_type. According to the product type, you might have a different set of fields describing the product; you might want, according to the product type the user selects as a search filter (if any) to show only the related columns.

The array $params, in this case, will contain the list of the search filters (if any) set by the user. For example, if the user is searching for products having price_product > 50 and type_product = 5, $params will contain the following values:

$params['select_types']['price_product'] = 'greater_than'
$params['select_types']['type_product'] = 'is_equal'
$params['values']['price_product'] = '50'
$params['values']['type_product'] = '5'

If the search filter is "between" and the user, for example, is searching for products having price between 10 and 15, $params will contain the following values:

$params['select_types']['price_product'] = 'between'
$params['values']['price_product'] = '10'
$params['values']['price_product_between____'] = '15'

If you have a field categories_product, select_multiple* field, and the user is searching for products having both the categories 2 and 20, $params will contain the following values:

$params['select_types']['categories_product'] = 'is_equal'
$params['values']['categories_product'][0] = '2'
$params['values']['categories_product'][1] = '20'

Please note that you might have in $params also references to the fields the user left blank. Eg. if for "type_product" the user left "is_equal" and an empty box, this parameter isn't used by DaDaBIK for the execution of the query, but you will see it in $params:

$params['select_types']['type_product'] = 'is_equal'
$params['values']['type_product'] = ''

At the moment you don't have as a parameter the "All the conditions required" Vs. "Any of the conditions required" search option selected by the user.

Here is an example: we want to show the field size_product only when the type_product selected is 4 (forms) or a filter type_product = 4 has been set (results grid)

function dadabik_display_required_size_product ($params, $page_type){
 if ( $page_type === 'form'){
    if ( $params['type_product'] === '4'){
        $a['show'] = true;
        $a['required'] = true;
    }
    else{
        $a['show'] = false;
        $a['required'] = false;
    }
 }
 if ( $page_type === 'results_grid'){
    if ( isset($params['values']['type_product']) && isset($params['select_types']['type_product']) && $params['values']['type_product'] === '4' && $params['select_types']['type_product'] === 'is_equal' ){
        $a['show'] = true;
    }
    else{
        $a['show'] = false;
    }
 }
 return $a;
    
}

As you can see, we need an additional function parameter, $page_type; furthermore,

Some additionale notes:

  • You have to enable the use of conditional fields for results grid by setting $enable_custom_display_results_grid = 1 in your config_custom.php file.
  • It only works with classic results grid (it doesn't work with the list view)
  • In the example above, ONLY if the filter is type_product = 4 the field will be displayed, a filter type_product > 3 (which probably doesn't make much sense anyway) wouldn't display it
  • The function doesn't have impact on the exported CSV
  • Being a custom function, you don't necessary have to use $params, you can use the logic you want; for example, do you want to show a field only on Monday? You can do it.

In this video chapter, you will see an example of conditional fields.



10.5. Custom default value functions (DaDaBIK Enterprise/Platinum only)


You can set the default value for a field in the form configurator, without writing any code. However, sometimes you might need to define the default value according to a more complex logic; in these cases, you can write a custom default value function and tell DaDaBIK, in form configurator, to use it for a specific field. You can write your custom default value function in include/custom_functions/custom_default_value_functions.php or, if you prefer, in the general include/custom_functions.php file.

In this simple example we provide, for the field date_order, as a default, the current date:

function dadabik_set_default_date_order (){
    return date('Y-m-d');
}

Here are some rules you should consider when you write a custom default value function.

  • A custom default value function doesn't receive anything in input
  • Each default value function must return the default value to set

10.6. The custom startup function


For some particular applications, you might need that DaDaBIK executes some custom PHP code every time a page is loaded. For this purpose, you can use a custom startup function: in /include/custom_functions.php you set the name of the function (if any) and you define the function itself.

In this example we have defined a dadabik_startup() function that will be executed every time a page is loaded. The function is executed after the connection to the database, so you can use the connection in your code, if you need it.

$custom_startup_function = 'dadabik_startup';
function dadabik_startup (){
    ... your custom code here ...
}

10.7. Custom buttons

(DaDaBIK Enterprise/Platinum only)


DaDaBIK automatically display the buttons you need to execute standard operations (e.g. the edit, delete, details buttons you  see in the results grid); however, you can also add additional buttons that execute custom PHP or Javascript code.

Some use cases:

  • you want to add a button in your form that copies the content of a series of fields (e.g., in a customer registration form, the contact address fields) to a series of other fields (e.g., in a customer registration form, the invoice address fields)
  • you want to add an "Approve" button at the top of your edit form that changes the status of your order from "waiting for approval" to "approved"
  • you want to add a "Send" button a the top of your details page that sends via email the current details of an order to someone.

To add a custom button, you have to add an element to the $custom_buttons array in the /include/custom_functions.php file, following the template we'll show in the example below. In the following example, we want to add a "Copy address" button next to the contact_address field of the customers insert form; that button copies the contact_address value into the invoice_address value, using Javascript.

In /include/custom_functions.php you have to write (we'll see the meaning of all the options later):

$custom_buttons['customers'][$cnt]['type'] = 'javascript';
$custom_buttons['customers'][$cnt]['callback_function'] = 'dadabik_copy_address';
$custom_buttons['customers'][$cnt]['permission_needed'] = 'insert';
$custom_buttons['customers'][$cnt]['show_in'][] = 'insert_form';
$custom_buttons['customers'][$cnt]['position_form'] = 'contact_address';
$custom_buttons['customers'][$cnt]['label_type'] = 'fixed';
$custom_buttons['customers'][$cnt]['label'] = 'Copy address';
$custom_buttons['customers'][$cnt]['style'] = 'background:#000;width:200px';
$custom_buttons['customers'][$cnt]['id'] = 'copy_address_button';
$cnt++;

Since we have set, as a callback function, a Javascript function, we have to write such function in /include/custom_functions.js

function dadabik_copy_address()

{

    $('[name="invoice_address"]')[0].value = $('[name="contact_address"]')[0].value;

}


If your callback function is, instead, a PHP function, you can write it in in /include/custom_functions.php.
Please note that, to enable custom button, you have to set $enable_custom_button_functions = 1 in config.php.

Let's see all the options you have when you define a custom button.

type, the type of callback function triggered by the button; the available options are:

  • javascript (a Javascript function)
  • php_standard (a PHP function, executed by loading a page in the browser)
  • php_ajax (a PHP function, executed asynchronously via AJAX)

callback_function, the name of the callback function (must start with "dadabik_")

permission_needed, the permission the user needs in order to see/use the button; the available options are:

  • none (permissions are not checked, the button is always shown)
  • read
  • delete
  • edit
  • insert
  • details
  • custom_function: DaDaBIK shows the button (and allows the current user to execute the related function) based on a custom function you wrote; you have to define the name of the function with the additional parameter permission_custom_function, the function must return TRUE or FALSE.

show_in, where to show the button; the available options are:

  • edit_form
  • details_page
  • insert_form
  • search_form
  • results_grid

Please note that, for show_in, you can also add multiple values; for example:

$custom_buttons['products'][$cnt]['show_in'][] = 'insert_form';

$custom_buttons['products'][$cnt]['show_in'][] = 'edit_form';


shows the button, for the products table, both in the insert and edit forms.

position_form, this parameter adds additional details about the position: if you selected show_in: results_grid,  you can choose between:

  • top (at the top of the results grid)
  • row (at each row of the results grid)

for all the other show_in options you can choose "top" (at the top of the form) or specify a field name if you want to display the button close to a form field.

label_type, the available options are:

  • fixed (the label of the button is exactly what you set for the parameter "label")
  • language_file (the label of the button depends on the language set, what you set for the parameter "label" is the key to look at in the corresponding custom language file)

the label_type parameter has also effect on confirmation_message.

label, the label of the button

confirmation_message, a confirmation message you want to show before executing the callback function (pelase note that at the moment, confirmation messages containing single or double quotes are not supported).

style, CSS style (if any) to add to the button

id, this is an optional parameter, you can specify an id and such id will be used by DaDaBIK for the HTML's id attribute of the button. It could be useful if you need to select the button from your custom javascript code. In more detail, the id actually used will be "cb__" + your ID where "cb__" can be changed in config.php ($custom_button_ids_prefix). If you are using custom buttons at row level, the id will be "cb__" + PKvalue + your ID instead, where PKvalue is the value, for the current record, of the field set as unique in DaDaBIK for the current table.
Naming rules: you should use a unique id, different for each button and the word you used for the id should not contain strings that are not allowed for field names in DaDaBIK (see "known bugs, limitations and unexpected behaviors" chapter, "Table and field names related" paragraph) otherwise you can get unexpected results. Please note that DaDaBIK doesn't do any check on the ID you choose.

Please note that, while a Javascript callback function doesn't receive any input (but you can access the form values using Javascript or Jquery) a PHP callback function receives, in input: $table_name (the name of the current table/view), $where_field (the DaDaBIK unique field name for  the table/view), $where_value (the value of the DaDaBIK unique field name). The combination of $table_name, $where_field and $where_value allows to identify a specific record. In some cases (positions: results_grid top, insert form, search form), $where_field and $where_value are not set (they are set to NULL) because the button is not related to a specific record. What we have described here for PHP callback functions is also valid for the PHP function you defined as permission_custom_function.

Let's see an example of php_standard function: we want to implement in the edit form a button that changes the status of the order.

In /include/custom_functions.php you have to write:

$custom_buttons['orders'][$cnt]['type'] = 'php_standard';
$custom_buttons['orders'][$cnt]['callback_function'] = 'dadabik_change_status';
$custom_buttons['orders'][$cnt]['permission_needed'] = 'custom_function';
$custom_buttons['orders'][$cnt]['permission_custom_function'] = 'dadabik_permission_change_status';
$custom_buttons['orders'][$cnt]['show_in'][] = 'edit_form';
$custom_buttons['orders'][$cnt]['position_form'] = 'top';
$custom_buttons['orders'][$cnt]['label_type'] = 'fixed';
$custom_buttons['orders'][$cnt]['label'] = 'Approve';
$custom_buttons['orders'][$cnt]['confirmation_message'] = 'Are you sure?';
$custom_buttons['orders'][$cnt]['style'] = 'background:#000;width:200px';
$cnt++;

Since we have set, as a callback function, a PHP function, we have to write such function in /include/custom_functions.php

function dadabik_change_status($table_name, $where_field, $where_value)
{
    global $conn, $dadabik_main_file, $quote;
    
   // since we want to modify the record and we are in the edit form, let's check if the user is still locking it
   // the check_refresh_record_lock() function provided by dadabik can do it for us
   if (check_refresh_record_lock($table_name, $where_field, $where_value) === false){
      die('You have lost the record lock, plese refresh the page and try again');
   }
   $sql = "UPDATE orders set status_order_user = 'approved' WHERE ".$quote.$where_field.$quote." = :where_value";
   
   $res_prepare = prepare_db($conn, $sql);
   
   $values_to_bind = array();
   
   $values_to_bind['where_value'] = $where_value;
   
   foreach ($values_to_bind as $key => $value)
   {
       $res_bind = bind_param_db($res_prepare, ':'.$key, $value);
   }
   
   $res = execute_prepared_db($res_prepare,0);
  
   // let's go back to the edit form
   header('Location:'.$dadabik_main_file.'?tablename='.urlencode($table_name).'&function=edit&where_field='.urlencode($where_field).'&where_value='.urlencode($where_value));    
   exit;
}

Since we have set, for permission_needed, "custom_function", we also have to write the permission function dadabik_permission_change_status in /include/custom_functions.php. Let's say that our permission rule is very simple: only the users belonging to the users group having id 3 can use this button:

function dadabik_permission_change_status($table_name, $where_field, $where_value)
{
    global $current_id_group;
    
   if ($current_id_group === '3'){
       return true;
   }
   else{
       return false;
   }
}

Please note that PHP callback functions normally are not used to produce output; php_ajax functions, in particular, raise errors if you produce output (via echo statements or in any other way).

Starting from DaDaBIK v. 11.1, your php_ajax functions can also return a message (for example a confirmation message or an error message) to show after the execution of the code. Here is an example of how to use the new feature:

function dadabik_change_status_2($table_name, $where_field, $where_value)
{
    ......your code...........
    if (..everything was fine....){
        $_cp_return['message'] = 'The status has been changed';
        $_cp_return['class_message'] = 'msg_ok';
    }
    else{
        $_cp_return['message'] = 'Unexpected error, the status cannot be changed';
        $_cp_return['class_message'] = 'msg_error';
    }
    return $_cp_return;
}

As you can see from the example, you can also return a CSS class to use for displaying the message, built-in classes are msg_ok, msg_error and msg_alert. To guarantee backward compatibility, a message is displayed only if an associative array having the element "message" is returned, otherwise a generic "done!" message is displayed. The class is optional, the default class is msg_ok.

Custom buttons and record ownership/Row-level Custom Filters
What happens if, for a specific group, let's say the group having id 4, you have set, for a specific permission, e.g. "Delete", the value MY? As you probably know, users belonging to that group will be able to delete only the records they own (see "Owner permissions" chapter). What if you use "delete" as permission_needed for a button then? All the users having delete permissions set to YES or MY will see the button (regardless of the record); however, if a user clicks the button for a record he/she doesn't own, he/she will get an error. This is valid just for php_standard and php_ajax callback functions, though: for a javascript callback function, all the users having delete permissions set to YES or MY will be able to execute the function. The same thing happens for Row-level Custom Filters.

Custom buttons and records selected by checkboxes
For a button on a results grid, you may want to execute your function ONLY on the selected (by checkboxes) records. If, for a table TABLENAME, the variable $_SESSION['checked_ids'][TABLENAME] is not null, it represents the set of IDs (the values of the primary keys of the records selected) that you can use to limit your action on the selected records.

In particular, $_SESSION['checked_ids'][TABLENAME] is an associative array: each element represents a record that has been checked by the currently logged user, where the key of the element is the ID of the record and the value of the element is always 1.

For example if, for the table customers, the logged user selected two records, having id 5 and 21, the resulting array will be the following:

$_SESSION['checked_ids']['customers']['5'] = 1
$_SESSION['checked_ids']['customers']['21'] = 1

You can easily iterate through all the selected records with a few lines of PHP:

foreach ($_SESSION['checked_ids']['customers'] as $key => $value){
    echo 'id of the selected element: '.$key;
}

Security note: don't assume that, since the user selected a record, he/she has the right to do some operations on it. Selecting (via checkbox) a record is an operation that doesn't require any permission. Through the normal application interface, a user can select only the records he/she is allowed to view, but a user could send a "fake" malicious request to include a record in the $_SESSION['checked_ids'] even if he/she cannot view it (this is by design, to keep the the operation as light as possible) so check the records before executing operations on them. Also consider that, for the same reason (we are talking about user-generated information), $_SESSION['checked_ids'] could contain malicious SQL code; to avoid SQL injections, always use prepared statements.

In this video tutorial you can see examples of PHP custom buttons.



In this video chapter you can see examples of Javascript custom buttons.



An additional related video:



10.8. Hooks


A DaDaBIK hook allows you to write some custom PHP code to be called under certain circumstances.

There are two types of hooks: operational hooks and layout hooks; the first ones are triggered before/after some data operations, the second ones before / after some elements of the layout.

Operational hooks

(DaDaBIK Enterprise/Platinum only)

DaDaBIK currently supports before insert, after insert, before update, after update, after delete, after login and after user self-registration hooks for each table installed in a DaDaBIK application, which means that for each table you can enrich the DaDaBIK workflow with your own code functions, that can be executed:

  • before an insert operation (before insert hook)
  • after an insert operation (after insert hook)
  • before an update operation (before update hook)
  • after an update operation (after update hook)
  • before a delete operation (before delete hook)
  • after a delete operation (after delete hook)
  • after a login operation (after login hook)
  • after a user self-registration operation (after self-registration hook)

The after insert, update and delete hook functions receive as parameter the value of the DaDaBIK unique field (typically, the value of the primary key) of the record just inserted (if it is an autoincrement field), updated or deleted. If the DBMS is PostgreSQL, the unique field doesn't need to be autoincrement.

The before update hook function receives as input:
1 - the value of the PK of the record the user is updating
2- an associative array containing all the values the user filled in the update form (where the array element's key is the name of the field)
3- the update type ('standard', 'csv' or 'live_edit').

If the update is the result of a live edit, the associative array contains the values currently stored in the database (except from the value of the field being live-edited, which is the actual value submitted by the user); if you are using live edit in combination with before update hooks, consider that in two cases (select_multiple_* and image_file/generic_file field types) values in the database are represented in a different way respect to the original values submitted by the user from the form:

  • The selected options of select_multiple_* fields, in the database are represented as a list of options separated by a separator character (by default ~) e.g. '~1~98~' means the options 1 and 98 were selected, while when you submit a "standard" form (no live edit), each select_multiple_* field is submitted as an array where each element represents one of the option selected; however, DaDaBIK will translate for you the raw DB values in an associative array, exactly as there was a form submission.
    The only non-obvious thing you should consider is that, if in the database you have an empty space value for a select_multiple_* field (not a very common situation, it means the user left the field blank and you have $treat_blank_as_null = 0 ), the corresponding element of the associative array won't be set (exactly as it would happen with form submissions if the user left the field blank and $treat_blank_as_null is 0 ); if the field, instead, is NULL, the corresponding element will be NULL.
  • For file fields, when you submit a form, the name of the file is used as a parameter during a "standard" (no live edit) update, but DaDaBIK stores in the database a unique name assigned by the system (e.g. picture_78658.jpg instead of picture.jpg).

The before insert hook function receives as input an associative array containing all the values the user filled in the insert form (where the array element's key is the name of the field).

The before delete hook function receives as input the value of the PK field of the record the user wants to delete. Your hook function can optionally returns "dont_delete" to prevent the deletion of the record (the execution of an after delete hook, if any, will also be prevented): in this case you also need to add a custom error messages in your language file, if the name of the funciton is, for example, dadabik_check_delete_customer, you have to add this entry in your language file
$error_messages_ar['dadabik_check_delete_customer_dont_delete'] = '.... write your error message here ....';

The after login hook function ($hooks['login']['after']) receives as input an associative array ($parameters_ar) having two keys, representing the id_user ($parameters_ar['id_user']) and the id_group ($parameters_ar['id_group']) of the user who just logged in. It is triggered only by a normal login executed through the login form (standard or LDAP), it is not triggered by a login executed via HTTP APIs or via Wordpress login sharing. In case your after login hook ends with an exit() or die(), you should first commit the transaction opened by DaDaBIK adding a complete_trans_db(); code line.

The after user self-registration hook function ($hooks['user_self_registration']['after']) receives as input an associative array ($parameters_ar) having only one key, representing the id_user ($parameters_ar['id_user']) assigned to the user who just registered.

Examples
Let's say for example that, when a new order is inserted into your orders management application, you want to automatically update your stock: you can do it by using a hook; in particular if you store your orders in the orders table and your products information and quantity available in the products table, you can add an after insert hook on the table orders and write the code needed to update the records of the table products.

You can write your hook function in include/custom_functions/operational_hooks.php or, if you prefer, in the general include/custom_functions.php file.

Let's say we want to send an email to a specific address when a new account is inserted in our system: this is a built-in DaDaBIK feature (see parameter $enable_insert_notice_email_sending in config.php) but let's say we don't to use the built-in feature and we want to implement it using our custom code. We could, as in the following example, define an after insert hook on the table accounts, setting the function that has to be executed (dadabik_send_notice_after_accounts_insert) and after we can write the code of the function, that retrieves the name of the account just inserted and send it via email to a specific address.

$hooks['accounts']['insert']['after'] = 'dadabik_send_notice_after_accounts_insert';

function dadabik_send_notice_after_accounts_insert($id_account)
{
    global $conn;

    // get the name from the ID
    $sql = "SELECT name_account FROM accounts WHERE id_account = :id_account";
    
    $res_prepare = prepare_db($conn, $sql);

    $res_bind = bind_param_db($res_prepare, ':id_account', $id_account);
    
    $res = execute_prepared_db($res_prepare,0);
    
    $row = fetch_row_db($res_prepare);
    
    mail ('john@mysite.com', 'New account inserted', 'A new account ('.$row['name_account'].') has been added.');
    
}

Let's look at another example with the before insert hook: an interesting use of the before insert hook is the modification of a value inserted by the user before the corresponding record is actually inserted, by overriding the content of the $_POST array. For example, let's say that, for the table customers, we want to capitalize the first name of the customer before insertion.

$hooks['customers']['insert']['before'] = 'dadabik_capitalize_first_name_customer';

function dadabik_capitalize_first_name_customer($parameters_ar)
{
    $first_name_customer_capital = strtoupper($parameters_ar['first_name_customer']);
    
    $_POST['first_name_customer'] = escape($first_name_customer_capital); // DaDaBIK expects the values in $_POST used for the insert to be properly escaped, so in case they aren't already (values in $parameters_ar are not) you should use the escape function.

   
}

Please note that:

  • you can use any name for hook functions, but the name MUST start with dadabik_
  • if you need to execute operations on the database, you have to add the global $conn; code line (as in the above example)
  • the delete hook doesn't work after a "delete all" operation.
  • for operational hooks, you don't have to worry about transactions in your code: the after insert, update and delete hook functions are executed inside a transaction that starts before the insert, update and delete operations are executed and ends after the hook functions execution; the before update hook is also inside the same transaction which starts before its execution.
  • for each hook, you can call just ONE function; you cannot, for example, link two different functions to hooks['accounts']['delete']['after']
  • The before insert hook doesn't work when a record is automatically inserted using the "other..." option from a dropdown menu (select_single field)
  • Operational hooks are not triggered by SQL operations executed by your custom code (e.g. if, for an after insert hook on table A, you execute an update on table B using your custom code, the before/after update hooks for table B are not executed).

In this video chapter you will see examples of operational hooks.



Layout hooks

DaDaBIK currently support:

  • header hook ($hooks['custom_header_1'])
  • footer 1 hook ($hooks['custom_footer_1'])
  • footer 2 hook ($hooks['custom_footer_2'])
  • before login form hook ($hooks['login_form']['before'])
  • And, for each table/view:
    • before results grid header hook ($hooks[TABLENAME]['resultsgrid_header']['before'])
    • after results grid header hook ($hooks[TABLENAME]['resultsgrid_header']['after'])
    • after quick search filters hook ($hooks[TABLENAME]['quicksearch']['after'])
    • after 2 results grid header hook ($hooks[TABLENAME]['resultsgrid_header']['after_2'])
    • before edit form header hook ($hooks[TABLENAME]['edit_form_header']['before'])
    • after edit form header hook ($hooks[TABLENAME]['edit_form_header']['after'])
    • before insert form header hook ($hooks[TABLENAME]['insert_form_header']['before'])
    • after insert form header hook ($hooks[TABLENAME]['insert_form_header']['after'])
    • before search form header hook ($hooks[TABLENAME]['search_form_header']['before'])
    • after search form header hook ($hooks[TABLENAME]['search_form_header']['after'])
    • before details page header hook ($hooks[TABLENAME]['details_form_header']['before'])
    • after details page header hook ($hooks[TABLENAME]['details_form_header']['after'])

Let's say for example that you want to display some contacts information in the footer of your application: you can do that by defining a footer 2 hook, which is triggered just before the end of the page (before the "p owered by: DaDaBIK database front-end" statement); here is an example (all your hook functions must be written in the /include/custom_functions.php file)

$hooks['custom_footer_2'] = 'dadabik_print_contacts';

function dadabik_print_contacts()
{
    echo '<p>Our contact address is ..... ';
}

Let's do another example: you want to give some instructions to your users when they fill the insert form of the product table; you can do that by defining a before insert form header hook on the table products:

$hooks['products']['insert_form_header']['before'] = 'dadabik_print_products_instructions';

function dadabik_print_products_instructions()
{
    echo '<p>Use this form when a new products needs to be registered. ';
}

Please note that:

  • you can use any name for hook functions, but the name MUST start with dadabik_
  • the functions triggered by layout hooks normally contain just echo statements, but they can also be more complex, there is no limit.
  • for each hook, you can call just ONE function; you cannot, for example, link two different functions to hooks['custom_footer_2']

In this video chapter you will see examples of layout hooks.



10.9. Row-level Custom Filters

(DaDaBIK Enterprise/Platinum only)


Sometimes you need to filter, according to some criteria, the records of a table/view that a user can see.

Take a helpdesk application, for example: let's say the help requests are inserted by some managers and each help request is then assigned by a manger to a member of the IT staff group; you might need to filter the requests so that each member of the IT staff can only see/modify/delete the requests assigned to them.

If your criteria is based on record ownership (who, user or group, inserted the record is the one who can see and/or modify and/or delete the record) you can use simply use the owner permissions (see later in this manual), in all the other cases, you can use row-level custom filters.

You can define your custom filters in include/custom_functions/custom_filters.php or, if you prefer, in the general include/custom_functions.php file. Let's define a row-level custom filter for a table/view:


$custom_filters['requests'] = 'dadabik_custom_filter_requests';

With the above line we are saying that we define a custom filter for the table requests and that the filter is generated by the custom function dadabik_custom_filter_requests(). Then we need to write such function, for example:

function dadabik_custom_filter_requests()
{
    global $current_user, $current_id_group, $quote;
    
    if ($current_id_group == 3){ // apply the filter only for the IT staff group
        $filter = $quote."assigned_to_request".$quote." = '".$current_user."'";
    }
    else{
        $filter = "1=1";
    }
    
    return $filter;
}

In our example we want to add, if the current user is part of the IT staff group (let's assume that the IT staff user groups has ID 3), an ADDITIONAL where clause, showing only the requests assigned to him/her (being assigned_to_request the field set by the IT manager). All the other users (not belonging to the id_group 3), instead, will see all the records.

Each field and table name must be surrounded by quotes; you can use the global $quote variable, which automatically add the correct quote according to your DBMS.

$current_user and $current_id_group are two global variables provided by DaDaBIK, they contain the username and the group ID of the user currently logged. In case authentication is disabled, the two variables will be set as "nobody".

The filter defined in the function is applied on the top of other filters applied by the user (for example by executing a search) or generated by the owner permissions (if set).

Here are a few more examples of use cases where you might find row-level filters useful:

  • In a sales management application, agents can only see orders related to their area
  • In a multi-schools application, each principal or manager can only view data for students belonging to their school
  • In the application for a physiotherapy practice,  each therapist can only view data records belonging to their patients

Since you can create your own additional filter, you have a lot of  flexibility; you can create very simple filters such as :

$filter = 'paid_invoice = 0'

to show, for everybody, only the invoices where the field paid_invoice is 0 (in this case, the filter is basically an alternative to a VIEW) or complex filters based on very complex criteria / parameters, not necessarily related to the characteristics of the user logged.

There are two situations where row-level custom filter don't apply, let's explain with two examples:

1) Customers table, edit form, field id_city (lookup field, linked table: cities). If you apply a row-level custom filter to the cities table, in the customer edit form an id_city value could appear even if it should be filtered out by the custom filter, but ONLY IF that id_city value is already stored in the customer record (to avoid blank values)

2) Customers table, results grid, field id_city (lookup field, linked table: cities). If you apply a row-level custom filter to the cities table, in the results grid of customers an id_city value (or, better, the related linked field value) could appear even if it should be filtered out by the custom filter, but ONLY IF that id_city value is already stored in the customer record (to avoid blank values).

Please note that you can use any name for the custom filter functions but the name MUST start with dadabik_

In this video chapter, you will see, step-by-step, how to use row-level custom filters


10.10. Custom Pages

(DaDaBIK Enterprise/Platinum only)


As explained in the introduction of this chapter, with DaDaBIK, from the pages section of the administration interface, you can create pages based on a database table/view or custom HTML and PHP pages. PHP pages are custom pages running your custom PHP code and to run them you have to enable the feature from config.php ($enable_custom_php_pages = 1).

Here are some instructions to follow when you build a custom PHP page.

To avoid conflicts with the DaDaBIK engine, you must use the prefix _cp_ (custom pages) to name your variables, functions and classes, for example:

  1. If in your code you need a variable $test name the variables $_cp_test instead.
  2. If in your code you need a session variable $_SESSION['test'] name it $_SESSION['_cp_test'] instead

The same rule applies to $_POST, $_GET and $_COOKIES so if you create a form that posts values using POST, a field first_name must be named _cp_first_name instead and the value will be then available in $_POST['_cp_first_name'].

For your local variables (variables having local scope in your functions), you don't need to follow the _cp_ naming rules.

YOU ARE RESPONSIBLE of following the above rules if you want to avoid conflicts and unexpected behaviours by the DaDaBIK engine, currently there isn't any check on the variable names you use. Also, please make sure that your database field names don't start with _cp_, because this could also lead to conflict.

You can, however, READ all the DaDaBIK variables you can access in your scope, in particular you will find useful the following ones:
$_SESSION['logged_user_infos_ar']["id_user"] -> the ID of the logged user
$_SESSION['logged_user_infos_ar']["username_user"] -> the username of the logged user
$_SESSION['logged_user_infos_ar']["id_group"] -> the group ID of the logged user


$conn -> the PDO connection link with the Database

All your custom pages need to start with the following line:

if(!defined('custom_page_from_inclusion')) { die(); }

this prevents your page from being called directly, bypassing the DaDaBIK authentication system

In custom PHP pages, $_POST, $_GET and $_COOKIES are not "escaped" for your queries (so a string POSTed as "tes't" will remain "tes't", regardless of your magic_quotes_gpc settings), we suggest to use prepared statements which don't require explicit escapinng for field values.

If you use custom code in other situations (custom functions, hooks, .... ), however, $_POST, $_GET and $_COOKIES are always escaped by DaDaBIK.

10.11. Custom pages and dashboards


Custom pages can be used to create dashboards. A dashboard is a collection of information that represent, synthetically, some key indicators of your data/application. For example a typical dashboard might contain some graphic reports that represent aggregated data and some tabular reports.

Building a dashboard in DaDaBIK is very easy: first you create a custom (PHP or HTML) page, then you embed all the reports (graphic or tabular) you want by:

  • clicking on the "show embed code" button you can find at the bottom of each report in your DaDaBIK application (you can see it only if you are logged in as admin)

    show embed code
  • paste the code (it's pure HTML) into the source code of the custom page you have created (your dashaboard)

[back to top]

11. Import data from MS Excel/CSV/ODS

(DaDaBIK Enterprise/Platinum only)


BASIC USAGE

The import feature allows you to import data from an external source: a MS Excel, CSV or ODS file (from now on, a "data file"). To use the feature, the config parameter $import_from_csv_feature must be set to 1. When this feature is enabled, for each table/view, in the results grid, you will see an "import" button, click it to start the import process.

By default, DaDaBIK expects to find, in your data file, for each field you have in your insert form, a corresponding column in your data file, having, in the first row, the name of field as it appears in the database. All and only the fields for which you have insert permissions set to YES will be considered, so if you have excluded a field from the insert form it will not be considered even if it is available in your data file.

During the import process, all the operations that DaDaBIK normally does during a standard insert process are executed, so the values are validated (required fields, field types), the operational hooks are executed, the email alerts are sent … The duplication check is a bit different, though: if you enable "Check for duplicated entries during INSERT?" in form configurator, DaDaBIK will check for duplicated records in the standard way, but if some duplications are found, you can't choose "insert anyway" as you can do during a standard insert operation, the import, instead, is cancelled. In case you want to skip the duplication check during the import process, you can set your config parameter $check_similarity_during_import = 0. Having $check_similarity_during_import = 0 can be partially compensated by enabling synchronization (see later).

ADVANCED SETTINGS

In your config_custom.php file you can set several (optional) parameters related to the import feature. 

Name of the sheet

By default, DaDaBIK expects the name of the sheet equal to the name of the table you are importing data in. You can change it; here, for example, we set, as sheet name for the table "customers", the name "customers2021"

$csv_sheet_name['customers'] = 'customers2021';


Name of the columns

As we discussed at the beginning of this chapter, DaDaBIK expects to find, in the first row of the sheet, the name of field as it appears in the database. You can choose custom names. E.g. here, for the field first_name_customer in the table customer, we set the column name in our data file as "firstname"

$csv_col_names['customers']['first_name_customer'] = 'firstname';

Transformation functions

You might need to apply a transformation function to your data, before register them in your database (for example, because in MS Excel the content is represented using a different format respect to the one required by your application/database). You can define, for each field, a transformation function. For example here we set, for the field "number_hours", table "projects", a function that replaces the value with "0" if the value is an empty string.

$csv_transform_functions['projects']['number_hours'] = 'dadabik_transform_number_hours';

function dadabik_transform_number_hours($value) {

if ($value === ''){

$value = '0';

}

return $value;

}

Please note that you must always return a string (as it happens when you submit an html form):

return 0 → NO
return '0' → YES

Preview transformation functions

After the upload, DaDaBIK displays a preview of the data that is about to be imported. For each field, you can define a preview transformation rule if you need to show the data in a particular format in the preview.

$csv_format_preview_functions['projects']['number_hours'] = 'dadabik_format_preview_number_hours';

function dadabik_format_preview_number_hours($value) { ……. }

Max rows

By default, DaDaBIK tries to import all the rows available in your data file. However, you can set a maximum number of rows. In case the maximum number of rows is reached, an error is generated and the import process is cancelled.

$csv_max_rows['customers'] = 250;

Synchronize your database with MS Excel/CSV/ODS (Platinum only)

By default, DaDaBIK process all the rows of your data file and for each row executes an INSERT statement to add the row as a record in your database. However, you might want to synchronize your table with the data file, i.e. UPDATE the records in case in the data file there is a row that corresponds to an existing record. To enable this feature, you should set your $enable_import_csv_synch config parameter to 1. If the feature is enabled, DaDaBIK, during the import process, checks if the row is already available in the table: if it's not available, the row is inserted as a new record; if it's already available, the corresponding record is updated using the values available in the row.

How can DaDaBIK recognize if a row in the data file is exactly the record we already have in the database? Let's make an example. We have an employees table:

employees (id, name, email, office, desk_number)

id is the unique field set in DaDaBIK for the table employees. DaDaBIK, by default will use such field to understand if the employee is importing is already available in the employees table.

However, you can choose your own field and even a combination of fields. For example here I will use the combination of
office + desk number
as my unique field for synchronization.

$csv_check_uniqueness_fields['employees'][] = 'office';
$csv_check_uniqueness_fields['employees'][] = 'desk_number';

DaDaBIK will alert you during the import process if some records will be updated (because they already exist according to your unique field) and you can decide if you want to proceed or to cancel the process. When an update operation is executed (instead of an insert operation), all and only the fields for which you have edit permissions set to YES will be considered from your data file.

SOME ADDITIONAL NOTES:

  • The data file you upload is stored by default in the uploads_import folder and then deleted when the process is completed. In case the import process is not correctly completed, the uploaded file might remain in the folder. For this reason, you should secure your uploads_import folder so that it's impossible to access to the files even if you know the URL. Please refer to the security section to see how to properly set upload_directory and upload_import_directory.
  • All the database operations executed during an import process (several insert statements and other operations) are wrapped into a database transaction, this means that even if one of the operation fails for any reason, also the other operations will be cancelled and the database will be reverted to its original state. If, however, your insert process also executes non-db operations (e.g. you send an email through the email notice or an after insert hook), those operations are all executed until the actual error arises (i.e. for some of the rows). This is valid also when the number of rows set with $csv_max_rows is reached.
  • If, in your custom functions, you need to detect the function used (e.g. for a custom validation where you distinguish between "insert" and "update" validation), the function name to use is "import_from_csv"
  • If for a field, in form configurator, you set the "prefix", a data file's cell containing just the prefix will be considered blank, as it happens with normal insert operations.
  • generic_file and image_file fields are not token into consideration during the import process, even if you have the corresponding columns in your data file; the corresponding PHP array's element $_FILES[field_name_here]['name'] is set to empty string.
  • For dates, a yyyy-mm-dd format is expected, but if in Excel the column is correctly set as "date" and you don't apply any transformation rule, any format is fine.
  • For select_single* and select_multiple* lookup fields, DaDaBIK expects to receive the value of the primary key, not the value of the linked field. So if you have a products table:

    products (id_product, name_product, id_brand)

    where id_brand is a select_single lookup field, DaDaBIK expects to receive, for example, "5" (the id of the brand), and not "Apple".
  • For seelct_multiple* fields, DaDaBIK expects to receive the options selected in this form:

    "~option_1~option_2~….~option_N~"

    where ~ is the character you set as FORM_CONFIGURATOR_SEPARATOR in config_custom (by default, "~"). The use of the import feature on a table that is a details table in a master/details form is not currently supported (even if the table is displayed as a "normal" table in the menu).
  • Records that are in your DB and are NOT in your Excel/CSV/ODS file are kept as they are, DaDaBIK does not delete any record during the import process.

[back to top]

12. DaDaBIK Wordpress Integration


DaDaBIK is released together with a Wordpress plug-in which allows to integrate a DaDaBIK application into a Wordpress site's page.

Furthermore, users authenticated through Wordpress can also be (optionally) automatically authenticated into DaDaBIK too, without logging-in again. The Wordpress authentication feature requires that the DaDABIK application is installed in a subdirectory of the Wordpress site (same domain) and that the same users (same username) is available both in Wordpress and DaDaBIK.

Follow the instruction included in the wordpress_plugin folder to install and configure the plugin.

In case the area of your DaDaBIK app, in the Wordpress page, seems to be too small, check if you are experiencing the issue described here: https://dadabik.com/forum/read.php?1,23793,23804

In this video tutorial, you will see, step-by-step, how to use DaDaBIK to add No-Code CRUD capabilities and auto-updatable Charts to your WordPress site.



[back to top]

13. HTTP API

(DaDaBIK Platinum only)


You can access a DaDaBIK application also through an HTTP API. This allows authorized external applications / Web sites to access the data managed by a DaDaBIK application. Such external applications / Web sites can be build using any kind of technology / programming language (PHP, Python, Java ...), they can access your DaDaBIK application simply by calling an URL and specifying some parameters, DaDaBIK will reply with a JSON document.

To use the HTTP API, you have to set $enable_http_api = 1 in config.php and, optionally, set the users you want to enable the API for using the $enable_rest_api_user_ids config.php parameter.

At the moment, the API has two available methods: authentication and selection.

Authentication

This method allows you to get a token that you can then use for selections and, in future, for other operations. The token grants, until its  (configurable) expiration, all the privileges the corresponding user has.

URL: login.php?function=get_token

METHOD: POST

PARAMETERS:

username_user: the username of the user you want to authenticate

password_user: the password of the user you want to authenticate

RESPONSE

A JSON document containing the following attributes:

  • status: regardless of the response, its value must be "ok", otherwise an unhandled error occurred.
  • result: possible values:
    • authentication error: username/password not correct or user not enabled for HTTP API
    • account blocked error: username blocked after too many wrong authentication attempts
    • input error: username and/or password are empty
    • general error:: any other error
    • done: success, a token has been generated
  • error_message: if an error occurred, a more detailed error message
  • token_value: the alphanumeric token generated

SELECTION

This method allows you to get records from a specific table / view, specifying search and sort criteria.

URL: index.php?function=search&tablename=[YOURTABLENAME]&execute_search=1&from_api=1&export_to_json=1&records_per_page=[NUMBEROFRECORDS]&page=[PAGENUMBER, STARTING FROM 0]&order=[ORDER BY FIELD]&order_type=[ORDER TYPE: ASC OR DESC]

METHOD: POST

ADDITIONAL PARAMETERS:

operator: must be and or or, it's the boolean operator you want to use to connect your search criteria
username_user: the username of the user you want to use to execute the selection
token: the token you got with the authentication
dadabik_____check_post: must always be "1" (the first part of the name, "dadabik_____" depends on $alias_prefix and $prefix_internal_table in your config.php, if you leave the default values, it's dadabik_____)
search criteria:
here you can specify a list of attribute name and value + a list of search operators, one for each attribute.

Example of request, get all the customers from Berlin having age > 30, order by id_customer DESC, get 100 records per page and show me the first page

<form method="POST" action="index.php?function=search&tablename=customers&execute_search=1&from_api=1&export_to_json=1&records_per_page=100&page=0&order=id_customer&order_type=desc">

<input type="hidden" name ="operator" value="and">
<input type="hidden" name ="city_customer__select_type" value="is_equal">
<input type="hidden" name ="city_customer" value="Berlin">
<input type="hidden" name ="age_customer__select_type" value="greater_than">
<input type="hidden" name ="age_customer" value="30">
<input type="hidden" name ="username_user" value="root">
<input type="hidden" name ="token" value="124c5288d857eecf72284cbe23efff6026">
<input type="hidden" name ="dadabik_____check_post" value="1">
<input type="submit" value="View Records">
</form>

The suffix "__select_type" reflects what you set as $select_type_select_suffix in config.php, if you leave the default value, it's "__select_type".

RESPONSE

A JSON document containing the following attributes:

    status: regardless of the response, its value must be "ok", otherwise an unhandled error occurred.
    total_rows: the total number of records corresponding to your search criteria
    rows: an array of results record; for each record you get, for each record's attribute, its value

A few notes about how the values of a record's attributes are represented in the Json document:

  • you get all the attributes that, for the user specified in username_user, are available in the details page of a record
  • you get all the rows that the user specified in username_user is allowed to see (so ownership permissions, for example, apply)
  • custom formatting functions are not applied
  • standard formatting functions are not applied (e.g. an email field is displayed in a DaDaBIK app using a mailto: link, in Json just as text); only dates are formatted according to the config.php parameter $date_format
  • NULL values are correctly represented as NULL, except when they are related to linked fields, in that case they are represented as an empty string

In case an handled error occurred, you will get a different response:

    status: regardless of the response, its value must be "ok", otherwise an unhandled error occurred.
    result: "generic_error"
    error_message: the actual error message

In this video, you will see, step by step, how to use HTTP API.



VIEW CUSTOM PAGES

Since V. 11.9, it is also possible to access via HTTP a DaDaBIK custom page, simply by adding to its URL:

&from_api=1

and passing username_user, token and dadabik_____check_post as you do for SELECTION. The response is handled by your (custom) code but DaDaBIK, according to the corresponding user's permissions, grants (or not) the user the possibility to access the page.

14. Audit/Revisions

(DaDaBIK Platinum only)


This feature allows to track the history of changes related to a record. You can enable it for a table/view from the pages tab, admin section. After any insert, update or delete operation, the current version of the record involved is registered in a table, together with date/time information and the user who executed the query.

Admin users can see the list of revisions by clicking on the "show revisions" button: the button under a results grid to show all the revisions for a table, the button under a record edit form to only see the revisions related to that record.

Please note that:

  • To correctly track insert operations, you NEED an auto_increment dadabik unique field (typically, it's the primary key) for the table you want to track revisions. If, despite the fact you have this auto_increment field, you specify the value of the field during an insert, the revision feature could work no correctly and track the wrong ID. If you use PostgreSQL, the unique field doesn't need to be auto increment.
  • In case of master/details view, the audit/revisions feature treats separately each of the tables involved.
  • Operations executed by custom code are not audited.
  • Operations executed on the users and groups tables are not audited.
  • Operations executed automatically by your DBMS (e.g. a "cascade" delete that your DBMS executes because you set a referential integrity constraint) are not audited.
  • If you change the primary key/unique field for your table or you change the primary key/unique field value for a record, you will lose the link with the related revisions.
  • If an update operation changes the primary key/unique field value for a record, the revision will not be correctly registered: it could be NOT registered or registered as linked to another (wrong) record.
  • If $delete_files_when_delete_record is 1 in config.php, after a deletion you loose the files (if any) related to the record, even if revisions are enabled.
  • The first time you execute an operation on a table, a dadabik_revision table is created (e.g. for a customers table, a dadabik_revisions_customers table is created). Such revision table reflects the current schema of the main table, if you change the schema of the main table by adding/removing fields, you should manually adjust the schema of the revisions table; if you change the schema of the main table by changing field types, you should also adjust the schema of the revisions table, unless you set $use_text_fields_for_revisions_table = 1.
  • The first time you execute an operation on a table, a dadabik_revision table is created (e.g. for a customers table, a dadabik_revisions_customers table is created). Such revision table and its content (the "history" of the records) is not deleted automatically when you disable revisions for the related table or if you drop the related table.

15. Live edit


Starting from V. 11 you can edit records "live", from the results grid, without opening a record in edit mode. To enable/disable this feature, you have to set the config parameter $enable_live_edit (by default, starting from V. 11.2, it is enabled).
You can easily change the value of a cell: double click the cell, modify its value and then press enter (or press ESC to cancel the edit). This is a first version of the live edit feature, having some limitations that we will resolve in the future:

  1. Live edit is not optimized for mobile devices (or in general for "touch" devices)
  2. It only works if you enable authentication and granular permissions (by default, they are both enabled)
  3. It only works for some field types (text, textarea, non-lookup select_single and select_single_radio, date, date_time), in a table where you don't use custom validation functions, custom display/required functions and calculated fields. In all the other cases, if you double click a cell a message will invite you to edit its value using the traditional "edit record" approach.
  4. There is no checkbox for setting a cell as "NULL", so you should keep the default values for $treat_blank_as_null (enabled) and $null_checkbox (disabled)
  5. No tooltips/hints will be displayed during live edit
  6. The "Other" option for select_single/select_single_radio fields is not available during live edit
  7. It doesn't work for grids in a master/details view
  8. It doesn't work for the users and groups tables

Apart from these limitations, live edit works exactly as the "standard" edit: values are validated (standard validation e.g. email validation), before and after update hooks are executed, email notices are sent, "record locking" rules are respected.

Starting from V. 11.2, you can also decide to enable live edit only for specific fields, using the $allowed_fields_live_edit parameter; this parameter also allows you to override some live edit limitations (i.e. in a table where you use custom validation functions, custom display/required functions or calculated fields). Let's see some examples:

$enable_live_edit = 1;

The above is the default situation, live edit is enabled for all the fields where it is possible to enable it (considering the limitations explained before).

$enable_live_edit = 0;

Live edit is disabled.

$enable_live_edit = 1;
$allowed_fields_live_edit['customers']['name_customers'] = 1;
$allowed_fields_live_edit['customers']['address_customers'] = 1;

In the example above, since there is at least one element for $allowed_fields_live_edit, DaDaBIK will allow live edit only on the table customers, for the fields name_customer and address_customer (again, if they respect the limitation we discussed before).

$enable_live_edit = 1;
$allowed_fields_live_edit['customers']['name_customers'] = 1;
$allowed_fields_live_edit['customers']['address_customers'] = 1;
$allowed_fields_live_edit['products']['name_product'] = 2;

In this last example, in addition to name_customer and address_customer, also name_product (table products) is allowed and since we set the corresponding element to 2, DaDaBIK will allow live edit EVEN if the products table has custom validation functions, custom display/required functions or calculated fields.

Using the value 2 can be dangerous but if you know what you are doing you have a lot of flexibility. Let's try to understand why, in general, live edit is not allowed if you have custom validation functions, custom display/required functions or calculated fields. Your custom functions, when used in standard insert/edit form, might need to access the values of one or more fields you have in the form. These values are NOT available when you are in the results grid, so this can lead to errors and unexpected behaviour. Furthermore, your custom functions might trigger a value change in a form field or make a field disappear (think about calculated fields or conditional fields): this at the moment can't be reproduced on the results grid so, again, it can lead to errors and unexpected behaviour.
However, there are cases where it is safe to use live edit even if you are in one of those "forbidden" situations. For example let's say you have a calculated field price_tax_product, that is calculated as price_product + taxes and let's assume for simplicity that it's the only custom function you use on the products table. Since you use this calculated field on the products table, DaDaBIK will forbid the user of live edit on ALL the products fields, however, for example, it would be totally safe to use live edit on name_product so you could set 2 for $allowed_fields_live_edit['products']['name_product'].

In this video, you will learn how live edit works in DaDaBIK.



16. Language translations


Front-end translation

DaDaBIK has been translated into 22 languages.  Some of the language translations are incomplete. If you open your language file (e.g. include/languages/spanish.php ) you can find the sentences not translated looking at the // to change comments at the end of each file row. You can complete the translation if you want. You are more than welcome to contribute to the localization sending back to info [at] dadabik [dot] org your improved translation.

A better way to modify a translation if you don't like the original one, however, is to use, instead of the original file (e.g. english.php) the corresponding custom file (english_custom.php); this is very useful to avoid losing your custom translations during the upgrade of DaDaBIK. It is also possible to differentiate your custom translations according to the table the user is using. Open the file include/languages/english_custom.php to read the simple instructions you need to override an original translation.

Adding a new language translation is also pretty straightforward. For example, imagine you want to add Hindi: you just need to create a copy of the file /include/languages/english.php, rename it as hindi.php, and edit all the sentences of the file (for each row, the quoted part after the arrow). You can then add the language to the list of available choices adding 'hindi' to the parameter $languages_ar is /include/config.php.

Multi-language support for labels, hints and tooltips

Starting from DaDaBIK 10, not only the general front-end interface is localized, but you can also specify multiple translations for your form field labels, hints and tooltips. For example, if you have a customers form having a name field, you may want to display name as label if the user chooses English as language, nome if the user chooses Italian and so on: in this case, for the label parameter of the form configurator you will specify something like:

EN:name~IT:nome~DE:name~ES:nombre (assuming your form configurator separator is ~, the default one)

In order to use this feature, you have to enable it from config.php, setting $enable_multilanguage_labels = 1; you can also speficy a default translation with $default_language_missing_translation.

In this video you will see some advanced uses of language translations in DaDaBIK.



[back to top]

17. Performances


DaDaBIK has been designed to provide good performances even when you need to manage a huge amount of data. For an example, look at the online demo n.3 (ERP demo) and in particular try to browse the reviews table, containing one million records and two lookup fields.

The performances of a DaDaBIK applications mainly depends:

1) On the database (and DBMS) the application is based on: if the SQL queries that DaDaBIK needs to execute are too slow, of course to the final user will perceive the application as slow. At this level, to increase the performances, you can:

  • Optimize the design of your database, for example by adding indexes and/or modifying the way you spread information through your tables.
  • Use a machine providing better performances

2) On the complexity of the application you have developed: in particular the total number of table fields and user groups you have in your application and the number of lookup fields you have in the table you are managing can impact on the performances. At this level, to optimize the performances, you can:

  • (of course) reduce the number of fields, user groups and lookup fields
  • set $always_refresh_permissions = 0 (see config.php for further details)
  • move to PHP7: if you are still using PHP 5, please note that DaDaBIK takes advantage of PHP 7 and guarantees better performance running with it

[back to top]

18. Security


Here is some security-related information which you must know:

  • If your DaDaBIK instance is public and you have disabled authentication (by default IS enabled), please consider disabling the export to CSV feature from /include/config.php because robots, accessing the CSV export link, could consume an inordinate amount of processor time.
  • You should have session.auto_start = 0 in your php.ini (by default, it's 0). If you set it to 1, DaDaBIK cannot set a custom session name and cookie path and this could make your application less secure. Starting from V. 10.4, DaDaBIK shows a warning if you have session.auto_start = 1. Before V 10.4, with session.auto_start = 0 DaDaBIK could also have unexpected behaviour.
  • As upload_directory and upload_import_directory, you should choose a folder that is above the WEB DOCUMENT ROOT or at least PROTECT the directory from public access, otherwise anyone who knows the URL of a file you have uploaded via DaDaBIK can access the file itself, even without logging-in. By default, the /uploads folder contains an .htaccess file which should prevent public access on Apache, please test it because it might not work in your environment; if you change the default upload directory or use a different Web server than Apache you should provide the needed security by yourself.
  • If you use SQLite, you should store your SQLite database file outside the Web root or protect it from public acces, otherwise anyone who knows the URL of the file can access it, even without logging-in.
  • You should protect the templates directory from public access, otherwise anyone who knows the URL of a template file can access the file itself, even without logging-in. Normally template files don't contain private information but they could. By default, the /templates folder contains an .htaccess file which should prevent public access on Apache, please test it because it might not work in your environment; if you use a different Web server than Apache you should provide the needed security by yourself.
  • After having installed and configured a DaDaBIK application, it's a good practice to remove the files install.php, install2.php, upgrade.php, upgrade2.php, up.2 and up2.php since they could be used by malicious users in order to change or even re-install your DaDaBIK installation.
  • The files admin.php, datagrid_configurator.php, db_synchro.php, internal_table_manager.php, permissions_manager.php and tables_inclusion.php can also be used to modify your application. If authentication is enabled, the access to the files will be protected, otherwise you should remove those files. For additional security, you can however remove the files even if authentication is enabled.
  • The HTML content type and the rich_editor field type can lead to high security risks: DaDaBIK uses the popular htmLawed library to prevent displaying of dangerous HTML/Javascript code but, as the authors state, (http://www.bioinformatics.org/phplabware/internal_utilities/htmLawed/htmLawed_README.htm), there are some minor cases in which htmLawed can fail. If a malicious user insert some arbitrary javascript code and the library fails in recognizing it, the code can be then executed by other users just by using the application. Among other problems, this can lead to XSS attack (http://en.wikipedia.org/wiki/Cross-site_scripting), which in turn can allow an unauthorized access to the application (http://en.wikipedia.org/wiki/Session_hijacking) and, if the Internet browser of the user contains security holes, even the execution of arbitrary code in the client machine.
  • If you set rules to limit the records the admin group can see (for example using the ownership permissions or the row-level custom rules), please note that, using the form configurator live preview, an admin user might see a record he/she is not allowed to see. This, normally, doesn't represent a problem because typically admin users don't have limits/filters on the records they can see.

Other security-related issues are described in the section Known bugs, limitations and unexpected behaviors

[back to top]

19. Backup


If you want to do a backup of your DaDaBIK application, you have to:

  1. Copy everything you have in your DaDaBIK folder (the one containing index.php) and its subfolder (you could create a ZIP file of the entire folder and put it in a safe place)
  2. Create a database dump of your entire database (for MySQL, for example, you can use mysqldump or any other similar utility)

Files + database allow you, in case you need it, to restore your DaDaBIK application exactly in the situation it was when you made the backup.

During the execution of the backup, nobody (neither admins nor final users) should use your application or your database

[back to top]

20. Best Practices


In these blog posts (part 1 and part 2) you can find some best practices for the development of Web applications using DaDaBIK.

[back to top]

21. FAQs


Please read the FAQ document.

[back to top]

22. Known bugs, limitations and unexpected behaviors


Please check also the Bugs forum to see the latest bugs discovered.

  • UTF-8 related:
    • DaDaBIK supports UTF-8; however, malformed UTF-8 characters can lead to unexpected results such as content corruption. The use of the HTML content type or of the rich text editor, in combination with UTF-8 content, is another factor that could lead, theoretically, to unexpected results.
  • Security/data-integrity related:
    • Malicious users could exploit a field with content type set to HTML or field type set to rich_edit to insert arbitrary javascript code, this can lead to security risks if the htmLawed library doesn't filter this content properly (see above, security section, for all the details).
  • Table and field names related:
    • The use, in a DaDaBIK application, of tables and fields whose names is longer than 64 characters or contain quote characters (such as ' ` "), blank spaces, semicolumns, dots and characters which need to be escaped (such as \, NULL byte) can lead to problems and security issues. The same for field names containing the value set in config.php for the parameters $alias_prefix, $null_checkbox_prefix, $select_type_select_suffix, $select_checkbox_prefix, $field_button_hint_container_id_prefix, $custom_button_ids_prefix. DaDaBIK, in case you are using table or field names containing such characters, displays a warning message; in some cases, the use of such characters can generate a query execution error.
  • Primary Key / Unique field related:
    • DaDaBIK requires, for each table you want to use, a "unique field" (see admin -> pages). Typically, this unique field is the primary key of the table. If your records, for that field, contains characters which need to be escaped (such as single quote, double quote, backslash or the NUL byte), you can experience problems and security issues. Here we are not referring to the field name (as in the previous point) but to the value stored in a field. The same rule applies to the values of the $users_table_id_field field, in case you are using a custom users table.
    • The $enable_lookup_insert_popup = 1 feature only works if the "linked table" has an auto-increment primary key (that is also set as the unique field in the DaDaBIK -> Admin -> Pages page). In case you use PostgreSQL, however, it works even if the field is not auto-increment.
  • User interface related:
    • Some language translations are not completed. If you open your language file (e.g. include/languages/spanish.php ) you can find the sentences not translated looking at the // to change comments at the end of each file row. You can complete the translation if you want.
    • The rich editor interface (TinyMCE) is displayed in english only.
  • PosgreSQL related:
    • DaDaBIK needs table names in lower case to work on PostgreSQL.
    • If you use cascade fields and the cascade parent field is a numeric field, you might get a "SQLSTATE[22P02]: Invalid text representation" error.
    • You can't use, for a DaDaBIK application, tables/views coming from different schemas, they need to be in the same database/schema.
    • The value you see in the "extra" column (admin, data tab), at the moment is still not 100% reliable, e.g. you can see the value empty even if the field is actully serial; we suggest to always check if a field is serial also from a db administration tool.
  • MySQL related:
    • DaDaBIK doesn't support the BIT MySQL field type.
    • If you use a MySQL version < 8 and you set Data types: simple in your Data -> settings page, you might see, in the Data page, the field type as blank for integer fields.
  • MS SQL Server related:
    • You cannot specify the schema to use inside your database. When you build a DaDaBIK application on MS SQL Server, you specify a database and DaDaBIK, by default, reads all the tables belonging to all the schemas (you can, however, exclude tables by using the $tables_to_exclude setting in config.php). This also means that you cannot have two tables having the same name in two different schemas of the same database.
  • Admin section related:
    • The field renaming feature of the administration page doesn't work as expected if the renamed field is used in one of the following properties: "Primary key field", "Linked fields", "Order by", "Where clause" in the forms configurator related to any of the tables. In particular, after the renaming, you will get "[08] Error: during query execution" messages from the DaDaBIK front end; you should edit the above properties by hand, renaming the field in the forms configurator.
    • The "refresh installation" admin operation doesn't work correctly and could lead to a forms configurator settings loss if the installation is the result of an upgrade from a DaDaBIK version < 4.1 rc2; the "refresh installation" bug is considered fixed only for tables installed (DaDaBIK installation from scratch or single table installation from the admin interface) using a DaDaBIK version >= 4.1 rc2.
  • WordPress related:
    • A bug related to the loss of DaDaBIK session when used with the WordPress plugin is explain in detail here.
  • Others:
    • If a user has insert or edit permissions on a table and the table contains at least a field set as image_file or generic_file, the user may be able to upload a file even if they don't have edit/insert permission on the file field. The file would be stored only in the file system, in the system temporary upload folder and in the dadabik upload folder, without be recorded in the related record.
    • The "previous" and "next" buttons don't work correctly after this sequence of operations: - perform a search based on a field A - change the value the field A for a record of the result set. Fore more details about previous/next and page navigation unexpected behaviour, check the next chapter: "Previous/Next buttons and page navigation bar, unexpected behavior".
    • Database BIT and Boolean field types at the moment are not fully supported, use INT (1/0) or char(Y/N) instead.
    • If you execute a standard search or a search by URL using a select_multiple field, the related field in the quick search filter is not properly filled with the value you used.
    • If you have two (ore more) select_single* fields in the same form having the same lookup table and you add a new option using the popup insert ($enable_lookup_insert_popup = 1), the new option will be immediately available for the listbox you are working on but not for the other ones (they will be available after having reloaded the page).
    • DaDaBIK needs a "unique field" to identify each single record in a table. Typically, this field is the primary key of the table but if the primary key is composed by multiple columns, DaDaBIK cannot use it, you have to add an additional unique field (or, for MySQL, let DaDaBIK add such field, see $add_additional_dadabik_id_field in config.php).
    • Upload and deletion of multiple files during the same insert/update/delete operation are not considered as a unique atomic operation: e.g. if you are uploading multiple files from the same form and one of the uploads fails, other files already uploaded will result available in the file system.
    • The "check for duplicated" feature doesn't work with file field types: DaDaBIK doesn't warn about similarity when two files have a similar name
    • The "check for duplicated" feature causes uploads to fail during insert, in particular the files are not uploaded if some possible duplicated records are found and the user decides to insert it anyway. This, however, happens only if "classic" upload is set, if you leave the default value for $upload_field_type default ("ajax"), the bug does not occur.
    • The "check for duplicated" feature doesn't work with date, date_time, insert_date and update_date field typed and with all fields which are hidden in the insert form.
    • HTML content is not set correctly as "Default value" (form configurator).
    • When a new option of a select_single field with linked fields is inserted through the "other..." option, the current user is not inserted in the ID_user field of the linked table. This means that the new record in the linked table won't get an owner and the  owner permissions won't work as expected.
    • If, while editing a record, you change the value of the primary key, DaDaBIK is not able to show you the updated record after saving.
    • If an insert/edit form needs to be re-displayed because an error occurred (e.g. a missing required field), FILE fields (only file fields) don't preserve the values set by the user before submitting the form
    • The "insert as new" feature doesn't work for generic_file / image_file fields and if the record is a master of a master/details view, the details records are not inserted.
    • If an image_file or generic_file field is used as a linked field for a lookup field, the file is not showed if the user doesn't have read permissions on the corresponding linked table.
    • Records selection checkboxes are not displayed  for the results grid in a master/details view.
    • The "DATA" tab in the admin section, introduced with DaDaBIK 10, at the moment has been implemented for MySQL, PostgreSQL and MS SQL Server, not for SQLite.
    • The revision feature, introduced with DaDaBIK 10, at the moment has been implemented for MySQL, PostgreSQL and SQLite, not for MS SQL Server.
    • The default value parameter in form configurator does not work for select_single and select_single_radio fields if you open the an insert form as the details part of a master/details form.
    • When you add a record to a lookup table using the + sign close to the lookup field, if a possible duplication is detected, the process is not correctly completed (the record is inserted but the lookup field in the underlying form is not updated).
  • Unexpected behaviors (not really DaDaBIK bugs but behaviors which the average user doesn't expect)
    • DaDaBIK relies on the $timezone parameter for date functions (e.g. to produce the current date), which can be different from the client's (user) one.
    • The value actually inserted in the database during an insert/update, if the value typed in the form is not part of the domain specified during table creation (e.g. integer, varchar,...), depends on the DBMS settings and on the domain itself. For example if an integer field is left blank during insert/update (not NULL, blank), MySQL could insert 0 as value, because an empty string is not part of the integer domain and very often the default value for integers is set to 0.
    • Formatting tags automatically inserted in a field through the HTML editor are not filtered during search so if you search "hey good" and you have "<b>hey</b> good morning" in your field you won't find it.
    • If you set a master/details view between table A (master) and table B (details) and you perform a search on B and then access (in edit or details mode) a record of the table A, you will then lose the search filter you had in B.
    • The Wordpress and DaDaBIK session expirations are not synchronized: if your session expires in Wordpress it doesn't mean it automatically expires in DaDaBIK. Use the logout button in Wordpress to be sure to disconnect to both Wordpress and DaDaBIK
    • If you have enabled the Wordpress authentication, you should login/logout through Wordpress without using DaDaBIK login/lougout; mixing Wordpress and DaDaBIK login/logout can lead to unexpected behaviours.
    • If an error occurs during installation or upgrade, the installation of DaDaBIK can ends up in a inconsistent state due to implicit commit policies of the DBMS.
    • Old versions of Microsoft Excel for Mac can't correctly represent UTF-8 characters for CSV files produced by DaDaBIK
[back to top]

23. Previous/Next buttons and page navigation bar, unexpected behavior


How do the "previous" and "next" record buttons work? How does the page navigation bar ("Page x of y") work?

It's important to understand how these features work because their behaviour could be unexpected for the final users of your application.

Let's start with the "previous" and "next" buttons, the buttons you can see when you click on the "edit" or "details" icon in the result grid to access, one by one, the records beloning to the current result set.

Let's say you have a table "cars" and you want to edit (or just see, one by one) all the cars having a price < 15000.

Imagine what you would do if your database was on paper (one paper sheet for each car): you could take from the folder all the sheets related to cars having price < 15000 (let's say there are 25 cars), modify them by changing some data (for example the price) and then put them back in the folder. When you take those 25 sheets, you are sure you are going through all the cars having price < 15000 and while you are working, nobody can take your 25 sheets or modify them.

What happens in your DaDaBIK app is a bit different. Let's say you search for cars having a price < 15000, you get your results (25 records), your order by price, you enter the first record (in edit mode), you modify the first record, then you click on "next", you modify the second record and so and so fourth. The total number of records you will go through could be 25 as expected but could also be less than 25 or more than 25. Why?

When you click on "next", the system re-executes the search query, find the position of the record you are viewing in the current recordset and then opens the record that, considering the current sorting, is immediately after that. If, while you are modifying the first record, someone adds a car having price < 15000, you could see this new record as a second, instead of the record you expected to see and you will finally go through 26 records instead of 25. If, while you are modifying the first record, someone deletes the second record, you will finally go through 24 records instead of 25. These two scenarios, however, are not problematic.

Let's see another case that is much more problematic: you modify the price of the first car, it was 7000 (the cheaper car, so the first record) and you change it to 145000 (one of the most expensive, let's say the 23th car): when you click on "next", you will see record 24 and then record 25 and you won't go through all the records between the 2nd and 22th.

Consider that on the recordset (cars having a price < 15000) you are working on, other users could also work at the same time (DaDaBIK provides a locking mechanism, but it prevents other users to edit the record you are working on, not an entire recordset you are working on!) so while you are editing the 3rd record, another user could change the price of the most expensive car (25th record) and make it the cheaper (1st record) so you won't see such modified car by using the "next" button, because you are already on record 3.

This is just an example but there are other cases of EDIT (made by the current user or other users) that can modify the current result set or the position of the records in the set and therefore if you are viewing, one by one, all the records belonging to your results set ("NEXT", "NEXT", "NEXT", ...), you could skip some of the records or you could see the same record twice.

This is not something easy to fix. In order to replicate what happens in the "physical" world, we should lock all the records belonging to your results set; this can be problematic, though, because maybe they are not free to be locked (other users are using them) and your results set could be so big that you need to lock the entire table.

The only safe way to work with "previous" "next" if your goal is to review all the records belonging to a results set is to sort the result by a field that nobody can modify and that you are not filtering on. Typically this field is the primary key of the table. It can still happen that some records disappear (because other users delete them) or some additional records appear (because other users add them) but at least you are sure you are not skipping records matching your search filter.

How about the page navigation bar? The behaviour is similar.

Let's say, again, that you search for cars having price < 15000, you get 25 results and you have 10 record per page. You want to give a short look at all the records, starting from the cheaper (you sort by price) without entering the edit/details pages; you stay on the results grid, you use the navigation bar and you assume you will see al the records.

After having read the first page (first 10 records) you click on page 2. While you are reading page 1, however, another user changes the price of the 11th car, which is now cheaper and gets position 10. Therefore, after having clicked on "page 2" link, you wont see the original 11th record, you will see again the 10th, and you will never see the original 11th (which is now on page 1).
Again, this is just an example but modifications made on the records belonging to the current result set can have an impact on the records you will see by clicking on the pages link. [back to top]


The content of this manual is copyrighted by Eugenio Tacchini

Top