Wizard’s Toolkit Documentation

DataPDO.php

This contains all Wizard’s 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

wtkConnectToDB()  : mixed
Connect to Database using PDO
wtkDisconnectToDB()  : mixed
Disconnect from Database
wtkSqlGetOneResult()  : mixed
SQL Get One Result
wtkSqlGetRow()  : mixed
SQL Get Row of data
wtkSqlValue()  : mixed
Returns SQL Value from result set for single column's value. Will return '' blank if data is NULL.
wtkSqlExec()  : mixed
SQL Exec
wtkSqlPrep()  : mixed
SQL Prep
wtkGetSelectOptions()  : mixed
Get Select Options
wtkEscapeStringForDB()  : bool
Prepares the string to be used in either a MySQL or PostgreSQL DB.
wtkSqlDateFormat()  : mixed
SQL Date Format
wtkBuildInsertSQL()  : mixed
Build Insert SQL
wtkExecInsertSQL()  : mixed
Execute Insert SQL
wtkBuildUpdateSQL()  : mixed
Build Update SQL
wtkExecUpdateSQL()  : mixed
Execute Update SQL
wtkBuildInsertLog()  : mixed
Build SQL to Insert into wtkUpdateLog table
wtkBuildUpdateLog()  : mixed
Build SQL to Insert into wtkUpdateLog table
wtkSaveUpdateLog()  : mixed
Save database changes to wtkUpdateLog
wtkCheckSqlInjection()  : mixed
Checks to see if there is a SQL injection in the SQL.
wtkGenerateFileName()  : mixed
Generate new file name. This is mostly used for creating a file name for image uploads.

Functions

wtkConnectToDB()

Connect to Database using PDO

wtkConnectToDB() : mixed
Tags
global

boolean $gloConnected - set to true when connected

global

array $gloWTKobjConn connection to data object

global

string $gloServer1 defined in /wtk/wtkServerInfo.php

global

string $gloDb1 defined in /wtk/wtkServerInfo.php

global

string $gloUser1 defined in /wtk/wtkServerInfo.php

global

string $gloPassword1 defined in /wtk/wtkServerInfo.php

Return values
mixed

wtkDisconnectToDB()

Disconnect from Database

wtkDisconnectToDB() : mixed
Tags
global

boolean $gloConnected

global

array $gloWTKobjConn connection to data object

Return values
mixed

wtkSqlGetOneResult()

SQL Get One Result

wtkSqlGetOneResult( 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

wtkSqlGetRow()

SQL Get Row of data

wtkSqlGetRow( 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

wtkSqlValue()

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

wtkSqlValue(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

wtkSqlExec()

SQL Exec

wtkSqlExec(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

wtkSqlPrep()

SQL Prep

wtkSqlPrep(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

wtkGetSelectOptions()

Get Select Options

wtkGetSelectOptions(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

wtkEscapeStringForDB()

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

wtkEscapeStringForDB(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

wtkSqlDateFormat()

SQL Date Format

wtkSqlDateFormat(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 wtk/wtkServerInfo.php ; choose your default datetime format

Return values
mixed

wtkBuildInsertSQL()

Build Insert SQL

wtkBuildInsertSQL(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

wtkExecInsertSQL()

Execute Insert SQL

wtkExecInsertSQL(string $fncTable) : mixed

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

Parameters
$fncTable : string

Database Table Name

Return values
mixed

wtkBuildUpdateSQL()

Build Update SQL

wtkBuildUpdateSQL(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

wtkExecUpdateSQL()

Execute Update SQL

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

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

Parameters
$fncTable : string

Table Name

$fncWhere : string

SQL WHERE statement

Return values
mixed

wtkBuildInsertLog()

Build SQL to Insert into wtkUpdateLog table

wtkBuildInsertLog(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 wtkBuildInsertSQL 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

wtkBuildUpdateLog()

Build SQL to Insert into wtkUpdateLog table

wtkBuildUpdateLog(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 wtkBuildUpdateSQL 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

wtkSaveUpdateLog()

Save database changes to wtkUpdateLog

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

Table Name

$fncSQL : string

SQL Query

Return values
mixed

wtkCheckSqlInjection()

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

wtkCheckSqlInjection(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

wtkGenerateFileName()

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

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

Pass in the Table Name and File Extension. The TableName is inserted into wtkGUID 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