aboutsummaryrefslogtreecommitdiff
path: root/admin/Library/Db/Mysqli.php
blob: 4e4c528ec13201e459fcffd31fb0c71299eab9d4 (plain) (blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
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');
	}
	
}