1e6b9d50678a0cc32d98aa1dd1ec3846

Typical MySQL wrapper written in PHP use functions such as : Query($sql), FetchRow(), ...
My approach is different.
The idea behind my mySQL class is that you just have to create your own classes and call protected methods to do the mapping with the database, without a single line of SQL in your classes.
For instance, with my mySQL wrapper, you just have to code classes like the student class.
If you look carefully, there is really nothing to code : mostly only verifications in the setter... sweet!

My vision was to add a single static property to your own class : the table name the class refers to.
Unfortunaltely, I was not able to do that with PHP 5, you have to add others variables in the class.
In fact my wrapper requires that you :
-subclass your own classes with my mysql abstract class (i know it's wired)
-define the following members in your class :
$_table
$_fields
$_primaryKey
$_loadFields
-call getFields(); and getPrimaryKey(); in the constructor (see my student class example to get an idea)

Then you just have to call the protected functions... the student class is showing what you can do.

//-------------------------

Please note that:
-i'm using utf8 in the connecting method
-i'm saying "A primary key is never NULL" ; i know this is not true, but its crappy if you have null primary key, really.
-every mysql call is done for a precise connection link (self::$db_link) => could enhance the code to use severals databases
-interesting cheat using eval used all over the class : eval('return '.get_class($this).'::$_primaryKey;');

Recommendations:
-use your own exception class
-have a look at http://www.phpdoctrine.org/ and http://coughphp.com/ (i think those are way better than my crappy code, i've JUST discover the ORM concept ^^)

//-------------------------

Where I need help:

-Get rid of the statics properties and functions in the student class
I think that's really hard to do so with PHP 5, maybe PHP 6 will help us doing such kind of stuff ("static::")

-Considering charge issues : is it better to use a function like `getFields()`, or to put fields manually in the class `$_fields = array('f1','f2',...);` ?
I think it's better manually because you don't ALTER a table very oftenly...
but for a large / dynamic system ... i don't know, what do you think ?

-look at disconnect() in the mySQL class ; what do you think of the comment (the exception if mysql_close returns an error) ?

<?php

abstract class mySQL // abstract => cannot be instanciated
{
	const db_host = 'localhost';		// host
	const db_base = 'MYBASE';		// database  
	const db_user = 'MYUSER';		// user
	const db_pass = 'THEPASSWORD';		// password
	private static $db_link = false;	// link
	private static $db_connected = false;	// connection established?
	
	//------------------------------
	// Constructor + Destructor
	
	public function __construct()
	{
		self::connect();
	}
	
	public function __destruct()
	{
		self::disconnect();
	}
	
	//------------------------------
	// Connect + Disconnect
	
	public static function connect()
	{
		if(!self::$db_connected)
		{
			self::$db_link = mysql_connect(self::db_host, self::db_user, self::db_pass);
			if(!self::$db_link)
				throw new Exception('Database connection error :-(');
			
			if(!mysql_select_db(self::db_base, self::$db_link))
				throw new Exception('Database selection error :-(');
			
			if(!mysql_query("SET NAMES 'utf8'", self::$db_link))
				throw new Exception('Impossible to use utf8 to communicate with the database :-(');
			
			self::$db_connected = true; // We're good !
		}
	}
	
	public static function disconnect()
	{
		if($db_connected)
			mysql_close(self::$db_link); // whatever the return value... (stupid to launch an exception here ?)
	}
	
	//------------------------------
	// Insert + Update + Delete
	
	protected function insert_bdd()
	{
		if(!$db_connected) // useless here because of child classes constructor (getFields & getPrimaryKey -> connect)
			self::connect();
		
		$table = eval('return '.get_class($this).'::$_table;');
		if(!$table)
			throw new Exception('Don\'t know what table to use for '.get_class($this).' :-(');
		
		$fields = eval('return '.get_class($this).'::$_fields;');
		if(!$fields)
			throw new Exception('Don\'t know what fields describe '.get_class($this).' :-(');
		$tb_champs = array_fill_keys($fields, 1);
		
		$txt_fields = '';
		$txt_values = '';
		$i = 0;
		
		foreach($this as $key => $value)
		{
			if(isset($tb_champs[$key]))
			{
				if($i >0)
				{
					$txt_fields .= ',';
					$txt_values .= ',';
				}
				$txt_fields .= $key;
				
				$c = self::quote_smart($value);				
				if(is_numeric($value))
					$txt_values .= $c;
				elseif(is_null($value))
					$txt_values .= 'NULL';
				else
					$txt_values .= "'$c'";
				
				$i++;
			}
		}
		
		$sql = 'INSERT INTO '.$table.'('.$txt_fields.') VALUES ('.$txt_values.');'; 
		if(!mysql_query($sql, self::$db_link))
			throw new Exception('Insertion error:<br />'.$sql.'<br />'.mysql_error(self::$db_link));
		return mysql_insert_id(); // don't forget that to update your object
	}
	
	protected function update_bdd()
	{
		if(!$db_connected) // useless here because of child classes constructor (getFields & getPrimaryKey -> connect)
			self::connect();
		
		$table = eval('return '.get_class($this).'::$_table;');
		if(!$table)
			throw new Exception('Don\'t know what table to use for '.get_class($this).' :-(');
		
		$fields = eval('return '.get_class($this).'::$_fields;');
		if(!$fields)
			throw new Exception('Don\'t know what fields describe '.get_class($this).' :-(');
		$tb_champs = array_fill_keys($fields, 1);
		
		$txt_requete = 'UPDATE '.$table;
		$i = 0;
		
		foreach($this as $key => $valeur)
		{
			if(isset($tb_champs[$key])) // Si le champ existe
			{
				if($i == 0)
					$txt_requete .= ' SET ';
				else
					$txt_requete .= ',';
				
				$txt_valeur = self::quote_smart($valeur);
				if(is_null($valeur))
					$txt_requete .= "$key=NULL";
				else
					$txt_requete .= "$key='$txt_valeur'";
				$i++;
			}
		}
		
		$i = 0;
		$primaryKey = eval('return '.get_class($this).'::$_primaryKey;');
		
		foreach($primaryKey as $key)
		{
			if($i == 0)
				$txt_requete .= ' WHERE ';
			else
				$txt_requete .= ' AND ';
			
			$c = self::quote_smart($this->$key); // __get() could throw an exception if this field doesn't exist
			$txt_requete .= "$key='$c'"; // A primary key is never NULL
		}
		
		$txt_requete .= ';';
		if(!mysql_query($txt_requete, self::$db_link))
			throw new Exception('Update error:<br />'.$txt_requete.'<br />'.mysql_error(self::$db_link));
		if(mysql_affected_rows(self::$db_link) == 0) // Primary Key does not match any record => exception
			throw new Exception('Update error:<br />'.$txt_requete.'<br />0 records affected');
	}
	
	protected function delete_bdd()
	{
		if(!$db_connected) // useless here because of child classes constructor (getFields & getPrimaryKey -> connect)
			self::connect();
		
		$table = eval('return '.get_class($this).'::$_table;');
		if(!$table)
			throw new Exception('Don\'t know what table to use for '.get_class($this).' :-(');
		
		$primaryKey = eval('return '.get_class($this).'::$_primaryKey;');
		if(!$primaryKey)
			throw new Exception('Don\'t know what are primary key fields for '.get_class($this).' :-(');
		
		$t = array();		
		foreach($primaryKey as $key => $value)
			$t[$value] = $this->$value;
		
		self::deleteDirectly($table, $primaryKey, $t);
	}
	
	//------------------------------	
	// Init an object from its primary key
	
	protected function init_by_primaryKey($Pk)
	{
		if(!$db_connected) // useless here because of child classes constructor (getFields & getPrimaryKey -> connect)
			self::connect();
		
		$table = eval('return '.get_class($this).'::$_table;');
		if(!$table)
			throw new Exception('Don\'t know what table to use for '.get_class($this).' :-(');
		
		$Pkfields = eval('return '.get_class($this).'::$_primaryKey;');
		if(!$Pkfields)
			throw new Exception('Don\'t know what are primary key fields for '.get_class($this).' :-(');
		
		// To be sure $Pk is filled with enough keys to describe a primary key, we have to verify
		$Pkfields = array_flip($Pkfields);
		if(count(array_intersect_key($Pk, $Pkfields)) != count($Pkfields))
			throw new Exception('Primary key fields does not match those of table '.$table);
		
		$req = 'SELECT * FROM '.$table;
		
		$i = 0;
		foreach($Pk as $key => $value)
		{
			if($i == 0)
				$req .= ' WHERE ';
			else
				$req .= ' AND ';
			
			$c = self::quote_smart($value);
			$req .= "$key='$c'"; // A primary key is never NULL
		}
		
		$res = mysql_query($req);
		if(!$res)
			throw new Exception('Invalid request to init by primary key');
		if($d = mysql_fetch_object($res))
		{
			foreach(get_object_vars($d) as $var => $value)
				$this->$var = $value; // call __set
		}
		else
			throw new Exception('No record for this primary key :-(');
	}
	
	//------------------------------
	// Get the primary key
	
	protected function getPrimaryKey()
	{
		if(!$db_connected)
			self::connect();
		
		$table = eval('return '.get_class($this).'::$_table;');
		$keys = array();
		
		$result = mysql_query('SHOW KEYS FROM '.$table, self::$db_link);
		if(!$result)
			throw new Exception('Impossible to get primary key(s) of table '.$table);		
		while($row = mysql_fetch_assoc($result))
		{
			if ($row['Key_name'] == 'PRIMARY')
				$keys[$row['Seq_in_index'] - 1] = $row['Column_name'];
		}
		
		return $keys;
	}
	
	//------------------------------	
	// Get the fields
	
	protected function getFields()
	{
		if(!$db_connected)
			self::connect();
		
		// Can't use self::$_table ; so here is a nice cheat :
		$table = eval('return '.get_class($this).'::$_table;');
		$tb = array();
		
		$result = mysql_query('SHOW COLUMNS FROM '.$table, self::$db_link);
		if(!$result)
			throw new Exception('Impossible to get information about table '.$table);		
		while($row = mysql_fetch_assoc($result))
			$tb[] = $row['Field'];
		
		return $tb;
	}
	
	//------------------------------
	// Static function to remove a record from database
	
	protected static function deleteDirectly($table, $Pkfields, $Pk)
	{
		if(!$db_connected)
			self::connect();
		
		// Cannot use get_class($this) to get the table because we're in a static function, so i'm using a parameter... same thing with $Pkfields
		
		// To be sure $Pk is filled with enough keys to describe a primary key, we have to verify
		$Pkfields = array_flip($Pkfields);
		if(count(array_intersect_key($Pk, $Pkfields)) != count($Pkfields))
			throw new Exception('Primary key fields does not match those of table '.$table);
		
		$txt_requete = 'DELETE FROM '.$table;
		$i = 0;
		
		foreach($Pk as $key => $value)
		{
			if($i == 0)
				$txt_requete .= ' WHERE ';
			else
				$txt_requete .= ' AND ';
			
			$c = self::quote_smart($value);
			$txt_requete .= "$key='$c'"; // A primary key is never NULL
		}
		
		$txt_requete .= ';';
		if(!mysql_query($txt_requete, self::$db_link))
			throw new Exception('Delete error:<br />'.$txt_requete.'<br />'.mysql_error(self::$db_link));
		if(mysql_affected_rows(self::$db_link) == 0) // Primary Key does not match any record => exception
			throw new Exception('Delete error:<br />'.$txt_requete.'<br />0 records affected');
	}
	
	//------------------------------
	// getAll + getCount
	
	protected static function getAll($class, $table)
	{
		if(!$db_connected)
			self::connect();
		
		$etu = array();
		
		$res = mysql_query('SELECT * FROM '.$table);
		if(!$res)
			throw new Exception('Impossible to retrieve all items of '.$table);
		while($d = mysql_fetch_object($res))
		{
			$e = new $class;
			foreach(get_object_vars($d) as $var => $value)
				$e->$var = $value; // call __set
			$etu[] = $e;
		}
		
		return $etu;
	}
	
	protected static function getCount($table)
	{
		if(!$db_connected)
			self::connect();
		
		$result = mysql_query('SELECT COUNT(*) AS nb FROM '.$table);
		if(!$result)
			throw new Exception('Impossible to count items of '.$table);
		$row = mysql_fetch_assoc($result);
		return $row['nb'];
	}
	
	//------------------------------
	// SQL protection
	
	private static function quote_smart($value)
	{
		if(get_magic_quotes_gpc())
			$value = stripslashes($value);
		
		if(!is_numeric($value))
			$value = mysql_real_escape_string($value);
		
		return $value;
	}
}

?>

<?php

/*
CREATE TABLE `phepsyl`.`Students` (
`IdStudent` INT NOT NULL AUTO_INCREMENT ,
`NameStudent` VARCHAR( 50 ) NOT NULL ,
`AgeStudent` INT NOT NULL ,
`PictureStudent` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `IdStudent` ) 
) ENGINE = InnoDB
*/

class student extends mySQL // student is a child of mySQL
{
	// Must-have members of the class
	public static $_table = 'Students';		// table name
	public static $_fields = array();		// fields of the table
	public static $_primaryKey = array();	// the primary key
	private static $_loadFields = false;	// are fields of this table aldready loaded?
	
	//------------------------------
	// Properties of the class
	
	protected $IdStudent;
	protected $NameStudent;
	protected $AgeStudent;
	protected $PictureStudent;
	
	//------------------------------
	// Constructor + Destructor
	
	public function __construct()
	{
		parent::__construct(); // call dady
		
		if(self::$_loadFields == false)
		{
			// Only once during execution : we get the table fields and the primary key of the concern table (here=Students)
			
			self::$_fields = self::getFields();
			self::$_primaryKey = self::getPrimaryKey();
			self::$_loadFields = true; // done
		}
		
		foreach($this as $key => $value)
			$this->$key = NULL; // set every member to NULL
	}
	
	public function __destruct()
	{	
		foreach($this as $key => $value)
			unset($this->$key);
	}
	
	//------------------------------
	// Getter + Setter
	
	public function __get($attribute)
	{
		if(!property_exists(get_class($this), $attribute))
			throw new Exception('Trying to get an invalid student member');
		
		return $this->$attribute;
	}
	
	public function __set($attribute, $value)
	{
		if(!property_exists(get_class($this), $attribute))
			throw new Exception('Trying to set an invalid student member');
		
		// Verifications here...
		if($attribute == 'AgeStudent' && (!is_numeric($value) || $value < 0))
			throw new Exception('Invalid student age: '.$value);
		
		$this->$attribute = $value;
	}
	
	//------------------------------
	
	public function init_student($IdS, $Name, $Age, $Picture) // Copy constructor
	{
		$this->IdStudent = $IdS;
		$this->NameStudent = $Name;
		$this->AgeStudent = $Age;
		$this->PictureStudent = $Picture;
	}
	
	public function insert()
	{
		$this->NameStudent = strtoupper($this->NameStudent);
		$this->IdStudent = $this->insert_bdd();
	}
	
	public function update()
	{
		$this->NameStudent = strtoupper($this->NameStudent);
		$this->update_bdd();
	}
	
	public function delete()
	{
		$this->delete_bdd();
	}
	
	//------------------------------
	
	public static function initByPrimaryKey($Pk)
	{
		$c = __CLASS__;
		$p = new $c;
		$p->init_by_primaryKey($Pk);
		return $p;
	}
	
	public static function deleteDirectly($Pk)
	{
		parent::deleteDirectly(self::$_table, self::$_primaryKey, $Pk);
	}
	
	public static function getAll()
	{
		return parent::getAll(__CLASS__, self::$_table);
	}
	
	public static function getCount()
	{
		return parent::getCount(self::$_table);
	}
}

?>

<?php

include_once('class_mySQL.php');
include_once('class_student.php');

//-------------------------

$p = new student;
$p->NameStudent = 'DUPONT';
$p->AgeStudent = 17;
$p->PictureStudent = 'pic124.jpg';
$p->insert(); // INSERT DEMO
print_r($p); // id automatically set
echo '<br />';

$p->AgeStudent++;
$p->update(); // UPDATE DEMO

//-------------------------

$q = new student; // don't re-connect ;)
$q->NameStudent = 'test'; // will be put in capital letters
$q->AgeStudent = 20;
$q->PictureStudent = 'pic125.jpg';
$q->insert();

$u = array('IdStudent' => $q->IdStudent); // primary key of $q
$r = student::initByPrimaryKey($u); // initByPrimaryKey DEMO
print_r($r);
echo '<br />';

//-------------------------

$n = student::getCount(); // getCount DEMO
echo 'We have '.$n.' students in database<br />';

$students = student::getAll(); // getAll DEMO
print_r($students);
echo '<br />';

//-------------------------

$p->delete(); // DELETE DEMO
student::deleteDirectly($u); // STATIC DELETE DEMO

//-------------------------

//$p->db_connected = 'lol'; // not possible (static member, not object member)
//$p->db_host = 'ahah'; // not possible
//echo $p->db_host; // not possible

?>

Refactorings

No refactoring yet !

6dc0e9a07bcff97ac9b111f36e12f1f6

Ishkur

August 2, 2008, August 02, 2008 03:04, permalink

No rating. Login to rate!

There seem to be a lot of these ideas floating around (myself included), and maybe its just me seeing the light (or lackthereof), but I've started using CoughPHP ORM. But your methods looks rather interesting.

And as for your disconnect();

<?php

	//...
	/**
	 * this refactoring might be asinine,
	 * but I think it's important to cover
	 * your ass just in case. 
	 */
	public static function disconnect()
	{
		if(self::$db_link === false)
			throw new Exception('no connection present');
		@mysql_close(self::$db_link);	
	}
	//...

?>
1e6b9d50678a0cc32d98aa1dd1ec3846

TiTi

August 2, 2008, August 02, 2008 10:41, permalink

No rating. Login to rate!

I don't think it's a good idea to use the '@' operator, see : http://michelf.com/weblog/2005/bad-uses-of-the-at-operator/
Maybe it doesn't slow down the execution with the mysql_close command, but as a rule of thumb, i don't use @.

6dc0e9a07bcff97ac9b111f36e12f1f6

Ishkur

August 2, 2008, August 02, 2008 19:14, permalink

No rating. Login to rate!

Can you elaborate some more as to why you dont? do you run a custom error handling mechanism, or do you just not handle extraneous errors ?

1e6b9d50678a0cc32d98aa1dd1ec3846

TiTi

August 2, 2008, August 02, 2008 19:38, permalink

No rating. Login to rate!

Well basically I don't use the @ operator because it suppresses errors. To answer your question, I'm handling errors with my own exception class. .... hum ok I got it, you add the @ here to respond to my original question : "stupid to launch an exception here ?" ... hum no, i got it NOW ! without the @ operator, the warning/fatal error is not suppressed... Damn I didn't think of that this way. I never used @ in order to the see errors but you're right : here i'm using exceptions but i'm completely missing thing kind of extraneous errors. Thanks for telling me that!
Ok i google it and this article explains how to deal with those extraneous errors : http://thesmithfam.org/blog/2006/05/07/php-the-operator/

6dc0e9a07bcff97ac9b111f36e12f1f6

Ishkur

August 5, 2008, August 05, 2008 05:39, permalink

No rating. Login to rate!

I've taken to liking your database wrapper, a lot. Among a few minor tweaks, I've added a method to it that you might (or might not) like, check this out:

<?php
	/**
	 * $page = page::getWhere(array('PageID' => 125));
	 */
	protected static function getWhere($class, $table, $where = array())
	{
		if(!self::$dbconnected)
			self::connect();
		$etu = array();
		//process WHERE statement
		$txt_query = 'SELECT * FROM '.$table;
		$i = 0;
		foreach($where as $key => $value)
		{
			if($i == 0)
				$txt_query .= ' WHERE ';
			else
				$txt_query .= ' AND ';
				
			$txt_value = self::quote_smart($value);
			if(is_numeric($value))
				$txt_query .= "$key = $value";
			elseif(is_null($value))
				$txt_query .= "$key = NULL";
			else
				$txt_query .= "$key = '$txt_value'";
			$i++;
		}
		
		$txt_query .= ';';
		if(($result = @mysql_query($txt_query)) === false)
			throw new Exception('Impossible to retrieve desired items of '.$table);
		while($d = @mysql_fetch_object($result))
		{
			$e = new $class;
			foreach(get_object_vars($d) as $var => $value)
				$e->$var = $value;
			//this returns an array, which makes us have to foreach the return.
			//might want to make some sort of checking to see if the result
			//is more than 1 row it returns an array, and if only just 1 row returns,
			//it sets it as a singular object (such as if you were selecting pages
			//from a database with like Categories, you'd need an array to loop through,
			//but if you were selecting a single user, that loop would be an unnecessary 
			//hassle)... something to work on later i guess
			$etu[] = $e;
		}
		return $etu;
	}
?>
<?php

	public static function getWhere($where)
	{
		return parent::getWhere(__CLASS__, self::$_table, $where);
	}

?>
<?php

//include required files
require_once('inc/classes/db.class.php');
require_once('inc/models/page.class.php');

//some generic scenario where
//you would needa WHERE clause
$login = array(
	'username' => "$_POST['username']",
	'password' => "$_POST['password']"
);

//fetch user records from database
$user = user::getWhere($login);

//...
//process login	
//...

?>
1e6b9d50678a0cc32d98aa1dd1ec3846

TiTi

August 6, 2008, August 06, 2008 16:57, permalink

No rating. Login to rate!

Woa! Very interesting way to do select queries with the where clause. And still the advantage of my orm : getWhere work for every single class your implementing...
Thanks for paying attention to my code and refactor it!
I don't have time to look for orm systems like coughphp or phpdoctrine because i'm not working on a php project at the moment and i'm very busy, but i think they are more optimized...
But as I said, I think it might be easier in the future to use my class with php6 (after some lifting...)

151e36cc7f789a4790c8ca437e3a1f60

Chris Dean

August 14, 2008, August 14, 2008 07:20, permalink

No rating. Login to rate!

I've had a play with doctrine and to me, although it has loads of great functionality, it's a bit of a bloated monster.

If you're just doing basic ORM, running simple operations, then something like you have presented is more suitable, in my opinion.

159fdebc1e198202eebdd68e05a57c46

Kingtona

May 7, 2010, May 07, 2010 09:10, permalink

No rating. Login to rate!

thank you for this code but i get 1 problem on getAll function it cannot get value from function and error page

159fdebc1e198202eebdd68e05a57c46

Kingtona

May 7, 2010, May 07, 2010 09:12, permalink

No rating. Login to rate!

thank you for this code but i get 1 problem on getAll function it cannot get value from function and error page

C55311e1e02a00b8b37a136bda66fc29

Wdudaseyx

October 14, 2010, October 14, 2010 16:39, permalink

No rating. Login to rate!

Hello gays, very cool forum!

Your refactoring





Format Copy from initial code

or Cancel