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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
|
1 |
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:
|
1 |
SELECT * FROM customers WHERE customers.customerName like ‘%Wheel%” |
c. #{…${formvariable}…}#:
This tag only works with Checkbox, multiSelect fields.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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:
|
1 2 3 4 5 6 7 |
……….
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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:
|
1 2 3 4 5 6 7 |
……….
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.
|
1 2 3 4 5 6 |
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
|
1 2 3 4 5 |
SELECT
columnA, columnB...
FROM [TABLE]
WHERE
...; |
If you are using form, see How to inserting form variables into SQL queries on this page.