Application Scripts

Overview

As you already know, DbFacePHP is completely non-programmer friendly. So don’t let complex looking scare you. DbFacePHP script is just the SQL script. Writing a DbFacePHP script is the same as a SQL script. DbFacePHP provides you Query Builder and DML Builder to help you build a querying and DML SQL scripts. You can inserting the form variables to the sql scripts to build a DbFacePHP script.

How to inserting form variables into SQL queries

There are four kinds tags that DbFacePHP script supported:

a. ${formvariable}

Here is a sample:

update
  products
set
  productName = ${productName},
  productLine = ${productLine},
  productScale = ${productScale},
  productVendor = ${productVendor},
  quantityInStock = ${quantityInStock},
  buyPrice = ${buyPrice},
  MSRP = ${MSRP}
where
  productCode = ${productCode}

At runtime, the sql will convert to a prepared statement like following:

update
  products
set
  productName = ?,
  productLine = ?,
  productScale = ?,
  productVendor = ?,
  quantityInStock = ?,
  buyPrice = ?,
  MSRP = ?
where
  productCode = ?

b. @{formvariable}:

Prepared statement not works everytime. Sometimes, you may want just replacing the variables with the submitted data simply, this case you should use @{formvariable} tag. e.g. You want to search the customerName matches user’s input, you may notice that prepared statement not works in this case:

SELECT * FROM customers WHERE customers.customerName like ‘%@{customerName}%”’

If the user input ‘Wheel’ in the customerName field, DbFacePHP will simply convert the script to following to execute:

SELECT * FROM customers WHERE customers.customerName like ‘%Wheel%”

c. #{…${formvariable}…}#:

This tag only works with Checkbox, multiSelect fields.

SELECT
  dbo.Products.ProductName,
  dbo.Suppliers.CompanyName AS Supplier,
  dbo.Products.UnitPrice,
  dbo.Products.QuantityPerUnit,
  dbo.Products.UnitsInStock,
  dbo.Products.UnitsOnOrder,
  dbo.Products.Discontinued
FROM
  dbo.Products
INNER JOIN
  dbo.Suppliers
ON
  (dbo.Products.SupplierID = dbo.Suppliers.SupplierID)
WHERE
  #{dbo.Products.CategoryID =${category}}#”

when your submitted data contains more than one category field(e.g.3), then the script above will be converted to following to execute:

……….
WHERE
  dbo.Products.CategoryID =${category1}
AND
  dbo.Products.CategoryID =${category2}
AND
  dbo.Products.CategoryID =${category3}

this tag used with the form containing checkbox, multiselect fields usually.

d. *{…${formvariable}…}*:

This tag only works with Checkbox, multiSelect fields.

SELECT
  dbo.Products.ProductName,
  dbo.Suppliers.CompanyName AS Supplier,
  dbo.Products.UnitPrice,
  dbo.Products.QuantityPerUnit,
  dbo.Products.UnitsInStock,
  dbo.Products.UnitsOnOrder,
  dbo.Products.Discontinued
FROM
  dbo.Products
INNER JOIN
  dbo.Suppliers
ON
  (dbo.Products.SupplierID = dbo.Suppliers.SupplierID)
WHERE
  *{dbo.Products.CategoryID =${category}}*

when your submitted data contains more than one category field(e.g.3), then the script above will be converted to following to execute:

……….
WHERE
  dbo.Products.CategoryID =${category1}
OR
  dbo.Products.CategoryID =${category2}
OR
  dbo.Products.CategoryID =${category3}

this tag used with the form containing checkbox, multiselect fields usually.

Scripts for Query Application

Query application use standard query sql statement (SELECT) as it’s application script.
If you are using form, see How to inserting form variables into SQL queries on this page.

Scripts for General Operation Application

General Operation application accepts any SQL DML statements which sperated by “;” as it’s application script.
If you are using form, see How to inserting form variables into SQL queries on this page.

Scripts for Query for Updating Application

Query for Updating Application use standard query sql statement (SELECT) as it’s application script, but it only support query data from one table that has primary/unique index.
If you are using form, see How to inserting form variables into SQL queries on this page.

Scripts for Chart Report Application

Scripts that making a chart report should have 2 columns, the first column taken as x-axis labels, the second column must be a number column, taken as the y-axis values.

SELECT
  [ANY COLUMN] AS [CATEGORY(x-axis) LABELS],
  [NUMBER COLUMN] AS [DATA(y-axis) VALUES]
FROM [TABLE] 
WHERE
...;

You can specify multi SQL script that sperated by “;” to make multi series chart reports.
If you are using form, see How to inserting form variables into SQL queries on this page.

Scripts for Resultset Checkpoint Application

Resultset checkpoint application save SQL query resultset as a checkpoint, this application script is a general query sql statement

SELECT
  columnA, columnB...
FROM [TABLE] 
WHERE
...;

If you are using form, see How to inserting form variables into SQL queries on this page.