I am releasing the mysqli database adaptor I developed that allows for the system to run on versions of PHP higher that 5.6 without any apache modifications or modifications to the $GLOBAL[‘ISC_CLASS_DB’]->Query, Fetch, FetchAll, etc queries. This is a major part of upgrading Interspire Shopping Cart to run on PHP 7+ where you are forced to utilize MySQLi which has speed gains of 30%+ and PHP 7 which has speed updates of over 50%+. If you haven’t noticed that most hosting providers are defaulting to PHP 7 and requiring at least the use of PHP 5.6. It is a dying version and the apache and PHP mods aren’t being updated any longer so it’s really time to update your site. If you have any add-ons or modifications where you utilize a secondary database class in add-ons or modules, if you manually call up mysql_query for example as a function, you will need to replace them to use the built in DB class.
This file below is in beta, as it is version 1.0.1. I have been using Zend Servers Event Monitor to debug it. You will need to modify the config.php file and the lib/init.php file to make it load the DB file by name specified in the config/config.php file. The biggest issue I have had is the FetchOne function. Feel free to post any feedback!
<?php /** * This file handles mysql database connections, queries, procedures etc. * Most functions are overridden from the base object. * * @version 1.0 * @author Dustin Holdiman (dustin@thinkgenius.com) * * @package Db * @subpackage MySQLDb */ /** * Include the base database class. */ require_once(dirname(__FILE__).'/db.php'); if (!function_exists('mysqli_connect')) { die("Your PHP installation does not have MySQLi support. Please enable MySQLi support in PHP or ask your web host to do so for you."); } /** * This is the class for the MySQL database system. * * @package Db * @subpackage MySQLDb */ class MySQLDb extends Db { /** * Should we use mysql_real_escape_string or the older mysql_escape_string function ? * * @var Boolean */ var $use_real_escape = false; /** * Is magic quotes runtime on ? * * @var Boolean */ var $magic_quotes_runtime_on = false; /** * MySQL uses ` to escape table/database names * * @var String */ var $EscapeChar = '`'; /** * This flag is checked when Query is called to see which mode to run the query in. * Calling the UnbufferedQuery function sets this flag to true, then lets the main Query function handle the rest. * * @see UnbufferedQuery * @see Query * * @var Boolean Defaults to false (don't run in unbuffered mode). */ var $_unbuffered_query = false; /** * Connection Variable */ var $connection = ''; /** * Constructor * Sets up the database connection. * Can pass in the hostname, username, password and database name if you want to. * If you don't it will set up the base class, then you'll have to call Connect yourself. * * @param String $hostname Name of the server to connect to. * @param String $username Username to connect to the server with. * @param String $password Password to connect with. * @param String $databasename Database name to connect to. * * @see Connect * @see GetError * * @return Mixed Returns false if no connection can be made - the error can be fetched by the Error() method. Returns the connection result if it can be made. Will return Null if you don't pass in the connection details. */ function MySQLDb($hostname='', $username='', $password='', $databasename='') { $this->use_real_escape = version_compare(PHP_VERSION, '4.3.0', '>=');
$this->magic_quotes_runtime_on = get_magic_quotes_runtime();
if ($hostname && $username && $databasename) {
$this->connection = $this->Connect($hostname, $username, $password, $databasename);
return $this->connection;
}
return null;
}
/**
* Connect
* This function will connect to the database based on the details passed in.
*
* @param String $hostname Name of the server to connect to.
* @param String $username Username to connect to the server with.
* @param String $password Password to connect with.
* @param String $databasename Database name to connect to.
*
* @see SetError
*
* @return False|Resource Returns the resource if the connection is successful. If anything is missing or incorrect, this will return false.
*/
function Connect($hostname=null, $username=null, $password=null, $databasename=null)
{
if ($hostname === null && $username === null && $password === null && $databasename === null) {
$hostname = $this->_hostname;
$username = $this->_username;
$password = $this->_password;
$databasename = $this->_databasename;
}
if ($hostname == '') {
$this->SetError('No server name to connect to');
return false;
}
if ($username == '') {
$this->SetError('No username name to connect to server '.$hostname.' with');
return false;
}
if ($databasename == '') {
$this->SetError('No database name to connect to');
return false;
}
if ($this->_retry && is_resource($this->connection)) {
$this->Disconnect($this->connection);
}
$mysqli = new mysqli($hostname, $username, $password, $databasename);
if ($mysqli->connect_errno) {
$this->SetError("Failed to connect to MySQLi: (" . $mysqli->connect_errno . ") " . $mysqli->error);
}
$this->connection = $mysqli;
$db_result = @mysqli_select_db($this->connection, $databasename);
if (!$db_result) {
$this->SetError('Unable to select database \''.$databasename.'\': '.$mysqli->error);
return false;
}
$this->_hostname = $hostname;
$this->_username = $username;
$this->_password = $password;
$this->_databasename = $databasename;
// Set the character set if we have one
if($this->charset) {
$this->Query('SET NAMES '.$this->charset);
}
// Do we have a timezone? Set it
if($this->timezone) {
$this->Query("SET time_zone = '".$this->timezone."'");
}
return $this->connection;
}
/**
* Disconnect
* This function will disconnect from the database handler passed in.
*
* @param String $resource Resource to disconnect from
*
* @see SetError
*
* @return Boolean If the resource passed in is not valid, this will return false. Otherwise it returns the status from pg_close.
*/
function Disconnect($resource=null)
{
if ($resource === null) {
$this->SetError('Resource is a null object');
return false;
}
if (!is_resource($resource)) {
$this->SetError('Resource '.$resource.' is not really a resource');
return false;
}
$close_success = mysqli_close($resource);
if ($close_success) {
$this->connection = null;
}
return $close_success;
}
/**
* Query
* This function will run a query against the database and return the result of the query.
*
* @param String $query The query to run.
*
* @see LogQuery
* @see SetError
*
* @return Mixed Returns false if the query is empty or if there is no result. Otherwise returns the result of the query.
*/
function Query($query='')
{
// if we're retrying a query, we have to kill the old connection and grab it again.
// if we don't, we get a cached connection which won't work.
if ($this->_retry) {
$this->connection = $this->Connect();
}
// Trim query
if (is_string($query)){
$query = trim($query);
}
if (!$query) {
$this->_retry = false;
$this->SetError('Query passed in is empty');
return false;
}
if (!$this->connection) {
$this->_retry = false;
$this->SetError('No valid connection');
return false;
}
if (!is_resource($query) || !is_object($query)){
if ($this->TablePrefix !== null) {
$query = str_replace("[|PREFIX|]", $this->TablePrefix, (string)$query);
} else {
$query = str_replace("[|PREFIX|]", '', (string)$query);
}
}
$this->NumQueries++;
if ($this->TimeLog !== null || $this->StoreQueryList == true) {
$timestart = $this->GetTime();
}
// Execute Query
$result = mysqli_query($this->connection, $query);
if ($this->TimeLog !== null) {
$timeend = $this->GetTime();
$this->TimeQuery($query, $timestart, $timeend);
}
if($this->StoreQueryList) {
if(!isset($timeend)) {
$timeend = $this->GetTime();
}
$this->QueryList[] = array(
"Query" => $query,
"ExecutionTime" => $timeend-$timestart
);
}
if ($this->QueryLog !== null) {
if ($this->_retry) {
$this->LogQuery("*** Retry *** Result type: " . gettype($result) . "; value: " . $result . "\t" . $query);
} else {
$this->LogQuery("Result type: " . gettype($result) . "; value: " . $result . "\t" . $query);
}
}
if (!$result) {
$error = mysqli_error($this->connection);
$errno = mysqli_errno($this->connection);
if ($this->ErrorLog !== null) {
$this->LogError($query, $error);
}
$this->SetError($error, E_USER_ERROR, $query);
// we've already retried? don't try again.
// or if the error is not '2006', then don't bother going any further.
if ($this->_retry || $errno !== 2006) {
$this->_retry = false;
return false;
}
// error 2006 is 'server has gone away'
// http://dev.mysql.com/doc/refman/5.0/en/error-messages-client.html
if ($errno === 2006) {
$this->_retry = true;
return $this->Query($query);
}
}
// make sure we set the 'retry' flag back to false if we are returning a result set.
$this->_retry = false;
return $result;
}
/**
* UnbufferedQuery
* Runs a query in 'unbuffered' mode which means that the whole result set isn't loaded in to memory before returning it.
* Calling this function sets a flag in the class to say run the query in unbuffered mode, then uses Query to handle the rest.
*
* @param String $query The query to run in unbuffered mode.
*
* @see _unbuffered_query
* @see Query
*
* @return Mixed Returns the result from the Query function.
*/
function UnbufferedQuery($query='')
{
$this->_unbuffered_query = true;
return $this->Query($query);
}
/**
* Fetch
* This function will fetch a result from the result set passed in.
*
* @param String $resource The result from calling Query. Returns an associative array (not an indexed based one)
*
* @see Query
* @see SetError
* @see StripslashesArray
*
* @return Mixed Returns false if the result is empty. Otherwise returns the next result.
*/
function Fetch($resource=null)
{
// Edit by Invelo (DH)
if (is_array($resource)){
return $resource;
}
// End Edit
if ($resource === null) {
$this->SetError('Resource is a null object');
return false;
}
if (!is_resource($resource) && !is_array($resource) && !is_object($resource)) {
$this->SetError('Resource '.$resource.' is not really a resource, array or object.');
return false;
}
if($this->magic_quotes_runtime_on) {
// Edit by Invelo (DH)
if (is_resource($resource)){
for ($set = array(); $row = $resource->fetch_assoc(); $set[] = $this->StripslashesArray($row));
return $set;
} else {
return $this->StripslashesArray($resource->fetch_assoc());
}
// End Edit
}
else {
// Edit by Invelo (DH)
if (is_resource($resource)){
for ($set = array(); $row = $resource->fetch_assoc(); $set[] = $row);
return $set;
} else {
return $resource->fetch_assoc();
}
// End Edit
}
}
/**
* FetchOne
* Fetches one item from a result and returns it.
*
* @param String $result Result to fetch the item from.
* @param String $item The item to look for and return.
*
* @see Fetch
*
* @return Mixed Returns false if there is no result or item, or if the item doesn't exist in the result. Otherwise returns the item's value.
*/
function FetchOne($result=null, $item=null)
{
if ($result === null) {
return false;
}
// Edit by Invelo (DH)
if (is_array($result)){
return $result;
}
if (is_resource($result)){
while ($row = $result->fetch_row()) {
return $row[0];
}
}
if (is_string($result)){
$query = $this->Query($result);
while ($row = $query->fetch_row()){
if (isset($row[0])){
$return = $row[0];
}
}
if (!empty($return)){
return $return;
} else {
return false;
}
}
if (!is_resource($result)){
while ($row = $result->fetch_row()) {
if (isset($row[0])){
return $row[0];
} else {
return false;
}
}
}
// End Edit
}
/**
* Build and execute a database insert query from an array of keys/values.
*
* @param string The table to insert into.
* @param array Associative array of key/value pairs to insert.
* @param bool TRUE to interpret NULL as being database NULL, FALSE to mean an empty string
* @return mixed Insert ID or true on successful insertion, false on failure.
*/
function InsertQuery($table, $values, $useNullValues=false)
{
// Edit by Invelo (DH)
if (is_object($values)){
$values = (array) $values;
}
$keys = array_keys($values);
$fields = implode($this->EscapeChar.",".$this->EscapeChar, $keys);
foreach ($keys as $key) {
if ($useNullValues) {
if (is_null($values[$key])) {
$values[$key] = "NULL";
} else {
$values[$key] = "'" . $this->Quote($values[$key]) . "'";
}
} else {
$values[$key] = "'" . $this->Quote($values[$key]) . "'";
}
}
$values = implode(",", $values);
$query = sprintf('INSERT INTO %1$s[|PREFIX|]%2$s%1$s (%1$s%3$s%1$s) VALUES (%4$s)', $this->EscapeChar, $table, $fields, $values);
if ($this->Query($query)) {
// only return last id if it contains a valid value, otherwise insertquery reports as failed if it returns a false value (0, null etc)
$lastId = $this->LastId();
if ((int)$lastId > 0) {
return $lastId;
}
else {
return true;
}
}
else {
return false;
}
}
/**
* Build and execute a database update query from an array of keys/values.
*
* @param string The table to insert into.
* @param array Associative array containing key/value pairs to update.
* @param string The where clause to apply to the update
* @param bool TRUE to interpret NULL as being database NULL, FALSE to mean an empty string
*
* @return boolean True on success, false on error.
*/
function UpdateQuery($table, $values, $where="", $useNullValues=false)
{
$fields = array();
// Edit by Invelo (DH)
if (is_object($values)){
$values = (array) $values;
}
// End Edit
foreach ($values as $k => $v) {
if ($useNullValues) {
if (is_null($v)) {
$v = "NULL";
} else {
$v = "'" . $this->Quote($v) . "'";
}
} else {
$v = "'" . $this->Quote($v) . "'";
}
$fields[] = $k . "=" . $v;
}
$fields = implode(", ", $fields);
if ($where != "") {
$fields .= " WHERE " . $where;
}
$query = "UPDATE [|PREFIX|]" . $table . " SET " . $fields;
if ($this->Query($query)) {
return true;
}
else {
return false;
}
}
/**
* NextId
* Fetches the next id from the sequence passed in
*
* @param String $sequencename Sequence Name to fetch the next id for.
* @param String $idcolumn The name of the column for the id field. By default this is 'id'.
*
* @see Query
*
* @return Mixed Returns false if there is no sequence name or if it can't fetch the next id. Otherwise returns the next id
*/
function NextId($sequencename=false, $idcolumn='id')
{
if (!$sequencename) {
return false;
}
$query = 'UPDATE '.$sequencename.' SET ' . $idcolumn . '=LAST_INSERT_ID(' . $idcolumn . '+1)';
$result = $this->Query($query);
if (!$result) {
return false;
}
return mysqli_insert_id($this->connection);
}
/**
* FullText
* Fulltext works out how to handle full text searches. Returns an sql statement to append to enable full text searching.
*
* @param Mixed $fields Fields to search against. This can be an array or a single field.
* @param String $searchstring String to search for against the fields
* @param Bool $booleanmode In MySQL, is this search in boolean mode ?
*
* @return Mixed Returns false if either fields or searchstring aren't present, otherwise returns a string to append to an sql statement.
*/
function FullText($fields=null, $searchstring=null, $booleanmode=false)
{
if ($fields === null || $searchstring === null) {
return false;
}
if (is_array($fields)) {
$fields = implode(',', $fields);
}
if ($booleanmode) {
$query = 'MATCH ('.$fields.') AGAINST (\''.$this->Quote($this->CleanFullTextString($searchstring)).'\' IN BOOLEAN MODE)';
} else {
$query = 'MATCH ('.$fields.') AGAINST (\''.$this->Quote($searchstring).'\')';
}
return $query;
}
/**
* CleanFullTextString
* Cleans and properly formats an incoming search query in to a string MySQL will love to perform fulltext queries on.
* For example, the and/or words are replaced with correct boolean mode formats, phrases are supported.
*
* @param String $searchstring The string you wish to clean.
* @return String The formatted string
*/
function CleanFullTextString($searchstring)
{
$searchstring = strtolower($searchstring);
$searchstring = str_replace("%", "\\%", $searchstring);
$searchstring = preg_replace("#\*{2,}#s", "*", $searchstring);
$searchstring = preg_replace("#([\[\]\|\.\,:])#s", " ", $searchstring);
$searchstring = preg_replace("#\s+#s", " ", $searchstring);
if (substr_count($searchstring, '"') % 2 != 0) {
// ISC-1412: odd number of double quote present, strip all
$searchstring = str_replace('"', '', $searchstring);
}
$words = array();
// Does this search string contain one or more phrases?
$quoted_string = false;
if (strpos($searchstring, "\"") !== false) {
$quoted_string = true;
}
$in_quote = false;
$searchstring = explode("\"", $searchstring);
foreach ($searchstring as $phrase) {
$phrase = trim($phrase);
if ($phrase != "") {
if ($in_quote == true) {
$words[] = "\"{$phrase}\"";
} else {
$split_words = preg_split("#\s{1,}#", $phrase, -1);
if (!is_array($split_words)) {
continue;
}
foreach ($split_words as $word) {
if (!$word) {
continue;
}
$words[] = trim($word);
}
}
}
if ($quoted_string) {
$in_quote = !$in_quote;
}
}
$searchstring = ''; // Reset search string
$boolean = '';
$first_boolean = '';
foreach ($words as $k => $word) {
if ($word == "or") {
$boolean = "";
} else if ($word == "and") {
$boolean = "+";
} else if ($word == "not") {
$boolean = "-";
} else {
$searchstring .= " ".$boolean.$word;
$boolean = '';
}
if ($k == 1) {
if ($boolean == "-") {
$first_boolean = "+";
} else {
$first_boolean = $boolean;
}
}
}
$searchstring = $first_boolean.trim($searchstring);
return $searchstring;
}
/**
* AddLimit
* This function creates the SQL to add a limit clause to an sql statement.
*
* @param Int $offset Where to start fetching the results
* @param Int $numtofetch Number of results to fetch
*
* @return String The string to add to the end of the sql statement
*/
function AddLimit($offset=0, $numtofetch=0)
{
$offset = intval($offset);
$numtofetch = intval($numtofetch);
if ($offset < 0) {
$offset = 0;
}
if ($numtofetch <= 0) { $numtofetch = 10; } $query = ' LIMIT '.$offset.', '.$numtofetch; return $query; } /** * FreeResult * Frees the result from memory. * * @param String $resource The result resource you want to free up. * * @return Boolean Whether freeing the result worked or not. */ function FreeResult($resource=null) { if ($resource === null) { $this->SetError('Resource is a null object');
return false;
}
if (!is_resource($resource)) {
$this->SetError('Resource '.$resource.' is not really a resource');
return false;
}
$result = mysqli_free_result($resource);
return $result;
}
/**
* CountResult
* Returns the number of rows returned for the resource passed in
*
* @param String $resource The result from calling Query
*
* @see Query
* @see SetError
*
* @return Int Number of rows from the result
*/
function CountResult($resource=null)
{
if ($resource === null) {
$this->SetError('Resource is a null object');
return false;
}
// Edit by Invelo (DH)
if (!is_resource($resource)) {
// If already object, return num_rows
if (is_object($resource)){
return (int)$resource->num_rows;
}
$resource = $this->Query($resource);
$count = mysqli_num_rows($resource);
return $count;
} else {
$count = mysqli_num_rows($resource);
return $count;
}
// End Edit
}
/**
* Count (Same as CountResult
* Returns the number of rows returned for the resource passed in
*
* @param String $resource The result from calling Query
*
* @see Query
* @see SetError
*
* @return Int Number of rows from the result
*/
function Count($resource=null)
{
if ($resource === null) {
$this->SetError('Resource is a null object');
return false;
}
// Edit by Invelo (DH)
if (!is_resource($resource)) {
// If already object, return num_rows
if (is_object($resource)){
return (int)$resource->num_rows;
}
$resource = $this->Query($resource);
$count = mysqli_num_rows($resource);
return $count;
} else {
$count = mysqli_num_rows($resource);
return $count;
}
// End Edit
}
/**
* Concat
* Concatentates multiple strings together. This method is mysql specific. It doesn't matter how many arguments you pass in, it will handle them all.
* If you pass in one argument, it will return it straight away.
* Otherwise, it will use the mysql specific CONCAT function to put everything together and return the new string.
*
* @return String Returns the new string with all of the arguments concatenated together.
*/
function Concat()
{
$num_args = func_num_args();
if ($num_args < 1) { return func_get_arg(0); } $all_args = func_get_args(); $returnstring = 'CONCAT('.implode(',', $all_args).')'; return $returnstring; } /** * Quote * Quotes the string ready for database queries. Runs mysql_escape_string or mysql_real_escape_string depending on the php version. * * @param Mixed $var Variable you want to quote ready for database entry. * * @return Mixed $var with quotes applied to it appropriately */ function Quote($var='') { if (is_string($var) || is_numeric($var) || is_null($var)) { if ($this->use_real_escape) {
return @mysqli_real_escape_string($this->connection, $var);
} else {
return @mysqli_escape_string($this->connection, $var);
}
} else if (is_array($var)) {
return array_map(array($this, 'Quote'), $var);
} else if (is_bool($var)) {
return (int) $var;
} else {
trigger_error("Invalid type passed to DB quote ".gettype($var), E_USER_ERROR);
return false;
}
}
/**
* LastId
*
* Returns the last insert id
*
* @return Int Returns mysqli_insert_id from the database.
*/
function LastId($seq='')
{
return mysqli_insert_id($this->connection);
}
/**
* CheckSequence
*
* Checks to make sure a sequence doesn't have multiple entries.
*
* @return Boolean Returns true if there is exactly 1 entry in the sequence table, otherwise returns false.
*/
function CheckSequence($seq='')
{
if (!$seq) {
return false;
}
$query = "SELECT COUNT(*) AS count FROM " . $seq;
$count = $this->FetchOne($query, 'count');
if ($count == 1) {
return true;
}
return false;
}
/**
* ResetSequence
*
* Resets a sequence to a new id.
*
* @param String $seq The sequence name to reset.
* @param Int $newid The new id to set the sequence to.
*
* @return Boolean Returns true if the sequence is reset, otherwise false.
*/
function ResetSequence($seq='', $newid=0)
{
if (!$seq) {
return false;
}
$newid = (int)$newid;
if ($newid <= 0) { return false; } $query = "TRUNCATE TABLE " . $seq; $result = $this->Query($query);
if (!$result) {
return false;
}
// since a sequence table only has one field, we don't care what the fieldname is.
$query = "INSERT INTO " . $seq . " VALUES (" . $newid . ")";
$result = $this->Query($query);
if (!$result) {
return false;
}
return $this->CheckSequence($seq);
}
/**
* OptimizeTable
*
* Runs "optimize" over the tablename passed in. This is useful to keep the database reasonably speedy.
*
* @param String $tablename The tablename to optimize.
*
* @see Query
*
* @return Mixed If no tablename is passed in, this returns false straight away. Otherwise it calls Query and returns the result from that.
*/
function OptimizeTable($tablename='')
{
if (!$tablename) {
return false;
}
$query = "OPTIMIZE TABLE " . $tablename;
return $this->Query($query);
}
/**
* NumAffected
*
* Returns the number of affected rows on success, and -1 if the last query failed.
*
* @param mixed $null Placeholder for postgres compatability
*
* @return int
*/
function NumAffected($null=null)
{
return mysqli_affected_rows($this->connection);
}
}