How To Support 14 SQL Databases

[iceiceboom]

Hello,

Ok, here's my attempt to help all the programmers out there support 14 SQL databases, I am going to use it in all of my scripts, feel free to correct anything of mine that is wrong as I am sure some of it is.
Here are the versions of PHP required to run each SQL database (the base version is 3.0.9 due to functioning):
FrontBase: PHP 4.0.6
Informix: PHP 3.0.9
Ingres II: PHP 4.0.2
InterBase: PHP 4.0.0
MS SQL: PHP 3.0.9
mSQL: PHP 3.0.9
MySQL: PHP 3.0.9
ODBC: PHP 3.0.9
ODBC_Adabas: PHP 3.0.9
OvrimosSQL: PHP 4.0.3
PostgreSQL: PHP 3.0.9
PostgreSQL_local: PHP 3.0.9
SESAM: PHP 3 CVS
Sybase: PHP 3.0.9

I will make this function (I call it SQLact) available on my website for free once I know it's bug free...

sql.php

<?php
$sql_host = "HOSTNAME";
$sql_data = "DATABASE";
$sql_user = "USERNAME";
$sql_pass = "PASSWORD";
$sql_port = "PORT";

$sql_mode = 1;
?>

vars.php

<?php
require "sql.php";

if ($sql_mode==4) {
  $crishn = pg_pconnect("host=$sql_host user=$sql_user password=$sql_pass port=5432 dbname=$sql_data");
} else if ($sql_mode==5) {
  $crishn = pg_pconnect("user=$sql_user password=$sql_pass dbname=$sql_data");
} else if ($sql_mode==2) {
  $crishn = mssql_pconnect($sql_host, $sql_user, $sql_pass);
  $crashn = mssql_select_db($sql_data);
} else if ($sql_mode==3) {
  $crishn = msql_pconnect("$sql_host:$sql_port", $sql_user, $sql_pass);
  $crashn = msql_select_db($sql_data);
} else if ($sql_mode==8) {
  $crishn = sybase_pconnect($sql_host, $sql_user, $sql_pass);
  $crashn = sybase_select_db($sql_data, $crishn);
} else if ($sql_mode==9) {
  $crishn = ifx_pconnect($sql_data."@".$sql_host, $sql_user, $sql_pass);
} else if ($sql_mode==6) {
  $crishn = odbc_pconnect($sql_data, $sql_user, $sql_pass);
} else if ($sql_mode==7) {
  $crishn = odbc_pconnect($sql_host.":".$sql_data, $sql_user, $sql_pass);
} else if ($sql_mode==10) {
  $crishn = fbsql_pconnect ($sql_host, $sql_user, $sql_pass);
  $crashn = fbsql_select_db($sql_data);
} else if ($sql_mode==11) {
  $crishn = ibase_pconnect($sql_host.":".$sql_data, $sql_user, $sql_pass);
} else if ($sql_mode==12) {
  $crishn = ingres_pconnect($sql_data, $sql_user, $sql_pass);
} else if ($sql_mode==13) {
  if ($sql_data == "") {
    $sql_data = $sql_port;
  }
  $crishn = ovrimos_connect($sql_host, $sql_data, $sql_user, $sql_pass);
} else if ($sql_mode==14) {
  $crishn = sesam_connect($sql_host, $sql_data, $sql_user);
} else {
  $crishn = mysql_pconnect($sql_host, $sql_user, $sql_pass);
  $crashn = mysql_select_db($sql_data);
}

require "act.php";
?>

act.php

<?php
function SQLact ($reaction, $revalue="") {
  global $crishn;
  if ($sql_mode==4 || $sql_mode==5) {
    if ($reaction == "query") {
      if (checkVersion("4.2.0") == "false") {
        return @pg_exec($crishn, $revalue);
      } else {
        return @pg_query($crishn, $revalue);
      }
    } else if ($reaction == "result") {
      $row = @pg_fetch_row($revalue);
      return $row;
    } else if ($reaction == "num_rows") {
      if (checkVersion("4.2.0") == "false") {
        return @pg_numrows($revalue);
      } else {
        return @pg_num_rows($revalue);
      }
    } else if ($reaction == "fetch_array") {
      return @pg_fetch_array($revalue);
    } else if ($reaction == "close") {
      return @pg_close($crishn);
    }
  } else if ($sql_mode==2) {
    if ($reaction == "query") {
      return @mssql_query($revalue);
    } else if ($reaction == "result") {
      $row = @mssql_fetch_row($revalue);
      return $row;
    } else if ($reaction == "num_rows") {
      return @mssql_num_rows($revalue);
    } else if ($reaction == "fetch_array") {
      return @mssql_fetch_array($revalue);
    } else if ($reaction == "close") {
      return @mssql_close($crishn);
    }
  } else if ($sql_mode==3) {
    if ($reaction == "query") {
      return @msql_query($revalue, $crishn);
    } else if ($reaction == "result") {
      $row = @msql_fetch_row($revalue);
      return $row;
    } else if ($reaction == "num_rows") {
      return @msql_num_rows($revalue);
    } else if ($reaction == "fetch_array") {
      return @msql_fetch_array($revalue);
    } else if ($reaction == "close") {
      return @msql_close($crishn);
    }
  } else if ($sql_mode==8) {
    if ($reaction == "query") {
      return @sybase_query($revalue, $crishn);
    } else if ($reaction == "result") {
      $row = @sybase_fetch_row($revalue);
      return $row;
    } else if ($reaction == "num_rows") {
      return @sybase_num_rows($revalue);
    } else if ($reaction == "fetch_array") {
      return @sybase_fetch_array($revalue);
    } else if ($reaction == "close") {
      return @sybase_close($crishn);
    }
  } else if ($sql_mode==9) {
    if ($reaction == "query") {
      return @ifx_query($revalue);
    } else if ($reaction == "result") {
      $row = @ifx_fetch_row($revalue);
      return $row;
    } else if ($reaction == "num_rows") {
      return @ifx_num_rows($revalue);
    } else if ($reaction == "fetch_array") {
      return @ifx_fetch_row($revalue,$crishn);
    } else if ($reaction == "close") {
      return @ifx_close($crishn);
    }
  } else if ($sql_mode==10) {
    if ($reaction == "query") {
      return @fbsql_query($revalue);
    } else if ($reaction == "result") {
      $row = @fbsql_fetch_row($revalue);
      return $row;
    } else if ($reaction == "num_rows") {
      return @fbsql_num_rows($revalue);
    } else if ($reaction == "fetch_array") {
      return @fbsql_fetch_array($revalue);
    } else if ($reaction == "close") {
      return @fbsql_close($crishn);
    }
  } else if ($sql_mode==6 || $sql_mode==7) {
    if ($reaction == "query") {
      return @odbc_exec($crishn, $revalue);
    } else if ($reaction == "result") {
      $row = array();
      $cols = @odbc_fetch_into($revalue, @odbc_num_rows($revalue), $row);
      return $row;
    } else if ($reaction == "num_rows") {
      return @odbc_num_rows($revalue);
    } else if ($reaction == "fetch_array" && sql_mode==6) {
      $res = $revalue;
      $nr = @odbc_num_rows($revalue);
      $row = array();
      $result = array();
      $result = @odbc_fetch_row($res, $nr);
      $nf = @odbc_num_fields($res);
      for($count=1;$count<$nf+1;$count++) {
        $field_name = @odbc_field_name($res, $count);
        $field_value = @odbc_result($res, $field_name);
        $row[$field_name] = $field_value;
      }
      return $row;
    } else if ($reaction == "fetch_array" && sql_mode==7) {
      $res = $revalue;
      $nr = @odbc_num_rows($revalue);
      $row = array();
      $result = array();
      $result = @odbc_fetch_row($res, $nr);
      $nf = count($result)+2;
   	  for($count=1;$count<$nf;$count++) {
	    $field_name = @odbc_field_name($res, $count);
	    $field_value = @odbc_result($res, $field_name);
	    $row[$field_name] = $field_value;
	  }
      return $row;
    } else if ($reaction == "close") {
      return @odbc_close($crishn);
    }
  } else if ($sql_mode==11) {
    if ($reaction == "query") {
      return @ibase_query($crishn,$revalue);
    } else if ($reaction == "result") {
      $row = @ibase_fetch_row($revalue);
      return $row;
    } else if ($reaction == "num_rows") {
      $i=0;
      while (@ibase_fetch_row($revalue)) {
        $i++;
      }
      return $i;
    } else if ($reaction == "fetch_array") {
      $orow=@ibase_fetch_object($revalue);
	  $row=get_object_vars($orow);
      return $row;
    } else if ($reaction == "close") {
      return @ibase_close($crishn);
    }
  } else if ($sql_mode==12) {
    if ($reaction == "query") {
      return @ingres_query($revalue,$crishn);
    } else if ($reaction == "result") {
      $row = @ingres_fetch_row($crishn);
      return $row;
    } else if ($reaction == "num_rows") {
      return @ingres_num_rows($crishn);
    } else if ($reaction == "fetch_array") {
      return @ingres_fetch_array();
    } else if ($reaction == "close") {
      return @ingres_close($crishn);
    }
  } else if ($sql_mode==13) {
    if ($reaction == "query") {
      return @ovrimos_exec($crishn, $revalue);
    } else if ($reaction == "result") {
      $row = @ovrimos_fetch_row($revalue);
      return $row;
    } else if ($reaction == "num_rows") {
      return @vrimos_num_rows($revalue);
    } else if ($reaction == "fetch_array") {
      $row = array();
      $result = array();
      $result = @ovrimos_fetch_row($revalue);
      $nf = @ovrimos_num_fields($revalue);
      for($count=1;$count<$nf+1;$count++) {
        $field_name = @ovrimos_field_name($revalue, $count);
        $field_value = @ovrimos_result($revalue, $field_name);
        $row[$field_name] = $field_value;
      }
      return $row;
    } else if ($reaction == "close") {
      return @ovrimos_close($crishn);
    }
  } else if ($sql_mode==14) {
    if ($reaction == "query") {
      return @sesam_query($revalue,$crishn);
    } else if ($reaction == "result") {
      $row = @sesam_fetch_row($crishn);
      return $row;
    } else if ($reaction == "num_rows") {
      $i=0;
      while (@sesam_fetch_row($revalue)) {
        $i++;
      }
      return $i;
    } else if ($reaction == "fetch_array") {
      return @sesam_fetch_array($revalue);
    } else if ($reaction == "close") {
      return @sesam_close($crishn);
    }
  } else {
    if ($reaction == "query") {
      return @mysql_query($revalue, $crishn);
    } else if ($reaction == "result") {
      $row = @mysql_fetch_row($revalue);
      return $row;
    } else if ($reaction == "num_rows") {
      return @mysql_num_rows($revalue);
    } else if ($reaction == "fetch_array") {
      return @mysql_fetch_array($revalue);
    } else if ($reaction == "close") {
      return @mysql_close($crishn);
    }
  }
}
?>

[ptbsG_Man]

hmm i dont use 14 DB's . thank god 😃 but it dose sound like a good idea.

but i would suggest using a case statment inplase if all your if's . elseif's

I.M.O

[adamgeorge]

Yeah, good idea, but it does look a bit messy though.

Maybe try using a class instead? Here's a snippet from a SQL wrapper class I wrote. It supports MySQL and PostgreSQL and can easly be modified to support other databases:

/* Wrapper class for handling databases.
 *
 * Currently supports MySQL and PostgreSQL databases.
 * Can be expanded to support many other databases as per PHP documentation.
 *
 */

class SQL{
	var $sDatabase, 	// String containing database provider name. Eg. MySQL or PostgreSQL
		$dbi, 			// Contains the current connection to the database.
		$bDebug, 		// Debug flag, which will print queries to the database to the screen.
		$aResults, 		// An array of result identifiers. (result identifiers are returned from successfull queries.
						//		User can specify which result identifer they wish to use on most functions.
		$sQuery;		// String containing current database query.

// Constructor
// $sDatabase		Set name of database provider being used.
function SQL($sDatabase){
	$this->sDatabase = strtolower($sDatabase);
	$this->bDebug = false;	
}

// Will turn on or off debuggind depending on current state.
function toggleDebug(){
	if($this->bDebug == false)
		$this->bDebug = true;
	else
		$this->bDebug = false;

}

// Return name of the database provider being used.
function getDatabase(){
	return $this->sDatabase;
}

// Returns true or false if $sDatabase is the database provider currently being used. Not case sensitive.
// $sDatabase		Name of database provider to check
function usingDatabase($sDatabase){
	if(strtolower($sDatabase) == $this->sDatabase)
		return true;
	else
		return false;
}

// Make a connection to the database
// $sDBHost			Host name that the database resides on. Can be a URL. Default 'localhost'
// $sDBName			Name of database
// $sDBUser			User name to connect to the database with
// $sDBPassword		Password for user to connect to the datbase with
// $sDBPort			Port that the database uses on host machine. Default 5432.
function connection($sDBHost = "localhost", $sDBName, $sDBUser, $sDBPassword, $sDBPort = "5432"){	
	switch($this->sDatabase){
		case "mysql":
			$this->dbi = mysql_connect($sDBHost, $sDBUser, $sDBPassword) or die("Could not connect to database");
			mysql_select_db ($sDBName, $this->dbi);
			break;
		case "postgresql":
			$this->dbi = pg_connect("host=" . $sDBHost . " dbname=" . $sDBName . " user=" . $sDBUser . " password=" . $sDBPassword)
				or die("Could not connect");
			break;
	}
}

// Send a query to the database.
// $sQuery		String containing query to send to the database
// $iResultID	The result identifier will be saved in the $aResults array with this index.
//				If you need to work with more then one result identifier then you will need to give the 
//				result identifier an index here so you can reference it later. Else it will be written over next query
//				Default index 0.
function query($sQuery, $iResultID=0){
	$this->sQuery = $sQuery;

	if($this->bDebug) 
		echo "<div><font color=blue><b>SQL query:</b></font> " . str_replace(",",", ",htmlentities($sQuery)) . "</div>"; 
	switch($this->sDatabase){
		case "mysql": $this->aResults[$iResultID] = @mysql_query($sQuery, $this->dbi); break;
		case "postgresql": $this->aResults[$iResultID] = @pg_exec($this->dbi, $sQuery); break;
	}
}

// Check whether a result identifier is valid.
// $iResultID		Index into $aResults to check the appropriate result. Default index = 0
// Returns:			True on a valid result, false otherwise
function checkError($iResultID=0){
	if(!$this->aResults[$iResultID])
		return false;
	else
		return true;		
}

// Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
// $iResultID		Index into $aResults to use the appropriate result identifier
// $iResultType		Number representing the type of array to return:
//						1	Associative indices only
//						2	Number indices only
//						3	Array with both associative and number indices
function fetchArray($iResultID=0, $iResultType=3){
	if(!array_key_exists($iResultID, $this->aResults)){
		echo "<p>Result identifier doesn't exist!</p>";
	}

	if(!$this->aResults[$iResultID])
		trigger_error("Invalid result", E_USER_ERROR);

	switch($this->sDatabase){
		case "mysql": return mysql_fetch_array($this->aResults[$iResultID], $iResultType);
		case "postgresql": return pg_fetch_array($this->aResults[$iResultID], "", $iResultType);
		//trigger_error("PostgreSQL does not support fetching an array from a result identifier", E_USER_ERROR);break;
	}
}


// Fetches one row of data from the result associated with the specified result identifier. 
// The row is returned as an array. Each result column is stored in an array offset, starting at offset 0. 
// Returns FALSE if there are no more rows.
// $iResultID		Index into $aResults array to use the appropriate result identifier
function fetchRow($iResultID=0){
	if(!array_key_exists($iResultID, $this->aResults)){
		echo "<p>Result identifier doesn't exist!</p>";
	}

	if(!$this->aResults[$iResultID])
		trigger_error("Invalid result", E_USER_ERROR);

	switch($this->sDatabase){
		case "mysql": $aRow = mysql_fetch_row($this->aResults[$iResultID]); break;
		case "postgresql": $aRow = pg_fetch_row($this->aResults[$iResultID]); break;
	}
	return $aRow;
}

// Returns the name of the specified field index. 
// $iResultID		Index to a valid result identifier in $aResults
// $iIndex 			The numerical offset of the field. 
function fieldName($iIndex=0, $iResultID=0){
	if(!array_key_exists($iResultID, $this->aResults)){
		echo "<p>Result identifier doesn't exist!</p>";
	}

	if(!$this->aResults[$iResultID])
		trigger_error("Invalid result", E_USER_ERROR);

	switch($this->sDatabase){
		case "mysql": $sFieldName = mysql_field_name($this->aResults[$iResultID], $iIndex); break;
		case "postgresql": $sFieldName = pg_fieldname($this->aResults[$iResultID], $iIndex); break;
	}
	return $sFieldName;
}

// Returns the number of rows in a result set. This command is only valid for SELECT statements. 
// $iResultID		Index to a valid result identifier in $aResults
function numRows($iResultID=0){
	if(!array_key_exists($iResultID, $this->aResults)){
		echo "<p>Result identifier doesn't exist!</p>";
	}

	if(!$this->aResults[$iResultID])
		trigger_error("Invalid result", E_USER_ERROR);

	switch($this->sDatabase){
		case "mysql": $iRows = mysql_num_rows($this->aResults[$iResultID]);	break;
		case "postgresql": $iRows = pg_numrows($this->aResults[$iResultID]); break;
	}
	return $iRows;
}

// Get number of fields in result
// $iResultID		Index to a valid result identifier in $aResults
function numFields($iResultID=0){
	if(!array_key_exists($iResultID, $this->aResults)){
		echo "<p>Result identifier doesn't exist!</p>";
	}

	if(!$this->aResults[$iResultID])
		trigger_error("Invalid result", E_USER_ERROR);

	switch($this->sDatabase){
		case "mysql": $iNumFields = mysql_num_fields($this->aResults[$iResultID]); break;
		case "postgresql": $iNumFields = pg_numfields($this->aResults[$iResultID]); break;
	}
	return $iNumFields;
}

// Appends the appropriate SQL syntax for limitting the number of rows returned by a query to $sSQL.
// $sSQL		Valid SQL query. Should allready contain a 'where' clause
// $iOffset		Offset from the first result record from which to return results from
// $iLimit		Maximum number of records to return
function limitQuery($sSQL, $iOffset, $iLimit){
	switch($this->sDatabase){
		case "mysql":  $sSQL = $sSQL . " limit $iOffset, $iLimit"; break;
		case "postgresql":  $sSQL = $sSQL . " limit $iLimit, $iOffset";break;
	}

	return $sSQL;
}
}

There's heaps of other PHP database functions you could add in there too... there the only ones I ever use though.

It works well though implementing it as a class... that way you can have nice neat code like:

$oSQL = new SQL("MySQL");
$oSQL->connection("localhost", "mydb", "adam", "password", "5432");

$oSQL->query("select * from table");

if($oSQL->checkError())
   return false;

while(list($id) = $oSQL->fetchRow()){
    echo $id;
}

etc.

Hope someone may find that intresting...

cya
-Adam 🙂