[PHP] On MySQL original PHP 5 wrapper class

2008-08-06T16:57:00+00:00

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...)

[PHP] On MySQL original PHP 5 wrapper class

2008-08-02T19:38:44+00:00

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/

[PHP] On MySQL original PHP 5 wrapper class

2008-08-02T10:41:05+00:00

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 @.

[PHP] MySQL original PHP 5 wrapper class

2008-08-01T20:44:15+00:00

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

?>

[PHP] On objects that deliver formatted HTML

2008-08-01T14:40:14+00:00

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="fr">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Demo 1</title>
</head>
<body>

<b>ID :</b> [var.guy.IdStudent]<br />
<b>NAME :</b> [var.guy.NameStudent]<br />
<b>AGE :</b> [var.guy.AgeStudent]

</body>
</html>

[PHP] On objects that deliver formatted HTML

2008-08-01T14:39:47+00:00

Everything is not in the class user : your controller (what you call "the rendering page") is outside !

It's a bad idea to do that way, for instance :
-How could you change the rendering of a user and another differently ?
-How could you display an array of users ?
-The designer need to know your classes to modify the display

I recommend you a template system like TinyButStrong for example : http://www.tinybutstrong.com/
I'm actually writing a tutorial (aldready 6 parts written) to create a structured website in PHP 5 Object Oriented + TinyButStrong.... Stay tuned it will be published in some days/weeks on the TinyButStrong website :)

To give you a simple example i joined the first demo.

<?php 

class student 
{ 
    public $IdStudent; 
    public $NameStudent; 
    public $AgeStudent; 
} 

$guy = new student(); 
$guy->IdStudent = 5; 
$guy->NameStudent = 'WOOD'; 
$guy->AgeStudent = 22; 

include_once('includes/tbs_class_php5.php'); 
$TBS = new clsTinyButStrong; 
$TBS->LoadTemplate('demo1.html'); 
$TBS->Show(); 

?>

Ishkur friends

[PHP] On MySQL original PHP 5 wrapper class

[PHP] On MySQL original PHP 5 wrapper class

[PHP] On MySQL original PHP 5 wrapper class

[PHP] MySQL original PHP 5 wrapper class

[PHP] On objects that deliver formatted HTML

[PHP] On objects that deliver formatted HTML