Tools

Database Storage

Author: Eileen Wilde Reference Number: AA-00155 Views: 16096 Last Updated: 07/31/2012 03:26 PM

50 Rating/ 3 Voters

You may execute any database query after each form submission to save user input to your database.

MySQL
PostgreSQL
MS SQL
SQLite
ODBC
Oracle

MySQL

Use a “mysql_query” action to save data to a MySQL database. This action works with INSERT and UPDATE queries. If you want to get data from a MySQL database, please use the "Database Select action". If you want to select and update several entries of your MySQL database at one time, please read the section "MySQL selection and update of several entries".

Syntax:

mysql_query = <query>

“query” may be any valid MySQL query.

Example:

mysql_query = INSERT INTO mytable (name, email) VALUES ("{#name#}", "{#emai#}")[; varible_name]

The variables {#name#} and {#email#} will be changed to the user submitted data on the “name” and “email” fields.

Optional parameter varible_name allows to specify name of variable (field name) where id of inserted row will be placed after query executes. You can then show this id on thank you page or send in email. Also you can use it to build url for direct access to this record or any other usage.

To show contents of this variable use {#varible_name#}

To set MySQL hostname, username, and password use the following settings in your config.php file:

mysql_host = MySQL hostname
mysql_user = MySQL username
mysql_password = MySQL password
mysql_db = the name of MySQL database to be used

These settings are common for for all forms and must be set in the first section of config.php (global section) before the individual form configuration blocks.

PostgreSQL

PostgreSQL query executor.

Syntax:

pgsql_query = <query>

<query> is any valid PostgreSQL query.

Example:

pgsql_query = INSERT INTO test (name, email) VALUES ("{#name#}", "{#email#}");

Additional settings:

pgsql_host = PostgreSQL hostname
pgsql_user = PostgreSQL user
pgsql_password = PostgreSQL password
pgsql_db = PostgreSQL database name

MS SQL

Microsoft SQL query executor.

Syntax:

mssql_query = <query>

Where <query> is any valid Microsoft SQL query.

Example:

mssql_query = INSERT INTO test (name, email) VALUES ("{#name#}", "{#email#}");

Additional settings:

mssql_host = Microsoft SQL hostname
mssql_user = Microsoft SQL user
mssql_password = Microsoft SQL password
mssql_db = Microsoft SQL database name

SQLite

SQLite query executor.

Syntax:

sqlite_query = <query>

Where <query> is any valid SQLite query.

Example:

sqlite_query = INSERT INTO test (name, email) VALUES ("{#name#}", "{#email#}");

Additional settings:

sqlite_file = SQLite filename

ODBC

ODBC SQL query executor.

Syntax:

odbc_query = <query>

Where <query> is any valid SQL query.

Example:

odbc_query = INSERT INTO test (name, email) VALUES ("{#name#}", "{#email#}");

Additional settings:

odbc_dsn = ODBC DSN value

Oracle

Queries to an Oracle database.

First, setup database connection details:

oracle_dsn = server_address (e.g. dbs/oracle)

oracle_user = login

oracle_password = password

Then you can use oracle_query action to run queries.

Example:

oracle_query = INSERT INTO test_fpp VALUES ('{#firstname#}', '{#email#}', '{#subject#}', '{#comment#}')

Note:

Database storage support is only available with the following licenses: Business, World Wide, ISP.