<?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>&nbsp;</td><td>&nbsp;</td></tr>';
        echo 
'<tr><td>Select Database</td><td>'.$this->selectdbtime.'</td><td>&nbsp;</td><td>&nbsp;</td></tr>';
        echo 
'<tr><td>Connect and Select Database</td><td>'.$this->connectselecttime.'</td><td>&nbsp;</td><td>&nbsp;</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"] == 'Yes' 'No').'</td><td align="center">'.($q["internal"]=='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 != && $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 ){
                    
$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 == $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 ){
            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 $quotes;
                        break;
                    case 
"(":
                        
$brackets++;
                        break;
                    case 
")":
                        
$brackets--;
                        break;
                    case 
",":
                        if( !
$quotes && $brackets == ){
                            
$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($res0,0);
            
$this->pagecount = (int)(($this->rowtotal $this->pagesize 1) / $this->pagesize);
            if( 
$this->currentpage $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 $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 == ){
                
$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($sql1);
        if( 
$this->result ){
            
$this->numrows mysql_num_rows($this->result);
            if( 
$this->pagesize == ){
                
$this->rowtotal $this->lastrow $this->numrows;
                
$this->firstrow 1;
                
$this->pagecount 1;
                
$this->currentpage 1;
            }
            if( 
$this->numrows == ){
                
$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;
        }
    }

}

?>