Mysql Selection and Update of Several Entries

This feature is a professional set of features for creating a set of forms, that will allow you to:

• add records to database
• read records and show them in a desired format
• select several records and update all of them with this form

To use this functionality, you need to create a column 'id' in your table with an auto-increment attribute. You should also set it as primary key.

For configuration of these forms you will need to use four actions:

• mysql_select
  
• mysql_select_custom
  
• mysql_batch_update
  
• mysql_select_table

To read more about mysql_select, please refer to the "Database Select Queries". Here are descriptions of other three actions you will use.

Syntax:

Where variable_name is the name of the variable where the form for editing data will be created, you can show it in any place on your page by placing {#variable_name#} into code.

path_to_template is the path to the file with the mini template, which will be duplicated for each entry.

id is the required key column in your table with the auto-increment attribute.

Example:

example of the mini template you can see below.

Syntax:

Where table_name is the name of the table.

column_name is the name of the column in your table where the value from the field variable_name will be inserted. You can specify as many columns as you need.

id = id is the WHERE clause for updating the appropriate records (this is our key column in the table).

/id(_?[0-9]{0,3})/i - regular expression for capturing indexes of our records (each record after mysql_select has it's own index).

Example:

Syntax:

Where variable_name is the name of the variable where the HTML table with selected data will be created, you can show it any place in your page by placing {#variable_name#} into the code.

query is the MySQL query for selecting records.

error message is an optional error message, which will be shown if no records are selected.

Insert form (add records to the database)

This form will collect user data and store it in the database.

To store data in the database, you need to add the mysql_query action with an insert query.

Lets say you have three fields in the form to store in the database: firstname, lastname, and email

To store this data you need to create a database and in the database a table with columns:

id (with an 'Extra' attribute 'auto_increment', this field should be a key field. This field will be needed later), firstname, lastname, email.

For example you created a table with the name "form_table".

Now you have a table where you can store data. As you can see above, you have to give table columns the same names as the fields which will be written in the table columns. Now add mysql_query action with an insert query:

This query will insert data into the database. As you see, we do not insert the id field value - it has the attribute 'auto_increment' and will be generated automatically.

Read form (read records and show them in the desired format)

This form will read information from the database and display it.

For this task, you will need to use the mysql_select_table action.

You need to add a query like this to your form configuration:

This action will work after the first page of the form - so this page will be like the login form.

In our case the first page will be the form with one field - email.

This action will read records from the entered email and display it. In the variable {#table_to_show#} will be the HTML table with results from the database. You can add {#table_to_show#} to the form page to show it.

Update form (select several records and update them in this form)

This form will allow you to read several records and update them.

For this purpose, we will need the actions mysql_select_custom, mysql_batch_update and mysql_select.

First, we need to add the action for reading from the database:

This will read the records from database.

Then we need to add an action that will generate part of the form for editing all the selected records.

We will add the mysql_select_custom action:

In our case, it will look like:

It will take the id field name and will use it to find all matching records.

The mini template will be in the file ../form_folder/part.html

This template contains the html code with fields that will be repeating for each record.

As you can see, this form includes the fields that will be retrieved from the database and they have the same names as columns in the database table.

Place {#custom_code#} on the form page to include the form for editing records.

Now this form reads data and displays the form where you can edit values from all records.

To update records you need to use the mysql_batch_update action.

It can look like:

Now you have three forms for adding, browsing and updating records.