MySQL Class

[kickassamd]

Just seeing what everyone thinks of my updated mySQL class.

 

Post suggestions, thoughts etc etc about it

 

<?
/******************************************************************************* 
*                         MySQL Database Class 
******************************************************************************* 
*      Author:     Chris York (KickassAMD) 
*      Email:      admin@kickassamd.com 
*      Website:    http://www.kickassamd.com 
* 
*      File:       mysqldb.class.php 
*      Version:    v1.1.2
*      Copyright:  (c) 2006 - Chris York  
*                  You are free to use, distribute, and modify this software  
*                  under the terms of the GNU General Public License. 
*       
******************************************************************************* 
*  VERSION HISTORY: 
*   
*      v1.0.0 [06.08.2006] - Initial Version.
*
*		v1.0.2 [10.02.2006] - Rewrote Class Completely.
*							- Updated Functions.
*							- New debugging system in place aswell.
*							- PHP 5 Will be required for usage of this class.
*
*		v1.0.3 [10.08.2006] - Small Changes To getLoggedQueries() function.
*							- Added new function execCreateDatabase().
*							- Added new function execDropDatabase().
*							- Added new function optimizeTable().
*
*		v1.0.4 [10.09.2006] - Added new function execQueryFromFile().
*
*		v1.0.5 [01.15.2007] - Removed class constructer. New functions to set
*							  SQL connection information via set().
*
*		v1.0.6 [02.06.2007] - Removed strtolower() from all query functions
*					 		  as it caused issues with case sensitive information.
*							- Added email function to mail a error report on SQL
*							  errors.
*
*		v1.0.7 [03.14.2007] - Added new function getNumFields()
*							- Added new function getDatabaseList()
*							- Added new function getTableList()
*							- Added new function getFieldName()
*							- Added new function getFieldType()
*							- Added new function getFieldLength()
*							- Added new function getFieldFlag()
*
*      v1.0.8 [04.15.2007] - Small change within execSafeQuery()
*
*		v1.0.9 [09.14.2007] - execQueryFromFile() Now adds table prefixes to table names.
*							- Small changes to execQuery() & execSafeQuery()
*
*
*		v1.1.0 [09.15.2007] - execQueryFromFile() Now removes comments from file based queries
*
*		v1.1.1 [09.16.2007] - Improvements to addPrefixToFile() and stripComments().
*							- Removed eMail error reporting functionality.
*							- Changes to getLoggedErrors() & getLoggedQueries().
*
*		v1.1.2 [10.10.2007] - Added new function execFormatedQuery()
*							- Added new function formatTimeStamp()
*							- Added new function formatDateTime()
*							- Added new function execQuery2XML()
*							- Added new function getObject()
* 
******************************************************************************* 
*  DESCRIPTION: 
* 
*      This class aids in MySQL database connectivity. It was written with 
*      my specific needs in mind.  It simplifies the database tasks I most 
*      often need to do thus reducing redundant code.  It is also written 
*      with the mindset to provide easy means of debugging erronous sql and 
*      data during the development phase of a web application.
* 
*      The future may call for adding a slew of other features, however, at 
*      this point in time it just has what I often need.  I'm not trying to 
*      re-write phpMyAdmin or anything.    Hope you find it  useful.
* 
*******************************************************************************
*/


/**
* @author Chris York (KickassAMD)
* @link http://www.kickassamd.com
* @version v1.1.2
* @name MySQL Server Database Class
* @package Core Functions For SQL Usage
*/
class mysqlDatabase
{

/**
 * Class Constructor
 */
public function __construct()
{

}

/**
 * Class Destructer
 */
public function __destruct()
{
	$this->terminateConn();
}

///////////////////////////////////////////////////////////////////////////
///////////////////// Below is MySQL Configuration Values /////////////////
///////////////////// These values cannot be set directly /////////////////
//////////////// And must be set using the set() fuctions /////////////////
/////////////////////////////// v1.0.6 //////////////////////////////////// 
///////////////////////////////////////////////////////////////////////////

/**
 * SQL Server Database Username
 * @var String
 */
private $_dbUsername;

/**
 * SQL Server Database Password
 * @var String
 */
private $_dbPassword;

/**
 * SQL Server Hostname \ IP Address
 * @var String
 */
private $_dbServerHost;

/**
 * SQL Server Database Name
 * @var String
 */
private $_dbSelectName;

/**
 * SQL Server Database Table Prefix (If Any)
 * @var String
 */
private $_dbTblPrefix;

/**
 * Maintain Persistant Connection To SQL Server
 * @var boolean
 */
private $_dbUsePersistantConn;

/**
 * SQL Server Connection Resource
 * @var Resource
 */
private $_dbResource;

/**
 * SQL Server Query Logger
 * @var Array()
 */
private $_dbQueryLogger = array();

/**
 * SQL Server Error Logger
 * @var Array()
 */
private $_dbErrorLogger = array();

///////////////////////////////////////////////////////////////////////////
////////////////// Below is MySQL Configuration Functions /////////////////
/////////////////////////////// v1.0.6 ////////////////////////////////////
///////////////////////////////////////////////////////////////////////////

/**
 * Set Database Username
 *
 * @param string $Username
 */
public function setUsername($Username)
{
	$this->_dbUsername = $Username;
}

/**
 * Set Database Password
 *
 * @param string $Password
 */
public function setPassword($Password)
{
	$this->_dbPassword = $Password;
}

/**
 * Set Database IP \ HostName
 *
 * @param string $Host
 */
public function setServerDSN($Host)
{
	$this->_dbServerHost = $Host;
}

/**
 * Set Database For Use
 *
 * @param string $DbName
 */
public function setDatabase($DbName)
{
	$this->_dbSelectName = $DbName;
}

/**
 * Use persistant connection to database
 *
 * @param boolean $Persistant
 */
public function setPersistant($Persistant = false)
{
	$this->_dbUsePersistantConn = $Persistant;
}

/**
 * Sets Table Prefix
 *
 * @param String $tablePrefix  (Name Of Prefix)
 * @param String $prefixSeperator  (Symbol That Separates Prefix From Table _ Is Default)
 */
public function setTblPrefix($tablePrefix, $prefixSeperator = "_")
{
	$this->_dbTblPrefix = $tablePrefix.$prefixSeperator;
}

///////////////////////////////////////////////////////////////////////////
///////////////////// Below is Functions Needed For MySQL /////////////////
/////////////////////////////// v1.0.6 //////////////////////////////////// 
///////////////////////////////////////////////////////////////////////////

/**
 * Connect To SQL Server And Select Database
 *
 * @param boolean $selectDb  Select Database For Use? True | False
 * @return boolean
 */
public function getConnected($selectDb = true)
{
	if ($this->_dbUsePersistantConn)
	{
		$this->_dbResource = @mysql_pconnect($this->_dbServerHost, $this->_dbUsername, $this->_dbPassword);
	}
	else
	{
		$this->_dbResource = @mysql_connect($this->_dbServerHost, $this->_dbUsername, $this->_dbPassword);
	}

	if ($this->_dbResource)
	{
		if ($selectDb)
		{
			if (@mysql_select_db($this->_dbSelectName))
			{
				return true;
			}
			else
			{
				$this->logError($this->getError(), $this->getErrorNum());
				return false;
			}
		}
		else
		{
			$this->logError("Connected To Server, No Database Selected","????");
			return false;
		}
	}
	else
	{
		$this->logError($this->getError(),$this->getErrorNum());
		return false;
	}
}

/**
 * Create Database On SQL Server
 * If $dbName is not defined, Function will use one defined in class constructor.
 * 
 * @param String $dbName
 * @return boolean
 */
public function execCreateDatabase($dbName)
{
	if ($this->_dbResource)
	{
		if ($dbName != '')
		{
			$sql = "create database `$dbName`";
		}
		else
		{
			$sql = "create database `".$this->_dbSelectName."`";
		}

		$this->execQuery($sql);
	}
	else
	{
		$this->logQuery($sql, "No Connection To Server", "x123");
		return false;
	}
}

/**
 * Drop Database On SQL Server
 * If $dbName is not defined, Function will use one defined in class constructor
 *
 * @param String $dbName
 * @return boolean
 */
public function execDropDatabase($dbName)
{
	if ($this->_dbResource)
	{
		if ($dbName != '')
		{
			$sql = "drop database `$dbName`";
		}
		else
		{
			$sql = "drop database `".$this->_dbSelectName."`";
		}
		$this->execQuery($sql);
	}
	else
	{
		$this->logQuery($sql, "No Connection To Server", "x123");
		return false;
	}
}

/**
 * Execute SQL Query On Database
 *
 * @param String  $sql
 * @param Boolean $execQuery (Execute Query = True)
 * @param Boolean $printSQL  (Echo SQL To Screen = True)
 * @return Boolean
 */
public function execQuery($sql, $execQuery = true, $printSQL = false)
{
	if ($this->_dbResource)
	{
		$sql = trim($sql);
		if ($execQuery)
		{
			if ($result = @mysql_query($sql, $this->_dbResource))
			{
				$this->logQuery($sql, $this->getError(), $this->getErrorNum());
				return $result;
			}
			else
			{
				$this->logQuery($sql, $this->getError(), $this->getErrorNum());
				return false;
			}
		}
		else
		{
			$this->logQuery($sql, "Query Not Executed!", "x124");
			return false;
		}
		if ($printSQL)
		{
			echo $sql."<br />";
		}
	}
	else
	{
		$this->logQuery($sql, "No Connection To Server", "x123");
		return false;
	}
}

/**
 * Only Allow 'SELECT' SQL Statements To Be Executed On Database
 *
 * @param String  $sql
 * @param Boolean $execQuery (Execute Query = True)
 * @param Boolean $printSQL  (Echo SQL To Screen = True)
 * @return Boolean
 */
public function execSafeQuery($sql, $execQuery = true, $printSQL = false)
{
	if ($this->_dbResource)
	{
		$sql = trim($sql);
		if (substr(strtolower($sql), 0, 6) == 'select')
		{
			return $this->execQuery($sql, $execQuery, $printSQL);
		}
		else
		{
			$this->logQuery($sql, "Tried Executing An Unsafe Query!", "x127");
			return false;
		}
	}
	else
	{
		$this->logQuery($sql, "No Connection To Server", "x123");
		return false;
	}
}

/**
 * Splits A Large SQL File Into Smaller Queries And Executes Them
 *
 * @param string $file  (Path to SQL file to execute from)
 * @param boolean $execQuery
 */
public function execQueryFromFile($file, $execQuery = true)
{
	if ($fp = @fopen($file, "r"))
	{
		$fr = trim(fread($fp, filesize($file)));
		// Remove Comments
		$preg = $this->stripComments($fr);

		$sql_queries = explode(";", $preg);
		foreach ($sql_queries as $query)
		{
			if ($query != "")
			{
				$query = trim($query);
				// Add Prefix To Table
				$query = $this->addPrefixToFile($query);
				$this->execQuery($query, $execQuery);
			}
		}
	}
	else
	{
		$this->logError("File <i>$file</i> was not found check file path and try again", "x126");
	}
}

/**
 * Format Query For Safe Execution On Database Server
 * This WILL execute query on server
 *
 * @param String $query
 * @param Boolean $execute
 * @return Boolean || String
 */
public function execFormatedQuery($query)
{
	$args  = func_get_args();
	$query = array_shift($args);
	$query = str_replace("?", "%s", $query);
	$args  = array_map('mysql_real_escape_string', $args);
	array_unshift($args,$query);
	$query = call_user_func_array('sprintf',$args);

	return $this->execQuery($query);
}

/**
 * Add Table Prefix To The Beginning Of A Table
 *
 * @param String $tableName
 * @return String
 */
public function addTblPrefix($tableName = '')
{
	if ($tableName != '')
	{
		return $this->_dbTblPrefix.$tableName;
	}
	else
	{
		return $this->_dbTblPrefix;
	}
}

/**
 * Add Table Prefix To Existing File Based Query
 * Can only handle CREATE | INSERT | DROP | ALTER statements.
 * 
 * @param String $string
 * @return String
 */
private function addPrefixToFile($string)
{
	if (stristr($string, "CREATE TABLE"))
	{
		$result = preg_replace('/CREATE\\sTABLE\\s`(\\b.*\\b)`/', 'CREATE TABLE `'.$this->_dbTblPrefix.'\\1`', $string);
	}
	elseif (stristr($string, "INSERT INTO"))
	{
		$result = preg_replace('/INSERT\\sINTO\\s`(\\b.*\\b)`/', 'INSERT INTO `'.$this->_dbTblPrefix.'\\1`', $string);
	}
	elseif (stristr($string, "DROP TABLE"))
	{
		$result = preg_replace('/DROP\\sTABLE\\s`(\\b.*\\b)`/', 'DROP TABLE `'.$this->_dbTblPrefix.'\\1`', $string);
	}
	elseif (stristr($string, "DROP TABLE IF EXISTS"))
	{
		$result = preg_replace('/DROP\\sTABLE\\sIF\\sEXISTS\\s`(\\b.*\\b)`/', 'DROP TABLE IF EXISTS `'.$this->_dbTblPrefix.'\\1`', $string);
	}
	elseif (stristr($string, "ALTER TABLE"))
	{
		$result = preg_replace('/ALTER\\sTABLE\\s`(\\b.*\\b)`/', 'ALTER TABLE `'.$this->_dbTblPrefix.'\\1`', $string);
	}
	elseif (stristr($string, "DROP VIEW IF EXISTS"))
	{
		$result = preg_replace('/DROP\\sVIEW\\sIF\\sEXISTS\\s`(\\b.*\\b)`/', 'DROP VIEW IF EXISTS `'.$this->_dbTblPrefix.'\\1`', $string);
	}
	elseif (stristr($string, "DROP VIEW"))
	{
		$result = preg_replace('/DROP\\sVIEW\\s`(\\b.*\\b)`/', 'DROP VIEW `'.$this->_dbTblPrefix.'\\1`', $string);
	}
	elseif (stristr($string, "CREATE VIEW"))
	{
		$result = preg_replace('/`\b(\w+)\b`/', '`'.$this->_dbTblPrefix.'\\1`', $string);
	}
	else
	{
		$result = $string;
	}

	return $result;
}

/**
 * Removes All Comments From File Based Query
 * NOTE: Does not actually remove comments from file
 * It only removed comments from the string being based to it.
 * 
 * @param String $string
 * @return String
 */
private function stripComments($string)
{
	$result = preg_replace('%(?>(?>--|#).*|\/\*.*?\*\/)%', '', $string);

	return $result;
}
/**
 * Optimize Tables On SQL Server
 *
 * @param String $tableName  Can be an array to optimize multiple tables at once
 * @return boolean
 */
public function optimizeTable($tableName)
{
	if ($this->_dbResource)
	{
		if (is_array($tableName))
		{
			foreach ($tableName as $optimize)
			{
				$sql = "optimize table `$optimize`";
				$this->execQuery($sql);
			}
		}
		else
		{
			$sql = "optimize table `$tableName`";
			$this->execQuery($sql);
		}
	}
	else
	{
		$this->logQuery($sql, "No Connection To Server", "x123");
		return false;
	}
}

/**
 * Clean Database String
 *
 * @param Stromg $string
 * @return String
 */
public function cleanString($string)
{
	return @mysql_real_escape_string($string, $this->_dbResource);
}

/**
 * Execute A Query Then Output Results To XML Document
 *
 * @param String $query
 */
public function execQuery2XMLOutput($query)
{
	$result = $this->execSafeQuery($query);

	$xml = '<?xml version="1.0" encoding="ISO-8859-1"?>';
	$xml .= '<output>';

	// Loop Over Rows
	while ($row = $this->getObject($result))
	{
		$xml .= "<data>";

		// Loop Over Columns
		for ($i = 0; $i < $this->getNumFields($result); $i++)
		{
			$fieldName = $this->getFieldName($result, $i);
			$xml .= "<$fieldName>";

			if (!empty($row->$fieldName))
			{
				$xml .= "{$row->$fieldName}";
			}
			$xml .= "</$fieldName>";
		}

		$xml .= "</data>";
	}
	$xml .= "</output>";

	if (!headers_sent())
	{
		header("Content-Type: application/xml");
	}

	echo $xml;
}

/**
 * Returns ID Given From 'Insert' Queries
 *
 * @return Integer
 */
public function getInsertId()
{
	return @mysql_insert_id($this->_dbResource);
}

/**
 * Return Number Of Rows Returned From 'select' Query
 *
 * @param Resource $result  (MySQL Query Result)
 * @return Integer
 */
public function getNumRows($result)
{
	return @mysql_num_rows($result);
}

/**
 * Return Number Of Fields In Table
 *
 * @param Resource $result
 * @return integer
 */
public function getNumFields($result)
{
	return @mysql_num_fields($result);
}

/**
 * Retuned Number Of Affected Rows From 'insert' or 'update' Query
 *
 * @return Integer
 */
public function getAffectedRows()
{
	return @mysql_affected_rows($this->_dbResource);
}

/**
 * Get a result row as an enumerated array
 *
 * @param Resource $result  (MySQL Query Result)
 * @return String  (Array)
 */
public function getRow($result)
{
	$returnArray = array();
	while ($dbArray = @mysql_fetch_row($result))
	{
		$returnArray[] = $dbArray;
	}
	return $returnArray;
}

/**
 * Fetch a result row as an associative array
 *
 * @param Resource $result  (MySQL Query Result)
 * @return String  (Array)
 */
public function getArray($result)
{
	$returnArray = array();
	while ($dbArray = @mysql_fetch_assoc($result))
	{
		$returnArray[] = $dbArray;
	}
	return $returnArray;
}

/**
 * Fetch a result in enumerated & associative array
 *
	 * @param Resource $result  (MySQL Query Result)
 * @return String  (Array)
 */
public function getBoth($result)
{
	$returnArray = array();
	while ($dbArray = @mysql_fetch_array($result, MYSQL_BOTH))
	{
		$returnArray[] = $dbArray;
	}
	return $returnArray;
}

/**
 * Get a list of all databases available to this user
 *
 * @return String (array)
 */
public function getDatabaseList()
{
	$databases = @mysql_list_dbs($this->_dbResource);
	$returnArray = array();

	while ($row = mysql_fetch_object($databases))
	{
		$returnArray[] = $row->Database;
	}

}

/**
 * Get a list of all tables in a specified database
 * If database isnt defined, script will use one defined in class.
 *
 * @param String $database
 * @return string (array)
 */
public function getTableList($database = "")
{
	// Use Function defined database... Or class defined
	if ($database != "")
	{
		$listTable = @mysql_list_tables($database, $this->_dbResource);
	}
	else
	{
		$listTable = @mysql_list_tables($this->_dbSelectName, $this->_dbResource);
	}

	// Get array list of tables
	$oddArray = $this->getRow($listTable);

	$returnArray = array();

	// For() loop to make array nicer 
	for ($i = 0; $i < sizeof($oddArray); $i++)
	{
		$returnArray[] = $oddArray[$i][0];
	}

	return $returnArray;
}

/**
 * Get a list of all fields (columns) in a specified table.
 *
 * @param resource $result
 * @param Int $i
 * @return String (array)
 */
public function getFieldName($result, $i)
{
	return @mysql_field_name($result, $i);
}

/**
 * Return field (column) type in a specified table
 *
 * @param resource $result
 * @param Int $i
 * @return String (array)
 */
public function getFieldType($result, $i)
{
	return @mysql_field_type($result, $i);
}

/**
 * Return field (column) length
 *
 * @param resource $result
 * @param Int $i
 * @return integer (array)
 */
public function getFieldLength($result, $i)
{
	return @mysql_field_len($result, $i);
}

/**
 * Returns field (column) flags
 * ie.. primary key, auto_increment etc etc.
 *
 * @param resource $result
 * @param Int $i
 * @return string (array)
 */
public function getFieldFlag($result, $i)
{
	return @mysql_field_flags($result, $i);
}

/**
 * Get MySQL Object
 *
 * @param Resource $result
 * @return Object
 */
public function getObject($result)
{
	return @mysql_fetch_object($result);
}

/**
 * Format TIMESTAMP Column Datatype To Human Format
 *
 * @param TimeStamp $timestamp
 * @param String $format
 * @return String
 */
public function formatTimeStamp($timestamp, $format = "F j, Y, g:i a")
{
	$year = strval(substr($timestamp, 0, 4)); 
	$month = strval(substr($timestamp, 4, 2)); 
	$day = strval(substr($timestamp, 6, 2)); 
	$hour = strval(substr($timestamp, 8, 2)); 
	$minute = strval(substr($timestamp, 10, 2)); 

	return date($format, mktime($hour, $minute, 0, $month, $day, $year)); 
}

/**
 * Format DATETIME Column Datatype to Human Format
 *
 * @param DateTime $dateTime
 * @param String $format
 * @return String
 */
public function formatDateTime($dateTime, $format = "F j, Y, g:i a") 
{ 
	$year = strval(substr($dateTime, 0, 4)); 
	$month = strval(substr($dateTime, 5, 2)); 
	$day = strval(substr($dateTime, 8, 2)); 
	$hour = strval(substr($dateTime, 11, 2)); 
	$minute = strval(substr($dateTime, 14, 2)); 

	return date($format, mktime($hour, $minute, 0, $month, $day, $year)); 
} 

/**
 * Get Number Of Queries Executed
 *
 * @return Int
 */
public function getQueryCount()
{
	return count($this->_dbQueryLogger);
}

/**
 * Return MySQL Error String
 *
 * @return String
 */
private function getError()
{
	return @mysql_error();
}

/**
 * Return MySQL Error Number
 *
 * @return Integer
 */
private function getErrorNum()
{
	return @mysql_errno();
}

/**
 * Add A Query To Logger System
 *
 * @param string  $sql
 * @param string  $error
 * @param integer $errno
 */
private function logQuery($sql, $error = null, $errno = null)
{
	$this->_dbQueryLogger[] = array("sql" => $sql, "error" => $error, "errno" => $errno); 
}

/**
 * Add Error To Logger System
 *
 * @param string $message
 */
private function logError($error = null, $errno = null)
{
	$this->_dbErrorLogger[] = array("error" => $error, "errno" => $errno); 
}

/**
 * Return All Logged Queries In HTML Format
 *
 * @return String
 */
public function getLoggedQueries()
{
	$return = '<table width="100%" cellspacing="1"><tr><th>Queries Logged</th></tr>';
	foreach ($this->_dbQueryLogger as $query)
	{
		if ($query['error'] != null)
		{
			$return .= '<tr><td><span style="color:#ff0000;">'.htmlspecialchars($query['sql']).'<br /><b>Error number: </b>'.htmlspecialchars($query['errno']).'<br /><b>Error message: </b>'.htmlspecialchars($query['error']).'</span></td></tr>';
			$return .= '<tr><td><hr color="#CCCCCC" /></td></tr>';
		}
		else
		{
			$return .= '<tr><td><span style="color:#009933;">'.htmlspecialchars($query['sql']).'</span></td></tr>';
			$return .= '<tr><td><hr color="#CCCCCC" /></td></tr>';
		}
	}
	$return .= '<tr><td>Total Queries: <span style="color:#009933;">'.count($this->_dbQueryLogger).'</span></td></tr>';
	$return .= '</table>';

	echo $return;
}

/**
 * Return All Logged Errors In HTML Format
 *
 * @return String
 */
public function getLoggedErrors()
{
	$return = '<table width="100%" cellspacing="1"><tr><th>Errors Logged</th></tr>';
	foreach ($this->_dbErrorLogger as $error)
	{
		$return .= '<tr><td><span style="color:#ff0000;"><br /><b>Error number: </b>'.htmlspecialchars($error['errno']).'<br /><b>Error message: </b>'.htmlspecialchars($error['error']).'</span></td></tr>';
		$return .= '<tr><td><hr color="#CCCCCC" /></td></tr>';
	}
	$return .= '<tr><td>Total Errors: <span style="color:#009933;">'.count($this->_dbErrorLogger).'</span></td></tr>';
	$return .= '</table>';

	echo $return;
}

/**
 * Terminate Connection To SQL Server
 *
 */
public function terminateConn()
{
	@mysql_close($this->_dbResource);
	$this->logError("Connection To The Server Was Terminated By The User", "x125");
}
}
?>

[teng84]

honestly i dont like your class lol

your functions, most of your functions work alone IMO functions should be in a class when they help each other to obtain the same goal im not saying all functions in that class should have that characteristic but its good to see most of your functions are working like a team

 

AND i dont like the way you do this

public function setUsername($Username)

{

$this->_dbUsername = $Username;

}

 

i think setting a variables is much shorter doing it manually and besides whats the use? functions makes codes easy and short do your think it will give youe shorter coding in easy way?

 

Well im not that good in OO but i guess theres still improvement needed

[kickassamd]

lol. Sure

 

Also all my functions do work together to achieve the same goal, the purpose of this class was to aid in debugging mySQL queries, errors and other items.

 

Also in OOP you are not supposed to allow direct access to your variables hense why I use functions to set them.

 

You do not find it usefull that is fine, many others do.

[RichardRotterdam]

Also in OOP you are not supposed to allow direct access to your variables hense why I use functions to set them.

 

I often wonder why that is so. using Java i also use set and get methods and set all of the variables to private. But in php i dont see the use of it. Does using set and get functions for the class variables make your code any safer? Somehow i doubt that

[pocobueno1388]

Honestly, I've never really understood the point of a MySQL class...isn't it easy enough already to do what you want with it?

[teng84]

sql class IMO makes your will make your querying mor efficeint no need to worry about formating your query couse its already done eg.. update i found that part as the most sensitive

when i do my my update using or select using it i simply do this

 

$array[field1] ='value';

$array[field2] ='value';

$array[field2] ='value';

$array[field2] ='value';

$condtion= 'x=x';

$class->update($array,'tablename',$condition);

 

for me that look very easy to understand and see what the writers mean

[trq]

Honestly, I've never really understood the point of a MySQL class...isn't it easy enough already to do what you want with it?

 

One good thing about using a database class is that it gives you an abstraction away from your actual dbms. For example, if you want your code to be portable over several different dbms you simply create a different class for each dbms with the same functionality.

 

Example, here we define two database classes with identicle functionality. We also define a small helper function that gets us a database object based on a string we pass it.

 

<?php

  class mysql {
    function select() {$sql};
    function update() {$sql};
    function delete() {$sql};
  }

  class postgres {
    function select() {$sql};
    function update() {$sql};
    function delete() {$sql};
  }

  function getdb($type) {
    return new $type;
  }

?>

 

Now, in our client code we might have something like...

 

<?php

  include 'config.php'; // include the $dbtype variable.
  $db = getdb($dbtype);
  $db->select("SELECT * FROM foo");

?>

 

This enables you to change dbms's simply by changing one variable within the config.php file. You no longer have to go through all your code changing every call to mysql_query() to pg_query() just to use postgres.

[Millar]

In my complete honesty that class seems a bit long winded. Why spend time doing multiple queries to set your connection data. Rather than send an array to the function with all the settings or send each bit of data in a new function parameter?

 

Also MySQL classes can be useful if you want to have multiple connection instances.

[aschk]

So i think what thorpe was trying to say was the abstract database should

a) Use a singleton pattern

b) Be database unspecific by abstraction.

 

So really there needs to be an interface or abstract class in place first otherwise there's no guarantee that the classes are interchangeable.

<?php
// Abstraction/Interface
interface database {
  function select();
  function update();
  function delete();
}

// Concrete class.
class mysql implements database {
  function select() { // do it here }
  function update() { // do it here }
  function delete() { // do it here }
}
// Postgres concrete class.
class postgres implements database {
  function select() { // different implementation }
  function update() { // different implementation }
  function delete() { // different implementation }
}

?>

[aschk]

Oops, i should probably also provide the singleton part

 

class mysql implements database {
  private static $engine = null;

  private function __construct(){
    // Do connection stuff here.
  }

  public static function getEngine(){
    if(is_null(self::$engine){
      self::$engine = new __CLASS__;
    }
    return self::$engine;
  }

}

 

Thus you can do the following :

$db = mysql::getEngine();

$db->select();

 

This saves you of recreating another mysql object on the stack each time and attempting to reconnect.

[sKunKbad]

Why not just use php's built in PDO class?

[aschk]

Yes indeed, however not all PHP hosting providers provide you with PDO, thus leaving you with the problem that it might work locally, but not on your hosting environment.

[kickassamd]

Yes I am seen a number of hosts who do not have the PDO extensions enabled, this class was ORIGINALLY built when PHP 5 was very beta, i just adapted it to run with 5, next one will use mysqli extensions.