formVista(tm) Developers Guide and Reference
 
formVista(tm): A Business Component Framework for PHP
PrevChapter 19. FVML TagsNext

19.39. <mysql> - Mysql Database Support

The <mysql> tag implements the interface to the Mysql Database Server. It adds a number of convenience features including:

  1. automatic generation of INSERT, UPDATE, DELETE and WHERE clauses.

  2. enforces Mysql quoting rules to prevent sql-injection.

  3. automatically cooperates with List components to generate LIMIT clauses.

  4. supports appending date ranges to the query - today, yesterday, this week, last week, this month, last month, etc.

  5. supports turning any time series query into hour, day, week, and year queries.

Which kind of query <mysql> generates will depend on the event it receives from it's parent. Standard form and list command handler sections, onDoInsert, onDoEdit, onDoDelete, onDoUpdate, onDoSearch, etc will generate corresponding events that <mysql> responds to.

19.39.1. Usage:

Usage in query auto-generation of INSERT, UPDATE, DELETE statements based on fields from the form that match column names in the specified table or that are mapped onto column names via <query_hint type=”set” ..> calls. 

<mysql mode="insert|update|delete"
       table="table_name"
       [database="database_name"]
       [namespace="namespace_name"]>
   <query_hint type="primary_key" name="primary_column_name"/>
   [<query_hint type="create_date" name="date_field"/>]
   [<query_hint type="set" name="var_name" value="var_value"/>
</mysql>

See the Complete Form Example for a number of auto-generated queries.

Typical time series report usage in SELECT statements: 

<mysql type="datafeed|preload"
   table="table_name"
   datecol="date_column"
   [database="database_name"]
   [namespace="namespace_name"]>
   SELECT $MY{DATE_GROUP} as date_group,
          $MY{DATE_GROUP_START} as group_start,
          $MY{DATE_GROUP_END} as group_end,
          COUNT( distinct( some_field ) ) as count
   FROM table_name
   WHERE "1" = "1"
   $MY{SEARCH_CLAUSE}
   $MY{DATE_RANGE}
   GROUP BY date_group
   $MY{LIMIT}   
</mysql>

See the Time Series Report Example in the formvista_demoshell to see the query above in action.

19.39.2. Used In:

  1. command handler sections of any form or list component.

  2. under special body section tags that are designed to host <mysql> tags such as <foreachrow>, <selectitemlist>, <validate type=”isUnique”>, etc.

19.39.3. Attributes

The mysql tag adds support for the following additional attributes.

19.39.3.1. database=”name_of_new_database”

The current database can be overridden to another for the duration of one <mysql> tag. The currently selected database will be set at before the query is run and will be reset after the query is finished.

19.39.3.2. datecol=”column_name”

The column in the current table that contains a date. Required in order to expand $MY{DATE_RANGE} clauses to support auto-generation of today, yesterday, this week, last week, this month, last month, year to date and last year WHERE clauses.

19.39.3.3. mode=”datafeed|preload|plain|update|insert|delete” (same as type=”...”)

The mode=”” attribute identifies what “mode” the <mysql> tag should operate in. Whether it should wait to pull in rows when asked , whether it should preload the first row, etc.

19.39.3.3.1. mode=”datafeed”

The datafeed mode designates this tag as the one that will feed data rows up to it's parent and is typically used in listt <onGetData> or form <onDoEdit> sections. This means the parent tag will use this <mysql> tag when it wants to get a row of data, as opposed to some other <mysql> tag.

You can have any number of <mysql> tags in a section, however only one may be run as the “datafeed” to actually deliver the data to higher tags. Typically it's the last <mysql> tag in a command handler section.

NOTE: If no content shows up in your component when you expect it to, it may be because you failed to set mode=”datafeed” on the last <mysql> tag in a command handler section.

19.39.3.3.2. mode=”insert”

Forces this <mysql> tag to generate an INSERT query regardless of what the current event is. Useful, for instance, if one wants to auto-generate INSERT statements into a temporary table in an <onDoSearch> handler.

19.39.3.3.3. mode=”update”

Similar to mode=”insert” above, this forces the <mysql> tag to generate an UPDATE query.

19.39.3.3.4. mode=”delete”

this forces the <mysql> tag to generate a DELETE query.

19.39.3.3.5. mode=”preload”

In “preload” mode, the first row of data is pulled in immediately after the query is run. If no namespace=”” attribute is set, mode=”preload” will load the row into ${}. If a namespace=”” attribute is specified, the row will be loaded into the named namespace and can be referred to afterwards by name.

NOTE: If your query returns more than one row, only the first row will be used in a mode=”preload”.

19.39.3.3.6. mode=”plain”

This mode is used if you want to build Insert, Update or Delete queries by hand overridding the auto-generation of SQL.

19.39.3.3.7. mode=”subquery”

this mode is used to indicate this <mysql> tag will act as a subquery to a parent <mysql> tag.

19.39.3.4. namespace=”namespace_name”

Only used in mode=”preload” cases. Identifies the namespace (variable) that the row should be pulled into. For instance, consider a fictitious table some_table with a key column of “entry” and let's say a “title” column: 

<mysql mode="preload" namespace="MYSPACE">
   SELECT *
   FROM some_table
   WHERE entry = 1
</mysql>

After this query is run, you can refer to values in $MYSPACE{title} anywhere in the rest of the component. See Variables and Namespaces. (See Chapter 17)

19.39.3.5. table=”table_name”

Required attribute in the case of auto-generated queries. This identifies the table that should be used when auto-generating where clauses.

19.39.3.6. export=”component”

Deprecated. Use namespace=”COMP” instead.

19.39.4. Options=”..” Attribute Extensions

The <mysql> tag understands the following additional options.

19.39.4.1. options=“insertid”

options=”insertid” is used for INSERT queries. It will store the INSERT_ID generated by the insert query in the $MY{INSERT_ID}. This is typically used for mode=”plain” queries. Auto-generated insert queries automatically store the INSERT_ID.

19.39.4.2. options=“marktime=<name>”

The formVista processor can keep timing intervals for marked events. Each mark has a unique name which is listed out in a report that can be optionally turned on at the bottom of the page. This is useful for optimizing components and figuring out which queries are taking the longest time.

19.39.5. Debug=”..” Attribute Extensions

The <mysql> tag supports the following additional debug options.

19.39.5.1. debug=“showsql”

<mysql> tags understand the showsql debug option. Use debug=”showsql” to have the mysql tag output the sql queries it generates.

19.39.6. Variables

19.39.6.1. INSERT_ID

After an auto-generated INSERT query, the unique id of the new row will be stored in $MY{INSERT_ID} and in whatever namespace the <mysql> tag has specified or in ${}. If namespace=”..” attribute is present, this value will be copied to that namespace.

See the Complete Form Example

19.39.6.2. KEY_FIELD

After an auto-generated INSERT or if mode=”insert” or options=”insertid”, $MY{KEY_FIELD} will contain the name of the primary key column for this table. If a namespace=”..” attribute is present, this value will be copied to that namespace.

19.39.6.3. KEY_VALUE

Alias of INSERT_ID. After an auto-generated INSERT or if mode=”insert” or options=”insertid”, $MY{KEY_VALUE} will contain the value of the primary key for this row. If a namespace=”...” attribute is present, this value will be copied to that namespace.

19.39.6.4. DATE_RANGE

Generated by the <mysql> tag and used by long-hand queries as $MY{DATE_RANGE} in the SQL query itself. It's used to generate date range WHERE clause fragments useful in writing reports over time. It handles values of today, yesterday, this week, last week, etc.

From the Time Series Report Example example, selecting this month, which is currently November 2005 $MY{DATE_RANGE} in the query is replaced by: 

( entry_date >= "2005-11-01 00:00:00" ) and 
( entry_date <= "2005-11-30 23:59:59" )

For this variable to be available the datecol=”..” attribute must be specified in the <mysql> tag. See the

19.39.6.5. DATE_GROUP

Generated by the <mysql> tag and made available for replacement in the query. This variable along with DATE_GROUP_START and DATE_GROUP_END are used in generated Time Series Reports which generally account for some event over time such as “web hits by day for the last month”.

See the Time Series Report Example.

19.39.6.6. DATE_GROUP_START

See the Time Series Report Example.

19.39.6.7. DATE_GROUP_END

See the Time Series Report Example.

19.39.6.8. LIMIT

Used exclusively inside LIST components that have a component level rowsperpage=”..” attribute set. This variable is replaced in the query with a limit clause such as: 

limit 0, 25

This variable is used to implement paging for any query in conjunction with the NEXT and PREV button functionality of the list component types.

See the Simple Report Example in the demoshell.

19.39.6.9. START_ROW

Exported to the parent tag level which usually promotes it up to the component level ${}.

Exported to the parent level. ${START_ROW} is made available to indicate the first result row number on the current page. Assumes a list component.

19.39.6.10. END_ROW

Exported to the parent tag level which usually promotes it up to the component level ${}.

${END_ROW} is made to available to indicate the last result row number on the current page. Assumes a list component.

19.39.7. Subtags Defined by <mysql>

19.39.7.1. <query_hint>

Used to provide hints to the <mysql> tag to help it auto-generation sql statements. These query_hints are executed right before the <msyql> tag executes the query.

19.39.7.1.1. <query_hint type=”primary_key” field=”column_name”/>

Identified “column_name” as the primary key. Used in INSERT, UPDATE, EDIT, and DELETE query generation to identify the unique key.

19.39.7.1.2. <query_hint type=”join” from=”source_column” to=”dest_column”/>

Deprecated.

Used in INSERT, UPDATE subqueries to indicate what column in the parent tag should be joined to which column in the child.

This method is deprecated. The preferred method is to use <query_hint type=”set” .../>.

19.39.7.1.3. <query_hint type=”map” from=”from_column” to=”to_column”/>

Deprecated

Used in INSERT, UPDATE and DELETE subqueries to map one column onto another.

This method is deprecated. The preferred method is to use <query_hint type=”set” .../>

19.39.7.1.4. <query_hint type=”set” name=”var_name” value=”var_value”/>

Used to set variables local to the <msyql> tag that may be used in the auto-generation of queries.

For instance, if you have a column in your table called “city” you could use 

<mysql table="sometable">
   <query_hint type="set" name="city" value="College Park"/>
</mysql>

to force the value of city to be “College Park”.

19.39.8. Events

The following events are caught by the <mysql> tag and correspond to the command handler sections they are called from. Knowing the list of events is useful for custom command handler sections that use the <on> tag.

PrevContentsNext
<multicmd>Up<on>
  
Copyright © 1997 - 2006 DTLink Software
All Rights Reserved

formVista is a trademark of DTLink, LLC.