ADODB, best php database abstraction class

2007-09-12

I have been using it for a while and I really like it, it has great performance and comes with a php extension that speeds up things by 100% percent.

Why is ADODB so great ?

Because it supports a lot of database drivers, here is a list :

  1. MySQL (my primary use)
  2. PostgreSQL
  3. Interbase
  4. Firebird
  5. Informix
  6. Oracle
  7. MS SQL
  8. Foxpro
  9. Access
  10. ADO
  11. Sybase
  12. FrontBase
  13. DB2
  14. SAP DB
  15. SQLite
  16. Netezza
  17. LDAP
  18. generic ODBC
  19. ODBTP
  20. Sybase
  21. Informix

Portability is the biggest advantage with ADODB, you can easilly move you web application to another RDBMS, with minor modification. This is useful if your application is growing and your current RDBMS can't handle all that information.

I've made a delegator class for ADODB. I used it to extend ADODB, to build new features on top of ADODB. I chose to do it this way because using a delegator class I can download the new version of adodb, replace the old files and my application works with the new version with no modification.

The modifications that I added to ADODB are minor helper classes, but this will evolve with my needs. I will add new helpers and functions if I need to.

How to use the delegator class

The delegator class is a singleton, so in order to use it you must obtain a instance of the class by calling

$DB = DB :: getInstance();//get the instance
$DB -> Execute('SELECT * FROM table');
//or you have only one call you can call it directly like this
DB :: getInstance() -> Execute('SELECT * FROM table');

Here is the list of helpers :

Update table method

$DB -> update('users',array('user' => 'update_test','name'=>'update_test','surname'=>'update_test','phone' => 4654646),'id=75');

And one of my favorite usage is this:

$DB -> update('useri',$_POST,'id=75');

I put the name of inputs from html form to match exactly the fields from the database table, so when updating i can pass the $_POST array to the update function.

Insert the array into table method

$DB -> insert('users',array('user' => 'insert_test','name'=>'James','surname'=>'Baker','user_level' => 5));
//
$DB -> insert('users',$_POST);
// the same as above, i put the html form input names to match the ones from the database

Return a prepared query method . Works just like execute , only instead of running the prepared query it returns it.

$DB = DB :: getInstance();
echo $DB -> returnPreparedQuery('SELECT * FROM table WHERE id = ?',5);
//this will output "SELECT * FROM table WHERE id = 5"

Delete record from table method

$DB = DB :: getInstance();
$DB -> delete('useri','id=75');

And here is the full class for you to use with ADODB

/*
   Delegator class for adodb
    Copyright (C) 2007 CodeAssembly.com  

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see http://www.gnu.org/licenses/
*/

/**
 * @package DB
 * @author CodeAssembly
 *
 * Delegator class for adodb
 * USAGE :
 * 
 *
 *  $DB =  db::getInstance();
 *
 *  try
 *  {
 *  	$DB -> insert('tablename',$insert_array);
 * 		$rs = $DB->Execute('select * from users');
 *  }
 *  catch (exception $e)
 *  {
 *  	print_r($e);
 *  }
 *
 *  while ($array = $rs->FetchRow()) {
 *      print_r($array);
 *  }
 *
 *  
 *
*/
define('ADODB_ASSOC_CASE', 2);

class db
{
	function getInstance()
	{
		if (self :: $instance === NULL)
		{
			self :: $instance = new db(); //create class instance
			include('adodb-exceptions.inc.php' ); //include exceptions for php5
			include('adodb.inc.php' );

			self :: $adodb = NewADOConnection(DB_TYPE);
			self :: $adodb -> Connect( DB_SERVER, DB_USERNAME,DB_PASSWORD, DB_NAME ); //connect to database constants are taken from config
			$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
		}
		return  self :: $instance;
	}

	/**
	 * Insert the array into table
	 *
	 * @param string $tablename
	 * @param array $record
	 *
	 * Example:
	 * 
	 * $DB -> insert('users',array('user' => 'insert_test','name'=>'James','surname'=>'Baker','user_level' => 5));
	 * $DB -> insert('users',$_POST);
	 * 
	 */
	function insert($tablename,$record)
	{
		
		$rs = self :: $adodb -> Execute( 'SELECT * FROM ' . $tablename . ' LIMIT 1');
		$insert_sql =  self :: $adodb -> GetInsertSQL($rs,$record);
		return self :: $adodb -> Execute( $insert_sql );
	}

	/**
	 * Update table
	 *
	 * @param string $tablename
	 * @param $record $array
	 * @param integer $where
	 * @return result
     *
	 * Example:
	 * 
	 * $DB -> update('useri',array('user' => 'update_test','name'=>'James','surname'=>'Baker','phone' => 04656454),'id=75');
	 * $DB -> update('users',$_POST,'id=75');
	 * 
	 */
	function update($tablename,$record,$where = null,$data = null)
	{
		if ( $where !== Null ) 
		{
			$rs = self :: $adodb -> Execute( 'SELECT * FROM ' . $tablename . ' WHERE ' . $where .' LIMIT 1',$data);
		} else
		{
			$rs = self :: $adodb -> Execute( 'SELECT * FROM ' . $tablename .' LIMIT 1',$data);
		}

		$update_sql = self :: $adodb -> GetUpdateSQL( $rs, $record );
		if ( $update_sql != '')
		{
			return self :: $adodb -> Execute( $update_sql );
		}
		return true;
	}

	/**
	 * Delete record from table
	 *
	 * @param string $tablename
	 * @param string $where
	 * @return result
	 *
	 * Example:
	 * 
	 * $DB -> delete('useri','id=75');
	 * 
	 */
	function delete( $tablename, $where, $params )
	{
		try 
		{
			$result = self :: $adodb -> Execute( 'DELETE FROM '.$tablename.' WHERE '.$where , $params);
		}
		catch ( exception $e )
		{
			if (constant( 'DEBUG' ) === true)
			{
				print_r($e);
			}
		}
		return result;
	}

	/**
	 * Returns a prepared query . Works just like execute , only instead of running the prepared query it returns it
	 *
	 * @param unknown_type $str
	 * @param unknown_type $arr
	 * @return unknown
	 */
	public function returnPreparedQuery ( $str , $arr )
	{
		$temp = explode ( '?' , $str ) ;
		$size = count ($temp) ;
		for ($x=0;$x<$size;$x++)
		{
			if ( ($temp[$x] != '') && ( $arr[$x]) != '' )
			{
				$temp[$x] .=  ' ' .  self ::$instance -> qstr ($arr[$x]) ;
			}			
		}
		return implode ( ' ' , $temp ) ;
	} 

	function __call($method, $args)//call adodb methods
	{
		return call_user_func_array(array(self :: $adodb, $method),$args);
	}

	function __get($property)
	{
		return self :: $adodb -> $property;
	}

	function __set($property, $value)
	{
		self :: $adodb[$property] = $value;
	}

	private function __clone()//do not allow clone
	{
	}

	static private $adodb = false;
	static private $instance = NULL;
}
?>

You can read the full ADODB documentaion here and you can also download ADODB from here

Share this with the world

Related

Comments

erichazann

I get an error (cannot access object of type ADODB_mysql as array) on this line:
self :: $adodb[$property] = $value;

Changed to
self :: $adodb->$property = $value;
and it works.

Posted on 2008-01-08 17:49:33
Matthew Browne

Thank you SO much! I have been looking for a way to do this forever. It's even better to have a whole class written already.

Posted on 2008-05-03 23:58:49
Matt Browne

Discovered a possible error: I think that before setting $ADODB_FETCH_MODE in the constructor, you should import the global variable, i.e.:

global $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;

Otherwise, it doesn't seem to be truly using ADODB_FETCH_ASSOC, but rather ADODB_FETCH_BOTH.

Thanks again for the class.

Posted on 2008-06-26 17:37:58
camc

How do security sql injection?

Posted on 2008-08-22 05:31:57
kyle

Works perfectly! I was about to write a class to do something like this myself but I thought i'd browse the web first...no sense re-inventing the wheel. Thanks a million.

Posted on 2009-02-12 15:43:15
Paul

Why are there selects in the update and insert methods?

Posted on 2009-10-07 09:27:54
Vlado

Hi,

I didn't manage to test it fully, but what happens with the extra fields in your $_POST array when you try to pass it to the insert method? Let's say some assoc keys are not presented in your database table: what happens then? Does it cleaverly skip the extra fileds or it throws an exception?

For example your $_POST array contains:

Array
(
[stat_type_id] => Array
(
[0] => 4
[1] => 6
)

[date_from] => 1/2001
[date_to] => 6/2010
[agoto] => statistical_graphics
[country_code] => RU
[sequence_mode] => m
[submit] => (Re) Generate
)

But your database record needs only these:

[date_from] => 1/2001
[date_to] => 6/2010
[country_code] => RU
[sequence_mode] => m

Should the DAL skip the other elements from the array? Or we have to take care not to pass them in the $_POST array?


Posted on 2010-09-27 23:29:11

Make yourself heard

Categories

Subscribe

All Posts

All Comments

© Copyright CodeAssembly

All code is licensed under LGPL, unless otherwise noted

littlebubu