Location: PHPKode > scripts > My simple Query Language Class for MySQL & MySQLi > mql.class.php
<?
/**
 *
 * My simple Query Language Class for MySQL & MySQLi(Improved)
 * @author Mustafa OZGUR
 * @link www.mitcore.com
 *
 * $Revision: 17041 $
 * $LastChangedBy: mitcore $
 * $LastChangedDate: 2009-01-27 02:02:05 +0200 (Tue, 27 Jan 2009) $
 *
 */


class MQL
{
  public $conf = NULL;

  public $cache_enabled = false;
  public $cache_dir     = '/tmp/indir.biz';
  public $cache_time    = 600;

  private $_mysql_conn_id = NULL;
  private $MYi            = false;

  function __construct($host, $user, $pass, $db, $charset = 'LATIN5')
  {
    $this->conf = array();
    $this->conf['host']     = $host;
    $this->conf['user']     = $user;
    $this->conf['pass']     = $pass;
    $this->conf['db']       = $db;
    $this->conf['charset']  = $charset;

    $this->conf['docroot']      = '';
    $this->conf['project_url']  = '';
    $this->conf['project_name'] = '';
    if(@function_exists('mysqli_connect'))
    {
      $this->MYi = true;
    }
    if(!$this->MYi)
    {
      if(@dl('mysqli.so'))
      {
        $this->MYi = true;
      }
    }

    if($this->MYi)
    {
      if(($this->_mysql_conn_id = mysqli_connect($this->conf['host'], $this->conf['user'], $this->conf['pass'], $this->conf['db'])) === false)
      {
        throw new Exception('Can not connect MySQL Server');
      }
    }
    else
    {
      if(($this->_mysql_conn_id = mysql_connect($this->conf['host'], $this->conf['user'], $this->conf['pass'])) === false)
      {
        throw new Exception('Can not connect MySQL Server');
      }
      else
      {
        if(mysql_select_db($db, $this->_mysql_conn_id) === false)
        {
          throw new Exception('Can not select DB:'.$this->conf['db']);
        }
      }
    }
    $this->query('SET NAMES "'.$this->conf['charset'].'"');
  }

  function __destruct()
  {
    $this->close();
  }

  /**
   * @example getInsert('table_name', 'field1=value1', 'field2=value2', ... 'fieldN=valueN');
   *
   * @return (int)mysql_insert_id
   */
  public function getInsert()
  {
    $arg_list   = func_get_args();
    $table_name = $arg_list[0];
    $field      = $arg_list[1];
    $where      = $arg_list[2];
    if(($ret = $this->getField($table_name, $field, $where)) == NULL)
    {
      $ret = $this->insert($table_name, $where);
    }
    return $ret;
  }

  /**
   * @example insert('table_name', 'field1=value1', 'field2=value2', ... 'fieldN=valueN');
   *
   * @return (bool)false | (int)mysql_insert_id
   */
  public function insert()
  {
    $arg_list   = func_get_args();
    $table_name = $arg_list[0];
    $sizeOf     = sizeof($arg_list);
    $ret        = false;
    if($sizeOf > 1 && $table_name != '')
    {
      $fields = array();
      $values = array();
      for ($i = 1; $i < $sizeOf; $i++)
      {
        $tmpArr = explode('=', $arg_list[$i], 2);
        $fields[] = $tmpArr[0];
        if(substr($tmpArr[1], -1) == '"' && substr($tmpArr[1], 0, 1) == '"')
        {
          $values[] = '"'.$this->escape_string(substr($tmpArr[1], 1, strlen($tmpArr[1]) - 2)).'"';
        }
        else
        {
          $values[] = $this->escape_string($tmpArr[1]);
        }
      }
      $sql = 'insert into '.$table_name.'('.implode(',', $fields).') values('.implode(',', $values).')';
      $ret = $this->query($sql);
    }
    return $ret;
  }

  public function query($sql, $showError = true)
  {
    $qry                = NULL;
    $retVal             = '';
    $sql                .= chr(0x00);
    $sql                = trim($sql);

    if($sql == '')
    {
      return 'EXIT';
    }

    if($this->MYi)
    {
      $qry = mysqli_query($this->_mysql_conn_id, $sql);
    }
    else
    {
      $qry = mysql_query($sql, $this->_mysql_conn_id);
    }
    return $qry;

    if($retVal != 'EXIT')
    {
      $arr    = array('select'=>$qry, 'insert'=>$this->insert_id(), 'update'=>$this->affected(), 'delete'=>$this->affected());
      $retVal = '';
      while (list($k, $v) = each($arr))
      {
        if(strlen(stristr(strtolower($sql), $k)) == strlen($sql))
        {
          $retVal = $v;
          break;
        }
      }

      if($retVal == '')
      {
        $retVal = $qry;
      }
    }
    elseif($showError == true)
    {
      $errorHtml = array();
      $dieBool = ob_end_clean();
      isBuggyIe() || ob_start('ob_gzhandler');
      if(!headers_sent()) header('Content-type: text/html; charset=UTF-8', true);
      $dbg = debug_backtrace();
      $errorHtml[] = '<div style="margin:0px;padding:5px;border:solid 2px #FF9933;background-color:#FFEEEE;font-size:12px;font-family:Tahoma, Arial, sans, _sans;">';
      $errorHtml[] = '<center><h3>MySQL Error occured</h3></center>';
      $errorHtml[] = '<b>'.$errno.'</b>: '.$error;
      $errorHtml[] = '<br>Query: '.$sql;
      $errorHtml[] = '<hr>';
      $errorHtml[] = 'error on file "'.str_replace($this->conf['docroot'], '<a href="'.$this->conf['project_url'].'">'.$this->conf['project_name'].'</a> &gt; ', $dbg[0]['file']).'" at line '.$dbg[0]['line'].'<br>';
      $errorHtml[] = '</div>';
      //print_r($dbg);
      if($dieBool == true)
      {
        die(implode('<br>', $errorHtml));
      }
      else
      {
        echo implode('<br>', $errorHtml);
      }
    }
    else
    {
      $retVal = 'EXIT';
    }

    return $retVal;
  }

  public function getField($table_name, $field, $where, $error = true)
  {
    $ret = $this->getArray($sql = 'select '.$field.' from '.$table_name.' where '.$where.' limit 0,1', '', $error);
//    decho($sql);
    if(sizeof($ret[0]) == 1)
    {
      $field = str_replace(' as ', '', stristr($field, ' as '));
      $retStr = $ret[0][$field];
    }
    else
    {
      $retStr = array_values((array)$ret[0]);
    }
    return $retStr;
  }

  public function getFieldA($table_name, $field = '*', $where = '', $error = true)
  {
    $ret = $this->getArray('select '.$field.' from '.$table_name.($where ? ' where '.$where : '').' limit 0,1', '', $error);
    if(sizeof($ret[0]) == 1)
    {
      $retStr = $ret[0][$field];
    }
    else
    {
      $retStr = (array)$ret[0];
    }
    return $retStr;
  }

  public function getAssoc($qry)
  {
    $retArr = array();
    $qry = $this->query($qry);
    while($arr = $this->fetch($qry))
    {
      $retArr[] = $arr;
    }
    return $retArr;
  }

  public function fetch($qry)
  {
    if ($qry != 'EXIT' && $qry)
    {
      if($this->MYi)
      {
        return @mysqli_fetch_assoc($qry);
      }
      else
      {
        return @mysql_fetch_assoc($qry);
      }
    }
    return false;
  }

  public function fetch_row($qry)
  {
    if ($qry)
    {
      if($this->MYi)
      {
        return @mysqli_fetch_row($qry);
      }
      else
      {
        return @mysql_fetch_row($qry);
      }
    }
    return false;
  }

  public function num_rows($result)
  {
    if ($result)
    {
      if($this->MYi)
      {
        return @mysqli_num_rows($result);
      }
      else
      {
        return @mysql_num_rows($result);
      }
    }
    return false;
  }

  public function getArray($sql, $key = '', $error = true)
  {
    $retarr = array();

    $filemtime          = 0;
    $cache_file         = '';
    if(strpos('1'.strtolower($sql), 'select') == '1')
    {

      if($this->cache_enabled)
      {
        $sql_cache_name = md5($sql.'|'.$key);
        $cache_file     = $this->cache_dir.'/'.$sql_cache_name[0].'/'.$sql_cache_name[1].'/'.$sql_cache_name[2].'/'.$sql_cache_name[3].'/'.$sql_cache_name;
        $filemtime      = file_exists($cache_file) ? (int)filemtime($cache_file) : 0;

        if(($this->cache_time < time() - $filemtime))
        {
          $query_from_cache = false;
        }
        else
        {
          $query_from_cache = true;
        }
      }
      else
      {
        $query_from_cache = false;
      }
    }

    if($query_from_cache)
    {
      $retarr = unserialize(file_get_contents($cache_file));
    }
    else
    {
      $qry = $this->query($sql, $error);
      while ($arr = $this->fetch($qry))
      {
        if($key == '')
        {
          $retarr[] = $arr;
        }
        else
        {
          $retarr[$arr[$key]] = $arr;
        }
      }
      if($cache_file)
      {
        if (!file_exists(dirname($cache_file)))
        {
          mkdir(dirname($cache_file), 0777, true);
        }
        file_put_contents($cache_file, serialize($retarr));
      }
    }
    return $retarr;
  }

  public function get($qry, $error = true)
  {
    return $this->fetch($this->query($qry, $error));
  }

  public function count($tableName, $whereClause = '')
  {
    $sql = 'select count(0) as ttl from '.$tableName.($whereClause != '' ? ' where '.$whereClause : '');
    $ret = $this->get($sql, false);
    return $ret['ttl'];
  }


  public function escape_string($string)
  {
    $string = nl2br($string);
    if(version_compare(phpversion(),"4.3.0") == "-1" && $this->MYi == false)
    {
      $string = mysql_escape_string($string);
    }
    else
    {
      if($this->MYi)
      {
        $string = mysqli_real_escape_string($this->_mysql_conn_id, $string);
      }
      else
      {
        $string = mysql_real_escape_string($string, $this->_mysql_conn_id);
      }
    }
    return $string;
  }

  public function unescape_string($string) {
    stripslashes($string);
    $string = str_replace('<br>', Chr(13), $string);
    return $string;
  }

  public function affected()
  {
    if($this->MYi)
    {
      return (int)@mysqli_affected_rows($this->_mysql_conn_id);
    }
    else
    {
      return (int)@mysql_affected_rows($this->_mysql_conn_id);
    }
  }

  public function insert_id()
  {
    if($this->MYi)
    {
      return (int)@mysqli_insert_id($this->_mysql_conn_id);
    }
    else
    {
      return (int)@mysql_insert_id($this->_mysql_conn_id);
    }
  }

  public function close()
  {
    if($this->MYi)
    {
      @mysqli_close($this->_mysql_conn_id);
    }
    else
    {
      @mysql_close($this->_mysql_conn_id);
    }
  }

  public function keepAlive()
  {
    if($this->MYi)
    {
      if(($ping = !mysqli_ping($this->_mysql_conn_id)))
      {
        @mysqli_close($this->_mysql_conn_id);
      }
    }
    else
    {
      if(($ping = !mysql_ping($this->_mysql_conn_id)))
      {
        @mysql_close($this->_mysql_conn_id);
      }
    }
    if($ping)
    {
      $this->__construct($this->conf['host'], $this->conf['user'], $this->conf['pass'], $this->conf['db'], $this->conf['charset']);
    }
  }
}
Return current item: My simple Query Language Class for MySQL & MySQLi