<?
/**
* MySQL database class for setting up a database connection and query functionality
* The queries that fail are stored into an error array which can be retrieved for further analyses.
*
* @uses TableColumn
* @version 1.0
* @author Sasa Radovanovic
* @package database
*
*/
define("FETCH_ARRAY", 1);
define("FETCH_ASSOC", 2);
define("FETCH_ROW", 3);
class MySQLConnection {
/** Database information **/
var $_database;
var $_user;
var $_pass;
var $_host;
var $_link;
var $_connected = false;
var $_arrQuery; //Array containing queries, corresponding with the resultset in arrResult (by key)
var $_arrResult; //Array containing resultsets, only needed when more then one resultset is asked for
/** @var _begincount Used for keeping track of the amount of commits */
var $_begincount;
/** @var _blnRollbackSet Can we rollback yet? */
var $_blnRollbackSet;
/**
* Initialize the class with the correct parameters needed to connect to the database
* @access public
* @param String $database Name of the database to connect to
* @param String $user Name of the user connecting to the database
* @param String $pass Password of the user connectin to the database
* @param String $host Host where the database resides
*/
function MySQLConnection($database, $user, $pass = null, $host = "mysqlhost") {
$this->_database = $database;
$this->_user = $user;
$this->_pass = $pass;
$this->_host = $host;
//Open the link to the database
$this->_open();
$this->_begincount = 0;
$this->_blnRollbackSet = false;
}
/**
* Create a link to the selected database
* @access private
*/
function _open() {
if(isset($this->_pass)) {
$this->_link = mysql_connect($this->_host, $this->_user, $this->_pass) or trigger_error("can not connect to $this->_user@$this->_host:$this->_database", DB_FATAL);
} else {
$this->_link = mysql_connect($this->_host, $this->_user) or trigger_error("can not connect to $this->_user@$this->_host:$this->_database", DB_FATAL);
}
mysql_select_db($this->_database, $this->_link); //Selects the appropriate database from the server
$this->_connected = true;
}
/**
* Close link to database if it is open
* @access public
*/
function close () {
if ($this->_connected) {
mysql_close($this->_link);
}
}
/**
* Select the database to be used for the querying
*
* @param String $dbName Name of the database to be selected
* @return Bool True/false depending on succes or failure
*/
function selectDatabase($dbName) {
$this->_database = $dbName;
return mysql_select_db($this->_database, $this->_link); //Selects the appropriate database from the server
}
/**
* Query the database with the query specified in the parameter, the query and resultset are both saved into an array
* this way we make sure, that we have the abbility to get results from two different result sets
*
* @param String $query The query to be sent to the database
* @param Int $resultNr The nr of the resultset (the place/index in the array)
*
* @access Public
*/
function query($query, $resultNr = 0) {
$this->_arrQuery[$resultNr] = $query;
$this->_arrResult[$resultNr] = mysql_query($query, $this->_link) or trigger_error(mysql_errno().": ".mysql_error().": ".$query, WARNING);
}
/**
* Retrieve the number of rows affected by the last query
*
* @param Int $resultNr The nr of the resultset (the place/index in the array)
*
* @return Int Nr of affected rows
*/
function getNumRows($resultNr = 0) {
return mysql_num_rows($this->_arrResult[$resultNr]);
}
/**
* Get field type for the specified field/column
*
* @param Int $fieldOffset The key of the field in the resultset
* @param Int $reslultNr The nr of the resultset (the place/index in the array)
*/
function getFieldType($fieldOffset, $resultNr = 0) {
return mysql_field_type($this->_arrResult[$resultNr], $fieldOffset);
}
/**
* Retrieve found data through the specified (if parameter is set) resultset
*
* @param Int resultNr The nr of the resultset (the place/index in the array)
*
* @access public
*/
function result($resultNr = 0, $fetchType = FETCH_ARRAY) {
switch($fetchType) {
case FETCH_ROW:
return mysql_fetch_row($this->_arrResult[$resultNr]);
break;
case FETCH_ASSOC:
return mysql_fetch_assoc($this->_arrResult[$resultNr]);
break;
case FETCH_ARRAY:
default:
return mysql_fetch_array($this->_arrResult[$resultNr]);
break;
}
}
/**
* Retrieve a complete associative array containing the results
*
* @param Int $resultNr The nr of the resultset (the place/index in the array)
* @param String $columnName Name of the specific column to store into the assoc array
*
* @access public
*/
function assocArrayResult($resultNr = 0, $columnName = null, $indexColumn = null) {
$arrAssoc = array();
$index = -1;
//Every array retrieved with the fetch command will be stored into the assoc array
while($row = mysql_fetch_assoc($this->_arrResult[$resultNr])) {
if(is_null($indexColumn)) {
$index++;
} else {
$index = $row[$indexColumn];
}
if(isset($columnName)) {
$arrAssoc[$index] = $row[$columnName];
} else {
$arrAssoc[$index] = $row;
}
}
return $arrAssoc;
}
/**
* Get the id of the last inserted record
*
* @access public
*
* @return Int $id Id of the last inserted record
*/
function insertId() {
if($id = mysql_insert_id($this->_link)) {
return $id;
}
}
/**
* Start a transaction by executing a "BEGIN" query
*
* @access public
*/
function begin() {
if($this->_begincount == 0) {
$this->_blnRollbackSet = false;
$this->query("BEGIN");
}
$this->_begincount++;
}
/**
* End a transaction (acknowledge the action) by executing a "COMMIT" query
*
* @access public
*/
function commit() {
$this->_begincount--;
if($this->_begincount <= 0) {
if(!$this->_blnRollbackSet) {
$this->query("COMMIT");
} else {
$this->rollback();
}
}
}
/**
* Rollback a transaction (undo the action) by executing a "ROLLBACK" query
*
* @access public
*/
function rollback() {
$this->_begincount--;
$this->_blnRollbackSet = true;
if($this->_begincount <= 0) {
$this->query("ROLLBACK");
}
}
/**
* This function is defined here, because it generates a query (wow really?) and it's for now the
* only place it is usefull from it must always be used in conjuction with the TableColmun class
* withouth that class, this function has no use
* This function generates a query based on the parameters in the call
*
* @see TableColumn
*
* @param String $type The type of query to be generated (for now only 'insert' or 'update')
* @param String $table A string containing the table to do the query upon
* @param Array $arrColumns An array of TableColumn objecten containing the to be update/save columns (and data)
* @param Array $arrWhere An array of TableColumn objecten containing the where part
*
* @access public
*/
function generateQuery($type, $table, $arrColumns, $arrWhere = array()) {
switch($type) {
case "insert" :
foreach($arrColumns as $objTableColumn) {
if(is_object($objTableColumn)) {
if(isset($beforeValues)) $beforeValues.= ", ";
$beforeValues.= $objTableColumn->getName();
if(isset($afterValues)) $afterValues.= ", ";
$afterValues.= $objTableColumn->getDBValue();
}
}
$query.= "INSERT INTO ".$table." (".$beforeValues.") VALUES (".$afterValues.")";
break;
case "update" :
foreach($arrColumns as $objTableColumn) {
if(is_object($objTableColumn)) {
if(isset($setData)) $setData.= ", ";
$setData.= $objTableColumn->getName()." = ".$objTableColumn->getDBValue();
}
}
foreach($arrWhere as $objTableColumn) {
if(is_object($objTableColumn)) {
if(isset($whereData)) $whereData.= " AND ";
$whereData.= $objTableColumn->getName()." = ".$objTableColumn->getDBValue();
}
}
$query = "UPDATE ".$table." SET ".$setData." WHERE ".$whereData;
break;
}
return $query;
}
}
/* class usage example:
$dbConn = new MySQLConnection("dbName", "userName", "password");
$dbConn->query("SHOW TABLES");
while($row = $dbConn->result()) {
echo $row["Field"]."<br>";
}
*/
?>