Location: PHPKode > projects > as_admintool > asadmintool/as_admintool_sqlqry.php
<?
/**
* @package as_admintool
* @desc as_admintool_sqlqry.php - "SQL query" plugin for as_admintool.php
* @author Alexander Selifonov <as-hide@address.com>
* @copyright Alexander Selifonov 2007
* @link http://as-works.narod.ru/en/php/
* @version 1.001.021
* modified 06.05.2007 (dd.mm.yyyy)
================================================================================
*/

require_once('as_dbutils.php'); // DB access wrapper class

define('ASADM_SQLQUERY','sqlqry'); // ID type for this module
define('ASADM_MAXRECORDS',200); // SQL query: maximal records returned
define('ASADM_QRYPARAM',4); // number of parameter fields shown for SQL query

define('ASADM_SQRYWHEIGHT',100); // SQL query textarea height (px)

# mandatory string - registering plugin
# example: $as_admt_plugins['unique_id'] = array('html-drawing_func','executing_func');
CAsAdminTool::RegisterPlugin('sqlqry','ASAdmt_sql_Form','ASAdmt_sql_exec');

// interface localization here !
if(empty($as_iface['predef-qry'])) $as_iface['predef-qry'] ='pre-defined queries...';
if(empty($as_iface['execqry'])) $as_iface['execqry'] ='Execute Query';
if(empty($as_iface['explainqry'])) $as_iface['explainqry'] ='Explain Query';
if(empty($as_iface['qryresult'])) $as_iface['qryresult'] ='Execution result';

if(empty($as_iface['msg_qrydone'])) $as_iface['msg_qrydone'] ='Query executed';
if(empty($as_iface['msg_qryerror'])) $as_iface['msg_qryerror'] ='Query error';

# $as_adm_qryaccess : SQL runing access:
# 0 - only pre-defined queries allowed (sqltext field is hidden), no 'explain query' button
# 1 - user can write queries, but only reading data, UPDATING queries will be denied
# 2 - full access, all queries executed
if(!isset($as_adm_qryaccess)) $as_adm_qryaccess = 0; 

# ASAdmt_sql_Form - function for drawing "SQL query" screen page
# first par $pginfo is array: [0] - pageid, [1],[2]-max page size (width,height)
function ASAdmt_sql_Form($pginfo,$qrylist='',$par2=false,$par3=false) {
  global $as_iface, $as_cssclass, $as_adm_qryaccess;
  $pageid=isset($pginfo[0])? $pginfo[0]: 0;
  $lwidth = isset($pginfo[1])? $pginfo[1]: 800;
  $lheight = isset($pginfo[2])? $pginfo[2]: 600;
  $rest_h = $lheight - 115;
  $self = $_SERVER['PHP_SELF'];
  static $sql_js_drawn = false;
  $dbname = is_string($par2)? $par2 : '';
  if(!$sql_js_drawn) { #<3> draw only once !!!
    $sql_js_drawn = true;
     ?>
<script language='javascript'>
var as_admt_stdsqls = []; // array for predefined queries
var as_admt_subpars = [];
function SqlQry_ChangeStdQry(pageid,obj) {
  var fm = document.getElementById('as_admt_sqlform_'+pageid);
  var iqry = obj.selectedIndex;
  if(iqry <=0) fm.sqltext.value = 'select * from';
  else fm.sqltext.value = as_admt_stdsqls[pageid][iqry][0];
  for(ik=1; ik<=<?= ASADM_QRYPARAM ?>; ik++) {
    document.getElementById('sqprm_'+pageid+'_'+ik).innerHTML= ((iqry<=0 || as_admt_stdsqls[pageid][iqry][ik]==undefined)? ('&amp;P'+ik) : as_admt_stdsqls[pageid][iqry][ik]);
  }
  if(typeof(as_admt_subpars[pageid][iqry])!='undefined') { fm.subpars.value=as_admt_subpars[pageid][iqry]; }
}
var ajax_sqlqrybusy = false;
function Admt_RunSqlQry(pageid,bexplain) {
  if (ajax_sqlqrybusy) return;
  fm = document.getElementById('as_admt_sqlform_'+pageid);
  if(fm.sqltext=='') { alert('empty query !'); return false; }
  var xmlreq = NewXMLHttpRequest();
  if(!xmlreq) return false;
  ajax_sqlqrybusy = true;
  xmlreq.onreadystatechange= function() { //<3>
    if (xmlreq.readyState == 4) { //<3A>
//      alert(xmlreq.responseText); //debug
      var spl = xmlreq.responseText.split("{|}");
      delete xmlreq;
      ajax_sqlqrybusy = false;
      if(spl.length < 2) {
        document.getElementById('sqlresult_'+pageid).innerHTML ='<?=$as_iface['msg_wrongreply']?> '+spl[0];
      }
      else {
        document.getElementById('sqlresult_'+pageid).innerHTML = spl[1];
      } //<4>
    } //<3A>
  } //<3>

  xmlreq.open('POST','<?=$self?>',true);
  xmlreq.setRequestHeader("Content-Type", postcont);
  params = 'adm_action_type=sqlqry&pageid=' + pageid + '&'+ComputeParamString('as_admt_sqlform_'+pageid);
  if(bexplain==1) params += '&b_explain=1';
  xmlreq.send(params);
  document.getElementById('sqlresult_'+pageid).innerHTML = '<?=$as_iface['msg_waiting']?>';
  return false;
}  

</script>
<?
  } #<3>
  $stdsqls = array();
  if(is_array($qrylist)) $stdsqls = $qrylist;
  elseif(is_file($qrylist)) {
    $tlst = file($qrylist);
    foreach($tlst as $strk) { 
      $tval = explode('|',trim($strk));
      // if(!empty($tval[1])) 
      $stdsqls[] = $tval; //[0] = $tval[1];
    }
  }
  if(count($stdsqls)>0) {
   echo "<script language='javascript'>\n as_admt_stdsqls[$pageid] = [];\n as_admt_subpars[$pageid] = [];\n";
   $km=1;
   for($kk=0; $kk<count($stdsqls); $kk++) { 
     $key = $stdsqls[$kk][0];
     $subpars = '';
     if(strlen($stdsqls[$kk][1])>1) { 
       $allval = "\"{$stdsqls[$kk][1]}\"";
       for($nn=2;$nn<=ASADM_QRYPARAM+1;$nn++) {
         if(isset($stdsqls[$kk][$nn]) ) {
           if($stdsqls[$kk][$nn][0]==='#') $subpars .= ($subpars===''? '':'|').$stdsqls[$kk][$nn];
           else $allval .= ",\"{$stdsqls[$kk][$nn]}\"";
         }
       }
       echo " as_admt_stdsqls[$pageid][$km] = [$allval];\n as_admt_subpars[$pageid][$km] = \"$subpars\";\n"; 
       $km++; 
     }
   }
   echo "</script>\n";
  }
?>
<table id='asadt_tbl_<?=$pageid?>'>
<tr><form name='as_admt_sqlform_<?=$pageid?>'><input type='hidden' name='subpars' value='' />
<!--td><?=$as_iface['parameters']?> :</td></tr-->
<tr>
 <?
  if(!empty($dbname)) echo "<input type=hidden name='_dbname_' value='$dbname'>";
  if(count($stdsqls)>0) {
    $rest_h -=40;
    echo "<tr><td colspan=4>{$as_iface['predef-qry']}<br><SELECT name='stdqry' style='width:200' onChange='SqlQry_ChangeStdQry($pageid,this)'>
   <OPTION value='0'>{$as_iface['predef-qry']}</OPTION>";
   for($kk=0; $kk<count($stdsqls); $kk++) /* as $kname=>$kval)*/ {
     $kname = $stdsqls[$kk][0];
     echo ( (strlen($stdsqls[$kk][1])>1)? "<OPTION value='$kname'>$kname</OPTION>" : "<OPTGROUP label='$kname'>");
   }
   echo "</SELECT></td></tr>";
  }                                                                                                                           
  for($kkp=1; $kkp<=ASADM_QRYPARAM; $kkp++) { 
    if($kkp>4 && ($kkp % 5 ==1)) { $rest_h -=36; echo "</tr><tr>"; } // NN parameter per line 
    echo "<td><span id='sqprm_{$pageid}_{$kkp}'> &amp;P{$kkp}</span><br><input type='TEXT' name='qparm{$kkp}' class='{$as_cssclass['textfield']}' style='width:120'></td>\n";
  }
 
  $attrib = ($as_adm_qryaccess>=1)? '':'READONLY';
  // with $as_adm_qryaccess=0 user won't even see SQL query text - just parameter fields
  if($as_adm_qryaccess>0) {
    $rest_h -=ASADM_SQRYWHEIGHT;
    $qryfield = "<tr><td><textarea name='sqltext' class='ibox' style='width:100%; Height=".ASADM_SQRYWHEIGHT."' {$attrib}>select * from</textarea></td></tr>";
  }
  else {
    $qryfield = "<input type='hidden' name='sqltext' value=''>";
  }
?> 
</tr></table>
<div align=center>
<table width='98%'>
<?=$qryfield?>
<tr><td><button class='button' name='runsql' onClick='Admt_RunSqlQry(<?=$pageid?>)'><?=$as_iface['execqry']?></button> 
<?
  if($as_adm_qryaccess>=1) { ?>
&nbsp; <button class='button' name='expsql' onClick='Admt_RunSqlQry(<?=$pageid?>,1)'><?=$as_iface['explainqry']?></button>
<? } ?>
</td></tr>
<tr><td><?=$as_iface['qryresult']?></td></tr></form>
<tr><td><div id='sqlresult_<?=$pageid?>' class='<?=$as_cssclass['resultarea']?>' style='overflow:auto; height:<?=$rest_h?>px; width:<?=$lwidth-20?>px;'>&nbsp;</div></td></tr>
</tr>
</table>
</div>
<?
}

// # ASAdmt_sql_exec - function for executing query and returning result through AJAX
 function ASAdmt_sql_exec($parms) {
    global $as_iface,$as_cssclass, $as_adm_qryaccess, $as_dbengine;
    $pageid = isset($parms['pageid'])? $parms['pageid'] : '1';
    if($as_adm_qryaccess<2) {
       $qarr = explode(' ',trim($parms['sqltext']));
       $first = strtolower($qarr[0]);
       if(!in_array($first, array('select','show','desc','describe','explain'))) { return "$pageid{|}UPDATES NOT ALLOWED ! ($first - operator denied or unknown)"; }
    }
    $subpars = empty($parms['subpars'])? '': explode('|',$parms['subpars']); // additional parameters: "href columns" etc.
    $s_from = array();
    $s_to = array();
    $dbname = isset($parms['_dbname_']) ? $parms['_dbname_']: '';
    if(strlen($dbname)) { $seldb = $as_dbengine->select_db($dbname); }
    for($kk=1 ; $kk<=ASADM_QRYPARAM; $kk++) { if(isset($parms['qparm'.$kk])) { $s_from[] = '&P'.$kk; $s_to[]=$parms['qparm'.$kk]; } }
    $sqry = isset($parms['sqltext'])? $parms['sqltext'] : '';
    $sqry = str_replace($s_from, $s_to, $sqry);
    $sqry = trim(stripslashes($sqry));
    if(empty($sqry)) { return $ret; }
    $ret = "$pageid{|}"; # <table _width='900px' border=0 cellspacing=0 cellpadding=0><tr class='head' style='text-align:left'><td><b>$sqry</b></td></tr></table>\n";
    if(empty($parms['b_explain'])) $result = $as_dbengine->sql_query($sqry);
    else $result = $as_dbengine->sql_explain($sqry);
    if($result)
    { //<2>
     if(is_resource($result)) { //<3> // show result recordset
        $ret .="<table border=0 cellspacing=1 >\n";
        $header = 0;
        $ii=0;
        while (($row = $as_dbengine->fetch_assoc($result)) && (ASADM_MAXRECORDS==0 || $ii<=ASADM_MAXRECORDS))
        { //<4>
           $values = array_values($row); // I'll need index-based values for HREF column composing
           if($header < 1)
           { //<5>
              $header = 1;
              $ret .="<tr>"; // class='{$as_cssclass['trowhead']}'
              foreach($row as $col_name=>$col_value) {
                if(is_string($col_name)) $ret .="<td class='{$as_cssclass['tdhead']}'>$col_name</td>";
              }
              if(is_array($subpars)) for($ipar=0;$ipar<count($subpars);$ipar++) $ret.="<td class='{$as_cssclass['tdhead']}'>&nbsp;</td>";
              $ret .='</tr>';
              reset($row); // back to first element !
           } //<5>
           $ii++;
           $classname = ($ii % 2) ? $as_cssclass['trowodd']:$as_cssclass['troweven'];
           $ret .= "\n<tr class='$classname'>";
           foreach($row as $col_name=>$col_value)
             if(is_string($col_name)) $ret .= "<td>$col_value</td>";
             
             if(is_array($subpars)) for($ipar=0;$ipar<count($subpars);$ipar++) {
                 $onepar = explode('^',$subpars[$ipar]);
                 $colvalue = '';
                 switch($onepar[0]) {
                   case '#HREF': $colvalue=@str_replace('{ID}',$values[$onepar[1]],$onepar[2]); break;
                   default: $colvalue=$onepar[0]; break;
                 }
                 $ret .="<td>$colvalue</td>";
             }
             
           $ret .='</tr>';
        } //<4>
        $as_dbengine->free_result($result);
        $ret .="</table></div>\n";
     }//<3>
     else
       $ret .= $as_iface['msg_qrydone'];
    }//<2>
    else $ret .= $as_iface['msg_qryerror'].' :'.$as_dbengine->sql_error();

    return $ret;
}

?>
Return current item: as_admintool