Set of extra functions and utilities for medoo database framework (http://medoo.in)

class.psydb.php

<?php

include_once "medoo.min.php";   // path to original medoo framework

class psydb extends medoo {
    /*
     * usage:
     * rquery($query): raw query. returns a simple mysql resource
     * aquery($query): return a single line in one array
     * mquery($query): return all results in a inception (array in array)
     * rmrow($table,$column,$value): removes a row from table
     * newtable($table,$array): creates a table from column=>type formatted array
     * destroytable($table): delete a table
     * insert($table,array): append a line from column=>value formatted array
     * update($tablo, $array, $where, $value): update row from column=>value formatted array
     * 
     * enum_fields($table): returns field list as array 
     * q2table($query,$id): returns sql output as html table
     * duplicatecheck($table, $column): return duplicate entries
     * duplicateremove($table, $column): remove duplicate entries
     * applyuserfunc($table, $column, $func, $customquery=""):filter sql output with
     * a custom user function which returns boolean 
     * 
     */

    private $c;
    private $p;
    private $res;
    private $buf;
    private $opened = false;
    private $prefix = "";

    function __construct($details = []) {
        if (count($details) > 0) {
            $tmp = [
                'database_type' => $details['type'],
                'database_name' => $details['name'],
                'server' => $details['host'],
                'username' => $details['user'],
                'password' => $details['pass']
            ];

            if ($details['type'] == 'sqlite') {
                $tmp['database_file'] = $details['host'];
            }

            $this->prefix = $details['prefix'];
            $tmp['charset'] = $details['charset'];

            if (__MODE__ == 'cli') {
                kernel::loadextension('pdo_' . $details['type']);
            }

            parent::__construct($tmp);
            unset($tmp);
        }
    }

    function __destruct() {
        
    }

    public function rquery($sql) {
        try {
            $db = $this->query($sql);
            $db->setFetchMode(PDO::FETCH_ASSOC);
            return $db->fetchAll();
        } catch (Exception $ex) {
            throw new Exception($ex->getMessage());
        }
    }

    private function multiparse($res) {
        $cikti = [];
        while ($this->buf = $res->fetch()) {
            array_push($cikti, $this->buf);
        }
        return $cikti;
    }

    /*
      public function update($tablo, $array, $where, $value) {
      $toplam = count($array);
      $basliklar = array_keys($array);
      $bstr = "SET ";
      for ($i = 0; $i < $toplam - 1; $i++) {
      $bstr.=mysql_escape_string($basliklar[$i]) . '=' . "'" . mysql_escape_string($array[$basliklar[$i]]) . "'" . ', ';
      }

      $bstr.=mysql_escape_string($basliklar[$i]) . '=' . "'" . mysql_escape_string($array[$basliklar[$i]]) . "'";
      $cikti = $this->rquery("UPDATE " . $this->prefix . $tablo . " " . $bstr . " WHERE " . $where . "=" . "'" . $value . "'");
      return $cikti;
      }
     */

    /* basic read-write functions */

    public function rmrow($table, $column, $value) {
        return $this->rquery('DELETE FROM ' . $this->prefix . $table . ' WHERE ' . $column . '=' . chr(39) . $value . chr(39));
    }

    public function search($table, $field, $keyword) {
        return $this->rquery("SELECT * FROM " . $table . " WHERE " . $field . " LIKE %" . $keyword . "%");
    }

    public function aquery($query) {
        $data = $this->rquery($query);
        $cikti = [];
        if (count($data) > 1) {
            foreach ($data as $key => $value) {
                if (!is_int($key)) {
                    $cikti[$key] = $value;
                }
            }
        }
        return $cikti;
    }

    public function mquery($query) {
        $link = $this->rquery($query);
        return $this->multiparse($link);
    }

    public function insert($tablo, $array) {
        /* "column" => "value" formatli bir array'i tabloya basar
         * array icinde en az 2 baslik bulunmali!
         */
        $toplam = count($array);
        $basliklar = array_keys($array);

        $bstr = "";
        $vstr = "";
        for ($i = 0; $i < $toplam - 1; $i++) {
            $bstr.=mysql_escape_string($basliklar[$i]) . ', ';
            $vstr.="'" . mysql_escape_string($array[$basliklar[$i]]) . "', ";
        }

        $bstr.=mysql_escape_string($basliklar[$toplam - 1]);
        $vstr.="'" . mysql_escape_string($array[$basliklar[$toplam - 1]]) . "'";
        $cikti = $this->rquery("INSERT INTO " . $this->prefix . $tablo . " (" . $bstr . ") VALUES (" . $vstr . ");");
        return $cikti;
    }

    public function newtable($name, $array, $engine = 'InnoDB') {
        /* "title" => "type" formatli bir array'den tablo olusturur
         * array icinde en az 2 baslik bulunmali!
         */
        $sqlstr = "";

        foreach (array_keys($array) as $caption) {
            if ($array[$caption] == 'int' || 'integer') {
                $array[$caption] = 'int(11) DEFAULT NULL';
            }
            $sqlstr.=$caption . " " . $array[$caption] . ', ';
        }

        $cikti = $this->rquery("CREATE TABLE IF NOT EXISTS " . $this->prefix . $name . " (ID int AUTO_INCREMENT NOT NULL, " . $sqlstr . "PRIMARY KEY (ID)) ENGINE = " . $engine . " AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;");
        return $cikti;
    }

    public function cleartable($table) {
        return $this->rquery('TRUNCATE TABLE ' . $this->prefix . $table);
    }

    public function destroytable($table) {
        return $this->rquery('DROP TABLE ' . $this->prefix . $table);
    }

    /* Extra Stuff */

    public function q2table($query, $id = "") {
        $array = $this->mquery($query);
        if ($id == "") {
            $id = md5($query);
        }
        if (count($array) > 1) {
            $output = '<table id="' . $id . '" border="1"><thead><tr>';
            foreach (array_keys($array[0]) as $title) {
                $output .= '<th>' . $title . '</th>';
            }
            $output .= '</tr></thead><tbody>';
            foreach ($array as $inarray) {
                $output.='<tr>';
                foreach (array_keys($inarray) as $title) {
                    $output .= '<td>' . $inarray[$title] . '</td>';
                }
                $output.='</tr>';
            }
            $output.='</tbody></table>';
            return $output;
        } else {
            return "No Results";
        }
    }

    /* Maintance Tools */

    public function applyuserfunc($table, $column, $func, $customquery = "") {
        /*
         * string analiz eden bir kullanici fonksiyonunu
         * belirtilen tablodan gecirir
         * 
         * true yada false ceviren tek parametreli bir fonksiyon olmali
         * eger true cevirirse cikti verir
         * 
         * $column must be a text field!
         *  
         */
        if ($customquery != "") {
            $hede = $this->mquery($customquery);
        } else {
            $hede = $this->mquery("SELECT * FROM " . $this->prefix . $table);
        }
        $cikti = [];
        foreach ($hede as $entry) {
            $var = false;
            eval('$var=' . $func . "('" . addslashes($entry[$column]) . "');");
            if ($var == true) {      // burasi duruma göre modifiye edilebilir
                $cikti[] = $entry;     // cikti alani
            }
        }
        return $cikti;
    }

    public function duplicatecheck($table, $column) {
        $hede = $this->mquery("SELECT DISTINCT(" . $column . ") AS entry, COUNT(" . $column . ") AS entrycount FROM " . $this->prefix . $table . " GROUP BY " . $column . " HAVING entrycount > 1");
        return $hede;
    }

    public function duplicateremove($table, $column) {
        /*
         * returns deleted line count 
         */
        $hede = $this->mquery("SELECT DISTINCT(" . $column . ") AS entry, COUNT(" . $column . ") AS entrycount FROM " . $this->prefix . $table . " GROUP BY " . $column . " HAVING entrycount > 1");
        $i = 0;
        foreach ($hede as $entry) {
            $this->rquery("DELETE FROM " . $this->prefix . $table . " WHERE " . $column . "='" . addslashes($entry['entry']) . "' LIMIT " . ($entry['entrycount'] - 1) . ";");
            $i++;
        }
        return $i;
    }

    public function import_sql($file) {

        $delimiter = ';';
        $file = fopen($file, 'r');
        $isFirstRow = true;
        $isMultiLineComment = false;
        $sql = '';

        while (!feof($file)) {
            $row = fgets($file);

            // remove BOM for utf-8 encoded file
            if ($isFirstRow) {
                $row = preg_replace('/^\x{EF}\x{BB}\x{BF}/', '', $row);
                $isFirstRow = false;
            }

            // 1. ignore empty string and comment row
            if (trim($row) == '' || preg_match('/^\s*(#|--\s)/sUi', $row)) {
                continue;
            }

            // 2. clear comments
            $row = trim(clearSQLcomment($row, $isMultiLineComment));

            // 3. parse delimiter row
            if (preg_match('/^DELIMITER\s+[^ ]+/sUi', $row)) {
                $delimiter = preg_replace('/^DELIMITER\s+([^ ]+)$/sUi', '$1', $row);
                continue;
            }

            // 4. separate sql queries by delimiter
            $offset = 0;
            while (strpos($row, $delimiter, $offset) !== false) {
                $delimiterOffset = strpos($row, $delimiter, $offset);
                if (isQuoted($delimiterOffset, $row)) {
                    $offset = $delimiterOffset + strlen($delimiter);
                } else {
                    $sql = trim($sql . ' ' . trim(substr($row, 0, $delimiterOffset)));
                    $this->rquery($sql);

                    $row = substr($row, $delimiterOffset + strlen($delimiter));
                    $offset = 0;
                    $sql = '';
                }
            }
            $sql = trim($sql . ' ' . $row);
        }
        if (strlen($sql) > 0) {
            $this->rquery($row);
        }

        fclose($file);
        return true;
    }

}

?>