<?php
// ------------------------------------------------------------------------- //
// Gestion générique de table via un objet php //
// ------------------------------------------------------------------------- //
// Auteur: Philippe Gaultier //
// Email: ph.gaultier@wanadoo.fr //
// Web: //
// ------------------------------------------------------------------------- //
# table.inc.php
# Classe développée par Philippe GAULTIER
# ph.gaultier@wanadoo.fr
# peut être redistribué et utilisé sous le mode de license GPL
#
## Classe générique d'accès aux tables
## Les test et validation ont été fait avec le wrapper mysql(_p) uniquement
Class table
{
## Nom de la table
## Ex : $TableName = "users"
Var $TableName;
## Définition des champs de la table
## type : number, string, date
## null : 0 => ne peut etre nul , 1 => peut etre nul
## pkey : 0 => ne fait pas partie de la clé primaire, 1 => fait partie de la cle primaire
## search : 0 => n'est pas un argument de recherche, 1 => est un argument de recherche
## Ex : $Fiels = array(
## 'users_id' => array('type' => 'number', 'null' => 0, 'pkey' => 1, 'search' => 0)
## , 'users_login' => array('type' => 'string', 'null' => 0, 'pkey' => 0, 'search' => 1)
## , 'users_password' => array('type' => 'string', 'null' => 0, 'pkey' => 0, 'search' => 0)
## , 'users_lastlogin' => array('type' => 'date', 'null' => 1, 'pkey' => 0, 'search' => 0)
## );
Var $Fields;
## Valeur des champs en fonction d'un select, ... généré automatiquement
## Tous writable sauf la pkey
## Tous readable
## Ex : $FieldsValues = array(
## 'user_id' => $userId
## , 'user_login' => $userLogin
## , 'user_password' => $userPassWord
## , 'user_lastlogin' => $userLastLogin
## );
Var $FieldsValues;
## Critères de recherche
## Liste des Id retournés lors d'une recherche
Var $IdList;
Var $SpecialWhereClause;
Var $SpecialOrderByClause;
## La classe est active ou non
Var $Actif;
## Database Handler
Var $Dbh;
## Variable contenant les erreurs
## Array dans lequel on empile les erreurs
Var $Errors;
function table( $oDbase=0, $Table="", $Fields=0 )
{
## Constructeur testé et validé sur une table avec 1 pkey, 3 champs
if ( is_array($Fields) && ($Table != "") && is_object($oDbase))
{
## Traitement
$this->Fields = $Fields;
$this->TableName = $Table;
$this->IdList = array();
$this->Errors = array();
$this->Dbh = $oDbase;
$this->SpecialWhereClause = "";
$this->SpecialOrderByClause = "";
$this->Actif = 1;
$this->FieldsValues = $this->_build_fieldsvalues();
if ($this->_get_pkey() == "")
{
$this->Actif = 0;
$this->Errors[] = "La primary key n'est pas définie";
}
}
else
{
## Erreur d'initialisation
$this->Fields = array();
$this->FieldsValues = array();
$this->IdList = array();
$this->Errors = array();
$this->TableName = "";
$this->SpecialWhereClause = "";
$this->SpecialOrderByClause = "";
$this->Dbh = 0;
$this->Actif = 0;
$this->Errors[] = "Erreur d'initialisation de la classe";
}
return ($this->Actif);
}
function set ($fieldName="", $fieldValue="")
{
$FieldIsOk = 0;
if (strtolower($fieldName) == "specialwhereclause")
{
$this->SpecialWhereClause = $fieldValue;
$FieldIsOk = 1;
}
elseif (strtolower($fieldName) == "specialorderbyclause")
{
$this->SpecialOrderByClause = $fieldValue;
$FieldIsOk = 1;
}
else
{
foreach($this->FieldsValues as $fieldKey => $fieldVal)
{
if ($fieldKey == $fieldName)
{
$this->FieldsValues[$fieldName] = $fieldValue;
$FieldIsOk = 1;
}
}
}
if($FieldIsOk == 0)
{
$this->Errors[] = "Propriété non trouvé";
}
return($FieldIsOk);
}
function get ($fieldName="")
{
## Accesseur testé et validé
$FieldIsOk = 0;
if (strtolower($fieldName) == "specialwhereclause")
{
$fieldValue = $this->SpecialWhereClause;
$FieldIsOk = 1;
}
elseif (strtolower($fieldName) == "specialorderbyclause")
{
$fieldValue = $this->SpecialOrderByClause;
$FieldIsOk = 1;
}
else
{
foreach($this->FieldsValues as $fieldKey => $fieldVal)
{
if ($fieldKey == $fieldName)
{
$fieldValue = $fieldVal;
$FieldIsOk = 1;
}
}
}
if($FieldIsOk == 0)
{
$fieldValue = $FieldIsOk;
$this->Errors[] = "Propriété non trouvé";
}
return($fieldValue);
}
function select( $PrimaryKey=0 )
{
## Selection dans la table testé et validé sur une table avec 1 pkey, 3 champs
if($this->Actif)
{
$fieldKey = $this->_get_pkey();
if($fieldKey != "")
{
$searchValue = $this->_quote_field($fieldKey, $this->_slashes( $PrimaryKey));
$reqSql = "SELECT * FROM ".$this->TableName." WHERE ".$fieldKey."=".$searchValue;
$this->Dbh->query( $reqSql );
$arrayResult = $this->Dbh->fetch_row( );
if( count($arrayResult) != 1 )
{
$this->Errors[] = "Le nombre d'enregistrements est différent de 1";
}
else
{
$i = 0;
foreach($arrayResult[0] as $selectedKey => $selectedValues)
{
$this->set($selectedKey, $selectedValues);
}
}
$this->Dbh->free();
}
else
{
$arrayResult = array();
$this->Errors[] = "Pas de clé primaire";
}
}
else
{
$arrayResult = array();
}
return($arrayResult);
}
function delete( $PrimaryKey=0 )
{
## Deletion dans la table testé et validé sur une table avec 1 pkey, 3 champs
$return = 0;
$fieldKey = $this->_get_pkey();
if($fieldKey != "")
{
$searchValue = $this->_quote_field($fieldKey, $this->_slashes( $PrimaryKey));
$reqSql = "DELETE FROM ".$this->TableName." WHERE ".$fieldKey."=".$searchValue;
$this->Dbh->query( $reqSql );
## $this->Dbh->free();
$return = 1;
}
else
{
$return = 0;
$this->Errors[] = "Pas de clé primaire";
}
return($return);
}
function insert( )
{
$return = 0;
$test = 0;
$fieldKey = $this->_get_pkey();
if($fieldKey != "")
{
$fieldListKeys = " (";
$fieldListValues = " (";
foreach($this->FieldsValues as $fieldName => $fieldValue)
{
if ($fieldName != $fieldKey)
{
if($test == 1)
{
$fieldListKeys .= " ,";
$fieldListValues .= " ,";
}
$fieldListKeys .= $fieldName;
$fieldListValues .= $this->_quote_field($fieldName, $this->_slashes( $fieldValue));
$test = 1;
}
}
$fieldListKeys .= ") ";
$fieldListValues .= ") ";
$reqSql = "INSERT INTO ".$this->TableName." ".$fieldListKeys." VALUES ".$fieldListValues;
$this->Dbh->query( $reqSql );
$return = 1;
}
else
{
$return = 0;
$this->Errors[] = "Pas de clé primaire";
}
return($return);
}
function search($fieldSearch="",$fieldSearchValue="",$fieldOrder="",$fieldOrderWay="ASC")
{
if(($fieldSearch != "") && $this->_is_field_searchable($fieldSearch))
{
$searchClause = " WHERE ".$fieldSearch;
switch($this->_get_field_type($fieldSearch))
{
case "date" :
case "number" :
$searchClause .= " = ".$this->_quote_field($fieldSearch, $this->_slashes( $fieldSearchValue))." ";
break ;
case "string" :
$searchClause .= " LIKE ".$this->_quote_field($fieldSearch, "%".$this->_slashes( $fieldSearchValue)."%")." ";
break ;
default :
$searchClause = " ";
break ;
}
}
elseif ($this->SpecialWhereClause != "")
{
$searchClause = " ".$this->SpecialWhereClause." ";
}
else
{
$searchClause = " ";
}
if(($fieldOrder != "") && $this->_field_exists($fieldOrder))
{
if ((strtolower($fieldOrderWay) == "asc") || (strtolower($fieldOrderWay) == "desc"))
{
$fieldOrderWay = strtoupper($fieldOrderWay);
}
else
{
$fieldOrderWay = "ASC";
}
$orderClause = " ORDER BY ".$fieldOrder." ".$fieldOrderWay." ";
}
elseif($this->SpecialOrderByClause != "")
{
$orderClause = " ".$this->SpecialOrderByClause." ";
}
else
{
$orderClause = " ";
}
$reqSql = "SELECT * FROM ".$this->TableName." ".$searchClause." ".$orderClause;
$this->Dbh->query( $reqSql );
$arrayResult = $this->Dbh->fetch_row( );
$primKey = $this->_get_pkey();
$this->IdList = array();
for($i=0; $i < count($arrayResult); $i++)
{
$this->IdList[] = $arrayResult[$i][$primKey];
}
$this->Dbh->free();
return($this->IdList);
}
function last_error()
{
## Last Error testé et validé
if(count($this->Errors) >= 1)
{
$errorCount = count($this->Errors);
$errorCount--;
$errorMsg = $this->Errors[$errorCount];
}
else
{
$errorMsg = "";
}
return($errorMsg);
}
function errors()
{
return($this->Errors);
}
## Fonctions privées de la classe
function _get_pkey()
{
if($this->Actif)
{
$primaryKey = "";
foreach($this->Fields as $fieldName => $arrayDef)
{
if($arrayDef['pkey'] == 1)
{
$primaryKey = $fieldName;
}
}
}
else
{
$primaryKey = "";
}
return ($primaryKey);
}
function _build_fieldsvalues()
{
$myArray = array();
$BuildIsOk = 1;
foreach($this->Fields as $fieldName => $arrayDef)
{
switch($arrayDef['type'])
{
case "date" :
$myArray[$fieldName] = time();
break ;
case "string" :
$myArray[$fieldName] = "";
break ;
case "number" :
$myArray[$fieldName] = 0;
break ;
default :
$BuildIsOk = 0;
$this->Errors[] = "Type de champ non reconnu";
break ;
}
}
/* if (!$BuildIsOk)
{
$arrayFields = array();
}*/
return ($myArray);
}
function _is_field_searchable($fieldName="")
{
$FieldIsOk = 0;
if($fieldName != "")
{
foreach($this->Fields as $fieldKey => $fieldVal)
{
if ($fieldKey == $fieldName)
{
$FieldIsOk = $this->Fields[$fieldName]['search'];
}
}
}
else
{
$FieldIsOk = 0;
}
return( $FieldIsOk );
}
function _field_exists($fieldName="")
{
$FieldIsOk = 0;
if($fieldName != "")
{
foreach($this->Fields as $fieldKey => $fieldVal)
{
if ($fieldKey == $fieldName)
{
$FieldIsOk = 1;
}
}
}
else
{
$FieldIsOk = 0;
}
return( $FieldIsOk );
}
function _get_field_type($fieldName="")
{
$FieldType = "";
if($fieldName != "")
{
foreach($this->Fields as $fieldKey => $fieldVal)
{
if ($fieldKey == $fieldName)
{
$FieldType = $this->Fields[$fieldName]['type'];
}
}
}
else
{
$FieldType = "";
}
return( $FieldType ) ;
}
## Fonctions de maintenances, devraient être déplacées dans le dbh...
function _slashes( $fieldValue="" )
{
if($this->Actif && is_string($fieldValue) && ($fieldValue != ""))
{
$fieldValue = addslashes($fieldValue);
}
return ($fieldValue);
}
function _quote_field( $fieldName="", $fieldValue="" )
{
if($this->Actif && ($fieldName != "") && ($fieldValue != "") )
{
$fieldType = $this->Fields[$fieldName]['type'];
switch($fieldType)
{
case "string" :
case "date" :
$fieldValue = "'".$fieldValue."'";
break ;
}
}
return ($fieldValue);
}
}
?>
<?
# mysql.inc.php
# Classe développée par Philippe GAULTIER
# ph.gaultier@wanadoo.fr
# peut être redistribué et utilisé sous le mode de license GPL
#
# classe database : PHP4 ou +
/*
Fonctions publiques :
$bEtat = function database( $user, $pass, $hostip, $name, $port = 1521 )
$bEtat = function set( $propriete, $valeur )
$xValue = function get( $propriete )
$bEtat = function connect( )
$bEtat = function disconnect( )
$xResult = function query( $req )
$hRows = function fetch_row ( )
$bEtat = function free( )
$bEtat = function commit( )
$bEtat = function rollback( )
Propriétés publiques :
dbh Database Handler
dbuser Nom de l'utilisateur de la base de données
dbpass Mot de passe de l'utilisateur de la base de données
dbhost Adresse IP du serveur de base de données
dbport Port pour accéder à la base de données (3306 par défaut)
dbname Nom de la base de donnée
iRequestNbRows Nombre de lignes retournées par la requette
Fonction privées :
void = function debug( $chaine )
*/
Class database
{
## Def: Attributs de la class
## Var: Handlet d'acces a la base
var $dbh;
## Var: Login
var $dbuser;
## Var: Password
var $dbpass;
## Var: Hostname
var $dbhost;
## Var: tnsName
var $dbname;
## Var: Port (default = 3306)
var $dbport;
## Var: Nomnre de lignes recuperees
var $iRequestNbRows;
## Var: Resultat des requettes
var $vRequestResult;
## Var: Code d'erreur
var $xErrorCode;
## Var: Hashage des erreurs
var $hhxTabExcept;
##
# ###########################################################################
#
# BeginSection = Constructeur
#
#
## Methode: Constructeur
## Object : Constructeur de la class
## Type : Public
## Entrees: $user := nom du user de connection
## $pass := password de connection
## $hostip := ip du host
## $name := nom de la base
## $port := port de connection (default = 3306)
## Sortie :
## Retour : objet database
function database( $user, $pass, $hostip, $name, $port = 3306 )
{
define( "_DATABASE_ERRCODE", 0 );
define( "_DATABASE_VERBOSE", 0 );
$this->dbuser = $user;
$this->dbpass = $pass;
$this->dbhost = $hostip;
$this->dbport = $port;
$this->dbname = $name;
# Hash de correspondance code/message erreur de la classe database
$this->hhxTabExcept = array (
"CONNECT" => array (
0 => "OK",
1 => "Connection refusee",
2 => "Choix de base impossible"
),
"DISCONNECT" => array (
0 => "OK",
1 => "Deconnection refusee"
),
"SET" => array (
0 => "OK",
1 => "Propriete inexistante"
),
"GET" => array (
0 => "OK",
1 => "Propriete inexistante"
),
"FREE" => array (
0 => "OK",
1 => "Erreur lors du Free"
),
"COMMIT" => array (
0 => "OK",
1 => "Erreur lors du Commit"
),
"ROLLBACK" => array (
0 => "OK",
1 => "Erreur lors du Rollback"
),
"QUERY" => array (
0 => "OK",
1 => "Erreur dans la Query"
)
);
return ( 1 );
}
##
#
# EndSection = Constructeur
#
# ###########################################################################
#
##
# ###########################################################################
#
# BeginSection = Accesseurs
#
#
## Methode:
## Object :
## Type :
## Entrees:
## Sortie :
## Retour :
function set( $propriete, $valeur )
{
if ( 0 )
{
$this->{$propriete} = $valeur ;
$this->xErrorCode = "DATABASE_SET_0";
$this->debug( "propriete = ".$propriete." valeur = ".$valeur );
$res = 1;
}
else
{
$this->xErrorCode = "DATABASE_SET_1";
$this->debug( "propriete = ".$propriete." valeur = ".$valeur );
$res = 0 ;
}
return ( $res );
}
## Methode:
## Object :
## Type :
## Entrees:
## Sortie :
## Retour :
function get( $propriete )
{
if ( $propriete == "iRequestNbRows" )
{
$res = $this->{$propriete} ;
$this->xErrorCode = "DATABASE_GET_0";
$this->debug( "propriete = ".$propriete." valeur = ".$res );
}
else
{
$res = 0;
$this->xErrorCode = "DATABASE_GET_1";
$this->debug( "propriete = ".$propriete." valeur = ".$res );
}
return ( $res );
}
## Methode:
## Object :
## Type :
## Entrees:
## Sortie :
## Retour :
##
#
# EndSection = Accesseurs
#
# ###########################################################################
#
##
# ###########################################################################
#
# BeginSection = Methodes publics
#
#
## Methode:
## Object :
## Type :
## Entrees:
## Sortie :
## Retour :
function connect( )
{
$result = 0;
if ( $this->dbh = mysql_connect($this->dbhost, $this->dbuser, $this->dbpass ) )
{
if ( mysql_select_db($this->dbname, $this->dbh) )
{
$this->xErrorCode = "DATABASE_CONNECT_0";
# $errmsg=mysql_error();
# $errmsg = "DEBUG COMMIT : ".$errmsg;
# $this->debug( $errmsg );
$this->debug( );
$result = 1;
}
else
{
$this->xErrorCode = "DATABASE_CONNECT_2";
$errmsg=mysql_error();
$errmsg = "ERREUR MYSQL : ".$errmsg;
$this->debug( $errmsg );
$result = 0;
}
}
else
{
$this->xErrorCode = "DATABASE_CONNECT_1";
$errmsg=mysql_error();
$errmsg = "ERREUR MYSQL : ".$errmsg;
$this->debug( $errmsg );
$result = 0;
}
return ( $result );
}
## Methode:
## Object :
## Type :
## Entrees:
## Sortie :
## Retour :
function disconnect( )
{
mysql_close( $this->dbh );
$this->xErrorCode = "DATABASE_DISCONNECT_0";
# $errmsg=ocierror();
# $errmsg = "DEBUG DISCONNECT : ".$errmsg['message'];
$this->debug( );
return( 1 );
}
## Methode:
## Object :
## Type :
## Entrees:
## Sortie :
## Retour :
function query( $req )
{
if ( $result = mysql_query( $req, $this->dbh ) )
{
$this->xErrorCode = "DATABASE_QUERY_0";
# $errmsg=ocierror($stmt);
# $errmsg = "Requette : ".$req."\n<br>DEBUG QUERY 1 : ".$errmsg['message'];
$errmsg = "Requette : ".$req."\n";
$this->debug( $errmsg );
if( eregi("select", $req) )
{
$this->iRequestNbRows = mysql_num_rows($result);
}
else
{
$this->iRequestNbRows = mysql_affected_rows($this->dbh);
}
$this->vRequestResult = $result;
}
else
{
$this->xErrorCode = "DATABASE_QUERY_1";
$errmsg=mysql_error($result);
$errmsg = "Requette : ".$req."\n<br><br>ERREUR MYSQL : ".$errmsg;
$this->debug( $errmsg );
$result = 0;
}
return( $result );
}
## Methode:
## Object :
## Type :
## Entrees:
## Sortie :
## Retour :
function fetch_row ( )
{
$resultat = array( );
$this->iRequestNbRows = 0;
while( $row = mysql_fetch_assoc( $this->vRequestResult ) )
{
$resultat[] = $row;
$this->iRequestNbRows++;
}
return ( $resultat );
}
## Methode:
## Object :
## Type :
## Entrees:
## Sortie :
## Retour :
function free( )
{
if( mysql_free_result( $this->vRequestResult ) )
{
$this->xErrorCode = "DATABASE_FREE_0";
# $errmsg=ocierror();
# $errmsg = "DEBUG FREE : ".$errmsg['message'];
$this->debug( );
$this->iRequestNbRows = "";
$this->vRequestResult = "";
$res = 1;
}
else
{
$this->xErrorCode = "DATABASE_FREE_1";
$errmsg=mysql_error();
# $errmsg = "ERREUR ORACLE : ".$errmsg;
$this->debug( $errmsg );
$this->iRequestNbRows = "";
$this->vRequestResult = "";
$res = 0;
}
return( $res );
}
## Methode:
## Object :
## Type :
## Entrees:
## Sortie :
## Retour :
function commit( )
{
$this->xErrorCode = "DATABASE_COMMIT_0";
# $errmsg=ocierror();
# $errmsg = "DEBUG COMMIT : ".$errmsg['message'];
$this->debug( );
$result = 1;
return( $result );
}
## Methode:
## Object :
## Type :
## Entrees:
## Sortie :
## Retour :
function rollback( )
{
$this->xErrorCode = "DATABASE_ROLLBACK_0";
# $errmsg=ocierror();
# $errmsg = "DEBUG ROLLBACK : ".$errmsg['message'];
$this->debug( );
$result = 1;
return( $result );
}
##
#
# EndSection = Methodes publics
#
# ###########################################################################
#
##
# ###########################################################################
#
# BeginSection = Methodes de debugage
#
#
## Methode:
## Object :
## Type :
## Entrees:
## Sortie :
## Retour :
function debug ( $chaine = "" )
{
if ( _DATABASE_ERRCODE == 1 )
{
print "--------------------------------------\n<br>";
$tab_err = split("_", $this->xErrorCode);
print "Classe : ".$tab_err[0]."\n<br>";
print "Methode : ".$tab_err[1]."\n<br>";
print "Code de retour : ".$this->hhxTabExcept[$tab_err[1]][$tab_err[2]]."\n<br>";
if (( _DATABASE_VERBOSE == 1 ) || ( $tab_err[2] != 0 ))
{
print "\n<br>";
print "Etat de l\'objet\n<br>";
print "\$dbh = ".$this->dbh."\n<br>";
print "\$dbuser = ".$this->dbuser."\n<br>";
print "\$dbpass = ".$this->dbpass."\n<br>";
print "\$dbhost = ".$this->dbhost."\n<br>";
print "\$dbname = ".$this->dbname."\n<br>";
print "\$dbport = ".$this->dbport."\n<br>";
print "\$iRequestNbRows = ".$this->iRequestNbRows."\n<br>";
print "\$vRequestResult = ".$this->vRequestResult."\n<br>";
}
if ( $chaine != "" )
{
print "\n<br>";
print "Informations supplementaires :\n<br>";
print $chaine."\n<br>" ;
}
print "--------------------------------------\n<br>";
}
}
##
#
# EndSection = Methodes de debugage
#
# ###########################################################################
#
}
?>
<?
# database.inc.php
# Classe développée par Philippe GAULTIER
# ph.gaultier@wanadoo.fr
# peut être redistribué et utilisé sous le mode de license GPL
#
# classe database : PHP4 ou +
# wrapper permettant de choisir le type de base
if (isset($database_type))
{
if (!isset($class_path))
{
$class_path = "./";
}
switch($database_type)
{
case "mysql" :
case "mysql_p" :
case "oci8" :
case "oci8_p" :
$database_set = 1;
break ;
default :
$database_set = 0;
break ;
}
if ($database_set)
{
include_once($class_path . $database_type .".inc.php");
}
}
?>
<?
# Fichier de test
# développée par Philippe GAULTIER
# ph.gaultier@wanadoo.fr
# peut être redistribué et utilisé sous le mode de license GPL
#
## DEBUT Variables d'application
$class_path = "./class/";
$database_type = "mysql_p";
## FIN Variables d'application
## DEBUT Gestion database
include_once($class_path."database.inc.php");
$oDatabase = new database("devel","devel","localhost","devel",3306);
$oDatabase->connect();
## FIN Gestion database
## DEBUT Gestion table users
include_once($class_path."table.inc.php");
$arrayLoginFields = array(
'login_id' => array('type' => 'number', 'null' => 0, 'pkey' => 1, 'search' => 0),
'login_login' => array('type' => 'string', 'null' => 0, 'pkey' => 0, 'search' => 1),
'login_password' => array('type' => 'string', 'null' => 0, 'pkey' => 0, 'search' => 0)
);
$oUsers = new table($oDatabase, "login", $arrayLoginFields);
$oUsers->select(3);
## FIN Gestion table users
?>
<!doctype html public "-//W3C//DTD HTML 4.0 //EN">
<html>
<head>
<title>Test</title>
</head>
<body>
<h2>User<? print 4; ?></h2>
login_login : <? print $oUsers->get("login_login"); ?><br>
login_password : <? print $oUsers->get("login_password"); ?><br>
<?
$oUsers->set("login_login", "luxa");
$oUsers->set("login_password", "axul");
// $oUsers->insert();
var_dump($oUsers->search("login_login","x","login_login","DESC"));
?>
</body>
</html>
<?
$oDatabase->disconnect();
?>