classSQLPDO

<?php

    class SQL {
        protected $con;
        private $server;
        private $host = 'localhost';
        private $user = 'root';
        private $pass = '';
        private $debe = 'absen';
        public $success = null;
        public $message = null;
        var $attribute = array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_CASE => PDO::CASE_NATURAL,
        );

        function __construct($h = null, $u = null, $p = null, $d = null) {
            if($h == null) {
                $this->server = 'mysql:host='.$this->host.';dbname='.$this->debe;
            } else {
                $this->server = 'mysql:host='.$h.';dbname='.$d;
                $this->user = $u;
                $this->pass = $p;
            }

            try {
                $this->con = new PDO($this->server, $this->user, $this->pass, $this->attribute);
                $this->success = true;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function dbstatus() {
            if($this->con == null) {
                return $this->message;
            }
        }

        public function response() {
            if($this->success == false) {
                return $this->message;
            } else {
                return 1;
            }
        }

        public function close() {
            $this->con = null;
        }

        public function insertid() {
            return $this->con->lastInsertId();
        }
        /*
        if any string with `field` will be replace with ``
        */
        public function insertUPLOAD($table ,$data)
        {
            try {
                $field = $value = '';
                foreach($data as $f=>$v) {
                    $field .= ',`'.str_replace(':','',str_replace("field","",$v)).'`';
                    $value .= ','.$v;
                }
                $field = substr($field,1);
                $value = substr($value,1);
                $query = 'INSERT INTO '.$table.' ('.$field.') VALUES ('.$value.')';
                $st = $this->con->prepare($query);
                $st->execute($data);
                $this->success = true;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }
        public function insert($table, $data) {
            try {
                $field = $value = '';
                foreach($data as $f=>$v) {
                    $field .= ','.str_replace(':','',$v);
                    $value .= ','.$v;
                }
                $field = substr($field,1);
                $value = substr($value,1);
                $query = 'INSERT INTO '.$table.' ('.$field.') VALUES ('.$value.')';
                $st = $this->con->prepare($query);
                $st->execute($data);
                $this->success = true;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function update($table, $data, $where, $condition='AND') {
            try {
                $field = $value = '';
                foreach($data as $f=>$v) {
                    $field .= ','.str_replace(':','',$f).'='.$f;
                }
                foreach($where as $f=>$v) {
                    $value .= ' && '.str_replace(':','',$f).'='.$f;
                }
                $field = substr($field,1);
                $value = substr($value,4);
                $value = str_replace('&&',$condition,$value);
                $query = 'UPDATE '.$table.' SET '.$field.' WHERE '.$value;
                $st = $this->con->prepare($query);
                $st->execute(array_merge($data,$where));
                $this->success = true;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function delete($table, $where, $condition='AND') {
            try {
                $value = '';
                foreach($where as $f=>$v) {
                    $value .= ' && '.str_replace(':','',$f).'='.$f;
                }
                $value = substr($value,4);
                $value = str_replace('&&',$condition,$value);
                $query = 'DELETE FROM '.$table.' WHERE '.$value;
                $st = $this->con->prepare($query);
                $st->execute($where);
                $this->success = true;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function select($table, $data='*', $where=null, $group=null, $order=null, $limit=null, $condition='AND') {
            try {
                $output = array();
                $value = '';
                $query = 'SELECT '.$data.' FROM '.$table;
                if($where!=null) {
                    foreach($where as $f=>$v) {
                        $value .= ' && '.str_replace(':','',$f).'='.$f;
                    }
                    $value = ' WHERE '.substr($value,4);
                    $value = str_replace('&&',$condition,$value);
                    $query .= $value;
                }
                ($group!=null) ? $query .= ' GROUP BY '.$group :null;
                ($order!=null) ? $query .= ' ORDER BY '.$order :null;
                ($limit!=null) ? $query .= ' LIMIT '.$limit :null;
                $st = $this->con->prepare($query);
                $st->execute($where);
                $this->success = true;
                while($result = $st->fetch()) {
                    $output[] = $result;
                }
                $output = str_replace("'","&#39;",$output);
                return $output;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function selectAssoc($table, $data='*', $where=null, $group=null, $order=null, $limit=null, $condition='AND') {
            try {
                $output = array();
                $value = '';
                $query = 'SELECT '.$data.' FROM '.$table;
                if($where!=null) {
                    foreach($where as $f=>$v) {
                        $value .= ' && '.str_replace(':','',$f).'='.$f;
                    }
                    $value = ' WHERE '.substr($value,4);
                    $value = str_replace('&&',$condition,$value);
                    $query .= $value;
                }
                ($group!=null) ? $query .= ' GROUP BY '.$group :null;
                ($order!=null) ? $query .= ' ORDER BY '.$order :null;
                ($limit!=null) ? $query .= ' LIMIT '.$limit :null;
                $st = $this->con->prepare($query);
                $st->execute($where);
                $this->success = true;
                while($result = $st->fetch(PDO::FETCH_ASSOC)) {
                    $output[] = $result;
                }
                $output = str_replace("'","&#39;",$output);
                return $output;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function fetch($table, $data='*', $where=null, $group=null, $order=null, $limit=null, $condition='AND') {
            try {
                $value = '';
                $query = 'SELECT '.$data.' FROM '.$table;
                if($where!=null) {
                    foreach($where as $f=>$v) {
                        $value .= ' && '.str_replace(':','',$f).'='.$f;
                    }
                    $value = ' WHERE '.substr($value,4);
                    $value = str_replace('&&',$condition,$value);
                    $query .= $value;
                }
                ($group!=null) ? $query .= ' GROUP BY '.$group :null;
                ($order!=null) ? $query .= ' ORDER BY '.$order :null;
                ($limit!=null) ? $query .= ' LIMIT '.$limit :null;
                $st = $this->con->prepare($query);
                $st->execute($where);
                $output = $st->fetch();
                $this->success = true;
                if($output=='') {
                    $output = array();
                    $field = explode(',',$data);
                    $i = 0;
                    foreach($field as $fil) {
                        $output = array_merge($output,array($i=>''));
                        $output = array_merge($output,array($fil=>''));
                        $i++;
                    }
                } else {
                    $output = str_replace("'","&#39;",$output);
                }
                return $output;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function fetchAssoc($table, $data='*', $where=null, $group=null, $order=null, $limit=null, $condition='AND') {
            try {
                $value = '';
                $query = 'SELECT '.$data.' FROM '.$table;
                if($where!=null) {
                    foreach($where as $f=>$v) {
                        $value .= ' && '.str_replace(':','',$f).'='.$f;
                    }
                    $value = ' WHERE '.substr($value,4);
                    $value = str_replace('&&',$condition,$value);
                    $query .= $value;
                }
                ($group!=null) ? $query .= ' GROUP BY '.$group :null;
                ($order!=null) ? $query .= ' ORDER BY '.$order :null;
                ($limit!=null) ? $query .= ' LIMIT '.$limit :null;
                $st = $this->con->prepare($query);
                $st->execute($where);
                $output = $st->fetch(PDO::FETCH_ASSOC);
                $this->success = true;
                if($output=='') {
                    $output = array();
                    $field = explode(',',$data);
                    $i = 0;
                    foreach($field as $fil) {
                        $output = array_merge($output,array($i=>''));
                        $output = array_merge($output,array($fil=>''));
                        $i++;
                    }
                } else {
                    $output = str_replace("'","&#39;",$output);
                }
                return $output;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function fieldcount($table, $data='*', $where=null, $group=null, $order=null, $limit=null, $condition='AND') {
            try {
                $value = '';
                $query = 'SELECT COUNT('.$data.') FROM '.$table;
                if($where!=null) {
                    foreach($where as $f=>$v) {
                        $value .= ' && '.str_replace(':','',$f).'='.$f;
                    }
                    $value = ' WHERE '.substr($value,4);
                    $value = str_replace('&&',$condition,$value);
                    $query .= $value;
                }
                ($group!=null) ? $query .= ' GROUP BY '.$group :null;
                ($order!=null) ? $query .= ' ORDER BY '.$order :null;
                ($limit!=null) ? $query .= ' LIMIT '.$limit :null;
                $st = $this->con->prepare($query);
                $st->execute($where);
                $this->success = true;
                $output = $st->columnCount();
                return $output;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function rowcount($table, $data='*', $where=null, $group=null, $order=null, $limit=null, $condition='AND') {
            try {
                $value = '';
                $query = 'SELECT COUNT('.$data.') FROM '.$table;
                if($where!=null) {
                    foreach($where as $f=>$v) {
                        $value .= ' && '.str_replace(':','',$f).'='.$f;
                    }
                    $value = ' WHERE '.substr($value,4);
                    $value = str_replace('&&',$condition,$value);
                    $query .= $value;
                }
                ($group!=null) ? $query .= ' GROUP BY '.$group :null;
                ($order!=null) ? $query .= ' ORDER BY '.$order :null;
                ($limit!=null) ? $query .= ' LIMIT '.$limit :null;
                $st = $this->con->prepare($query);
                $st->execute($where);
                $this->success = true;
                $output = $st->fetchColumn(0);
                return $output;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function rowsum($table, $data='*', $where=null, $group=null, $order=null, $limit=null, $condition='AND') {
            try {
                $value = '';
                $query = 'SELECT SUM('.$data.') FROM '.$table;
                if($where!=null) {
                    foreach($where as $f=>$v) {
                        $value .= ' && '.str_replace(':','',$f).'='.$f;
                    }
                    $value = ' WHERE '.substr($value,4);
                    $value = str_replace('&&',$condition,$value);
                    $value = str_replace("!=","<>",$value);
                    $query .= $value;
                }
                ($group!=null) ? $query .= ' GROUP BY '.$group :null;
                ($order!=null) ? $query .= ' ORDER BY '.$order :null;
                ($limit!=null) ? $query .= ' LIMIT '.$limit :null;
                $st = $this->con->prepare($query);
                $st->execute($where);
                $this->success = true;
                $output = $st->fetchColumn(0);
                return $output;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function truncate($table) {
            try {
                $query = 'TRUNCATE '.$table;
                $st = $this->con->prepare($query);
                $st->execute();
                $this->success = true;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function query($query) {
            try {
                $st = $this->con->prepare($query);
                $st->execute();
                return $st->fetchAll();
                $this->success = true;
            }
            catch (PDOException $e) {
                $this->success = false;
                $this->message = $e->getMessage();
            }
        }

        public function dbsize() {
            $output = $this->query("SELECT sum( data_length + index_length ) AS dbsize FROM information_schema.TABLES WHERE table_schema='".$this->debe."'");
            return $output[0][0];
        }
    }
?>