Location: PHPKode > scripts > PDO Multi Connection Class > pdo_database.class.php
<?php
/**
* PDO Database class
*
* @author:    	Evert Ulises German Soto
* @copyright: 	wArLeY996 2011
* @version: 	1.5
*
* v1.5: Se agregaron dos nuevas funciones: 1.- ShowTables and 2.- ShowDBS
* v1.4: Se controlan los mensajes de error.
* v1.3: Se reparo un error, la funcion "insert" solo funcionaba para MySQL y SQLSrv.
* v1.2: Se agrego la libreria "mssql", y se agrego tambien la funcion getLatestId(tabla, id)
* v1.1: Al ejecutar un insert, delete o update, regresa el total de renglones afectados.
* v1.0: Se creo la clase funcional.
*/

class wArLeY_DBMS{

	var $database_types="";
	
	var $host;
	var $database;
	var $user;
	var $password;
	var $port;
	var $database_type;
	var $root_mdb;
	
	var $sql;	
	var $con;
	
	/**
	* Constructor of class - Initializes class and connects to the database
	* @param string $database_type the name of the database (sqlite2=SQLite2,sqlite3=SQLite3,sqlsrv=MS SQL,mssql=MS SQL,mysql=MySQL,pg=PostgreSQL,ibm=IBM,dblib=DBLIB,odbc=Microsoft Access,oracle=ORACLE,ifmx=Informix,fbd=Firebird)
	* @param string $host the host of the database
	* @param string $database the name of the database
	* @param string $user the name of the user for the database
	* @param string $password the passord of the user for the database
	*
	*  You can use this shortcuts for the database type:
	*
	* 		sqlite2	-> SQLite2
	* 		sqlite3	-> SQLite3
	* 		sqlsrv 	-> Microsoft SQL Server (Works under Windows, accept all SQL Server versions [max version 2008]) - TESTED
	* 		mssql 	-> Microsoft SQL Server (Works under Windows and Linux, but just work with SQL Server 2000) - TESTED
	* 		mysql 	-> MySQL - TESTED
	* 		pg 		-> PostgreSQL - TESTED
	*		ibm		-> IBM
	*		dblib	-> DBLIB
	*		odbc	-> Microsoft Access
	*		oracle	-> ORACLE
	*		ifmx 	-> Informix
	*		fbd		-> Firebird - TESTED
	*/
	
	//Initialize class and connects to the database
	function wArLeY_DBMS($database_type,$host,$database,$user,$password, $port){			
		try {
			$database_type=strtolower($database_type);
			$this->host=$host;
			$this->database=$database;
			$this->user=$user;
			$this->password=$password;
			$this->port=$port;
			
			$this->database_types=array("sqlite2","sqlite3","sqlsrv","mssql","mysql","pg","ibm","dblib","odbc","oracle","ifmx","fbd");
			
			if(in_array($database_type, $this->database_types)){		
				$this->database_type=$database_type;
		
				if($this->database_type=="mssql"){
					$this->con = new PDO("mssql:host=$host;dbname=$database", $user, $password);
				}
				if($this->database_type=="sqlsrv"){
					$this->con = new PDO("sqlsrv:server=$host;database=$database", $user, $password);
				}
				if($this->database_type=="ibm"){
					//default port = ?
					$this->con = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database; HOSTNAME=$host;PORT=$port;PROTOCOL=TCPIP;", $user, $password);
				}
				if($this->database_type=="dblib"){
					//default port = 10060
					$this->con = new PDO("dblib:host=$host:$port;dbname=$database",$user,$password);
				}
				if($this->database_type=="odbc"){
					$this->con = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\accounts.mdb;Uid=$user");
				}
				if($this->database_type=="oracle"){
					$this->con = new PDO("OCI:dbname=$database;charset=UTF-8", $user, $password);
				}
				if($this->database_type=="ifmx"){
					$this->con = new PDO("informix:DSN=InformixDB", $user, $password);
				}
				if($this->database_type=="fbd"){
					$this->con = new PDO("firebird:dbname=$host:$database", $user, $password);
				}
				if($this->database_type=="mysql"){
					$this->con = new PDO("mysql:host=$host;dbname=$database", $user, $password);
				}
				if($this->database_type=="sqlite2"){
					$this->con = new PDO("sqlite:/path/to/database.sdb");
				}
				if($this->database_type=="sqlite3"){
					$this->con = new PDO("sqlite::memory");
				}
				if($this->database_type=="pg"){
					$this->con = new PDO("pgsql:dbname=$database;host=$host", $user, $password);
				}
				
				$this->con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
				//$this->con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
				//$this->con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
				//$this->con->setAttribute(PDO::SQLSRV_ATTR_DIRECT_QUERY => true);
				
				/*
				print_r($db->getAttribute(PDO::ATTR_CONNECTION_STATUS));
				print_r($db->getAttribute(PDO::ATTR_DRIVER_NAME));
				print_r($db->getAttribute(PDO::ATTR_SERVER_VERSION));
				print_r($db->getAttribute(PDO::ATTR_CLIENT_VERSION));
				print_r($db->getAttribute(PDO::ATTR_SERVER_INFO));
				*/
			}
		} catch(PDOException $e) {
			//echo "Error: ". $e->getMessage();
			return false;
		}		
	}	
		
	//Iterate over rows
	function query($sql_statement){		
		if($this->con!=null){
			try {
				$this->sql=$sql_statement;
				return $this->con->query($this->sql);			
			} catch(PDOException $e) {
				return false;
			}
		}
		else{
			return false;
		}
	}
	
	//Fetch the first row
	function query_first($sql_statement){
		if($this->con!=null){
			try {
				$sttmnt = $this->con->prepare($sql_statement);
				$sttmnt->execute();	
				return $sttmnt->fetch();
			} catch(PDOException $e) {
				return false;
			}
		}
		else{
			return false;
		}
	}
	
	//Select single table cell from first record
	function query_single($sql_statement){
		if($this->con!=null){
			try {
				$sttmnt = $this->con->prepare($sql_statement);
				$sttmnt->execute();	
				return $sttmnt->fetchColumn();
			} catch(PDOException $e) {
				return false;
			}
		}
		else{
			return false;
		}
	}
	
	//Return total records from query as integer
	function rowcount(){
		if($this->con!=null){
			try {	
				$rows = $this->con->query($this->sql);
				$count = 0;
				foreach($rows as $row){
					$count++;  //Not is the best way but works!
				}
				return $count;
			} catch(PDOException $e) {
				return -1;
			}
		}
		else{
			return false;
		}
	}
	
	//Return name columns as vector
	function columns($table){
		$this->sql="Select * From $table";
		
		if($this->con!=null){
			try {
				$q = $this->con->query($this->sql);
				$column = array();
				foreach($q->fetch(PDO::FETCH_ASSOC) as $key=>$val){
					 $column[] = $key;
				}			
				return $column;
			} catch(PDOException $e) {
				return false;
			}
		}
		else{
			return false;
		}
	}
	
	//Insert and get newly created id
	function insert($table, $data){
		$count = 0;
		if($this->con!=null){
			try {
				if($this->database_type=="mysql"){
					//Testing the easy way for insert in MySQL
					$this->con->exec("insert into $table set $data;");
					//$count = $this->con->exec("insert into $table set $data;");
				}
				else{			
					$texto = "Insert Into $table (";
					$texto_extra = ") Values (";
					$texto_close = ")";
					$data_column = explode(",", $data);
					for($x=0;$x<count($data_column);$x++){
						$data_content = explode("=", $data_column[$x]); //0=Field, 1=Value
						if($x==0){ $texto.= $data_content[0]; }
						else{ $texto.= "," . $data_content[0]; }
						if($x==0){ $texto_extra.= $data_content[1]; }
						else{ $texto_extra.= "," . $data_content[1]; }					
					}
					$this->con->exec("$texto $texto_extra $texto_close");
					//$count = $this->con->exec("$texto $texto_extra $texto_close");
				}
				return $this->con->lastInsertId();
				//return $count;
			} catch(PDOException $e) {
				return false;
			}
		}
		else{
			return false;
		}
	}
	
	//Update tables
	function update($table, $data, $condition){
		$count = 0;
		if($this->con!=null){
			try {
				$count = $this->con->exec("update $table set $data where $condition");
				return $count;
			} catch(PDOException $e) {
				return false;
			}
		}
		else{
			return false;
		}
	}
	
	//Delete records from tables
	function delete($table, $condition){
		$count = 0;
		if($this->con!=null){
			try {
				$count = $this->con->exec("delete from $table where $condition");
				return $count;
			} catch(PDOException $e) {
				return false;
			}
		}
		else{
			return false;
		}
	}
	
	//Get latest specified id from specified table
	function getLatestId($db_table, $table_field){
		$sql_statement = "";
		$dbtype = $this->database_type;
		
		if($dbtype=="sqlsrv" || $dbtype=="mssql" || $dbtype=="ibm" || $dbtype=="dblib" || $dbtype=="odbc" || $dbtype=="sqlite2" || $dbtype=="sqlite3"){
			$sql_statement = "select top 1 $table_field from $db_table order by $table_field desc";
		}
		if($dbtype=="oracle"){
			$sql_statement = "select $table_field from $db_table where ROWNUM<=1 order by $table_field desc";
		}
		if($dbtype=="ifmx" || $dbtype=="fbd"){
			$sql_statement = "select first 1 $table_field from $db_table order by $table_field desc";
		}
		if($dbtype=="mysql"){
			$sql_statement = "select $table_field from $db_table order by $table_field desc limit 1";
		}
		if($dbtype=="pg"){
			$sql_statement = "select $table_field from $db_table order by $table_field desc limit 1 offset 0";
		}
		
		if($this->con!=null){
			try {
				$latest_value = 0;
				$rows = $this->con->query($sql_statement);
				foreach($rows as $row){
					$latest_value = $row[$table_field];
				}
				$rows = null;
				return $latest_value;
			} catch(PDOException $e) {
				return false;
			}
		}
		else{
			return false;
		}
	}
	
	//Get all tables from specified database
	function ShowTables($database){
		$complete = "";		
		$sql_statement = "";		
		$dbtype = $this->database_type;
		
		if($dbtype=="sqlsrv" || $dbtype=="mssql" || $dbtype=="ibm" || $dbtype=="dblib" || $dbtype=="odbc" || $dbtype=="sqlite2" || $dbtype=="sqlite3"){
			$sql_statement = "select name from sysobjects where xtype='U'";
		}
		if($dbtype=="oracle"){
			//If the query statement fail, try with uncomment the next line:
			//$sql_statement = "SELECT table_name FROM tabs";
			$sql_statement = "SELECT table_name FROM cat";
		}
		if($dbtype=="ifmx" || $dbtype=="fbd"){
			$sql_statement = "SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG = 0 AND RDB$VIEW_BLR IS NULL ORDER BY RDB$RELATION_NAME";
		}
		if($dbtype=="mysql"){
			if($database!=""){ $complete = " from $database"; }
			$sql_statement = "show tables $complete";
		}
		if($dbtype=="pg"){
			$sql_statement = "select relname as name from pg_stat_user_tables order by relname";
		}
		
		if($this->con!=null){
			try {
				$this->sql=$sql_statement;
				return $this->con->query($this->sql);			
			} catch(PDOException $e) {
				return false;
			}
		}
		else{
			return false;
		}
	}
	
	//Get all databases from your server
	function ShowDBS(){
		$sql_statement = "";		
		$dbtype = $this->database_type;
		
		if($dbtype=="sqlsrv" || $dbtype=="mssql" || $dbtype=="ibm" || $dbtype=="dblib" || $dbtype=="odbc" || $dbtype=="sqlite2" || $dbtype=="sqlite3"){
			$sql_statement = "SELECT name FROM sys.Databases";
		}
		if($dbtype=="oracle"){
			//If the query statement fail, try with uncomment the next line:
			//$sql_statement = "select * from user_tablespaces";
			$sql_statement = "select * from v$database";
		}
		if($dbtype=="ifmx" || $dbtype=="fbd"){
			$sql_statement = "";
		}
		if($dbtype=="mysql"){
			$sql_statement = "SHOW DATABASES";
		}
		if($dbtype=="pg"){
			$sql_statement = "select datname as name from pg_database";
		}
		
		if($this->con!=null){
			try {
				$this->sql=$sql_statement;
				return $this->con->query($this->sql);			
			} catch(PDOException $e) {
				return false;
			}
		}
		else{
			return false;
		}	
	}
	
	//Disconnect database
	function disconnect(){
		if($this->con){
			$this->con = null;
			return true;
		}else{
			return false;
		}
	}
}
?>
Return current item: PDO Multi Connection Class