Source for file db.class.php
Documentation is available at db.class.php
* @package Template Engine
public $req_index = -
1; // index the results in progress
public $db =
array(); // object which contains the connection informations
protected $req =
array(); // storage of results
protected $cons_query =
array(); // the dislocated request is stored here
protected $url =
''; // contient les variables
protected $url_var =
''; // contains the parameters of the url
protected $UrlRgxPatterns =
''; // contains the regex for formatting the urls
protected $T_first =
0; // which used to determinate the id
// DataBase ******************************************************************************************************************* @author H2LSOFT */
* this method allows you to connect to your MySQL database.
* @param string $password
* @param boolean $new_connection
public function dbConnect($db_type =
'', $host =
'', $login =
'', $password =
'', $base =
'', $port =
'', $new_connection =
'')
// initialisation of variable
if(empty($db_type))$db_type =
TPLN_DB_TYPE_DEFAULT;
if(empty($host))$host =
TPLN_DB_HOST_DEFAULT;
if(empty($login))$login =
TPLN_DB_LOGIN_DEFAULT;
if(empty($password))$password =
TPLN_DB_PASSWORD_DEFAULT;
if(empty($base))$base =
TPLN_DB_BASE_DEFAULT;
if(empty($port))$port =
TPLN_DB_PORT;
if(empty($new_connection))$new_connection =
TPLN_DB_NEW_CONNECTION;
if(!empty($port))$this->db[$this->db_index]->port =
$port;
$persistant =
($new_connection) ?
false :
true;
$this->db[$this->db_index] =
@new PDO("$db_type:host=$host;dbname=$base;port=$port", $login, $password, array(PDO::ATTR_PERSISTENT =>
$new_connection));
$this->dbError(0, $e->getMessage());
if(TPLN_DB_INIT_QUERIES !=
'')
$t =
explode(';', TPLN_DB_INIT_QUERIES);
* this method allows to change connection.
if($db_index <
0 ||
$db_index >=
count($this->db))
* return db connection object to share with framework
if($db_index ==
'')$db_index =
$this->db_index;
return $this->db[$db_index];
* @see DbSetConnectionId().
* method allows you to change connection.
$this->setConnectionId($db_index);
* this method allows to change connection.
if($req_index <
0 ||
$req_index >=
count($this->req))
$this->dbError('2.11', $req_index);
* This method allows to change queries resultset.
* This method allows to retriece the index of the current query.
* This method allows you to retriece the index of the current query.
return $this->getQueryId();
* method allows you to close database connection.
foreach($value as $key =>
$val)
// return addslashes($value);
return strtr($value, array("\x00" =>
'\x00', "\n" =>
'\n', "\r" =>
'\r', '\\' =>
'\\\\', "'" =>
"\'", '"' =>
'\"', "\x1a" =>
'\x1a'));
// return mysql_escape_string($value);
//return ((!get_magic_quotes_runtime() && !get_magic_quotes_gpc()) ? mysql_real_escape_string($value) : $value);
foreach($value as $key =>
$val)
* This method returns the maximum value for a ID, it is useful to know the value of an id after an insertion in a table.
public function getMaxID($table, $ID=
'ID')
$sql =
"SELECT MAX($ID) FROM $table";
* This method returns the maximum value for a ID, it is useful to know the value of an id after an insertion in a table.
* @param string $ID default ID
return $this->getMaxId($table, $ID);
* this method allows to execute database query.
* If second parameter is filled, the array structure for data is different
public function doQuery($sql, $FETCH_MODE=
PDO::FETCH_BOTH)
$msg =
"{
$msgs[2]} (code #{
$msgs[1]})
";
$msg .=
" <br><br>\n<pre><i><strong>`".
$sql.
'`</strong></i></pre>';
$this->dbError(2, $msg , TPLN_SQL_QUERY_DEBUG);
* This method allows to generate and execute insert query from an associative array.
* You can exlude some fields from your insert query
* @param array $exlude_fields
* @param bool $return_last_id
public function dbInsert($table, $arr, $exlude_fields=
array(), $return_last_id=
false)
foreach($arr as $key =>
$val)
foreach($exlude_fields as $ef)
//if(eregi("^$str", $key))
if(!in_array($key, $exlude_fields) &&
!$joker)
$sql =
"INSERT INTO $table\n ";
$sql .=
"\t(\n\t\t".
join(",\n\t\t", $fields).
"\n\t)\n";
for($i=
0; $i <
count($vals); $i++
)
if($i <
count($vals)-
1)$s .=
',';
return $this->dbGetMaxId($table, 'ID');
* This method allows you to generate and execute update query from an associative array.
* You can exlude some fields from your update query
* @param array $exlude_fields
public function dbUpdate($table, $arr, $where=
'', $exlude_fields=
array())
foreach($arr as $key =>
$val)
foreach($exlude_fields as $ef)
//if(eregi("^$str", $key))
if(!in_array($key, $exlude_fields) &&
!$joker)
$sql =
"UPDATE $table SET\n";
for($i=
0; $i <
count($vals); $i++
)
if($i <
count($vals)-
1)$s .=
',';
$sql .=
" \t\t{$fields[$i]} = $s\n";
$where =
"WHERE\n\t\t".
$where;
* This method allows to generate and execute REPALCE query from an associative array.
* You can exlude some fields from your update query, use * character to exclude a name generically mce*
* will exclude all name in the array that begins with mce.
* @param array $exlude_fields
public function dbReplace($table, $arr, $exlude_fields=
array())
foreach($arr as $key =>
$val)
foreach($exlude_fields as $ef)
//if(eregi("^$str", $key))
if(!in_array($key, $exlude_fields) &&
!$joker)
$sql =
"REPLACE $table SET\n";
for($i=
0; $i <
count($vals); $i++
)
if($i <
count($vals)-
1)$s .=
',';
$sql .=
" \t\t{$fields[$i]} = $s\n";
$where = "WHERE\n\t\t".$where;
* This method allows you to execute a query SELECT with parameters,
* each parameter is protected. This method uses like the php function vsprintf
* This method allows to know if the database connection is active
* This method allows you to generate and execute delete query from an associative array.
public function dbDelete($table, $where=
'')
$where =
'WHERE '.
$where;
$sql =
"DELETE FROM $table $where";
* This method returns the last SQL error
* this method allows to return database query results count.
* this method allows to return database query results count.
* allows to obtain in structured array form database query results.
* It is different in the array created which has the fields as keys in the array.
* The array depends by DoQuery() parameter by default associative array is created.
$row =
$this->req[$this->req_index]->fetch(PDO::FETCH_ASSOC);
* method allows to obtain as array form database query results.
* allows to obtain in structured array form database query results.
* It is different in the array created which has the fields as keys in the array.
$row =
$this->req[$this->req_index]->fetch(PDO::FETCH_ASSOC);
* allows to release memory allocated for your server.
* For example, you can to use this method after long treatments.
* This method allows to get your recorset in one time
* This method allows to get your recorset in one time
* method allows to return the first result of a database query.
* This method is useful for a count database query for exemple.
* this method allows to return the database query fields number.
$fields_count =
$this->req[$this->req_index]->columnCount();
* allows you to return the database query fields number
* this method allows to return the database query fields name.
* this method allows to return the database query fields name.
* allows to return the field name from your database query.
$row =
$this->DBFetchAssoc();
// $row = $this->req[$this->req_index]->fetchColumn();
* allows to return the field name from the database query.
* this method allows to obtain the database names of the server.
* this method allows to obtain the database names of the server.
return $this->getDBList();
* this method allows to obtain arrays name of the database.
* this method allows to obtain arrays name of the database.
// constuction the request
// encodage de la requete
// $query = str_replace("\n",' ',$query);
// $query = str_replace("\r",' ',$query);
/*if(count($this->cons_query) > 0 || is_array($this->user_resulset)) return;
$this->cons_query['STRING'] = $query;
// on parcours la requete � l'envers ;-)'
if(preg_match("/LIMIT(.*)/msi", $query, $match))
$this->cons_query['LIMIT'] = trim($match[1]);
$query = str_replace($match[0], '', $query); // on remplace ds la chaine
if(preg_match("/ORDER BY(.*)/msi", $query, $match))
$this->cons_query['ORDER BY'] = trim($match[1]);
$query = str_replace($match[0], '', $query); // on remplace ds la chaine
// y a t il une clause speciale ? order by filter ?
if(isset($_GET['torder_by']))
$this->cons_query['ORDER BY'] = "{$_GET['torder_by']} {$_GET['tsens']}, ".$this->cons_query['ORDER BY'];
if(isset($_GET['torder_by']))
$this->cons_query['ORDER BY'] = "{$_GET['torder_by']} {$_GET['tsens']}";
if(preg_match("/GROUP BY(.*)/msi", $query, $match))
$this->cons_query['GROUP BY'] = trim($match[1]);
$query = str_replace($match[0], '', $query); // on remplace ds la chaine
if(preg_match("/WHERE(.*)/msi", $query, $match))
$this->cons_query['WHERE'] = trim($match[1]);
$query = str_replace($match[0], '', $query); // on remplace ds la chaine
if(!preg_match("/FROM(.*)/msi", $query, $match))$this->_DBError(4);
$this->cons_query['FROM'] = trim($match[1]);
$query = str_replace($match[0], '', $query); // on remplace ds la chaine
if(!preg_match("/SELECT(.*)/msi", $query, $match))$this->_DBError(3);
$this->cons_query['SELECT'] = trim($match[1]);
$query = str_replace($match[0], '', $query); // on remplace ds la chaine
// recreate a request with the new parameters of LIMIT.
// calcul du nombre de pages
// on determine debut du limit
if(!is_array($this->user_resulset))
// on reconstruit la requete
$query .= $this->cons_query['SELECT']." \n";
$query .= $this->cons_query['FROM']." \n";
if(!empty($this->cons_query['WHERE']))
$query .= ' WHERE '." \n";
$query .= $this->cons_query['WHERE']." \n";
if(!empty($this->cons_query['GROUP BY']))
$query .= ' GROUP BY '." \n";
$query .= $this->cons_query['GROUP BY']." \n";
if(!empty($this->cons_query['ORDER BY']))
$query .= ' ORDER BY '." \n";;
$query .= $this->cons_query['ORDER BY']." \n";
// if($this->First == 0){$this->First = 1;}
if(!is_array($this->user_resulset))
$query .= " LIMIT $this->First,$this->NbRecordPerPage";
if(!is_array($this->user_resulset))
* return the correct formatted url
$url =
$this->Url.
"tpg=$t_pg".
$this->url_var;
* apply rewrite rule in URL
* @param string $patterns
* asign color alternation
// prise totale des r�sulats
// on regarde s'il y a un distinct
$d = trim($this->cons_query['SELECT']);
if(strpos($d, 'DISTINCT') === false)
// on capture le champs qui possede le distinct
list($f) = explode(',', $this->cons_query['SELECT']);
list($f) = explode(' AS ', $f);
$query .= $this->cons_query['FROM'];
if(!empty($this->cons_query['WHERE']))
$query .= $this->cons_query['WHERE'];
$this->DoQuery($query) or die($this->DBGetError());
// group by sans clause distinct
if(eregi('group by', $query) && !$distinct)
$this->Count = $this->getRowsCount();
$this->Count = $this->GetOne();
$this->Url =
$_SERVER['PHP_SELF'].
'?';
if($this->itemExists('_Count', 'data'))
$this->parse('data._Count', $this->Count);
if($this->itemExists('_First', 'data'))
$this->Parse('data._First', $this->First);
if($this->itemExists('_Last', 'data'))
$this->parse('data._Last', $this->Last);
if($this->itemExists('_PageCount', 'data'))
$this->parse('data._PageCount', $this->PageCount);
if($this->itemExists('_PageNumber', 'data'))
$this->parse('data._PageNumber', $this->PageNumber);
$tpg =
(isset
($_GET['tpg'])) ?
$_GET['tpg'] :
1;
if($_GET['torder_by'] ==
$f)
$sens =
($_GET['tsens'] ==
'asc') ?
'desc' :
'asc';
$u =
str_replace("tsens={$_GET['tsens']}", "tsens=$sens", $u);
$img =
"<a href=\"$u\"><img src=\"$this->OrderByImgsPath/order_{$_GET['tsens']}_actived.gif\" style=\"border:0;\" align=\"absmiddle\" /></a>";
$u =
str_replace("torder_by={$_GET['torder_by']}&tsens={$_GET['tsens']}", "torder_by=$f&tsens=desc", $u);
$img =
"<a href=\"$u\"><img src=\"$this->OrderByImgsPath/order_desc.gif\" style=\"border:0;\" /></a>";
//$u = str_replace("torder_by={$_GET['torder_by']}&tsens={$_GET['tsens']}", "torder_by=$f&tsens=asc", $u);
//$img .= "<a href=\"$u\"><img src=\"$this->OrderByImgsPath/order_asc.gif\" style=\"border:0;\" /></a>";
$this->parse("data._order_by::$f", $img);
if($this->blocExists('previous'))
if(!$this->itemExists('_Url', 'previous'))
$this->error('1.1', $this->f[$this->f_no]['name'], 'previous', '_Url');
if($this->PageNumber <=
$this->PageCount /*&& $this->PageCount != 1 && $this->PageNumber != 1* @author H2LSOFT */)
if($prev_pg <
1)$prev_pg =
1;
$this->parse('data.previous._Url', $url);
$this->eraseBloc('data.previous');
if($this->blocExists('next'))
if(!$this->itemExists('_Url', 'next'))
$this->error('1.1', $this->f[$this->f_no]['name'], 'next', '_Url');
/*if($this->PageNumber < $this->PageCount /*&& $this->PageCount != 1)
* @author H2LSOFT */ $next_pg =
$this->PageNumber +
1;
$this->parse('data.next._Url', $url);
$this->EraseBloc('data.next');
if($this->blocExists('start'))
if(!$this->itemExists('_Url', 'start'))
$this->error('1.1', $this->f[$this->f_no]['name'], 'start', '_Url');
//if($this->PageCount > 1)
$this->parse('data.start._Url', $url);
//$this->EraseBloc('data.start');
if($this->blocExists('end'))
if(!$this->itemExists('_Url', 'end'))
$this->error('1.1', $this->f[$this->f_no]['name'], 'end', '_Url');
//if($this->PageCount > 1)
$this->parse('data.end._Url', $url);
//$this->EraseBloc('data.end');
if($this->blocExists('pager'))
if(!$this->blocExists('out'))
$this->error(2, $this->f[$this->f_no]['name'], 'out');
if(!$this->itemExists('_Url', 'out'))
$this->error('1.1', $this->f[$this->f_no]['name'], 'out', '_Url');
if(!$this->itemExists('_Page', 'out'))
$this->error('1.1', $this->f[$this->f_no]['name'], 'out', '_Page');
if(!$this->blocExists('in'))
$this->error(2, $this->f[$this->f_no]['name'], 'in');
if(!$this->itemExists('_Page', 'in'))
$this->error('1.1', $this->f[$this->f_no]['name'], 'in', '_Page');
$in =
$this->getBlocInFile('data.pager.in');
$out =
$this->getBlocInFile('data.pager.out');
// $this->eraseBloc("in");
// $this->eraseBloc("out");
$this->parseBloc('data.pager', $str);
$this->eraseBloc('data.pager');
// initialize the navigation
* this method allows to create field variables
for($i =
0;$i <
count($path);$i++
)
$this->error('13', $this->f[$this->f_no]['name'], $arr[0]);
$lastbloc =
$arr[count($arr)-
1];
// Verification du mot clefs _Field
if(!$this->itemExists('_Field', $lastbloc) &&
!$this->itemExists('_FieldLabel', $lastbloc))
$this->error('1.1', $this->f[$this->f_no]['name'], $lastbloc, '_Field');
$cur_bloc_ini =
$this->getBlocInFile($lastbloc);
for($i =
0;$i <
count($fields);$i++
)
// on remplace le mot clef field
$str =
str_replace('{_Field}', '{'.
$fields[$i].
'}', $cur_bloc_ini);
// on remplace le contenu du champs
// $this->ParseBloc($path, $all);
$this->parseBloc($lastbloc, $all);
// on retire le dernier du path
// on veut que le chemin des peres
$path =
$this->getFathers($path, 'ARRAY', 0);
$path =
join($path, '.');
$this->reloadBlocVars($path);
* This method will manage your filter automatically.
* You have to put a preformatted variable in your template {_order_by::my_field}.
if(!isset
($_GET['torder_by']) ||
!in_array($_GET['torder_by'], $fields))
$_GET['torder_by'] =
$fields[0];
if(!isset
($_GET['tsens']) ||
!in_array($_GET['tsens'], array('asc','desc')))
$this->url_var .=
"&torder_by={$_GET['torder_by']}&tsens={$_GET['tsens']}";
* allows to parse automatically the datas from your databasse,
* you have only to create your template and this method make all the tasks for you!
* @param int $nb_result_per_page
* @param string $func_data
public function showRecords($query, $nb_result_per_page =
0, $func_data =
'')
// check the second parameter
if(!is_int($nb_result_per_page))
if($nb_result_per_page <
0)
// check the results array
$this->breaker_name =
(!$this->blocExists('breaker')) ?
'' :
'breaker';
$this->PageNumber =
$_GET['tpg']; // variable on parameter
// $this->setQuery(''); // reconstruit la requete et assigne les variables
// $this->_GetTotalCount(); // nb total d'enregistrements
//$res = $this->db[$this->db_index]->Execute($query);
// $this->Count = $res->numRows();
// patch query by laurent hayoun ******************************
// assign dynamic count values for large table
// $query_tmp = str_replace("\n", ' ', $query_tmp);
// replace subqueries bu _TPLN_REP_ID_
$query_tmp2 =
$query_tmp;
$query_tmp2 =
str_replace(' ) ) ', ') ) ', $query_tmp2);
//preg_match_all("#\(SELECT(.*)\)#msU", $query_tmp, $matches);
for($i=
0; $i <
count($matches[0]); $i++
)
$reps_pattern[] =
'_TPLN_REP_'.
$i.
'_';
$reps_rep[] =
$matches[0][$i];
$query_tmp2 =
str_replace($reps_rep, $reps_pattern, $query_tmp2);
if(preg_match("/LIMIT(.*)/s", $query_tmp2, $match))
$str =
str_replace($reps_pattern, $reps_rep, $match[1]);
$query_tmp2 =
str_replace($match[0], '', $query_tmp2); // delete string
if(preg_match("/ORDER BY(.*)/s", $query_tmp2, $match))
$str =
str_replace($reps_pattern, $reps_rep, $match[1]);
$query_tmp2 =
str_replace($match[0], '', $query_tmp2); // delete string
if(isset
($_GET['torder_by']))
if(isset
($_GET['torder_by']))
$this->cons_query['ORDER BY'] =
"{
$_GET['torder_by']} $t_sens";
if(preg_match("/GROUP BY(.*)/s", $query_tmp2, $match))
$str =
str_replace($reps_pattern, $reps_rep, $match[1]);
$query_tmp2 =
str_replace($match[0], '', $query_tmp2); // delete string
if(preg_match("/WHERE(.*)/s", $query_tmp2, $match))
$str =
str_replace($reps_pattern, $reps_rep, $match[1]);
$query_tmp2 =
str_replace($match[0], '', $query_tmp2); // delete string
if(preg_match("/FROM(.*)/s", $query_tmp2, $match))
$str =
str_replace($reps_pattern, $reps_rep, $match[1]);
$query_tmp2 =
str_replace($match[0], '', $query_tmp2); // delete string
if(preg_match("/SELECT(.*)/s", $query_tmp2, $match))
$str =
str_replace($reps_pattern, $reps_rep, $match[1]);
$query_tmp2 =
str_replace($match[0], '', $query_tmp2); // delete string
// hack prevent SQL_CALC_NUM_ROWS
if(strpos($query, 'SQL_CALC_FOUND_ROWS') !==
false)
$sql_tmp =
'SELECT FOUND_ROWS()';
// end of patch query by laurent hayoun ***********************
// y a t il une clause speciale ? order by filter ?
$query .=
" ,{$_GET['torder_by']} {$_GET['tsens']}";
$query .=
" ORDER BY {$_GET['torder_by']} {$_GET['tsens']}";
// $this->doQuery($query);
// $this->Count = $this->dbNumRows();
$tmp_nb_result_per_page =
$nb_result_per_page;
if($tmp_nb_result_per_page ==
0)$tmp_nb_result_per_page = -
1;
$limit_start =
($this->PageNumber-
1) *
$nb_result_per_page;
if($limit_start <
0)$limit_start =
0;
if($nb_result_per_page ==
0)
$query_limit =
$query.
" LIMIT ".
$limit_start.
', '.
$tmp_nb_result_per_page;
$this->setQuery(''); // rebuild the request and assign the variables
$no_records =
$this->getBlocInFile('data.norecord');
$this->eraseBloc('data.norecord');
$this->parseBloc('data', $no_records);
$sql_results =
$this->dbGetData(); // recupere les résultats
if($this->NbResults ==
0) // il y'en a pas pour une recherche
// to define for the research
$no_records =
$this->getBlocInFile('data.norecord');
$this->eraseBloc('data.norecord');
$this->parseBloc('data', $no_records);
else // if there is results then
$this->eraseBloc('data.norecord'); // erase norecord
$this->T_i =
0; // for the _NavColor
// while($row = $this->dbFetchAssoc())
foreach($sql_results as $row)
// take just the wished results
* @param string $func_data
// if(!empty($func_data))$row = $func_data($row);
if(strpos($func_data, '::') !==
false ||
strpos($func_data, '->') !==
false) // methode ?
eval
("\$row = $func_data(\$row);");
$keys =
@array_keys($row); // take the names of the keys
if($this->itemExists('_NavColor', 'loop'))
$this->parse('data.loop._NavColor', $color);
if($this->itemExists('_Id', 'loop'))
$this->parse('data.loop._Id', $this->T_id);
if(!is_int($key) &&
$this->itemExists($key, 'loop'))
$this->parse("data.loop.$key", $row[$key]);
if(!is_int($key) &&
$this->itemExists($key, 'breaker'))
$this->parse("data.loop.breaker.$key", $row[$key]);
$this->eraseBloc('data.loop.breaker');
// $this->loop('data.loop.breaker');
$this->loop('data.loop');
protected function dbError($err_no, $msg =
'', $exit =
1)
$err_msg =
$_err['DB']["$err_no"];
$this->error_msg =
"<B>TPLN DB Error $err_no:</B> <table border=1><tr><td>$err_msg</td></tr></table>";
$this->error_user_level =
E_USER_ERROR;
$this->outPutMessage($exit);
* this method allows to parse all the variables of a block by the database query returned values.
* The parse_function is Parse() method parameter, you could add php methods inside this method.
public function parseDbRow($bloc, $msg =
'', $func =
'')
$this->pathVerify($bloc);
$this->parseBloc($bloc, $msg);
$keys =
@array_keys($row); // prise du mon des clefs
if($this->itemExists($key, $bloc))
$this->parse("$bloc.$key", $row[$key], $func);
//$this->Parse("$bloc.$key", $row[$key]);
if($this->itemExists('_NavColor', $bloc))
$this->parse($bloc.
'._NavColor', $color);
* this method allows to parse all the variables of a block by the database query field returned values.
$this->pathVerify($bloc);
// simulate that we take an item
// $last_bloc = $this->_GetItem($bloc);
for($i =
0; $i <
count($fields); $i++
)
$field_name =
$fields[$i];
// if($this->ItemExists($field_name, $last_bloc))
if($this->itemExists('_Field', $bloc))
$this->parse("$bloc._Field", $field_name, $func);
//$this->Parse("$bloc._Field", $field_name);
if($this->itemExists('_NavColor', $bloc))
$this->parse($bloc.
'._NavColor', $color);
* This method allows to convert a date in mysql format:
* DD-MM-YYYY becomes YYYY-MM-DD DD-MM-YYYY HH:MM becomes YYYY-MM-DD HH:MM:SS
list
($d, $m, $Y) =
explode('/', $date);
list
($d, $m, $Y) =
explode('/', $date);
list
($H, $i) =
explode(':', $tmp[1]);
$new_date =
"$Y-$m-$d $H:$i:00";
* This method allows you to convert a date in mysql format to:
* YYYY-MM-DD becomes DD-MM-YYYY YYYY-MM-DD HH:MM:SS becomes DD-MM-YYYY HH:MM
if(empty($date) ||
$date ==
'0000-00-00' ||
$date ==
'0000-00-00 00:00:00')return '';
list
($Y, $m, $d) =
explode('-', $date);
list
($Y, $m, $d) =
explode('-', $date);
list
($H, $i) =
explode(':', $tmp[1]);
$new_date =
"$d/$m/$Y $H:$i";
* Enable protection mode for input data recommended true
* Protect data agains xss attack
foreach($array as $key =>
$val)
Documentation generated on Sat, 06 Mar 2010 21:33:47 +0100 by phpDocumentor 1.4.3