Location: PHPKode > scripts > Table Data Management > table-data-management/MySQLConnection.class.php
<?
/**
* 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>";
}
*/

?>
Return current item: Table Data Management