Pastable version of MySQL original PHP 5 wrapper class

<pre class='prettyprint' language='php'>&lt;?php

abstract class mySQL // abstract =&gt; 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(&quot;SET NAMES 'utf8'&quot;, 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 &amp; getPrimaryKey -&gt; 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 =&gt; $value)
		{
			if(isset($tb_champs[$key]))
			{
				if($i &gt;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 .= &quot;'$c'&quot;;
				
				$i++;
			}
		}
		
		$sql = 'INSERT INTO '.$table.'('.$txt_fields.') VALUES ('.$txt_values.');'; 
		if(!mysql_query($sql, self::$db_link))
			throw new Exception('Insertion error:&lt;br /&gt;'.$sql.'&lt;br /&gt;'.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 &amp; getPrimaryKey -&gt; 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 =&gt; $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 .= &quot;$key=NULL&quot;;
				else
					$txt_requete .= &quot;$key='$txt_valeur'&quot;;
				$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-&gt;$key); // __get() could throw an exception if this field doesn't exist
			$txt_requete .= &quot;$key='$c'&quot;; // A primary key is never NULL
		}
		
		$txt_requete .= ';';
		if(!mysql_query($txt_requete, self::$db_link))
			throw new Exception('Update error:&lt;br /&gt;'.$txt_requete.'&lt;br /&gt;'.mysql_error(self::$db_link));
		if(mysql_affected_rows(self::$db_link) == 0) // Primary Key does not match any record =&gt; exception
			throw new Exception('Update error:&lt;br /&gt;'.$txt_requete.'&lt;br /&gt;0 records affected');
	}
	
	protected function delete_bdd()
	{
		if(!$db_connected) // useless here because of child classes constructor (getFields &amp; getPrimaryKey -&gt; 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 =&gt; $value)
			$t[$value] = $this-&gt;$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 &amp; getPrimaryKey -&gt; 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 =&gt; $value)
		{
			if($i == 0)
				$req .= ' WHERE ';
			else
				$req .= ' AND ';
			
			$c = self::quote_smart($value);
			$req .= &quot;$key='$c'&quot;; // 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 =&gt; $value)
				$this-&gt;$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 =&gt; $value)
		{
			if($i == 0)
				$txt_requete .= ' WHERE ';
			else
				$txt_requete .= ' AND ';
			
			$c = self::quote_smart($value);
			$txt_requete .= &quot;$key='$c'&quot;; // A primary key is never NULL
		}
		
		$txt_requete .= ';';
		if(!mysql_query($txt_requete, self::$db_link))
			throw new Exception('Delete error:&lt;br /&gt;'.$txt_requete.'&lt;br /&gt;'.mysql_error(self::$db_link));
		if(mysql_affected_rows(self::$db_link) == 0) // Primary Key does not match any record =&gt; exception
			throw new Exception('Delete error:&lt;br /&gt;'.$txt_requete.'&lt;br /&gt;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 =&gt; $value)
				$e-&gt;$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;
	}
}

?&gt;

&lt;?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 =&gt; $value)
			$this-&gt;$key = NULL; // set every member to NULL
	}
	
	public function __destruct()
	{	
		foreach($this as $key =&gt; $value)
			unset($this-&gt;$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-&gt;$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' &amp;&amp; (!is_numeric($value) || $value &lt; 0))
			throw new Exception('Invalid student age: '.$value);
		
		$this-&gt;$attribute = $value;
	}
	
	//------------------------------
	
	public function init_student($IdS, $Name, $Age, $Picture) // Copy constructor
	{
		$this-&gt;IdStudent = $IdS;
		$this-&gt;NameStudent = $Name;
		$this-&gt;AgeStudent = $Age;
		$this-&gt;PictureStudent = $Picture;
	}
	
	public function insert()
	{
		$this-&gt;NameStudent = strtoupper($this-&gt;NameStudent);
		$this-&gt;IdStudent = $this-&gt;insert_bdd();
	}
	
	public function update()
	{
		$this-&gt;NameStudent = strtoupper($this-&gt;NameStudent);
		$this-&gt;update_bdd();
	}
	
	public function delete()
	{
		$this-&gt;delete_bdd();
	}
	
	//------------------------------
	
	public static function initByPrimaryKey($Pk)
	{
		$c = __CLASS__;
		$p = new $c;
		$p-&gt;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);
	}
}

?&gt;

&lt;?php

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

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

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

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

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

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

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

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

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

$students = student::getAll(); // getAll DEMO
print_r($students);
echo '&lt;br /&gt;';

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

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

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

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

?&gt;</pre>
<a href="http://www.refactormycode.com/codes/416-mysql-original-php-5-wrapper-class" style="color:#fff" title="As seen on RefactorMyCode.com"><img alt="Small_logo" src="http://www.refactormycode.com/images/small_logo.gif" style="border:0" /></a>

Refactor :my => 'code'

Recent

Popular

Pastable version of MySQL original PHP 5 wrapper class