Page 1 of 1
queries and classes
Posted: Wed Jun 22, 2005 3:50 pm
by neophyte
I'm thinking about implementing a database abstraction class (Hope I'm using that term right) to interact with other classes that will make calls to it. I don't want to have to explicitly declare an object ($object = new foobar;) in every class that needs to make a query. So I think the best way would be to make all the methods static so I can just call it within classes and move on. The problem is how do you get your ADODB/PEAR object into the script. Here one solution that seems to work:
Code: Select all
//Connect to the database
$db = &NEWADOConnection(DB_TYPE);
$db->PConnect(DB_SERVER, DB_USER, DB_PASSWD, DB_NAME);
//SET UP THE STATIC QUERIES
class db_abstract {
static function query(){
global $db;
$sql = "select * from $sometable where article_id = '$var'";
if($result = $db -> Execute($sql)){
$row = $result ->FetchRow();
return $row;
} else {
return false;
}
}
}
//EXAMPLE CLASS THAT CALLS DB_ABSTRACT
class example {
function __construct(){
$ds = db_abstract::query();
if ($ds){
echo $ds['data'];
}
}
}
$foo = new example;
Is there a better way than using global declarations in every method?
Posted: Wed Jun 22, 2005 6:16 pm
by patrikG
You are aware of
PDO? No need for DB abstraction stuff once PHP 5.x has replaced PHP 4.x, hence the days of db-abstraction are numbered.

Re: queries and classes
Posted: Wed Jun 22, 2005 6:49 pm
by McGruff
neophyte wrote:Is there a better way than using global declarations in every method?
Globals are evil.
http://www.phppatterns.com/index.php/ar ... ew/75/1/1/
GlobalVariablesAreBad
Your mortal soul may be in danger. Recite after me:
Our Fowler, which art in heaven,
Hello world be thy name.
The kingdom come, thy will be done, in my own scripts as it is in Patterns Of Enterprise Architecture
Give us this day our daily enterprise patterns
And forgive us our dependencies and our anti-patterns
Lead us not into globals but deliver us from evil
For thine is the kingdom, the power and the glory
for($i=1; $i>0; $i++)
Amen
Posted: Wed Jun 22, 2005 7:06 pm
by Roja
patrikG wrote:You are aware of
PDO? No need for DB abstraction stuff once PHP 5.x has replaced PHP 4.x, hence the days of db-abstraction are numbered.

Actually, PDO doesn't quite solve all the world's woes. There are still a number of things that adodb provides beyond PDO, even in PHP 5.1 (Do note that PDO in 5.0 isn't solid).
The date/time functions, the qstr function, and many other fantastic features all make adodb worth keeping around - even in PHP 5.1.
And of course, 5.1 isn't even out yet, and php 5.0 has barely made it to 20% of the webhosts out there.
However, back on topic, for the original poster, I really don't know. Much of what you are abstracting, I use directly. Adodb is already an abstraction layer for databases, so I guess I'm not seeing what value the additional abstraction layer above THAT is bringing to the table.
Posted: Wed Jun 22, 2005 10:48 pm
by neophyte
Thanks for the education on the term "database abstraction layer."
I hadn't heard of PDO before. Can't wait for it to become standard. The mysql_this_that_or_the_other() are tiresome after a while. I asked my host about PHP 5 they said is wasn't secure enough for shared environments yet.
I'm trying to avoid the need to either declare global or import the db object into every class that needs to execute a query.
Code: Select all
// like this
global $db;
//or like this
function db_object($db){
$this->db =$db;
}
So I thought of creating a class with static methods where the $db object was already sitting waiting to be used. Sort of a clearing house of all queries. I'm trying to figure out the most efficient way of making the database object available to all my classes -- hence the example in my first post. So I guess the question is what is the best method/pattern to use for this problem? I like the idea of having a registry. I'll have to read more about that and study the code closer.
Posted: Thu Jun 23, 2005 4:16 am
by patrikG
The registry patterns would be the one for globalising variable scope in an OOP application without using globals (which pretty much defy the idea of OOP anyway). The benefit is that you have much more control over how globals behave and what they do. To use the registry pattern you will need a very solid API and well thought out classes. You will have more power over your application, but you have to think about it much more clearly about its individual components and how they interact and communicate than before.
Probably the most relevant article is
http://www.phppatterns.com/index.php/ar ... ew/75/1/1/ which deals exactly with your db-scenario.
Reg.: PDO - the number of db-abstraction layers must go into the thousands, adoDB and PEAR::DB being the most prominent ones. With PDO most of the reasons for writing a seperate db-abstraction in PHP will become more or less invalidated - which is a good thing. It saves server resources (that's for example the reason behind adoDB Lite who needed a less resource-hungry db-wrapper), reduces complexity and will make PHP more transparent and db-portable.
Posted: Thu Jun 23, 2005 1:12 pm
by McGruff
You can do this anywhere:
Code: Select all
$db =& new MysqlDatabase(DB_SERVER, DB_USER, DB_PASS);
Otherwise, to re-use a db and so avoid the overhead of creating a new connection each time, the first choice would be to pass the db object around. If that gets ugly the second choice would be a Registry.
Posted: Thu Jun 23, 2005 10:08 pm
by neophyte
Mcgruff wrote:so avoid the overhead of creating a new connection each time, the first choice would be to pass the db object around
I am trying to avoid passing the db objec and having multiple connections. That's why I thought of throwing all my queries into a class full of static methods. At least that way all my db passing would be in one spot. For the immeadiate future I think I'm sticking with passing the db variable around like a hot p0tat03. Thanks for the advice all. I would have never thought of the registry idea. Ever...
Posted: Thu Jun 30, 2005 9:15 am
by neophyte
McGruff wrote:You can do this anywhere:
Code: Select all
$db =& new MysqlDatabase(DB_SERVER, DB_USER, DB_PASS);
I've been looking and trying to figure out what the & in front of new does. I've read the reference manual and dug around the object manual but I still haven't found an explanation. What does it do? Error suppression?...meh...
Posted: Thu Jun 30, 2005 10:42 am
by sweatje
neophyte wrote:McGruff wrote:You can do this anywhere:
Code: Select all
$db =& new MysqlDatabase(DB_SERVER, DB_USER, DB_PASS);
I've been looking and trying to figure out what the & in front of new does. I've read the reference manual and dug around the object manual but I still haven't found an explanation. What does it do? Error suppression?...meh...
Here is a good explanation:
PHP and Variable References
Posted: Thu Jun 30, 2005 10:47 am
by neophyte
THANKS O' PLENTY!!!
Posted: Mon Jul 25, 2005 7:36 am
by fastfingertips
Take a look to my database class
Code: Select all
class Database {
//variable used to build singleton pattern
static private $instance = null;
private $strDbServer;
private $strDbUser;
private $strDbPassword;
private $strDbDatabase;
//connection id
public $objConnection;
//the recorde set returned by a query
public $arrResultSet;
//new record id generated by an insert query
public $intNewId;
//rows number returned by a query
public $intRowsNumber;
//rows affected by a query
public $intRowsAffected ;
//fields of a record set
public $intFieldsNumber;
//records are loaded as associative arrays
public $arrRowsData;
//a record is loaded like an object, fields are properties
public $objRowsObject ;
#---------------------------------------------------------------
# Add Date: Wed Jul 20 17:30:40 EEST 2005
#
# Description: class constructor
#---------------------------------------------------------------
function __construct(){
$this->strDbServer = CONF::APP_MYSQL_SERVER;
$this->strDbDatabase = CONF::APP_MYSQL_DATABASE;
$this->strDbUser = CONF::APP_MYSQL_USER;
$this->strDbPassword = CONF::APP_MYSQL_PASSWORD;
}
function __destruct() {
if(!empty($this->objConnection)) {
mysql_close($this->objConnection);
}
}
//i will use this function to create a singleton pattern
static function instance() {
if(is_null(Database::$instance)){
Database::$instance = new Database();
}
return Database::$instance;
}
#---------------------------------------------------------------
# Add Date: Wed Jul 20 17:30:53 EEST 2005
#
# Description: change database server
#---------------------------------------------------------------
public function setDatabaseServer($_strDbServer,$_strDbUser,$_strDbPassword,$_strDbDatabase){
$this->strDbServer = $_strDbServer;
$this->strDbUser = $_strDbUser;
$this->strDbPassword = $_strDbPassword;
$this->strDbDatabase = $_strDbDatabase;
}
#---------------------------------------------------------------
# Add Date: Wed Jul 20 18:00:15 EEST 2005
#
# Description: send quesry to database server
#---------------------------------------------------------------
public function setQuery($_strSql) {
//connect to mysql server
$this->getConnection();
//send query to database server
$this->arrResultSet = mysql_query($_strSql,$this->objConnection);
if(!$this->arrResultSet) {
//Query has problems, email to admin
trigger_error(mysql_error(),E_USER_ERROR);
}
else {
//load the number of affected rows
$this->intRowsAffected = $this->getRowsAffected();
//i will pre-set some variables
if(eregi("^SELECT", $_strSql)) {
//if i have an select type query
$this->intRowsNumber = $this->getRowsNumber();
$this->intFieldsNumber = $this->getFieldsNumber();
}
else {
$this->intRowsNumber = 0;
$this->intFieldsNumber = 0;
}
}
}
#---------------------------------------------------------------
# Add Date: Wed Jul 20 18:25:35 EEST 2005
#
# Description: returns an object with properties that correspond to the fetched row
#---------------------------------------------------------------
public function getRowsObject() {
if($this->intRowsAffected != 0) {
$this->objRowsObject = mysql_fetch_object($this->arrResultSet);
}
}
#---------------------------------------------------------------
# Add Date: Wed Jul 20 18:29:52 EEST 2005
#
# Description: will fetch a result set that has multiple records
#---------------------------------------------------------------
public function getFetchedArray() {
$arrData = array();
if($this->intRowsAffected != 0) {
while($row = mysql_fetch_array($this->arrResultSet,MYSQL_ASSOC)){
$arrData[] = $row;
}
}
return $arrData;
}
#---------------------------------------------------------------
# Add Date: Wed Jul 20 18:30:22 EEST 2005
#
# Description: will fetch a result set with just one row
#---------------------------------------------------------------
public function getFetchedRow(){
if($this->intRowsAffected != 0) {
return mysql_fetch_row($this->arrResultSet);
}
else {
return null;
}
}
#---------------------------------------------------------------
# Add Date: Wed Jul 20 17:49:29 EEST 2005
#
# Description: connect to a mysql server and select database
#---------------------------------------------------------------
private function getConnection(){
$this->objConnection = mysql_connect($this->strDbServer,$this->strDbUser,$this->strDbPassword);
if(!$this->objConnection) {
//Could not connect to database server
trigger_error(mysql_error(),E_USER_ERROR);
}
else {
if(!mysql_select_db($this->strDbDatabase,$this->objConnection)) {
//Could not find database on server
trigger_error(mysql_error(),E_USER_ERROR);
}
}
}
#---------------------------------------------------------------
# Add Date: Wed Jul 20 18:00:26 EEST 2005
#
# Description: if was an insert operation get last insert id
#---------------------------------------------------------------
private function getNewId(){
return mysql_insert_id($this->objConnection);
}
#---------------------------------------------------------------
# Add Date: Wed Jul 20 18:19:02 EEST 2005
#
# Description: get the number of rows affected by a query
#---------------------------------------------------------------
private function getRowsAffected() {
return mysql_affected_rows($this->objConnection);
}
#---------------------------------------------------------------
# Add Date: Wed Jul 20 18:19:20 EEST 2005
#
# Description: get the number of records in a result set
#---------------------------------------------------------------
private function getRowsNumber() {
return mysql_num_rows($this->arrResultSet);
}
#---------------------------------------------------------------
# Add Date: Wed Jul 20 18:20:08 EEST 2005
#
# Description: get the field number in a result set
#---------------------------------------------------------------
private function getFieldsNumber() {
return mysql_num_fields($this->arrResultSet);
}
}