MySQL original PHP 5 wrapper class


    <?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

?>
    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) ?
    2008-08-01T20:44:15+00:00
    416
    PHP
    mysql-original-php-5-wrapper-class
    7
    MySQL original PHP 5 wrapper class
    
    2008-08-14T07:20:25+00:00
    880
    
      880
      http://titi.myopenid.com
      TiTi
      0.0
      5