diff options
Diffstat (limited to 'h-source/Library/Db/Mysql.php')
| -rwxr-xr-x | h-source/Library/Db/Mysql.php | 394 |
1 files changed, 394 insertions, 0 deletions
diff --git a/h-source/Library/Db/Mysql.php b/h-source/Library/Db/Mysql.php new file mode 100755 index 0000000..8b96f44 --- /dev/null +++ b/h-source/Library/Db/Mysql.php @@ -0,0 +1,394 @@ +<?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_Mysql { + + 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 $dblink; + private $fieldsType = array('tinyint','smallint','int','mediumint','bigint','float','double'); + /** + + *connect to the database + *'host','user','password','db_name' + + */ + + //PHP-Mysql charset translation table + private $charsetTranslationTable = array( + 'UTF-8' => 'utf8', + 'ISO-8859-1' => 'latin1', + 'EUC-JP' => 'ujis', + 'SJIS' => 'sjis' + ); + + private function __construct($host,$user,$pwd,$db_name) + { + + $this->dblink = mysql_connect($host,$user,$pwd); + + if ($this->dblink === FALSE) { + die ("Connection error. Verify parameters in config.php"); + } + + $db2 = mysql_select_db($db_name, $this->dblink) + or die ("Database selection error. Verify parameters in config.php"); + + $charset = array_key_exists(DEFAULT_CHARSET,$this->charsetTranslationTable) ? $this->charsetTranslationTable[DEFAULT_CHARSET] : 'utf8'; + + if (!@mysql_set_charset($charset,$this->dblink)) $this->charsetError = false; + + $this->charset = mysql_client_encoding(); + } + + 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() + { + mysql_close($this->dblink); + } + + //the text of the error message from previous MySQL operation + public function getError() + { + return mysql_error($this->dblink); + } + + //the numerical value of the error message from previous MySQL operation + public function getErrno() + { + return mysql_errno($this->dblink); + } + + 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 = mysql_query($query); + if ($ris) { + $num_rows = mysql_num_rows($ris); + return $num_rows; + } else { + return false; + } + } + + //get the maximum value of the field $field of the table $table having the $where conditions + 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 = mysql_query($query); + if ($result) + { + $row = mysql_fetch_array($result); + 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 = mysql_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 = mysql_query($query); + if ($result) + { + $fieldsNumber = mysql_num_fields($result); + while ($row = mysql_fetch_array($result)) + { + for ($i = 0;$i < $fieldsNumber;$i++) + { + $tableName = mysql_field_table($result, $i); + if (strcmp($tableName,'') === 0) $tableName = Params::$aggregateKey; + $fieldName = mysql_field_name($result, $i); + $temp[$tableName][$fieldName] = $row[$i]; + } + array_push($data,$temp); + } + 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 = mysql_query($query); + $temp = array(); + while ($row = mysql_fetch_assoc($result)) { + $temp[$row['Field']] = reset(explode('(',$row['Type'])); + } + + $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 = mysql_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 mysql_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 = mysql_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 = mysql_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 = mysql_query($query); + if ($result === false) + { + return false; + } + else if ($result === true) + { + return true; + } + else if (@get_resource_type($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 |