<?php
///////////////////////////////////////////////////////////////////////////////
//
// Common utilities
//
///////////////////////////////////////////////////////////////////////////////
//
// (c) 2005-2018 by Martin Willisegger
//
// Project : Common scripts
// Component : MySQLi data processing class
// Website : https://sourceforge.net/projects/nagiosql/
// Version : 3.4.0
// GIT Repo : https://gitlab.com/wizonet/NagiosQL
//
///////////////////////////////////////////////////////////////////////////////////////////////
//
///////////////////////////////////////////////////////////////////////////////////////////////
//
// Class: Common database functions for MySQL (mysqli database module)
//
///////////////////////////////////////////////////////////////////////////////////////////////
//
// Includes any functions to communicate with an MySQL database server
//
// Name: MysqliDbClass
//
// Class variables: $arrParams Array including the server settings
// ---------------- $strErrorMessage Database error string
// $error Boolean - Error true/false
// $strDBId Database connection id
// $intLastId ID of last dataset
// $intAffectedRows Counter variable of all affected data dows
// $booSSLuse Use SSL connection
//
// Parameters: $arrParams['server'] -> DB server name
// ----------- $arrParams['port'] -> DB server port
// $arrParams['user'] -> DB server username
// $arrParams['password'] -> DB server password
// $arrParams['database'] -> DB server database name
//
///////////////////////////////////////////////////////////////////////////////////////////////
namespace functions;
class MysqliDbClass
{
// Define class variables
public $error = false; // Will be filled in functions
public $strDBId; // Will be filled in functions
public $intLastId = 0; // Will be filled in functions
public $intAffectedRows = 0; // Will be filled in functions
public $strErrorMessage = ''; // Will be filled in functions
public $booSSLuse = false; // Defines if SSL is used or not
public $arrParams = array(); // Must be filled in while initialization
/**
* MysqliDbClass constructor.
*/
public function __construct()
{
$this->arrParams['server'] = '';
$this->arrParams['port'] = 0;
$this->arrParams['username'] = '';
$this->arrParams['password'] = '';
$this->arrParams['database'] = '';
}
/**
* MysqliDbClass destructor.
*/
public function __destruct()
{
$this->dbDisconnect();
}
/**
* Opens a connection to the database server and select a database
* @param int $intMode 1 = connect only / 0 = connect + dbselect
* @return bool true = successful / false = error
* Status messages are stored in class variable
*/
public function hasDBConnection($intMode = 0)
{
$booReturn = true;
$this->dbconnect();
if ($this->error == true) {
$booReturn = false;
}
if (($booReturn == true) && ($intMode == 0)) {
$this->dbselect();
if ($this->error == true) {
$booReturn = false;
}
}
return $booReturn;
}
/**
* Sends an SQL statement to the server and returns the result of the first data field
* @param string $strSQL SQL Statement
* @return string <data> = successful / <empty> = error
* Status messages are stored in class variable
*/
public function getFieldData($strSQL)
{
// Reset error variables
$this->strErrorMessage = '';
$this->error = false;
$strReturn = '';
// Send the SQL statement to the server
$resQuery = mysqli_query($this->strDBId, $strSQL);
// Error processing
if ($resQuery && (mysqli_num_rows($resQuery) != 0) && (mysqli_error($this->strDBId) == '')) {
// Return the field value from position 0/0
$arrDataset = mysqli_fetch_array($resQuery, MYSQLI_NUM);
$strReturn = $arrDataset[0];
} elseif (mysqli_error($this->strDBId) != '') {
$this->strErrorMessage .= mysqli_error($this->strDBId). '::';
$this->error = true;
}
return $strReturn;
}
/**
* Sends an SQL statement to the server and returns the result of the first data set
* @param string $strSQL SQL Statement
* @param array|null $arrDataset Result array (by reference)
* @return bool true = successful / false = error
* Status messages are stored in class variable
*/
public function hasSingleDataset($strSQL, &$arrDataset)
{
//$arrDataset = array();
$booReturn = true;
// Reset error variables
$this->strErrorMessage = '';
$this->error = false;
// Send the SQL statement to the server
$resQuery = mysqli_query($this->strDBId, $strSQL);
// Error processing
if ($resQuery && (mysqli_num_rows($resQuery) != 0) && (mysqli_error($this->strDBId) == '')) {
// Put the values into the array
$arrDataset = mysqli_fetch_array($resQuery, MYSQLI_ASSOC);
} elseif (mysqli_error($this->strDBId) != '') {
$this->strErrorMessage .= mysqli_error($this->strDBId). '::';
$this->error = true;
$booReturn = false;
}
return $booReturn;
}
/**
* Sends an SQL statement to the server and returns the result of all dataset in a data array
* @param string $strSQL SQL Statement
* @param array $arrDataset Result array (by reference)
* @param int $intDataCount Number of data result sets
* @return bool true = successful / false = error
* Status messages are stored in class variable
*/
public function hasDataArray($strSQL, &$arrDataset, &$intDataCount)
{
$arrDataset = array();
$intDataCount = 0;
$booReturn = true;
// Reset error variables
$this->strErrorMessage = '';
$this->error = false;
// Send the SQL statement to the server
$resQuery = mysqli_query($this->strDBId, $strSQL);
// Error processing
if ($resQuery && (mysqli_num_rows($resQuery) != 0) && (mysqli_error($this->strDBId) == '')) {
$intDataCount = mysqli_num_rows($resQuery);
$intCount = 0;
// Put the values into the array
while ($arrDataTemp = mysqli_fetch_array($resQuery, MYSQLI_ASSOC)) {
foreach ($arrDataTemp as $key => $value) {
$arrDataset[$intCount][$key] = $value;
}
$intCount++;
}
} elseif (mysqli_error($this->strDBId) != '') {
$this->strErrorMessage .= mysqli_error($this->strDBId). '::';
$this->error = true;
$booReturn = false;
}
return $booReturn;
}
/**
* Insert/update or delete data
* @param string $strSQL SQL Statement
* @return bool true = successful / false = error
* Status messages are stored in class variable
*/
public function insertData($strSQL)
{
// Reset error variables
$this->strErrorMessage = '';
$this->error = false;
$booReturn = false;
// Send the SQL statement to the server
if ($strSQL != '') {
mysqli_query($this->strDBId, $strSQL);
// Error processing
if (mysqli_error($this->strDBId) == '') {
$this->intLastId = mysqli_insert_id($this->strDBId);
$this->intAffectedRows = mysqli_affected_rows($this->strDBId);
$booReturn = true;
} else {
$this->strErrorMessage .= mysqli_error($this->strDBId) . '::';
$this->error = true;
}
}
return $booReturn;
}
/**
* Count the sum of data records
* @param string $strSQL SQL Statement
* @return int <number> = successful / 0 = no dataset or error
* Status messages are stored in class variable
*/
public function countRows($strSQL)
{
// Reset error variables
$this->strErrorMessage = '';
$this->error = false;
$intReturn = 0;
// Send the SQL statement to the server
$resQuery = mysqli_query($this->strDBId, $strSQL);
// Error processing
if ($resQuery && (mysqli_error($this->strDBId) == '')) {
$intReturn = mysqli_num_rows($resQuery);
} else {
$this->strErrorMessage .= mysqli_error($this->strDBId);
$this->error = true;
}
return $intReturn;
}
/**
* Returns a safe insert string for database manipulations
* @param string $strInput Input String
* @return string Output String
*/
public function realEscape($strInput)
{
return mysqli_real_escape_string($this->strDBId, $strInput);
}
/**
* Initialize a mysql database connection
* @return bool true = successful / false = error
*/
private function dbinit()
{
$this->strDBId = mysqli_init();
return true;
}
/**
* Connect to database server
* @param string $dbserver Server name
* @param int $dbport TCP port
* @param string $dbuser Database user
* @param string $dbpasswd Database password
* @return bool true = successful / false = error
* Status messages are stored in class variable
*/
private function dbconnect($dbserver = null, $dbport = null, $dbuser = null, $dbpasswd = null)
{
// Reset error variables
$this->strErrorMessage = '';
$this->error = false;
$booReturn = true;
// Get parameters
if ($dbserver == null) {
$dbserver = $this->arrParams['server'];
}
if ($dbport == null) {
$dbport = $this->arrParams['port'];
}
if ($dbuser == null) {
$dbuser = $this->arrParams['username'];
}
if ($dbpasswd == null) {
$dbpasswd = $this->arrParams['password'];
}
// Not all parameters available
if (($dbserver == '') || ($dbuser == '') || ($dbpasswd == '')) {
$this->strErrorMessage .= gettext('Missing server connection parameter!'). '::';
$this->error = true;
$booReturn = false;
}
if ($booReturn == true) {
$this->dbinit();
//if ($this->booSSLuse == true) {
// TO BE DEFINED
//}
$intErrorReporting = error_reporting();
error_reporting(0);
if ($dbport == 0) {
$booReturn = mysqli_real_connect($this->strDBId, $dbserver, $dbuser, $dbpasswd);
} else {
$booReturn = mysqli_real_connect($this->strDBId, $dbserver, $dbuser, $dbpasswd, null, $dbport);
}
error_reporting($intErrorReporting);
// Connection fails
if ($booReturn == false) {
$this->strErrorMessage = '[' .$dbserver. '] ' .gettext('Connection to the database server has failed '
. 'by reason:'). ' ::';
$strError = mysqli_connect_error();
$this->strErrorMessage .= $strError. '::';
$this->error = true;
}
}
return $booReturn;
}
/**
* Select a database
* @param string $database Database name
* @return bool true = successful / false = error
* Status messages are stored in class variable
*/
private function dbselect($database = null)
{
// Reset error variables
$this->strErrorMessage = '';
$this->error = false;
$booReturn = true;
// Get parameters
if ($database == null) {
$database = $this->arrParams['database'];
}
// Not all parameters available
if ($database == '') {
$this->strErrorMessage .= gettext('Missing database connection parameter!'). '::';
$this->error = true;
$booReturn = false;
}
if ($booReturn == true) {
$bolConnect = mysqli_select_db($this->strDBId, $database);
// Session cannot be etablished
if (!$bolConnect) {
$this->strErrorMessage .= '[' .$database. '] ' .
gettext('Connection to the database has failed by reason:'). ' ::';
$this->strErrorMessage .= mysqli_error($this->strDBId). '::';
$this->error = true;
$booReturn = false;
}
}
if ($booReturn == true) {
mysqli_query($this->strDBId, "set names 'utf8'");
if (mysqli_error($this->strDBId) != '') {
$this->strErrorMessage .= mysqli_error($this->strDBId). '::';
$this->error = true;
$booReturn = false;
}
}
if ($booReturn == true) {
mysqli_query($this->strDBId, "set session sql_mode = 'NO_ENGINE_SUBSTITUTION'");
if (mysqli_error($this->strDBId) != '') {
$this->strErrorMessage .= mysqli_error($this->strDBId). '::';
$this->error = true;
$booReturn = false;
}
}
return $booReturn;
}
/*
/**
* Set SSL connection parameters
* @param string $sslkey SSL key
* @param string $sslcert SSL certificate
* @param string $sslca SSL CA file (optional)
* @param string $sslpath SSL certificate path (optional)
* @param string $sslcypher SSL cypher (optional)
* @return bool true = successful
* Status messages are stored in class variable
*/
/*
private function dbsetssl($sslkey, $sslcert, $sslca = null, $sslpath = null, $sslcypher = null)
{
// Reset error variables
$this->strErrorMessage = "";
$this->error = false;
$booReturn = true;
// Values are missing
if (($sslkey == "") || ($sslcert == "")) {
$this->strErrorMessage = gettext("Missing MySQL SSL parameter!")."::";
$this->error = true;
$booReturn = false;
}
if ($booReturn == true) {
mysqli_ssl_set($this->strDBId, $sslkey, $sslcert, $sslca, $sslpath, $sslcypher);
}
return($booReturn);
}
*/
/**
* Close database server connectuon
* @return bool true = successful / false = error
*/
private function dbDisconnect()
{
mysqli_close($this->strDBId);
return true;
}
}