<?php
/*************************************************
morecache.php - easily display mysql query results with paging, tabular layout, etx
home page: www.samscripts.com/scripts/mysqltemplate
support: support.samscripts.com/index.php
licence: freeware / optionally giftware ( www.samscripts.com/donate.php )
author: Sam Yapp
date: 8th September 2002
documentation and examples at www.samscripts.com
*************************************************/
class mysqltemplate{
var $cache = 0;
var $queriesdone = 0;
var $queriescached = 0;
var $totalqueries = 0;
var $connection;
var $database;
var $username;
var $password;
var $host;
var $errors;
var $header;
var $footer;
var $template;
var $pagesize = 0;
var $currentpage = 1;
var $nextpage = 1;
var $prevpage = 1;
var $pagelist = "";
var $tableorder = "columns";
var $columns = 1;
var $connection = 0;
var $host = "";
var $user = "";
var $password = "";
var $database = "";
var $replace_object = "";
var $replace_function = "";
var $headervars = array();
var $footervars = array();
var $rowvars = array();
var $cachename = "";
var $cacheexpiry = 3600;
var $queries = array();
function setcallback($args){
if( !is_array($args) ){
$this->replace_object = 0;
$this->replace_function = $args;
}else{
$this->replace_object = &$args[0];
$this->replace_function = $args[1];
}
}
function mysqltemplate($connection = 0, $database = "", $host = "", $user = "", $password = ""){
$this->connection = $connection;
$this->database = $database;
$this->host = $host;
$this->user = $user;
$this->password = $password;
$this->settemplate();
}
function settemplate($template="", $resetfields = true){
if( $resetfields){
unset($this->replace_object);
$this->replace_object = "";
$this->replace_function = "";
$this->pagesize = 0;
$this->currentpage = 1;
$this->nextpage = 1;
$this->prevpage = 1;
$this->pagelist = "";
$this->tableorder = "columns";
$this->columns = 1;
$this->cachename = "";
}
$this->headervars = array();
$this->footervars = array();
$this->template =& $template;
$this->compiled = false;
$this->rows = array();
$this->rowcount = 0;
$this->cnt = 0;
$this->groupheader = "";
$this->groupfooter = "";
}
function getmicrotime(){
list($usec, $sec) = explode(" ",microtime());
return ((float)$usec + (float)$sec);
}
function connect(){
if( !$this->connection ){
if( isset($this->profiler) ) $this->profiler->profile("connect to database");
$start = $this->getmicrotime();
$this->connection = @mysql_connect($this->host, $this->user, $this->password)or die(mysql_error());
if( isset($this->profiler) ) $this->profiler->profile("finished connect to database");
$this->connecttime = $this->getmicrotime() - $start;
if( !$this->connection ){
return false;
}
if( isset($this->profiler) ) $this->profiler->profile("select database");
$selected =@mysql_select_db($this->database, $this->connection);
if( isset($this->profiler) ) $this->profiler->profile("finish select database");
$this->selectdbtime = $this->getmicrotime() - $start + $this->connecttime;
$this->connectselecttime = $this->selectdbtime + $this->connecttime;
if( !$selected ) return false;
}
return true;
}
function print_debug(){
echo '<table class="box" align="center"><tr><td><b>Query</b></td><td><b>Time Taken</b></td><td align="center"><b>From Cache?</b></td><td align="center"><b>Internal?</b></td></tr>';
echo '<tr><td>Connect To MySQL</td><td>'.$this->connecttime.'</td><td> </td><td> </td></tr>';
echo '<tr><td>Select Database</td><td>'.$this->selectdbtime.'</td><td> </td><td> </td></tr>';
echo '<tr><td>Connect and Select Database</td><td>'.$this->connectselecttime.'</td><td> </td><td> </td></tr>';
for( $i = 0; $i < $this->totalqueries; $i++){
$q = $this->queries[$i];
echo '<tr><td>'.htmlspecialchars($q["sql"]).'</td><td>'.$q["timetaken"].'</td><td align="center">'.($q["cached"] == 1 ? 'Yes' : 'No').'</td><td align="center">'.($q["internal"]==1 ? 'Yes' : 'No').'</td></tr>';
}
echo '</table>';
}
function mysql_query($sql, $internal=0){
if( !$this->connect() ) return false;
$this->queriesdone++;
$this->totalqueries++;
$this->queries[$this->totalqueries-1]["sql"] = $sql;
$this->queries[$this->totalqueries-1]["cached"] = 0;
$this->queries[$this->totalqueries-1]["internal"] = $internal;
$start = $this->getmicrotime();
if( isset($this->profiler) ) $this->profiler->profile("query: $sql");
$result = @mysql_query($sql, $this->connection);
if( isset($this->profiler) ) $this->profiler->profile("finished query: $sql");
$this->queries[$this->totalqueries-1]["timetaken"] = $this->getmicrotime()-$start;
return $result;
}
function query($sql){
if( $this->cache != 0 && $this->cachename != "" ){
$caching = 1;
$start = $this->getmicrotime();
if( isset($this->profiler) ) $this->profiler->profile("checking caching): $sql");
$update = $this->cache->cache($this->cachename, $this->cacheexpiry);
if( !$update ){
if( isset($this->profiler) ) $this->profiler->profile("query (cached): $sql");
$this->totalqueries++;
$this->queriescached++;
$this->queries[$this->totalqueries-1]["internal"] = 0;
$this->queries[$this->totalqueries-1]["sql"] = $sql;
$this->queries[$this->totalqueries-1]["cached"] = 1;
$this->queries[$this->totalqueries-1]["timetaken"] = $this->getmicrotime() - $start;
}
}else{
$caching = 0;
$update = true;
}
if( $update ){
$start = $this->getmicrotime();
if( $this->do_query(&$sql)){
if( $this->columns > 1 ){
$result = true;
$this->printquery_tabled();
}else{
$result = true;
$this->printquery();
}
}else{
$result = false;
}
if( $caching ){
$this->cache->stop();
}
}else{
return true;
}
return $result;
}
function printquery_tabled(){
$result =& $this->result;
$cols = $this->columns;
$method = preg_match("/vert/is", $this->tableorder) ? "vert" : "horiz";
$headervars =& array_merge(&$this->headervars, &$this->defaultvars);
$footervars =& array_merge(&$this->footervars, &$this->defaultvars);
$rowvars =& array_merge(&$this->rowvars, &$this->defaultvars);
$cnt = $this->numrows;
$this->printheader(array_keys($headervars), array_values($headervars));
$numrows = (int)(($cnt + ($cols-1))/ $cols);
$fullcols = $cnt % $cols;
if ($fullcols == 0 ) $fullcols = $cols;
$numxfull = $numrows * $fullcols;
$colsminusfullcols = $cols - $fullcols;
$i = 0;
for( $r = 0; $r < $numrows; $r++){
$rx = $r * $colsminusfullcols + $numxfull - $fullcols;
$this->printgroupheader("","");
for( $col = 0; $col < $cols; $col++){
if( $method != "horiz" ){
if( $col < $fullcols ){
$rownum = $r + ($col * ($numrows));
}else{
$rownum = $rx + $col;
}
if( $rownum < $cnt ){
mysql_data_seek($result,$rownum);
$row = mysql_fetch_assoc($result);
}else{
$row = array();
}
}else{
$row = mysql_fetch_assoc($result);
}
$i++;
if( $row ){
if( $this->replace_function != "" ){
$func = $this->replace_function;
if( $this->replace_object != "" ){
$obj = $this->replace_object;
$obj->$func(&$row);
}else{
$func(&$row);
}
// eval($repfunc.'($row);');
}
$row["rownumber"] = $i;
$this->printrow($row);
}else{
$this->printerror("emptycell", "", "");
}
}
$this->printgroupfooter("","");
}
$this->printfooter(array_keys($footervars), array_values($footervars));
}
function printquery(){
$headervars =& array_merge(&$this->headervars, &$this->defaultvars);
$footervars =& array_merge(&$this->footervars, &$this->defaultvars);
$rowvars =& array_merge(&$this->rowvars, &$this->defaultvars);
$this->printheader(array_keys($headervars), array_values($headervars));
$i = 0;
while( $row = mysql_fetch_assoc($this->result) ){
if( $this->replace_function != "" ){
$func = $this->replace_function;
if( $this->replace_object != "" ){
$obj = $this->replace_object;
$obj->$func(&$row);
}else{
$func(&$row);
}
// eval($repfunc.'($row);');
}
$i++;
$row["rownumber"] = $i;
$this->printrow(array_merge($rowvars, $row));
}
$this->printfooter(array_keys($footervars), array_values($footervars));
}
function printheader($search="", $replace=""){
if( is_array($search) ) $search = split(":::", "<:".join(">:::<:", $search).">");
// echo "search:",$search,"<br>replace:",$replace, "<br>header:",$this->header,"<br>";
echo str_replace(&$search, &$replace, &$this->header);
}
function printfooter($search="", $replace=""){
if( is_array($search) ) $search = split(":::", "<:".join(">:::<:", $search).">");
echo str_replace(&$search, &$replace, &$this->footer);
}
function printgroupheader($search, $replace){
if( is_array($search) &&count($search) ) $search = split(":::", "<:".join(">:::<:", $search).">");
echo str_replace(&$search, &$replace, &$this->groupheader[0]);
}
function printgroupfooter($search, $replace){
if( is_array($search) && count($search) ) $search = split(":::", "<:".join(">:::<:", $search).">");
echo str_replace(&$search, &$replace, &$this->groupfooter[0]);
}
function printerror($errorname, $search, $replace){
if( is_array($search) ) $search = split(":::", "<:".join(">:::<:", $search).">");
if( isset($this->errors[$errorname]) ) echo str_replace(&$search, &$replace, &$this->errors[$errorname]);
// echo join("<br>", $replace);
}
function printrow($row){
eval($this->rows[$this->cnt]);
$this->cnt++;
if( $this->cnt == $this->rowcount) $this->cnt = 0;
}
function converttemplate(&$t){
$tnew = 'echo "'.preg_replace('~<:[ ]*?([a-z0-9_]+)[ ]*.*?>~is', '".$row["$1"]."', str_replace('"', '\"',$t)).'";';
return $tnew;
}
function do_query(&$sql){
if( !$this->compiled ){
$this->splittemplate();
$this->compiletemplate();
}
if( !$this->connect() ) return $this->printerror("dberror", array("message", "sql"), array(mysql_error(), $sql));
if( $this->pagesize > 0 ){
if( preg_match("/group by (.*?) /is", $sql, $match) ){
$selectme = "COUNT(DISTINCT ".trim($match[1]).")";
}else{
$selectme = "COUNT(*)";
}
preg_match_all("/select (.*?) from (.*)/is", $sql, $matches);
$fieldstr = $matches[1][0];
$conditions = $matches[2][0];
$len = strlen($fieldstr);
$fields = array();
$brackets = 0;
$quotes = 0;
$field = "";
for( $i = 0; $i < $len; $i++){
$dontadd = false;
switch ($fieldstr[$i]){
case "'":
$quotes = 1 - $quotes;
break;
case "(":
$brackets++;
break;
case ")":
$brackets--;
break;
case ",":
if( !$quotes && $brackets == 0 ){
$fields[] = trim($field);
$field = "";
$dontadd = true;
}
break;
}
if( !$dontadd ) $field.=$fieldstr[$i];
}
if( trim($field) != "" ) $fields[] = trim($field);
for( $i = 0; $i < count($fields); $i++){
if( preg_match("/(.*) as ([\w\d_-]*)/is", $fields[$i], $matches) ){
$conditions = preg_replace("/[^\.]\b".$matches[2]."\b/is", $matches[1], $conditions);
}
}
$conditions = preg_replace("/ order by.*/is", "", $conditions);
$conditions = preg_replace("/ group by.*/is", "", $conditions);
$res = $this->mysql_query("SELECT $selectme FROM $conditions", 1);
if( !$res )return $this->printerror("dberror", array("message", "sql"), array(mysql_error(), $sql));
$this->rowtotal = mysql_result($res, 0,0);
$this->pagecount = (int)(($this->rowtotal + $this->pagesize - 1) / $this->pagesize);
if( $this->currentpage < 1 ) $this->currentpage = 1;
if( $this->currentpage > $this->pagecount) $this->currentpage = $this->pagecount;
$this->firstrow = $this->pagesize * ($this->currentpage - 1) + 1;
if( $this->firstrow > $this->rowtotal ) $this->firstrow = $this->rowtotal - $this->pagesize;
if( $this->firstrow < 0 ) $this->firstrow = 0;
$this->lastrow = $this->firstrow + $this->pagesize - 1;
if( $this->lastrow > $this->rowtotal ) $this->lastrow = $this->rowtotal;
$sql .= " LIMIT ".($this->firstrow-1).", ".$this->pagesize;
if( $this->currentpage == $this->pagecount ){
$this->nextpage = $this->pagecount;
$nextrep = "";
}else{
$this->nextpage = $this->currentpage+1;
$nextrep = '$1';
}
if( $this->currentpage == 1 ){
$this->prevpage = 1;
$prevrep = "";
}else{
$this->prevpage = $this->currentpage - 1;
$prevrep = '$1';
}
$pagelists = array();
for( $i = 1; $i <= $this->pagecount; $i++){
if( $i == $this->currentpage ){
$pagelists[$i-1] = str_replace("<:page>", $i, $this->pagelistthispageformat);
}else{
$pagelists[$i-1] = str_replace("<:page>", $i, $this->pagelistformat);
}
}
$this->pagelist = join("", $pagelists);
$this->header = preg_replace(array("~<:ifnext>(.*?)</:ifnext>~is","~<:ifprev>(.*?)</:ifprev>~"),
array($nextrep, $prevrep), $this->header);
$this->footer = preg_replace(array("~<:ifnext>(.*?)</:ifnext>~is","~<:ifprev>(.*?)</:ifprev>~"),
array($nextrep, $prevrep), $this->footer);
}
$this->result = $this->mysql_query($sql, 1);
if( $this->result ){
$this->numrows = mysql_num_rows($this->result);
if( $this->pagesize == 0 ){
$this->rowtotal = $this->lastrow = $this->numrows;
$this->firstrow = 1;
$this->pagecount = 1;
$this->currentpage = 1;
}
if( $this->numrows == 0 ){
$this->printerror("norecords", "", "");
return false;
}
$this->defaultvars = array("rowcount"=>$this->numrows, "totalrows"=>$this->rowtotal, "currentpage"=>$this->currentpage,
"pagesize"=>$this->pagesize, "pagecount"=>$this->pagecount, "firstrow"=>$this->firstrow,
"lastrow"=>$this->lastrow, "nextpage"=>$this->nextpage, "prevpage"=>$this->prevpage,"pagelist"=>$this->pagelist);
return true;
}else{
$this->printerror("dberror", array("message", "sql"), array(mysql_error(),$sql));
return false;
}
}
function compileheaders(){
$this->ghcompiled = array();
$this->gfcompiled = array();
for( $i = 0; $i < count($this->groupheader); $i++){
$this->ghcompiled[$i] = $this->converttemplate($this->groupheader[$i]);
}
for( $i = 0; $i < count($this->groupfooter); $i++){
$this->gfcompiled[$i] = $this->converttemplate($this->groupfooter[$i]);
}
$this->ghcount = count($this->ghcompiled);
$this->gfcount = count($this->gfcompiled);
}
function compiletemplate(){
$this->rows = array();
$found = preg_match_all('~<:row>(.*?)</:row>~is', $this->template, $temp);
if( $found ){
for( $i =0; $i < count($temp[1]); $i++){
$this->rows[] = $this->converttemplate($temp[1][$i]);
}
}else{
$this->rows[] = $this->converttemplate($this->template);
}
$this->rowcount = count($this->rows);
$this->compiled = true;
}
function splittemplate(){
$tags = array("pagelistformat", "header", "footer", "error", "groupheader", "groupfooter");
$tags = join("|", $tags);
$regex = "~<:($tags)[=]?([a-z0-9_]*)[ ]*>(.*?)</:\\1[ =]?\\2?[ ]*>[\n]?~is";
if( preg_match_all($regex, $this->template, $temps) ){
$out = array();
for( $i = 0; $i < count($temps[1]); $i++){
if( $temps[2][$i] == "" ){
$out[$temps[1][$i]][] = $temps[3][$i];
}else{
$out[$temps[1][$i]][$temps[2][$i]] = $temps[3][$i];
}
}
}
$this->template =& preg_replace($regex, "", &$this->template);
$this->errors = isset($out["error"]) ? $out["error"] : array();
$this->header = isset($out["header"][0]) ? $out["header"][0] : "";
$this->footer = isset($out["footer"][0]) ? $out["footer"][0] : "";
$this->groupheader = isset($out["groupheader"]) ? $out["groupheader"] : array("");
$this->groupfooter = isset($out["groupfooter"]) ? $out["groupfooter"] : array("");
$this->plformat = isset($out["pagelistformat"]) ? $out["pagelistformat"][0] : "";
global $REQUEST_URI, $HTTP_SERVER_VARS;
$query = isset($HTTP_SERVER_VARS["QUERY_STRING"]) ? $HTTP_SERVER_VARS["QUERY_STRING"] : "";
$query = preg_replace("/[&|?]*currentpage=[0-9]*/", "", $query);
$this->pagelistformat = '<a href="'.$REQUEST_URI.'?currentpage=<:page>&'.$query.'"><:page></a> ';
$this->pagelistthispageformat = "<b><:page></b> ";
if( $this->plformat != "" ){
if( preg_match("~<:thispage>(.*?)</:thispage>~is", $this->plformat, $match) ){
$this->pagelistthispageformat = $match[1];
$this->plformat = preg_replace("~<:thispage>.*</:thispage>~is", "", $this->plformat);
}
$this->pagelistformat = $this->plformat;
}
}
}
?>