wbDatabase Class Reference

The wbDatabase class can be used to access the MySQL database for wbTeamPro development within the WHMCS system.

Construct

If you are including wbDatabase.php within a custom script you will need to pass database parameters when creating the first instance. The first instance of wbDatabase will become the global instance, and can be later adopted using the getInstance method.

$dbh = new wbDatabase(array(
  'host'   => 'DatabaseHost',
  'name'   => 'DatabaseName',
  'user'   => 'Username',
  'pass'   => 'Password',
  'hash'   => 'EncryptionHash',
  'encode' => 'CharacterSet'
  ));

Invoking an Instance

From within the wbTeamPro system, or once the global wbDatabase class has been initialized, you may adopt an instance of the wbDatabase class by invoking the getInstance method.

$dbh = wbDatabase::getInstance();

Method Reference

getCfgVal( $key ):String - Returns a class configuration value

getInstance():Object - Returns a class instance

runQuery( $query ):DBReference - Performs a MySQL query and caches pointer
  $query:String - The literal MySQL query to be executed

runInsert( $tblName, $data, $xtra=null, $ignore=false ):DBReference - Performs an INSERT MySQL Query
  $tblName:String - The database table to receive the insert
  $data:Array|Object - The array or object containing the key => value pairs of the record
  $xrtra:String = Additional SQL to be inserted after VALUES in the query
  $ignore:Boolean - Triggers use of INSERT IGNORE feature

runUpdate( $tblName, $data, $where, $xtra=null ):DBReference - Performs an UPDATE MySQL Query
  $tblName:String - The database table to receive the update
  $data:Array|Object - The array or object containing the key => value pairs of the record
  $where:Array = The WHERE filters to be applied to the query (AND Joined)
  $xtra:String = Additional SQL to be inserted after WHERE in the query

getRow( $item=0 ):Array - Seeks and returns a row from the cached query
  $item:Int - The row number to return

getRows( $start=null, $limit=null ):Array - Returns the row(s) of a cached query
  $start:Int - The first row to return
  $limit:Int - The maximum number of rows to return

getObject( $item=0 ):Object - Formats the getRow() response as an object
  $field:Int - The table field to return the value of 

getObjects( $field ):Array - Formats the getRows() response as an array of objects
  $field:Int - The table field to return the value of 

getValue( $field ):String - Returns the value if a field in the first row of a cached query
  $field:Int - The table field to return the value of 

getRowCount():Integer - Returns a count of rows in the cached query

getFields( $tblName ):Array - Returns the fields for a given database table
  $tblName:String - The database table to examine

getNextID( $tblName ):Integer - Returns the next autoindex value for a given database table
  $tblName:String - The database table to examine

getLastID():Integer - Returns the primary key of the last insert command

getErrMsg():String - Returns the last error message

getErrNum():Integer - Returns the last error number

getEscaped( $str ):String - Safely Escape a value for use with a query
  $str:String - The string to be escaped

isNullDate( $val ):Boolean - Compares a given value to the MySQL Null Date 0000-00-00 00:00:00
  $val:String - The string to be compared

Error Reporting

The wbDatabase class will abort operation if a query operation fails, pushing the database error message to the client.

Sample Implementation

/****************************************************** Database Instance */
$dbh = wbDatabase::getInstance();

/****************************************************** SELECT Query */
$dbh->runQuery("
    SELECT *
    FROM `tbladdon_wbteampro_project`
    ORDER BY `date_created` DESC
    ");
echo "Found ". $dbh->getRowCount() ." Project Rows... \n";
$rows = $dbh->getRows(0,10);
foreach($rows AS $row){ echo $row['project_name']."\n"; }

/****************************************************** Get the next AUTOINDEX */
$nextInsertID = $dbh->getNextID('tbladdon_wbteampro_project');
echo "Next AutoIndex is: $nextInsertID \n";

/****************************************************** INSERT a new record */
$dbh->runInsert(
  'tbladdon_wbteampro_project',
  array(
    'project_name'  => 'wbDatabase Insert - Example Project',
    'date_created' => date('Y-m-d H:i:s')
    ));

/****************************************************** Get the last AUTOINDEX */
$lastInsertID = $dbh->getLastID();
echo "Created Record: $lastInsertID \n";

/****************************************************** UPDATE the new record */
$dbh->runUpdate(
  'tbladdon_wbteampro_project',
  array(
    'date_modified' => date('Y-m-d H:i:s')
    ),
  array(
    "`project_id` = '". (int)$lastInsertID ."'"
    ));

/****************************************************** SELECT WHERE Query */
$dbh->runQuery("
    SELECT *
    FROM `tbladdon_wbteampro_project`
    WHERE `project_name` LIKE '%". $dbh->getEscaped('Example') ."%'
    ORDER BY `date_modified` DESC
    LIMIT 10
    ");
echo "Found ". $dbh->getRowCount() ." rows... \n";
while( $row = $dbh->getRow() ){ echo $row['project_name']."\n"; }

/****************************************************** DELETE the new record */
if( $nextInsertID == $lastInsertID ){
  $dbh->runQuery("
    DELETE FROM `tbladdon_wbteampro_project`
    WHERE `project_id` = '". (int)$lastInsertID ."'
    ");
  echo "Deleted Record: $lastInsertID \n";
}