Help And Documentation

2: Object Reference

2.7: The Database Query Object

A Database Query object is used to interact with the database of the site by getting, inserting, updating or deleting data. They can be added to some other objects such as the Table and Loop objects as a source of data, or they can be used separately. If they are used separately you can add the results of the database query to a variable for later use.

Please note that if you are editing (updating) or deleting data, you should add the correct conditions or Query Condition objects or every row in the table will be permantently updated to the same values or permantently deleted.


Action

This method can only be used once.

The action set by this method will tell this Database Query object if it should be getting, adding, editing or deleting data from the site's database.

Please note that if you are editing (updating) or deleting data, you should add the correct conditions or Query Condition objects or every row in the table will be permantently updated to the same values or permantently deleted.

Parameters

Name Text Variable Object Array
Type Yes No No No

Table

This method can only be used once.

Use this method to tell this Database Query object which table to perform an action on.

Parameters

Name Text Variable Object Array
Table Yes No No No

Join Table

This method can be used multiple times.

This method can be used to get the data of other tables which is linked to the first table set in this Database Query object so that the extra data can be returned or used in any conditions or Query Condition objects that belong to this Database Query object.

Parameters

Name Text Variable Object Array Description
From Table And Field Yes No No No The field that we are joining from.
To Table And Field Yes No No No The field that we are joining to with matching values.
Type Yes No No No The type of join that we are performing.

Get Field

This method can be used multiple times.

This method can be used to set a data variable.

This method can be used to select which fields are returned when getting data from the site's database. If it is not used then all data will be returned.

Parameters

Name Text Variable Object Array
Field Yes No No No

Insert Into Or Update Field

This method can be used multiple times.

This method automatically escapes database values.

This method can be used to specify data values for different fields when you are inserting into or updating a database row.

Parameters

Name Text Variable Object Array
Field Yes No No No
Value Yes Yes Yes No

Comparison Type

This method can only be used once.

Choose how conditions are compared to each other. If you select 'And' then all of the conditions must be met for the database query to return or affect a database row. If you select 'Or' then any of the conditions must be met for the database query to return or affect a database row.

Parameters

Name Text Variable Object Array
Comparison Yes No No No

Add Condition

This method can be used multiple times.

This method automatically escapes database values.

This method allows you to add a condition which is used to filter out rows from your database query so that you can select or affect only the rows that you want to. These conditions are compared against each other using the 'And' comparison type by default if a comparison type has not been set for this object.

These conditions are also compared against any Query Condition objects that have been added to this object using the same comparison type that has been set for this object.

Parameters

Name Text Variable Object Array
Field Yes No No No
Operator Yes No No No
Value Yes Yes Yes No

Condition Objects

This method can be used multiple times.

This method allows you to add one or more Query Condition objects which group together other conditions that may use their own comparison type. These Query Condition objects are compared against any conditions that have been added to this object using the comparison type set for this object.

Parameters

Name Text Variable Object Array
Comparison No No Yes No

Group By

This method can be used multiple times.

This method allows your selected database rows to be filtered by combining rows that contain duplicate data in the field that you select.

Parameters

Name Text Variable Object Array
Field Yes No No No

Order By

This method can be used multiple times.

This method allows you to put your selected rows into an order based on the values of the data inside of the fields that you select. You can also select the direction into which the rows are sorted alphabetically and numerically.

Parameters

Name Text Variable Object Array
Field Yes No No No
Direction Yes No No No

Limit

This method can only be used once.

This method automatically escapes database values.

This method allows you to limit the number of rows that are selected or affected. For example, if you set a limit of 20 then only the first 20 rows that would have been selected or affected by your database query will be selected or affected.

Parameters

Name Text Variable Object Array
Limit Yes Yes No No

Offset

This method can only be used once.

This method automatically escapes database values.

This method allows you to not return an initial selection of rows that would have been returned by your database query. For example, if you set an offset of 5 then the first 5 rows will not be returned.

Parameters

Name Text Variable Object Array
Offset Yes Yes No No

Set SQL

This method can be used multiple times.

This method does not escape database values.

This method allows you to manually set the SQL of this database query. This overrides all other methods of this object except for the 'Set Variables' method. When using this method you are responsible for making sure that any variables or data that you use to create your SQL are escaped to be made safe to pass to the database. If you do not escape your database values then SQL injection and security vulnerabilities are possible.

Parameters

Name Text Variable Object Array
Sql Yes Yes Yes No

Set Variables

This method can only be used once.

This method can be used to set a data variable.

Set variables with specific names with values returned from this database query.

Parameters

Name Text Variable Object Array
Results Yes No No No
Rows Yes No No No
Insert ID Yes No No No
Affected Rows Yes No No No
Error Number Yes No No No

Next Page » « Previous Page