Abstraction Layers
|
|
|
|
<?php
// ***********************************************************************************************************
//
// Name: Database PHP Object
// Version: 1.0
// Description: A PHP wrapper for working with various databases
// Compatibility: PHP3, PHP4
//
// Author: Simon Belak
// E-mail: Simon_Belak@yahoo.com
//
// Last Modified: 01/12/2001
//
// Copyright (c) Simon Belak
// All rights reserved.
//
// This Software is distributed under the GPL. For a list of your obligations and rights
// under this license please visit the GNU website at http://www.gnu.org/
//
// ***********************************************************************************************************
class DbObject
{
// ***********************************************************************************************************
// *** (private) variables ***
// ***********************************************************************************************************
var $mDbType = ''; // Server (currently supported: MySQL,ODBC and MSSQL)
var $mCaseFolding = TRUE; // Set this to false for easier field accessing, but with a bit of a preformance hit
// see function GetField for details
var $mLink_ID = 0; // Link identifier of the currently opened connection
var $mQuery_ID = 0; // Result indenifier of the last query
var $EOF = TRUE; // End Of File (recordset)
var $BOF = TRUE; // Begining Of File (reocrdset)
var $mRecord = array(); // Current record
var $mCurrentRow = -1; // Current row
var $mNumRows = -1; // Number of rows in the recordset
var $mNumFields = -1; // Number of fields in the recordset
var $mFieldNames = array(); // Names of fields in the recordset
// ***********************************************************************************************************
// *** Constructor ***
// ***********************************************************************************************************
function DbObject($dbType='',$host='',$dbName='',$user='',$password='')
{
if (''==$dbType && ''==$this->mDbType)
{
$this->_Halt('Server is not selected');
}
else if (''==$dbType)
{
$dbType=$this->mDbType;
}
else
{
$this->mDbType=$dbType;
} // if
$this->Connect($host,$dbName,$user,$password);
} // DbObject - constructor
// ***********************************************************************************************************
// *** Public functions ***
// ***********************************************************************************************************
function SelectServer($dbType='',$host='',$dbName='',$user='',$password='')
{
if (''==$dbType)
{
$this->_Halt('Server is not selected');
return FALSE;
}
return $this->_ResetConnection($dbType,$host,$dbName,$user,$password);
} // SelectServer
function SelectHost($host='',$dbName='',$user='',$password='')
{
if (''==$host)
{
$this->_Halt('Host is not selected');
return FALSE;
} // if
return $this->_ResetConnection($this->mDbType,$host,$dbName,$user,$password);
} // SelectHost
function SelectDb($dbName='',$user='',$password='')
{
if (''==$dbName)
{
$this->_Halt('Database name is not set');
return FALSE;
} // if
$this->FreeResult();
if (FALSE==$this->_db_select_db($dbName,$user,$password))
{
$this->_Halt('Error using database: '.$dbName);
return FALSE;
} // if
return TRUE;
} // SelectDb
function Connect($host='',$dbName='',$user='',$password='')
{
if ( 0 == $this->mLink_ID )
{
$this->mLink_ID=$this->_db_connect($host,$user,$password);
if (FALSE==$this->mLink_ID)
{
$this->_Halt('mLink_ID == FALSE, connect failed');
return FALSE;
} // if
if ($dbName!='')
{
$this->SelectDb($dbName,$user,$password);
}
} // if
return $this->mLink_ID;
} // Connect
function Close()
{
$status=TRUE;
if ( 0 != $this->mLink_ID && FALSE != $this->mLink_ID)
{
$this->FreeResult();
$status=$this->_db_close();
$this->mLink_ID = 0;
} // if
return $status;
} // Close
function FreeResult()
{
$status=TRUE;
if (0 != $this->mQuery_ID && FALSE != $this->mQuery_ID)
{
$this->mRecord = array();
$this->mCurrentRow = -1;
$this->mNumRows = -1;
$this->mNumFields = -1;
$this->mFieldNames = array();
$this->BOF=TRUE;
$this->EOF=TRUE;
$status=$this->_db_free_result();
$this->mQuery_ID = 0;
} // if
return $status;
} // FreeResult
function Query($query='')
{
$this->Connect();
$this->FreeResult();
$this->mQuery_ID = $this->_db_query($query);
if (FALSE==$this->mQuery_ID)
{
$this->_Halt('Invalid SQL: '.$query);
return FALSE;
}
else if (1==$this->mQuery_ID)
{
$this->mQuery_ID=0;
}
else
{
$this->mNumRows = $this->_db_num_rows();
$this->mNumFields = $this->_db_num_fields();
$this->mFieldNames = $this->_SetFieldNames();
$this->BOF=TRUE;
if (0 < $this->mNumRows)
{
$this->EOF=FALSE;
}
else
{
$this->EOF=TRUE;
} // if
} // if
return $this->mQuery_ID;
} // Query
function GoToRecord($pos=0)
{
$status=TRUE;
if ($pos > $this->mNumRows-1)
{
$this->mCurrentRow = -1;
$this->EOF=TRUE;
$status=FALSE;
}
else if ($pos < 0)
{
$this->mCurrentRow = -1;
$this->BOF=TRUE;
$status=FALSE;
}
else
{
if ($pos !=$this-> mCurrentRow+1) // Optimization: if we move one record forward, data_seek isn't needed
{
if (FALSE == $this->_db_data_seek($pos)) // Unexpected error
{
return FALSE;
} // if
} // if
$this->mCurrentRow = $pos;
$this->mRecord = $this->_db_fetch_array();
$status = is_array($this->mRecord); // Unexpected error
$this->BOF=FALSE;
$this->EOF=FALSE;
} // if
return $status;
} // GoToRecord
function GetField($index=0)
{
if (FALSE != $this->EOF || FALSE != $this->BOF)
{
return FALSE;
} // if
if (FALSE == $this->mCaseFolding && FALSE != is_string($index))
{
$index = strtoupper($index);
reset($this->mFieldNames);
while (list(,$name)=each($this->mFieldNames))
{
if (strtoupper($name) == $index)
{
$index=$name;
BREAK;
} // if
} // while
} // if
if (FALSE==isset($this->mRecord[$index]))
{
return FALSE;
} // if
return $this->mRecord[$index];
} // GetField
function NextRecord()
{
return $this->GoToRecord($this->mCurrentRow + 1);
} // NextRecord
function PreviousRecord()
{
return $this->GoToRecord($this->mCurrentRow-1);
} // PreviousRecord
function GoToRecordRelative($pos=0)
{
return $this->GoToRecord($this->mCurrentRow+$pos);
} // GoToRecordRelative
function FirstRecord()
{
return $this->GoToRecord(0);
} // FirstRecord
function LastRecord()
{
return $this->GoToRecord($this->mNumRows-1);
} // LastRecord
function GetNumFields()
{
if (-1==$this->mNumFields)
{
$this->mNumFields = $this->_db_num_fields();
} // if
return $this->mNumFields;
} // GetNumFields
function GetNumRows()
{
if (-1==$this->mNumRows)
{
$this->mNumRows = $this->_db_num_rows();
} // if
return $this->mNumRows;
} // GetNumRows
function GetFieldNames($pos=-1)
{
if (1 > count($this->mFieldNames))
{
$this->_SetFieldNames();
} // if
if (-1< $pos && count($this->mFieldNames) > $pos)
{
return $this->mFieldNames[$pos];
}
else
{
return $this->mFieldNames;
} // if
} // GetFieldNames
function SetCaseFolding($val=TRUE)
{
$this->mCaseFolding=$val;
return TRUE;
} // SetCaseFolding
// ***********************************************************************************************************
// *** Private functions ***
// ***********************************************************************************************************
function _Halt($msg='')
{
$this->Close();
printf("</td></tr></table><b>Error:</b> %s<br>n", $msg); // we escape the html table so that Netscape (and other browsers) can display this page properly
die('Session halted.');
return TRUE;
} // _Halt
function _ResetConnection($dbType='',$host='',$dbName='',$user='',$password='')
{
$this->Close();
$this->mDbType=$dbType;
$this->Connect($host,$dbName,$user,$password);
if ( 0 == $this->mLink_ID || FALSE == $this->mLink_ID)
{
return FALSE;
} // if
return TRUE;
} // _ResetConnection
function _SetFieldNames()
{
for ($i=0;$i<$this->mNumFields;$i++)
{
$this->mFieldNames[]=$this->_db_field_name($i);
} // for
return $this->mFieldNames;
} // _SetFieldNames
// ***********************************************************************************************************
// *** Functios that unify database accessing ***
// ***********************************************************************************************************
function _db_connect($host='',$user='',$password='')
{
switch (strtoupper($this->mDbType))
{
case 'MYSQL':
return mysql_connect($host, $user, $password);
case 'ODBC':
// odbc_connect doesn't accept the argument host, and is therefore (in functionalaty) the same as select_db,
// so we just skip the connect step and call odbc_connect in select_db step
return TRUE;
case 'MSSQL':
return mssql_connect($host, $user, $password);
default:
$this->_Halt('Server is not selected');
return FALSE;
} // switch
} // _db_connect
function _db_close()
{
switch (strtoupper($this->mDbType))
{
case 'MYSQL':
return mysql_close($this->mLink_ID);
case 'ODBC':
return odbc_close($this->mLink_ID);
case 'MSSQL':
return mssql_close($this->mLink_ID);
default:
$this->_Halt('Server is not selected');
return FALSE;
} // switch
} // _db_close
function _db_select_db($dbName='',$user='',$password='')
{
switch (strtoupper($this->mDbType))
{
case 'MYSQL':
return mysql_select_db($dbName,$this->mLink_ID);
case 'ODBC':
// See _db_connect case "ODBC" for details
$this->mLink_ID=odbc_connect($dbName, $user, $password,SQL_CUR_USE_ODBC);
if (FALSE==$this->mLink_ID)
{
return FALSE;
}
else
{
return TRUE;
} // if
case 'MSSQL':
return mssql_select_db($dbName,$this->mLink_ID);
default:
$this->_Halt('Server is not selected');
return FALSE;
} // switch
} // _db_select_db
function _db_free_result()
{
switch (strtoupper($this->mDbType))
{
case 'MYSQL':
return mysql_free_result($this->mQuery_ID);
case 'ODBC':
return odbc_free_result($this->mQuery_ID);
case 'MSSQL':
return mssql_free_result($this->mQuery_ID);
default:
$this->_Halt('Server is not selected');
return FALSE;
} // switch
} // _db_free_result
function _db_query($query='')
{
switch (strtoupper($this->mDbType))
{
case 'MYSQL':
{
$temp_query_id=mysql_query($query,$this->mLink_ID);
// Becouse for queries DELETE, INSERT, REPLACE and UPDATE mysql doesn't return a result indenifier
// We (in those cases) set the result indenifier to 1, so we can distict it from a failed query
if (FALSE==$temp_query_id && 0 < mysql_affected_rows($this->mLink_ID))
{
$temp_query_id = 1;
} // if
return $temp_query_id;
} // case 'MySQL'
case 'ODBC':
return odbc_exec($this->mLink_ID,$query);
case 'MSSQL':
return mssql_query($query,$this->mLink_ID);
default:
$this->_Halt('Server is not selected');
return FALSE;
} // switch
} // _db_query
function _db_fetch_array()
{
switch (strtoupper($this->mDbType))
{
case 'MYSQL':
return mysql_fetch_array($this->mQuery_ID);
case 'ODBC': // A unfied result:
{ // Record is returned in an array with numeric keys and field names for keys
// Row and field counting start is set to 0 (ODBC default is 1)
if (FALSE != odbc_fetch_row($this->mQuery_ID,$this->mCurrentRow+1))
{
$resultArray = array();
for ($i=0;$i < odbc_num_fields($this->mQuery_ID);$i++)
{
$name=odbc_field_name($this->mQuery_ID,$i+1);
$resultArray[$name]=null;
unset($resultArray[$name]); // isset() function now returns false, but the field name key stays
if (FALSE != odbc_result($this->mQuery_ID,$i+1))
{
$resultArray[$i]=($resultArray[$name] = odbc_result($this->mQuery_ID,$i+1));
} // if
} // for
return $resultArray;
}
else
{
return FALSE;
} // if
} // case 'ODBC'
case 'MSSQL':
return mssql_fetch_array($this->mQuery_ID);
default:
$this->_Halt('Server is not selected');
return FALSE;
} // switch
} // _db_fetch_array
function _db_data_seek($pos=0)
{
switch (strtoupper($this->mDbType))
{
case 'MYSQL':
return mysql_data_seek($this->mQuery_ID, $pos);
case 'ODBC':
// No need to move the internal pointer becouse we provide the optional row_number argument for odbc_fatch_row
return TRUE;
case 'MSSQL':
return mssql_data_seek($this->mQuery_ID, $pos);
default:
$this->_Halt('Server is not selected');
return FALSE;
} // switch
} // _db_data_seek
function _db_num_rows()
{
switch (strtoupper($this->mDbType))
{
case 'MYSQL':
return mysql_num_Rows($this->mQuery_ID);
case 'ODBC':
{
$numRows=odbc_num_Rows($this->mQuery_ID);
// Some ODBC drivers don't support odbc_num_rows for results of an SELECT query and return an error (-1)
if (-1==$numRows)
{
// Optimization: first we roughly estemate the number of rows
// than the last 9 are counted
for ($i=0;FALSE != odbc_fetch_Row($this->mQuery_ID,$i+10);$i+=10);
for ($numRows=$i+1;FALSE !=odbc_fetch_Row($this->mQuery_ID,$numRows+1);$numRows++);
odbc_fetch_Row($this->mQuery_ID,$this->mCurrentRow); // We set the internal pointer back to where we started
} // if
return $numRows;
} // case 'ODBC'
case 'MSSQL':
return mssql_num_Rows($this->mQuery_ID);
default:
$this->_Halt('Server is not selected');
return FALSE;
} // switch
} // _db_num_rows
function _db_num_fields()
{
switch (strtoupper($this->mDbType))
{
case 'MYSQL':
return mysql_num_fields($this->mQuery_ID);
case 'ODBC':
return odbc_num_fields($this->mQuery_ID);
case 'MSSQL':
return mssql_num_fields($this->mQuery_ID);
default:
$this->_Halt('Server is not selected');
return FALSE;
} // switch
} // _db_num_fields
function _db_field_name($fieldNumber=0)
{
switch (strtoupper($this->mDbType))
{
case 'MYSQL':
return mysql_field_name($this->mQuery_ID , $fieldNumber);
case 'ODBC':
return odbc_field_name($this->mQuery_ID , $fieldNumber+1);
case 'MSSQL':
return mssql_field_name($this->mQuery_ID , $fieldNumber);
default:
$this->_Halt('Server is not selected');
return FALSE;
} // switch
} // _db_field_name
} // class
// ***********************************************************************************************************
//
// *** Class DbObject ***
//
// Public variables: 0
//
// Private variables: 11
//
// $mDbType Server (currently supported: MySQL,ODBC and MSSQL)
//
// $mCaseFolding Set this to false for easier field accessing, but with a bit of a preformance hit
// see function GetField for details
//
// $mLink_ID Link identifier of the currently opened connection
// $mQuery_ID Result indenifier of the last query
//
// $EOF End Of File (recordset)
// $BOF Begining Of File (reocrdset)
//
// $mRecord Current record
// $mCurrentRow Current row
//
// $mNumRows Number of rows in the recordset
// $mNumFields Number of fields in the recordset
// $mFieldNames Names of fields in the recordset
//
// Custom cunstuctor: yes (DbObject)
//
// Public functions: 18
//
// SelectServer($dbType='',$host='',$dbName='',$user='',$password='')
// SelectHost($host='',$dbName='',$user='',$password='')
// SelectDb($dbName='',$user='',$password='')
//
// Connect($host='',$dbName='',$user='',$password='')
// Close()
//
// FreeResult()
//
// Query($query='')
//
// GoToRecord($pos=0)
// GoToRecordRelative($pos=0)
// NextRecord()
// PreviousRecord()
// FirstRecord()
// LastRecord()
//
// GetField($index=0)
//
// GetNumFields()
// GetNumRows()
// GetFieldNames($pos=-1)
//
// SetCaseFolding($val=TRUE)
//
// Private functions: 3
//
// _Halt($msg='')
// _ResetConnection($dbType='',$host='',$dbName='',$user='',$password='')
// _SetFieldNames()
//
// Special: yes - Functios that unify database accessing
//
// _db_connect($host='',$user='',$password='')
// _db_close()
// _db_select_db($dbName='',$user='',$password='')
// _db_free_result()
// _db_query($query='')
// _db_fetch_array()
// _db_data_seek($pos=0)
// _db_num_rows()
// _db_num_fields()
// _db_field_name($fieldNumber=0)
//
//
//
// *** Example: ***
//
// 1. $db1 = new DbObject("Mysql","host","database","user","password");
//
// 2. $db1->Query("select * from mytable1");
//
// 3. while ($db1->NextRecord())
// 4. {
// 5. for ($i=0;$i<$db1->GetNumFields();$i++)
// 6. {
// 7. echo $db1->GetField($i);
// 8. }
// 9. }
//
// 10. $db1->FirstRecord();
// 11. echo $db1->GetField("fieldname1");
//
// 12. $db1->SetCaseFolding(FALSE);
// 13. echo $db1->GetField("fiELDnaME1");
//
// ***********************************************************************************************************
?>
|
|
|
Usage Example
|
1. $db1 = new DbObject("Mysql","host","database","user","password");
2. $db1->Query("select * from mytable1");
3. while ($db1->NextRecord())
4. {
5. for ($i=0;$i<$db1->GetNumFields();$i++)
6. {
7. echo $db1->GetField($i);
8. }
9. }
10. $db1->FirstRecord();
11. echo $db1->GetField("fieldname1");
12. $db1->SetCaseFolding(FALSE);
13. echo $db1->GetField("fiELDnaME1");
|
|
|
Rate This Script
|
|
|
|
Store
Your email: