Project

General

Profile

1 4 ryan
<?php
2 1362 Luisehahne
/**
3
 *
4
 * @category        framework
5
 * @package         database
6
 * @author          WebsiteBaker Project
7
 * @copyright       2004-2009, Ryan Djurovich
8
 * @copyright       2009-2011, Website Baker Org. e.V.
9
 * @link            http://www.websitebaker2.org/
10
 * @license         http://www.gnu.org/licenses/gpl.html
11
 * @platform        WebsiteBaker 2.8.x
12 1374 Luisehahne
 * @requirements    PHP 5.2.2 and higher
13 1362 Luisehahne
 * @version         $Id$
14 1457 Luisehahne
 * @filesource      $HeadURL$
15
 * @lastmodified    $Date$
16 1362 Luisehahne
 *
17
 */
18 4 ryan
/*
19
Database class
20
This class will be used to interface between the database
21
and the Website Baker code
22
*/
23 1496 DarkViper
/* -------------------------------------------------------- */
24
// Must include code to stop this file being accessed directly
25 1499 DarkViper
if(!defined('WB_PATH')) {
26
	require_once(dirname(__FILE__).'/globalExceptionHandler.php');
27
	throw new IllegalFileException();
28
}
29 1496 DarkViper
/* -------------------------------------------------------- */
30 4 ryan
if(!defined('DB_URL')) {
31
	//define('DB_URL', DB_TYPE.'://'.DB_USERNAME.':'.DB_PASSWORD.'@'.DB_HOST.'/'.DB_NAME);
32
}
33
34
define('DATABASE_CLASS_LOADED', true);
35
36
class database {
37 1362 Luisehahne
38
	private $db_handle  = null; // readonly from outside
39
40
	private $connected  = false;
41
42
	private $error      = '';
43
	private $error_type = '';
44
	private $message    = array();
45
46
47 4 ryan
	// Set DB_URL
48
	function database($url = '') {
49
		// Connect to database
50
		$this->connect();
51
		// Check for database connection error
52
		if($this->is_error()) {
53
			die($this->get_error());
54
		}
55
	}
56
57
	// Connect to the database
58
	function connect() {
59
		$status = $this->db_handle = mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD);
60
		if(mysql_error()) {
61
			$this->connected = false;
62
			$this->error = mysql_error();
63
		} else {
64
			if(!mysql_select_db(DB_NAME)) {
65
				$this->connected = false;
66
				$this->error = mysql_error();
67
			} else {
68
				$this->connected = true;
69
			}
70
		}
71
		return $this->connected;
72
	}
73
74
	// Disconnect from the database
75
	function disconnect() {
76 95 stefan
		if($this->connected==true) {
77 4 ryan
			mysql_close();
78
			return true;
79
		} else {
80
			return false;
81
		}
82
	}
83
84
	// Run a query
85
	function query($statement) {
86
		$mysql = new mysql();
87
		$mysql->query($statement);
88 342 stefan
		$this->set_error($mysql->error());
89 4 ryan
		if($mysql->error()) {
90
			return null;
91
		} else {
92
			return $mysql;
93
		}
94
	}
95 1362 Luisehahne
96 4 ryan
	// Gets the first column of the first row
97 1362 Luisehahne
	function get_one( $statement )
98
	{
99
		$fetch_row = mysql_fetch_array(mysql_query($statement) );
100 4 ryan
		$result = $fetch_row[0];
101 342 stefan
		$this->set_error(mysql_error());
102 4 ryan
		if(mysql_error()) {
103
			return null;
104
		} else {
105
			return $result;
106
		}
107
	}
108
109
	// Set the DB error
110
	function set_error($message = null) {
111
		global $TABLE_DOES_NOT_EXIST, $TABLE_UNKNOWN;
112
		$this->error = $message;
113
		if(strpos($message, 'no such table')) {
114
			$this->error_type = $TABLE_DOES_NOT_EXIST;
115
		} else {
116
			$this->error_type = $TABLE_UNKNOWN;
117
		}
118
	}
119
120
	// Return true if there was an error
121
	function is_error() {
122
		return (!empty($this->error)) ? true : false;
123
	}
124
125
	// Return the error
126
	function get_error() {
127
		return $this->error;
128
	}
129
130 1362 Luisehahne
/*
131
 * default Getter
132
 */
133
	public function __get($var_name)
134
	{
135
		if($var_name == 'db_handle')
136
		{
137
			return $this->db_handle;
138
		}
139
		return null;
140
	}
141
142
/*
143
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
144
 * @param string $field_name: name of the field to seek for
145
 * @return bool: true if field exists
146
 */
147
	public function field_exists($table_name, $field_name)
148
	{
149
		$sql = 'DESCRIBE `'.$table_name.'` `'.$field_name.'` ';
150
		$query = $this->query($sql);
151
		return ($query->numRows() != 0);
152
	}
153
154
/*
155
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
156
 * @param string $index_name: name of the index to seek for
157
 * @return bool: true if field exists
158
 */
159
	public function index_exists($table_name, $index_name, $number_fields = 0)
160
	{
161
		$number_fields = intval($number_fields);
162
		$keys = 0;
163
		$sql = 'SHOW INDEX FROM `'.$table_name.'`';
164
		if( ($res_keys = $this->query($sql)) )
165
		{
166
			while(($rec_key = $res_keys->fetchRow()))
167
			{
168
				if( $rec_key['Key_name'] == $index_name )
169
				{
170
					$keys++;
171
				}
172
			}
173
174
		}
175
		if( $number_fields == 0 )
176
		{
177
			return ($keys != $number_fields);
178
		}else
179
		{
180
			return ($keys == $number_fields);
181
		}
182
	}
183
/*
184
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
185
 * @param string $field_name: name of the field to add
186
 * @param string $description: describes the new field like ( INT NOT NULL DEFAULT '0')
187
 * @return bool: true if successful, otherwise false and error will be set
188
 */
189
	public function field_add($table_name, $field_name, $description)
190
	{
191 1442 Luisehahne
		if( !$this->field_exists($table_name, $field_name) )
192 1362 Luisehahne
		{ // add new field into a table
193
			$sql = 'ALTER TABLE `'.$table_name.'` ADD '.$field_name.' '.$description.' ';
194
			$query = $this->query($sql);
195
			$this->set_error(mysql_error());
196
			if( !$this->is_error() )
197
			{
198 1442 Luisehahne
				return ( $this->field_exists($table_name, $field_name) ) ? true : false;
199 1362 Luisehahne
			}
200
		}else
201
		{
202
			$this->set_error('field \''.$field_name.'\' already exists');
203
		}
204
		return false;
205
	}
206
207
/*
208
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
209
 * @param string $field_name: name of the field to add
210
 * @param string $description: describes the new field like ( INT NOT NULL DEFAULT '0')
211
 * @return bool: true if successful, otherwise false and error will be set
212
 */
213
	public function field_modify($table_name, $field_name, $description)
214
	{
215
		$retval = false;
216 1486 DarkViper
		if( $this->field_exists($table_name, $field_name) )
217 1362 Luisehahne
		{ // modify a existing field in a table
218 1486 DarkViper
			$sql  = 'ALTER TABLE `'.$table_name.'` MODIFY `'.$field_name.'` '.$description;
219
			$retval = ( $this->query($sql) ? true : false);
220
			$this->set_error(mysql_error());
221 1362 Luisehahne
		}
222 1486 DarkViper
		return $retval;
223 1362 Luisehahne
	}
224
225
/*
226
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
227
 * @param string $field_name: name of the field to remove
228
 * @return bool: true if successful, otherwise false and error will be set
229
 */
230
	public function field_remove($table_name, $field_name)
231
	{
232
		$retval = false;
233 1507 Luisehahne
		if( $this->field_exists($table_name, $field_name) )
234 1362 Luisehahne
		{ // modify a existing field in a table
235
			$sql  = 'ALTER TABLE `'.$table_name.'` DROP `'.$field_name.'`';
236
			$retval = ( $this->query($sql) ? true : false );
237
		}
238
		return $retval;
239
	}
240
241
/*
242
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
243
 * @param string $index_name: name of the new index
244
 * @param string $field_list: comma seperated list of fields for this index
245
 * @param string $index_type: kind of index (UNIQUE, PRIMARY, '')
246
 * @return bool: true if successful, otherwise false and error will be set
247
 */
248
	public function index_add($table_name, $index_name, $field_list, $index_type = '')
249
	{
250
		$retval = false;
251
		$field_list = str_replace(' ', '', $field_list);
252
		$field_list = explode(',', $field_list);
253
		$number_fields = sizeof($field_list);
254
		$field_list = '`'.implode('`,`', $field_list).'`';
255
		if( $this->index_exists($table_name, $index_name, $number_fields) ||
256
		    $this->index_exists($table_name, $index_name))
257
		{
258
			$sql  = 'ALTER TABLE `'.$table_name.'` ';
259
			$sql .= 'DROP INDEX `'.$index_name.'`';
260
			if( $this->query($sql))
261
			{
262
				$sql  = 'ALTER TABLE `'.$table_name.'` ';
263
				$sql .= 'ADD '.$index_type.' `'.$index_name.'` ( '.$field_list.' ); ';
264
				if( $this->query($sql)) { $retval = true; }
265
			}
266
		}
267
		return $retval;
268
	}
269
270
/*
271
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
272
 * @param string $field_name: name of the field to remove
273
 * @return bool: true if successful, otherwise false and error will be set
274
 */
275
	public function index_remove($table_name, $index_name)
276
	{
277
		$retval = false;
278
		if( $this->index_exists($table_name, $index_name) )
279
		{ // modify a existing field in a table
280
			$sql  = 'ALTER TABLE `'.$table_name.'` DROP INDEX `'.$index_name.'`';
281
			$retval = ( $this->query($sql) ? true : false );
282
		}
283
		return $retval;
284
	}
285
286 1535 Luisehahne
	/**
287
	* retuns the type of the engine used for requested table
288
	* @param string $table name of the table, including prefix
289
	* @return boolean/string false on error, or name of the engine (myIsam/InnoDb)
290
	*/
291
	public function getTableEngine($table)
292
	{
293
		$retVal = false;
294
		$mysqlVersion = mysql_get_server_info($this->db_handle);
295
		$engineValue = (version_compare($mysqlVersion, '5.0') < 0) ? 'Type' : 'Engine';
296
		$sql = "SHOW TABLE STATUS FROM " . DB_NAME . " LIKE '" . $table . "'";
297
		if(($result = $this->query($sql))) {
298
			if(($row = $result->fetchRow(MYSQL_ASSOC))) {
299
				$retVal = $row[$engineValue];
300
			}
301
		}
302
		return $retVal;
303
	}
304
305
306 1362 Luisehahne
} /// end of class database
307
308 4 ryan
class mysql {
309
310
	// Run a query
311
	function query($statement) {
312
		$this->result = mysql_query($statement);
313
		$this->error = mysql_error();
314
		return $this->result;
315
	}
316
317
	// Fetch num rows
318
	function numRows() {
319
		return mysql_num_rows($this->result);
320
	}
321 1011 Ruebenwurz
322
	// Fetch row  $typ = MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH
323
	function fetchRow($typ = MYSQL_BOTH) {
324
		return mysql_fetch_array($this->result, $typ);
325 4 ryan
	}
326 1011 Ruebenwurz
327 4 ryan
	// Get error
328
	function error() {
329
		if(isset($this->error)) {
330
			return $this->error;
331
		} else {
332
			return null;
333
		}
334
	}
335
336
}
337 1364 Luisehahne
/* this function is placed inside this file temporarely until a better place is found */
338
/*  function to update a var/value-pair(s) in table ****************************
339
 *  nonexisting keys are inserted
340
 *  @param string $table: name of table to use (without prefix)
341
 *  @param mixed $key:    a array of key->value pairs to update
342
 *                        or a string with name of the key to update
343
 *  @param string $value: a sting with needed value, if $key is a string too
344
 *  @return bool:  true if any keys are updated, otherwise false
345
 */
346
	function db_update_key_value($table, $key, $value = '')
347
	{
348
		global $database;
349
		if( !is_array($key))
350
		{
351
			if( trim($key) != '' )
352
			{
353
				$key = array( trim($key) => trim($value) );
354
			} else {
355
				$key = array();
356
			}
357
		}
358
		$retval = true;
359
		foreach( $key as $index=>$val)
360
		{
361
			$index = strtolower($index);
362
			$sql = 'SELECT COUNT(`setting_id`) FROM `'.TABLE_PREFIX.$table.'` WHERE `name` = \''.$index.'\' ';
363
			if($database->get_one($sql))
364
			{
365
				$sql = 'UPDATE ';
366
				$sql_where = 'WHERE `name` = \''.$index.'\'';
367
			}else {
368
				$sql = 'INSERT INTO ';
369
				$sql_where = '';
370
			}
371
			$sql .= '`'.TABLE_PREFIX.$table.'` ';
372
			$sql .= 'SET `name` = \''.$index.'\', ';
373
			$sql .= '`value` = \''.$val.'\' '.$sql_where;
374
			if( !$database->query($sql) )
375
			{
376
				$retval = false;
377
			}
378
		}
379
		return $retval;
380
	}