How to store the data sent on a different database? (Calculated Fields Form – Developer version)

The Developers version of the “Calculated Fields Form” plugin, allows storing the data submitted in a database different to the own plugin database.

To use this feature are required some basic skills, because should be edited the “cp_calculatedfieldsf_insert_in_database.php” file included in the code of plugin.

This file is a mockup to integrate the plugin with other MySQL database. The file should be modified manually because each database has its own structure, and gives to the users a total control on the process.

The file’s edition:

Open the file with the text editor your choice. There are some text editors, widely recommended for code editing, like: Notepad++, Sublime Text, Vim, Atom, UEditor.

The first section of file allows defining the constants needed for connecting with the database, and its authentication:

define( ‘DATABASE_HOST’, ” );
define( ‘DATABASE_USER’, ” );
define( ‘DATABASE_PASS’, ” );
define( ‘DATABASE_NAME’, ” );
define( ‘DATABASE_TABLE’, ” );

The name of constants are auto-descriptives:

DATABASE_HOST: hosname or IP of database server.
DATABASE_USER: username for database’s authentication.
DATABASE_PASS: password for database’s authentication.
DATABASE_NAME: database name.
DATABASE_TABLE: table name.

The plugin checks if the constants have been defined, before for running the insertion queries.

The second section to be edited by the developers, is the creation of the variables to use in the queries. The file includes some variables as a guide of section:

$field1 = mysqli_escape_string( $db_link, $params[ ‘fieldname%’ ] );
$field2 = mysqli_escape_string( $db_link, $params[ ‘fieldname%’ ] );
$field3 = mysqli_escape_string( $db_link, $params[ ‘fieldname%’ ] );

I’ll explain this section with an example. Suppose you want store in the “my_users” table, the firstname, lastname, and email of the user, submitted through the form, and the corresponding fields in the form for these information are: fieldname1, fieldname2, and fieldname3, respectively. So, the variables are created like follow:

$field1 = mysqli_escape_string( $db_link, $params[ ‘fieldname1’ ] );
$field2 = mysqli_escape_string( $db_link, $params[ ‘fieldname2’ ] );
$field3 = mysqli_escape_string( $db_link, $params[ ‘fieldname3’ ] );

In the previous code has been created a varaible for each field in the form. All fields are included in the $params array.

After create the variables, is time to generate the insertion queries. The plugin includes a mockup for a hypotetical insertion query, that must be replaced with the structure of your database and the fields created previously.

mysqli_query( $db_link, “INSERT INTO `”.DATABASE_TABLE.”` (field1, field2, field3) VALUES (‘$field1’, ‘$field2’, ‘$field3’);” );

Returning to the previous example. If the columns of the table are: firstname, lastname, and email, respectively, the query should be modified like follow:

mysqli_query( $db_link, “INSERT INTO `”.DATABASE_TABLE.”` (firstname, lastname, email) VALUES (‘$field1’, ‘$field2’, ‘$field3’);” );

Pay attention, if the type of data in a column of table is a numeric value, should be removed the quotes around the variable name. For example, suppose the form includes a DropDown field for the year of birth, that evidently is a numeric value, I will assume this is the fieldname4. So, you should create a new variable for the new field:

$field4 = mysqli_escape_string( $db_link, $params[ ‘fieldname4’ ] );

and modify the insertion query too:

mysqli_query( $db_link, “INSERT INTO `”.DATABASE_TABLE.”` (firstname, lastname, email, year) VALUES (‘$field1’, ‘$field2’, ‘$field3’,$field4);” );

The $field4 variable is not closed between quotes.

A last tip: It is possible create as many insertion queries as needed, even can use the table name directly without use the constant defined in the first section.