diff options
Diffstat (limited to 'admin/Library/Db/Mysqli.php')
| -rw-r--r-- | admin/Library/Db/Mysqli.php | 400 |
1 files changed, 400 insertions, 0 deletions
diff --git a/admin/Library/Db/Mysqli.php b/admin/Library/Db/Mysqli.php new file mode 100644 index 0000000..4e4c528 --- /dev/null +++ b/admin/Library/Db/Mysqli.php @@ -0,0 +1,400 @@ +<?php + +// All EasyGiant code is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. +// See COPYRIGHT.txt and LICENSE.txt. + +if (!defined('EG')) die('Direct access not allowed!'); + +//class to manage the database +//singleton! +class Db_Mysqli +{ + + public $query = null; //the last query executed + public $charsetError = true; //true: non eccor occurred during the modification of the connection charset, false: one error occurred + public $charset = null; //the charset of the client connection + + private static $instance = null; //instance of this class + + private $db; + private $fieldsType = array('tinyint','smallint','int','mediumint','bigint','float','double'); + + //PHP-Mysql charset translation table + private $charsetTranslationTable = array( + 'UTF-8' => 'utf8', + 'ISO-8859-1' => 'latin1', + 'EUC-JP' => 'ujis', + 'SJIS' => 'sjis' + ); + + /** + + *connect to the database + *'host','user','password','db_name' + + */ + + private function __construct($host,$user,$pwd,$db_name) + { + + $this->db = new mysqli($host,$user,$pwd,$db_name); + + if (mysqli_connect_error()) + { + die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error()); + } + + $charset = array_key_exists(DEFAULT_CHARSET,$this->charsetTranslationTable) ? $this->charsetTranslationTable[DEFAULT_CHARSET] : 'utf8'; + + if (!@$this->db->set_charset($charset)) $this->charsetError = false; + + $this->charset = $this->db->character_set_name(); + + } + + //return the $this->db property + public function getDb() + { + return $this->db; + } + + public static function getInstance($host = null, $user = null, $pwd = null, $db_name = null) + { + if (!isset(self::$instance)) { + $className = __CLASS__; + self::$instance = new $className($host,$user,$pwd,$db_name); + } + + return self::$instance; + } + + + //close the connection + public function disconnect() + { + $this->db->close(); + } + + //the text of the error message from previous MySQL operation + public function getError() + { + return $this->db->error; + } + + //the numerical value of the error message from previous MySQL operation + public function getErrno() + { + return $this->db->errno; + } + + public function createSelectQuery($table,$fields='*',$where=null,$group_by=null,$order_by=null,$limit=null,$on=null,$using=null) + { + if (isset($where)) + { + $where='WHERE '.$where; + } + if (isset($using)) + { + $using ='USING ('.$using.')'; + $on = null; + } + if (isset($on) and !isset($using)) + { + $on='ON '.$on; + } + if (isset($order_by)) { + $order_by='ORDER BY '.$order_by; + } + if (isset($group_by)) { + $group_by='GROUP BY '.$group_by; + } + if (isset($limit)) { + $limit='LIMIT '.$limit; + } + + $query="SELECT $fields FROM $table $on $using $where $group_by $order_by $limit;"; + return $query; + } + + public function get_num_rows($table,$where=null,$group_by=null,$on=null,$using=null) { + + $query = $this->createSelectQuery($table,'*',$where,$group_by,null,null,$on,$using); + + $this->query = $query; + $ris = $this->db->query($query); + if ($ris) { + $num_rows = $ris->num_rows; + $ris->close(); + return $num_rows; + } else { + return false; + } + } + + public function getMath($func,$table,$field,$where=null,$group_by = null, $on=null,$using=null) + { + $query = $this->createSelectQuery($table,"$func($field) AS m",$where,$group_by,null,null,$on,$using); + + $this->query = $query; + $result = $this->db->query($query); + if ($result) + { + $row = $result->fetch_array(); + $result->close(); + return $row['m']; + } + else + { + return false; + } + } + + //get the maximum value of the field $field of the table $table having the $where conditions + public function getMax($table,$field,$where=null,$group_by = null,$on=null,$using=null) + { + return $this->getMath('MAX',$table,$field,$where,$group_by,$on,$using); + } + + //get the minimum value of the field $field of the table $table having the $where conditions + public function getMin($table,$field,$where=null,$group_by = null,$on=null,$using=null) + { + return $this->getMath('MIN',$table,$field,$where,$group_by,$on,$using); + } + + //get the sum of the fields + public function getSum($table,$field,$where=null,$group_by = null,$on=null,$using=null) + { + return $this->getMath('SUM',$table,$field,$where,$group_by,$on,$using); + } + + //get the average of the fields + public function getAvg($table,$field,$where=null,$group_by = null,$on=null,$using=null) + { + return $this->getMath('AVG',$table,$field,$where,$group_by,$on,$using); + } + + public function select($table,$fields='*',$where=null,$group_by=null,$order_by=null,$limit=null,$on=null,$using=null) + { + $query = $this->createSelectQuery($table,$fields,$where,$group_by,$order_by,$limit,$on,$using); + + $this->query = $query; + $result = $this->db->query($query); + return $this->getData($result); + } + + +// public function select($table,$fields='*',$where=null,$group_by=null,$order_by=null,$limit=null) { +// $query = $this->selectQuery($table,$fields,$where,$group_by,$order_by,$limit); +// return $this->getData($query); +// } + + + //obtain an associative array containing the result values (keys:tableName_fieldsName) + //$par = 'single/multi' single table,multi table + public function getData($result) { + $data = array(); //data from the query + $temp = array(); //temporary array (values of a single record) +// $result = $this->db->query($query); + if ($result) { + $fieldsNumber = $result->field_count; + while ($row = $result->fetch_array()) { + for ($i = 0;$i < $fieldsNumber;$i++) { + $finfo = $result->fetch_field_direct($i); + $tableName = $finfo->table; + if (strcmp($tableName,'') === 0) $tableName = Params::$aggregateKey; + $fieldName = $finfo->name; + $temp[$tableName][$fieldName] = $row[$i]; + } + array_push($data,$temp); + } + $result->close(); + return $data; + } else { + return false; + } + } + + //return an array containing all the types of the fields (indicated in $fields) of a table (indicated in $table) + public function getTypes($table, $fields) + { + $query = "DESCRIBE $table;"; + $result = $this->db->query($query); + $temp = array(); + while ($row = $result->fetch_assoc()) { + $temp[$row['Field']] = reset(explode('(',$row['Type'])); + } + $result->close(); + + $types = array(); + $fields = explode(',',$fields); + for ($i = 0; $i < count($fields); $i++) + { + if (!array_key_exists($fields[$i],$temp)) return false; + $types[] = $temp[$fields[$i]]; + } + + return $types; + } + + public function insert($table,$fields,$values) { + + #$table is a string + #$fields has to be a string with comma as separator: name1,name2,... + #$values has to be an array + $values = array_values($values); + if (strcmp($fields,'') !== 0) + { + //get the type of the fields + $types = $this->getTypes($table,$fields); + if (!$types) return false; + + for($i = 0; $i < count($values); $i++) + { + if (!in_array($types[$i],$this->fieldsType)) + { + $values[$i] = '"'.$values[$i].'"'; + } + else + { + if (strcmp($values[$i],'') === 0) $values[$i] = '"'.$values[$i].'"'; + } + } + + $values = implode(',',$values); + $query="INSERT INTO $table ($fields) VALUES ($values);"; + $this->query=$query; + + $ris = $this->db->query($query); + + #check the result + if ($ris) { + return true; + } else { + return false; + } + + } else { + return false; + } + } + + // Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT). + public function lastId() + { + return $this->db->insert_id; + } + + public function update($table,$fields,$values,$where) { + + #$table and $where are two strings + #$fields has to be a string with comma as separator: name1,name2,... + #$values has to be an array + $values = array_values($values); +// if (isset($where)) { + $where='WHERE '.$where; +// } + #get the array from the $fields string + if (strcmp($fields,'') !== 0) + { + //get the type of the fields + $types = $this->getTypes($table,$fields); + if (!$types) return false; + + $fields = explode(',',$fields); + $str = array(); + + for ($i=0;$i<count($fields);$i++) { + if (!in_array($types[$i],$this->fieldsType)) + { + $values[$i] = '"'.$values[$i].'"'; + } + else + { + if (strcmp($values[$i],'') === 0) $values[$i] = '"'.$values[$i].'"'; + } + $str[$i]= $fields[$i].'='.$values[$i]; + } + + #set the string name1=value1,name2=... + $str=implode(',',$str); + $query="UPDATE $table SET $str $where;"; + $this->query=$query; + $ris = $this->db->query($query); + + #check the result + if ($ris) { + return true; + } else { + return false; + } + } else { + return false; + } + + } + + + public function del($table,$where) { + + #$table and $where are two strings +// if (isset($where)) { + $where='WHERE '.$where; +// } + $query="DELETE FROM $table $where;"; + $this->query=$query; + $ris = $this->db->query($query); + #check the result + + if ($ris) { + return true; + } else { + return false; + } + + } + + + //function to check if exist the record having the field $id_name=$id_value + public function recordExists($table,$fieldName,$fieldValue,$where = null,$groupBy=null,$on=null,$using=null) + { + if (isset($where)) + { + $where=' AND '.$where; + } + + $fieldValue = '"'.$fieldValue.'"'; + + $num = $this->get_num_rows($table,$fieldName.'='.$fieldValue.$where,$groupBy,$on,$using); + $res=($num>0) ? true : false; + return $res; + + } + + + //send a generic query to the database + //$query: the query to be sent + public function query($query) + { + $this->query = $query; + $result = $this->db->query($query); + if ($result === true) + { + return true; + } + else if ($result === false) + { + return false; + } + else if ($result instanceof MySQLi_Result) + { + return $this->getData($result); + } + } + + // Prevent users to clone the instance + public function __clone() + { + throw new Exception('error in '. __METHOD__.': clone is not allowed'); + } + +}
\ No newline at end of file |