DB Class
From MyUPBWiki
This is a database class developed by Jonathan on the forums. It handles SQL abstraction. We may perhaps use it to extend UPB to SQL engines. If you are interested in this project, post to the developer's list.
<?php
# SQL interface class
# By: Xylium Scripting Solutions
# Developer: Jonathan Kern
# Version: 1.0
# License: GNU/GPL
/*
//------------------------------------------------------------------------------------
//--------------------------------| Documentation |-----------------------------------
//------------------------------------------------------------------------------------
//--------------------------------| Member Variables |--------------------------------
$link - int, resource id for sql server connection
$lastquery - string, last sql query executed
$dbtype - string, type of db server used
$lasterror - string, last error message set by _error()
//-----------------------------------| Methods |--------------------------------------
void dbclass(hostname, username, base64 encoded password, database_name, database_type)
--constructor, makes the object and connects to the database
int query(sql_query)
--executes the query and returns an sql resource id
void _error(error_message)
--private method, sets the messages to the $lasterror variable and returns false
int rowcount(result_resource)
--returns the row count of the passed in sql result id
array[] result2array(result_resource)
--returns an array of rows (arrays) of results. Does all the steps of fetch_array and spits out
finished product, can be used in a for loop later
anytype getResult(result_resource)
--returns the first result of a single value row
array getRow(result_resource)
--returns a full row in an SQL result in array form
string getLastQuery()
--accessor, returns last executed query
string getLastError()
--accessor, returns last error
*/
class dbclass
{
var $link_id; // holds sql server link id
var $lastquery; // holds last sql query sent
var $dbtype; // holds type of db server
var $lasterror; // holds last error message sent by db server
//------------------------------------------------------------------------------------
//--------------------------------| Connect to DB |-----------------------------------
//------------------------------------------------------------------------------------
function dbclass($Host, $User, $Pass, $DB, $DBtype)
// Precondition: all params are prep'd for the right db server, $Pass is base64 encoded
// Postcondition: db is connected to, dbclass object created and ready
{
$Pass = base64_decode($Pass);
$this->dbtype = $DBtype;
switch( $this->dbtype )
{
case "mysql":
if(!$this->link_id = mysql_connect($Host, $User, $Pass))
{
$this->_error(mysql_error());
return false;
}
if(!mysql_select_db($DB))
{
$this->_error(mysql_error());
return false;
}
break;
case "postgresql":
if(!$this->link_id = pg_connect("host=".$Host." user=".$User." pass=".$Pass." dbname=".$DB))
{
$this->_error(pg_last_error());
return false;
}
break;
case "msql":
if(!$this->link_id = msql_connect(NULL, $Host, $User, $Pass))
{
$this->_error(msql_error());
return false;
}
if(!msql_select_db($DB))
{
$this->_error(msql_error());
return false;
}
break;
case "mssql":
if(!$this->link_id = mssql_connect($Host, $User, $Pass))
{
$this->_error(mssql_get_last_message());
return false;
}
if(!mssql_select_db($DB))
{
$this->_error(mssql_get_last_message());
return false;
}
break;
}
}
//------------------------------------------------------------------------------------
//---------------------------------| Query the DB |-----------------------------------
//------------------------------------------------------------------------------------
function query($sql)
// Precondition: $sql is in correct SQL syntax
// Postcondition: $sql is executed on the db server and a result resource is returned
{
$this->lastquery = $sql;
switch( $this->dbtype )
{
case "mysql":
if(!$result = mysql_query($sql, $this->link_id))
{
$this->_error(mysql_error());
return false;
}
break;
case "postgresql":
if(!$result = pg_query($this->link_id, $sql))
{
$this->_error(pg_last_error());
return false;
}
break;
case "msql":
if(!$result = msql_query($sql, $this->link_id))
{
$this->_error(msql_error());
return false;
}
break;
case "mssql":
if(!$result = mssql_query($sql, $this->link_id))
{
$this->_error(mssql_get_last_message());
return false;
}
break;
}
return $result;
}
function _error($error) // Note: _error is a private method
// Precondition: $error is a string
// Postcondition: $error is assigned to the $lasterror var in the
// object and method returns false so calling method can report
// an error to the driver program
{
$this->lasterror = $error;
}
function rowcount($result)
// Precondition: $result is a valid sql result resource
// Postcondition: a count of how many sql rows there are
// in the result is returned
{
switch( $this->dbtype )
{
case "mysql":
if(!$count = mysql_num_rows($result))
{
$this->_error(mysql_error());
return false;
}
break;
case "postgresql":
if(!$count = pg_num_rows($result))
{
$this->_error(pg_last_error());
return false;
}
break;
case "msql":
if(!$count = msql_num_rows($result))
{
$this->_error(msql_error());
return false;
}
break;
case "mssql":
if(!$count = mssql_num_rows($result))
{
$this->_error(mssql_get_last_message());
return false;
}
break;
}
return $count;
}
function result2array($result)
// Precondition: $result is a valid sql result id with rows >= 1
// Postcondition: returns an array of arrays of row values with associations
{
switch( $this->dbtype )
{
case "mysql":
while($row = mysql_fetch_array($result))
$array[] = $row;
break;
case "postgresql":
while($row = pg_fetch_array($result))
$array[] = $row;
break;
case "msql":
while($row = msql_fetch_array($result))
$array[] = $row;
break;
case "mssql":
while($row = mssql_fetch_array($result))
$array[] = $row;
break;
}
return $array;
}
function getResult($result)
// Precondition: $result is a valid sql result id and represents
// a single sql result value
// Postcondition: returns the value of the result
{
switch( $this->dbtype )
{
case "mysql":
if(!$res = mysql_fetch_array($result))
{
$this->_error(mysql_error());
return false;
}
$ret = $res[0];
break;
case "postgresql":
if(!$res = pg_fetch_array($result))
{
$this->_error(pg_last_error());
return false;
}
$ret = $res[0];
break;
case "msql":
if(!$res = msql_fetch_array($result))
{
$this->_error(msql_error());
return false;
}
$ret = $res[0];
break;
case "mssql":
if(!$res = mssql_fetch_array($result))
{
$this->_error(mssql_get_last_message());
return false;
}
$ret = $res[0];
break;
}
return $ret;
}
function getRow($result)
// Precondition: $result is a valid sql result id and represents
// at least one row
// Postcondition: returns the first row in the result resource in
// array format
{
switch( $this->dbtype )
{
case "mysql":
if(!$res = mysql_fetch_array($result))
{
$this->_error(mysql_error());
return false;
}
break;
case "postgresql":
if(!$res = pg_fetch_array($result))
{
$this->_error(pg_last_error());
return false;
}
break;
case "msql":
if(!$res = msql_fetch_array($result))
{
$this->_error(msql_error());
return false;
}
break;
case "mssql":
if(!$res = mssql_fetch_array($result))
{
$this->_error(mssql_get_last_message());
return false;
}
break;
}
return $res;
}
function getLastQuery()
// Precondition: none
// Postcondition: returns last sent query
{
return $this->lastquery;
}
function getLastError()
// Precondition: none
// Postcondition: returns last error sent by _error()
{
return $this->lasterror;
}
}
?>
