Online Chat
 Call Us: 
1-877-744-1221
Browse Submit a Ticket
 
Advanced Search
Tools
Rss Categories

Database Select Queries

Author: Eileen Wilde Reference Number: AA-00156 Views: 11998 Last Updated: 09/15/2015 05:25 AM 0 Rating/ Voters

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

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

If you want to select and update several entries of your MySQL database at one time, please read the "MySQL selection and update of several entries".

Syntax:

mysql_select = query[; error message]

query -  mysql SELECT query.

error message -  optional parameter. If present, the message will be shown on the form in the "Error_Message_Block" if the query returns 0 records. If an error message is not present, form processing will continue 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 selects from the "form_table" table where 'name' and 'surname' values match submitted fields, and puts the data from the first matched result in the "country", "city", "street", and "phone" fields. You can use these fields later in templates and form pages as if they where input by the 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 the following fields:

name
surname
country
city
street
phone

The form has the following query in the configuration file:

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

The user can submit this form and his/her information will be added to the database by the mysql_query action.

Now lets consider a form where the user can edit his/her record. This form should have three pages: a login page, an edit page and a thank you page. The login page contains two fields (or any other number of fields), these fields will be used to identify the correct record in the database.Let's assume we have a login page with these two fields:

name
surname

So the user can update his/her data using his/her name and surname.

The second page has the following fields:

name
surname
country
city
street
phone

In the 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#}'

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

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

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

Note:
MySQL database queries support is only available with the following licenses: Business, World Wide, ISP.