Database Select Queries

The "mysql_select" action selects data from database using MySQL query and puts it to specified fields with corresponding names.

This action supports "SELECT" queries only. If you want to update or insert data to MySQL database, use MySQL Database Storage action.

Syntax:

mysql_select query[; error message]

query -  mysql SELECT query.

error message -  optional parameter. If present, will be shown on the form in Error_Message_Block if query returns 0 records. If not present, form processing will be continued even if 0 records were returned.

Example:

mysql_select SELECT  `country`, `city`, `street`, `phone` FROM `form_table`  WHERE `name` = '{#name#}' AND `surname` = {#surname#}

Result:

This query makes selection from the "form_table" table where 'name' and 'surname' values matches submitted fields, and puts data from the first matched result to "country", "city", "street", "phone" fields. You can use these fields later in templates and form pages as if they where inputted by user (e.g.: {#country#}, {#city#}).

Example showing two forms with add record and edit record functions:

Let's say we have a small form with following fields:

name

surname

country

city

street

phone

This form has following query in configuration file:

mysql_query = INSERT INTO `form_table` (`name`, `surname` , `country`, `city`, `street`, `phone`) VALUES ('{#name#}', '{#surname#}', '{#country#}', '{#city#}', '{#street#}', '{#phone#}', )

User can submit this form and his information will be added to the database by mysql_query action.

Now lets think we have a form where user can edit his record.

This form should have 3 pages: login page, edit page and thank you page

Login page can contain 2 fields (or any other number of fields), these fields will be used to identify the correct record in the database.

Let's think we have login page with fields:

name

surname

So user can restore his data using his name and surname.

Second page has following fields:

name

surname

country

city

street

phone

In config file we have:

mysql_select = SELECT  `country`, `city`, `street`, `phone` FROM `form_table`  WHERE `name` = '{#name#}' AND `surname` = {#surname#}; Error: no such name/surname in the database

mysql_query = UPDATE  `form_table`  SET `name` = '{#name#}', `surname` = '{#surname#}', `country` = '{#country#}', `city` = '{#city#}', `street` = '{#street#}', `phone` = '{#phone#}'

First query loads user information (`country`, `city`, `street`, `phone`) from the database record which contains user's name and surname. If record with such name and surname doesn't exist the error message after ';' sign will be shown and form will not go to edit page.

If record was loaded, user will go to edit page with editable fields filled with information from the database.

When user submits edit page, second MySQL query runs and updates record in the database.