perbaikan sql lib 112

<?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
        {
            $fields = $values = array();
            foreach ($data as $f => $v)
            {
                $fields[]= '`' . str_replace(':', '', str_replace("field", "", $f)) . '`';
                $values[]= "'" . addslashes($v)."'";
            }

            $field = implode(",", $fields );
            $value = implode(",", $values );
            $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 = "error:".$e->getMessage()."\nSql:".$query;
        }
    }
    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];
    }
}
?>