The "Database Queries for Calculated Fields Form" plugin allows inserting, updating or deleting rows from the database based on the information collected by the form.

---

The "Database Queries for Calculated Fields Form" plugin allows inserting, updating or deleting rows from the database based on the information collected by the form.

The plugin allows interacting with the website database or with third-party MySQL databases.

The plugin interface is flexible. It is possible to define queries by components (table name, column names and values), or enter the query directly (INSERT INTO ...)

In addition, if the CFF form is integrated with a payment gateway, the plugin "Database queries for calculated fields form" includes an attribute to evaluate the queries when the form is submitted, or after receiving the payment confirmation from the payment gateway.

The "Database Queries for Calculated Fields Form" plugin installation is similar to other plugins in WordPress. It requires downloading the zip file with the plugin's code to your computer (visiting the download link provided after purchasing) and following the steps below::

1. Go to the Plugins section on your WordPress.
2. Press the "Add New" button at the top of the section.
3. Press the "Upload Plugin" button, and select the zipped file downloaded previously.
4. Finally, install and activate the plugin.

The plugin adds the "Calculated Fields Form > Database Queries" submenu and a new section in the form settings. Through the menu option, you can register the plugin copy to receive updates directly on the website, enter the email address for debugging where receiving error notifications, or delete the plugin and its data.

The plugin includes a new section in the CFF forms settings entitled: "Database Query Integration" to configure the database connection and define the queries.

Before the queries list, the plugin includes the "Run queries into transactions" checkbox. By ticking it, queries that run on the same database are grouped into transactions. If at least one of the queries fails, the rollback occurs to restore the database to its original state. If no error occurs, the entire set of statements is committed to the database. The transactions are supported only by InnoDB tables.

The configuration area allows you to define as many queries as needed. To configure additional queries, just click on the "New query" button. Queries are evaluated consecutively after form submission or payment confirmation.

The plugin supports three types of queries: INSERT, UPDATE, and DELETE. All of them have a common section to enable or disable the query, the "Type" attribute to select the query type (INSERT, UPDATE, DELETE), and the database connection settings.

The database connection section includes a check box to use the website database. You can also uncheck it to enter the database connection attributes, "Hostname", "Username", "Password", and "Database".

Each query type enables a specific set of attributes.

Insertion Query

• Table name: name of database table.
• Column name: name of the column to be filled.
• Field name: name of the form field whose value will be used to fill the table column.
• Default value if empty: value to use if the field does not exist or it is empty.

Update Query

It includes the same attributes as the insert query, and the "Where" section to select the rows to update.

Delete Query

Activate only the "Where" section to select the rows to delete.

As an alternative to the query components, you can check the "Custom Query" option and enter the query manually.

With queries you can refer to form fields by their tags in <%fieldname#%> format. Ex. <%fieldname1%>. Also, you can use constants:

Constants

{insert_id.0}, {insert_id.1}, {insert_id.2}, etc. allows to access the insert ids generated by the insertion queries.

Each query has an index assigned based on their position in the query list, starting at zero.

Ex. Assuming your second query (index 1) is an insertion query. And you are implementing another query that uses the insert_id generated by the second query. The constant to use in this case would be {insert_id.1}

{wpdb.insert_id} after insert, the ID generated for the AUTO_INCREMENT column. It includes only the last insertion id

{wpdb.prefix} the prefix used by WordPress in the name of database's tables, for example, the text "wp_" into the table's name "wp_posts"

{wpdb.comments} the name of Comments table

{wpdb.commentmeta} the name of Comment Metadata table

{wpdb.links} the name of Links table

{wpdb.options} the name of Options table

{wpdb.postmeta} the name of Post Metadata table

{wpdb.posts} the name of Posts table

{wpdb.terms} the name of Terms table

{wpdb.term_relationships} the name of Term Relationships table

{wpdb.term_taxonomy} the name of Term Taxonomy table

{wpdb.termmeta} the name of Term Meta table

{wpdb.usermeta} name of User Metadata table

{wpdb.users} the name of Users table

{wpdb.blogs} the name of Multisite Blogs table

For logged user:

{user.id} the id of the current user

{user.login} the username of the current user

{user.nicename} the URL-friendly name for the current user

{user.email} the email address of the current user

{user.url} the URL associated to the current user

{user.display_name} the user's name that is shown on the site for the current user

{user.first_name} the first name of current user

{user.last_name} the last name of current user

Ex. INSERT INTO {wpdb.posts} (post_title, post_status, post_author) VALUES ("<%fieldname1%>", "draft", {user.id})

Fields and submitted data

In query components	In custom queries	Description
fieldname#	<%fieldname#%>	Fields' values. Ex. fieldname1 or <%fieldname1%>
fieldname#_link	<%fieldname#_link%>	Path to the uploaded file in Upload File fields. Ex. fieldname1_link or <%fieldname1_link%>
fieldname#_url	<%fieldname#_url%>	URL to the uploaded file in Upload File fields. Ex. fieldname1_url or <%fieldname1_url%>
formid	<%formid%>	Form id
itemnumber	<%itemnumber%>	Submission id
submissiondate_mmddyyyy	<%submissiondate_mmddyyyy%>	Submission date/time in the format mm/dd/yyyy hh:mm:ss
submissiondate_ddmmyyyy	<%submissiondate_ddmmyyyy%>	Submission date/time in the format dd/mm/yyyy hh:mm:ss
final_price	<%final_price%>	Calculated price with the discount applied
coupon	<%coupon%>	Coupon/discount applied
couponcode	<%couponcode%>	Coupon code
payment_option	<%payment_option%>	Payment option or payment gateway
ipaddress	<%ipaddress%>	User IP address
from_page	<%from_page%>	URL of the page with the form

Finally, the choices "Execute query after payment confirmation" and "Log failed queries to error log file" allow you to decide when to execute the query, or to add a new record to the server's error log file each time the query fails, respectively.

Filters

database_queries_get_query allows editing the SQL query before evaluating. Receives two parameters, the SQL query and submitted data.