Wizards Toolkit Documentation

DataPDO.php

This contains all Wizards Toolkit functions that involve database access.

All rights reserved.

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Tags
author
Programming Labs
license
All rights reserved
version
2.0

Table of Contents

wzConnectToDB()  : mixed
Connect to Database using PDO
wzDisconnectToDB()  : mixed
Disconnect from Database
wzSqlGetOneResult()  : mixed
SQL Get One Result
wzSqlGetRow()  : mixed
SQL Get Row of data
wzSqlValue()  : mixed
Returns SQL Value from result set for single column's value. Will return '' blank if data is NULL.
wzSqlExec()  : mixed
SQL Exec
wzSqlPrep()  : mixed
SQL Prep
wzGetSelectOptions()  : mixed
Get Select Options
wzEscapeStringForDB()  : bool
Prepares the string to be used in either a MySQL or PostgreSQL DB.
wzSqlDateFormat()  : mixed
SQL Date Format
wzBuildInsertSQL()  : mixed
Build Insert SQL
wzExecInsertSQL()  : mixed
Execute Insert SQL
wzBuildUpdateSQL()  : mixed
Build Update SQL
wzExecUpdateSQL()  : mixed
Execute Update SQL
wzBuildInsertLog()  : mixed
Build SQL to Insert into wzUpdateLog table
wzBuildUpdateLog()  : mixed
Build SQL to Insert into wzUpdateLog table
wzSaveUpdateLog()  : mixed
Save database changes to wzUpdateLog
wzCheckSqlInjection()  : mixed
Checks to see if there is a SQL injection in the SQL.
wzGenerateFileName()  : mixed
Generate new file name. This is mostly used for creating a file name for image uploads.

Functions

wzConnectToDB()

Connect to Database using PDO

wzConnectToDB() : mixed
Tags
global

boolean $gloConnected - set to true when connected

global

array $gloWTKobjConn connection to data object

global

string $gloServer1 defined in /wz/wtkServerInfo.php

global

string $gloDb1 defined in /wz/wtkServerInfo.php

global

string $gloUser1 defined in /wz/wtkServerInfo.php

global

string $gloPassword1 defined in /wz/wtkServerInfo.php

Return values
mixed

wzDisconnectToDB()

Disconnect from Database

wzDisconnectToDB() : mixed
Tags
global

boolean $gloConnected

global

array $gloWTKobjConn connection to data object

Return values
mixed

wzSqlGetOneResult()

SQL Get One Result

wzSqlGetOneResult( string  $fncSQL , array<string|int, mixed>  $fncSqlFilter [, string  $fncDefault = '' ]) : mixed
Parameters
$fncSQL : string

SQL Query that should return a single result

$fncSqlFilter : array<string|int, mixed>

array that has PDO names of fields and their values

$fncDefault : string = ''

Defaults to blank but can pass in the desired default to return if no rows are found.

Tags
returns

string returns a single value

global

array $gloWTKobjConn connection to data object

Return values
mixed

wzSqlGetRow()

SQL Get Row of data

wzSqlGetRow( string  $fncSQL , array<string|int, mixed>  $fncSqlFilter) : mixed
Parameters
$fncSQL : string

SQL Query that returns set of results

$fncSqlFilter : array<string|int, mixed>

array that has PDO names of fields and their values

Tags
global

array $gloWTKobjConn connection to data object

global

array $gloPDOrow stores first row's data into $gloPDOrow

Return values
mixed

wzSqlValue()

Returns SQL Value from result set for single column's value. Will return '' blank if data is NULL.

wzSqlValue(string $fncColName) : mixed

If page is in 'ADD' mode as per $gloWTKmode = 'ADD' then will return blank ''.

Parameters
$fncColName : string

Field name in a query

Tags
global

string $gloWTKmode will be 'ADD' or 'EDIT'.

global

array $gloPDOrow stores a row's data

returns

string value of a field in a SQL query

Return values
mixed

wzSqlExec()

SQL Exec

wzSqlExec(string $fncSQL, array<string|int, mixed> $fncSqlFilter) : mixed

Execute a SQL call (update, insert, delete, etc.) that does not return a result

Parameters
$fncSQL : string

SQL Query

$fncSqlFilter : array<string|int, mixed>

array that has PDO names of fields and their values

Tags
global

boolean $gloConnected

global

array $gloWTKobjConn connection to data object

Return values
mixed

wzSqlPrep()

SQL Prep

wzSqlPrep(string $fncSQL) : mixed

change ` to DB_COL_QUOTE so can use same code for MySQL, PostgreSQL or MS SQL

Parameters
$fncSQL : string

Pass in SQL Query to be modified

Return values
mixed

wzGetSelectOptions()

Get Select Options

wzGetSelectOptions(string $fncSQL, array<string|int, mixed> $fncSqlFilter, string $fncDisplayField, string $fncValueField, string $fncCurrentValue) : mixed

Pass in SELECT statement to retrieve value and display values for drop list. Sends back

Parameters
$fncSQL : string

SQL Query

$fncSqlFilter : array<string|int, mixed>

array that has PDO names of fields and their values

$fncDisplayField : string

Display Field for example 'Male', 'Female'

$fncValueField : string

Value Field for example 'M', 'F'

$fncCurrentValue : string

Current Value so knows which value to mark as SELECTED

Tags
global

array $gloWTKobjConn connection to data object

returns

string Return HTML built SELECT drop down menu

Return values
mixed

wzEscapeStringForDB()

Prepares the string to be used in either a MySQL or PostgreSQL DB.

wzEscapeStringForDB(string $fncRawString) : bool

Replaces single quote with two single quotes.

Parameters
$fncRawString : string
Tags
global

string $gloDriver1 used to determine whether to use pg_escape_string or str_replace

Return values
bool

wzSqlDateFormat()

SQL Date Format

wzSqlDateFormat(string $fncColName[, string $fncAlias = '' ][, string $fncFormat = '' ]) : mixed

Used in SELECT statements so common code can be used which will format date appropriately regardless of whether MySQL or PostgreSQL database.

Parameters
$fncColName : string
$fncAlias : string = ''

Defaults to column name if blank

$fncFormat : string = ''

Defaults to use $gloSqlDateTime if not passed or blank

Tags
global

string $gloDriver1 used to determine whether to use DATE_FORMAT or to_char

global

string $gloSqlDateTime is set in wz/wtkServerInfo.php ; choose your default datetime format

Return values
mixed

wzBuildInsertSQL()

Build Insert SQL

wzBuildInsertSQL(string $fncTable, string $fncField, string $fncValue) : mixed

Pass in the table, field and value parameters which are added to variables to construct an INSERT statement. This is called by Save.php. It saves the information in $pgInsertColumns, $pgInsertValues, $pgPDOvalues;

Parameters
$fncTable : string

Database Table Name

$fncField : string

Database Field Name

$fncValue : string

Database Value

Return values
mixed

wzExecInsertSQL()

Execute Insert SQL

wzExecInsertSQL(string $fncTable) : mixed

Pass in the Table name and this performs the INSERT script that was created by previously calling wzBuildInsertSQL. This is called by Save.php

Parameters
$fncTable : string

Database Table Name

Return values
mixed

wzBuildUpdateSQL()

Build Update SQL

wzBuildUpdateSQL(string $fncTable, string $fncField, string $fncOldValue, string $fncNewValue) : mixed

Pass in the table, field, prior value and new value which are added to variables to construct an UPDATE statement Checks old and new field values -> Only updates if value has changed. This is called by Save.php

Parameters
$fncTable : string

Table Name

$fncField : string

Field Name

$fncOldValue : string

Old Value

$fncNewValue : string

New Value

Return values
mixed

wzExecUpdateSQL()

Execute Update SQL

wzExecUpdateSQL(string $fncTable, string $fncWhere) : mixed

Pass in the Table name and WHERE clause and this performs the UPDATE that was created by previously calling wzBuildUpdateSQL. This is called by Save.php

Parameters
$fncTable : string

Table Name

$fncWhere : string

SQL WHERE statement

Return values
mixed

wzBuildInsertLog()

Build SQL to Insert into wzUpdateLog table

wzBuildInsertLog(string $fncTable, string $fncField, string $fncNewValue) : mixed

Pass in the table, field and value parameters which are added to variables to construct an INSERT statement. This is called by wzBuildInsertSQL to show log file of who INSERTed what and when.

Parameters
$fncTable : string

Table Name

$fncField : string

Field Name

$fncNewValue : string

New Value

Return values
mixed

wzBuildUpdateLog()

Build SQL to Insert into wzUpdateLog table

wzBuildUpdateLog(string $fncTable, string $fncField, string $fncOldValue, string $fncNewValue) : mixed

Pass in the table, field and value parameters which are added to variables to construct an INSERT statement This is called by wzBuildUpdateSQL to show log file of who UPDATEd what and when

Parameters
$fncTable : string

Table Name

$fncField : string

Field Name

$fncOldValue : string

Old Value

$fncNewValue : string

New Value

Return values
mixed

wzSaveUpdateLog()

Save database changes to wzUpdateLog

wzSaveUpdateLog(string $fncTable, string $fncSQL) : mixed
Parameters
$fncTable : string

Table Name

$fncSQL : string

SQL Query

Return values
mixed

wzCheckSqlInjection()

Checks to see if there is a SQL injection in the SQL.

wzCheckSqlInjection(string $fncSqlColumn) : mixed

Pass in a value and it will mark as SQL injection if ;, single quote or information_schema .

Parameters
$fncSqlColumn : string
Return values
mixed

wzGenerateFileName()

Generate new file name. This is mostly used for creating a file name for image uploads.

wzGenerateFileName(string $fncTableName, string $fncFileExt) : mixed

Pass in the Table Name and File Extension. The TableName is inserted into wzGUID and the auto-generated GUID is used as part of the file name.

Parameters
$fncTableName : string

name of table that is asociated with new file

$fncFileExt : string

the file extension

Tags
returns

string Returns new file name as 'w' . File GUID . '.' . $fncFileExt

Return values
mixed

Search results