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
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
boolean $gloConnected - set to true when connected
array $gloWTKobjConn connection to data object
string $gloServer1 defined in /wtk/wtkServerInfo.php
string $gloDb1 defined in /wtk/wtkServerInfo.php
string $gloUser1 defined in /wtk/wtkServerInfo.php
string $gloPassword1 defined in /wtk/wtkServerInfo.php
Return values
mixed —wtkDisconnectToDB()
Disconnect from Database
wtkDisconnectToDB()
: mixed
Tags
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
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
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
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
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
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
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
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