Subversion Repositories PHPX

Rev

Rev 27 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
27 PointedEar 1
<?php
2
 
29 PointedEar 3
require_once __LIB__ . '/../global.inc';
27 PointedEar 4
 
5
/* NOTE: Obsolete with autoloader */
6
//require_once 'Zend/Registry.php';
7
//require_once 'Zend/Db/Table.php';
8
 
9
/**
10
 * @property-read array $lastId The last generated ID
11
 * @property-read array $result The result of the last query
12
 *
13
 * @author rvejseli, tlahn
14
 */
15
class Database
16
{
17
  private $db_host = DB_HOST;
18
  private $db_user = DB_USER;
19
  private $db_pass = DB_PASS;
20
  private $db_name = DB_NAME;
21
 
22
  private $connection = false;
23
  private $_result = array();
24
 
25
  /**
26
   * The last inserted ID
27
   *
28
   * @var int
29
   */
30
  private $_lastId;
31
 
32
  /* Anlegen der Instanz */
33
  private static $instance = NULL;
34
 
35
  /* Konstruktor private, damit die Klasse nur aus sich selbst heraus instanziiert werden kann. */
36
  private function __construct()
37
  {
38
    $this->connect();
39
    $this->_resetLastID();
40
  }
41
 
42
  /**
43
   * Returns a singleton Database instance
44
   * @return Database
45
   */
46
  public static function getInstance()
47
  {
48
    if (self::$instance === NULL)
49
    {
50
      self::$instance = new self;
51
    }
52
 
53
    return self::$instance;
54
  }
55
 
56
  /* Klonen per 'clone()' von aussen verbieten. */
57
  private function __clone() {}
58
 
59
  /**
60
   * Universal getter
61
   *
62
   * @param string $name
63
   */
64
  public function __get($name)
65
  {
66
    switch ($name)
67
    {
68
      case 'lastId':
69
      case 'result':
70
        return $this->{"_$name"};
71
    }
72
  }
73
 
74
  /**
75
   * Connects to the Database
76
   */
77
  public function connect()
78
  {
79
    if (!$this->connection)
80
    {
81
      $myconn = @mysql_connect($this->db_host, $this->db_user, $this->db_pass);
82
      if ($myconn)
83
      {
84
        $seldb = @mysql_select_db($this->db_name, $myconn);
85
        if ($seldb)
86
        {
87
          $this->connection = true;
88
          return true;
89
        }
90
        else
91
        {
92
          return false;
93
        }
94
      }
95
      else
96
      {
97
        return false;
98
      }
99
    }
100
    else
101
    {
102
      return true;
103
    }
104
  }
105
 
106
  /**
107
   * Resets the last ID to a default value, indicating that no rows where inserted
108
   */
109
  private function _resetLastID()
110
  {
111
    $this->_lastId = -1;
112
  }
113
 
114
  public function setDatabase($name)
115
  {
116
    if ($this->connection)
117
    {
118
      if (@mysql_close())
119
      {
120
        $this->connection = false;
121
        $this->_result = null;
122
        $this->db_name = $name;
123
        $this->connect();
124
      }
125
    }
126
  }
127
 
128
  /**
129
   * Makes an encoding-safe database query
130
   *
131
   * @param string $query
132
   * @param string encoding
133
   *   The encoding in which the query result should be returned from the DBMS.
134
   *   The default is 'utf8'.
135
   * @return resource|null
136
   *   The query result if successful, <code>null</code> otherwise
137
   */
138
  private function _query($query, $encoding = 'utf8')
139
  {
140
    $this->_resetLastID();
141
 
142
    if (mysql_query("SET CHARACTER SET $encoding"))
143
    {
144
      if (DEBUG > 1)
145
      {
146
        echo "<pre>$query</pre>";
147
      }
148
 
149
      return @mysql_query($query);
150
    }
151
    else
152
    {
153
      /* DEBUG */
154
      echo mysql_errno() . ':' . mysql_error();
155
      exit;
156
    }
157
 
158
    return null;
159
  }
160
 
161
  private function _tableExists($table)
162
  {
163
    $tablesInDb = $this->_query("SHOW TABLES FROM {$this->db_name} LIKE '{$table}'");
164
    if ($tablesInDb)
165
    {
166
      if (mysql_num_rows($tablesInDb) == 1)
167
      {
168
        return true;
169
      }
170
      else
171
      {
172
        return false;
173
      }
174
    }
175
  }
176
 
177
  /**
178
   * Determines if an array is associative (does not have a '0' key)
179
   *
180
   * @param array $a
181
   * @return boolean
182
   *   <code>true</code> if <var>$a</var> is associative,
183
   *   <code>false</code> otherwise
184
   */
185
  private function _isAssociativeArray(array $a)
186
  {
187
    return !array_key_exists(0, $a);
188
  }
189
 
190
  /**
191
   * Escapes an associative array so that its string representation can be used
192
   * in a query.
193
   *
194
   * NOTE: Intentionally does not check whether the array actually is associative!
195
   *
196
   * @param array &$array
197
   *   The array to be escaped
198
   * @return array
199
   *   The escaped array
200
   */
201
  private function _escapeArray(array &$array)
202
  {
203
    foreach ($array as $column => &$value)
204
    {
205
      if (is_string($value))
206
      {
207
        $value = mysql_real_escape_string($value);
208
      }
209
 
210
      $value = "`" . mysql_real_escape_string($column) . "`='{$value}'";
211
    }
212
 
213
    return $array;
214
  }
215
 
216
  /**
217
   * Constructs the WHERE part of a query
218
   *
219
   * @param string|array $where Condition
220
   * @return string
221
   */
222
  private function _where($where)
223
  {
224
    if (!is_null($where))
225
    {
226
      if (is_array($where))
227
      {
228
        if (count($where) < 1)
229
        {
230
          return '';
231
        }
232
 
233
        if ($this->_isAssociativeArray($where))
234
        {
235
          $this->_escapeArray($where);
236
        }
237
 
238
        $where = '(' . join(') AND (', $where) . ')';
239
      }
240
 
241
      return ' WHERE ' . $where;
242
    }
243
 
244
    return '';
245
  }
246
 
247
  /**
248
   * Selects data from one or more tables; the resulting records are stored
249
   * in the <code>result</code> property.
250
   *
251
   * @param string|array[string] $tables Table(s) to select from
252
   * @param string|array[string] $columns Column(s) to select from (optional)
253
   * @param string|array $where Condition (optional)
254
   * @param string $order Sort order (optional)
255
   * @param string $limit Limit (optional)
256
   * @return bool
257
   * @throws <code>Exception</code> if the query fails
258
   */
259
  public function select($tables, $columns = null, $where = null, $order = null, $limit = null)
260
  {
261
    $this->_result = array();
262
 
263
    if (is_null($columns))
264
    {
265
      $columns = array('*');
266
    }
267
 
268
    if (!is_array($columns))
269
    {
270
      $columns = array($columns);
271
    }
272
 
273
    $columns = join(',', $columns);
274
 
275
    if (!is_array($tables))
276
    {
277
      $tables = array($tables);
278
    }
279
 
280
    $tables = join(',', $tables);
281
 
282
    $q = "SELECT $columns FROM $tables" . $this->_where($where);
283
 
284
    if (!is_null($order))
285
    {
286
      $q .= " ORDER BY $order";
287
    }
288
 
289
    if (!is_null($limit))
290
    {
291
      $q .= " LIMIT $limit";
292
    }
293
 
294
    if (DEBUG > 1)
295
    {
296
      echo "<pre>$q</pre>";
297
    }
298
 
299
    /* DEBUG */
300
//    debug($q);
301
 
302
    $_result = $this->_query($q);
303
    if ($_result)
304
    {
305
      $this->numResults = mysql_num_rows($_result);
306
      for ($i = 0, $len = $this->numResults; $i < $len; ++$i)
307
      {
308
        $_results = mysql_fetch_array($_result);
309
 
310
        /* DEBUG */
311
        //        debug($_results);
312
 
313
        $keys = array_keys($_results);
314
        foreach ($keys as $key)
315
        {
316
          if (!is_int($key))
317
          {
318
            if (mysql_num_rows($_result) > 0)
319
            {
320
              $this->_result[$i][$key] = $_results[$key];
321
            }
322
            else if (mysql_num_rows($_result) < 1)
323
            {
324
              $this->_result = null;
325
            }
326
          }
327
        }
328
      }
329
 
330
      return $q;
331
    }
332
    else
333
    {
334
      throw new Exception(mysql_error() . ". Query: " . $q);
335
    }
336
  }
337
 
338
  /**
339
   * Inserts a record into a table.<p>The AUTO_INCREMENT value of the inserted
340
   * row, if any (> 0), is stored in the {@link $lastId} property of
341
   * the <code>Database</code> instance.</p>
342
   *
343
   * @param string $table
344
   *   Table name
345
   * @param array|string $values
346
   *   Associative array of column-value pairs, indexed array,
347
   *   or comma-separated list of values.  If <var>$values</var> is not
348
   *   an associative array, <var>$cols</var> must be passed if the
349
   *   values are not in column order (see below).
350
   * @param array|string $cols
351
   *   Indexed array, or comma-separated list of column names.
352
   *   Needs only be passed if <var>$values</var> is not an associative array
353
   *   and the values are not in column order (default: <code>null</code>);
354
   *   is ignored otherwise.  <strong>You SHOULD NOT rely on column order.</strong>
355
   * @return bool
356
   *   <code>true</code> if successful, <code>false</code> otherwise
357
   */
358
  public function insert($table, $values, $cols = null)
359
  {
360
    if ($cols != null)
361
    {
362
      $cols = ' ('
363
      . (is_array($cols)
364
      ? join(',', array_map(create_function('$s', 'return "`$s`";'), $cols))
365
      : $cols) . ')';
366
    }
367
    else
368
    {
369
      $cols = '';
370
    }
371
 
372
    /* DEBUG */
373
    //          debug($values);
374
 
375
    if ($this->_isAssociativeArray($values))
376
    {
377
      $this->_escapeArray($values);
378
 
379
      $cols = '';
380
      $values = 'SET ' . join(', ', $values);
381
    }
382
    else
383
    {
384
      foreach ($values as &$value)
385
      {
386
        if (is_string($value))
387
        {
388
          $value = "'" . mysql_real_escape_string($value) . "'";
389
        }
390
      }
391
 
392
      $values = ' VALUES (' . join(', ', $values) . ')';
393
    }
394
 
395
    $insert = "INSERT INTO `{$table}` {$cols} {$values}";
396
 
397
    /* DEBUG */
398
//    echo "Insert:<br>";
399
//    debug($insert);
400
 
401
    $ins = $this->_query($insert);
402
    $this->_lastId = mysql_insert_id();
403
 
404
    if ($ins)
405
    {
406
      return true;
407
    }
408
    else
409
    {
410
      return false;
411
    }
412
  }
413
 
414
  /**
415
   * Deletes records from a table
416
   *
417
   * @param string $table
418
   * @param string|array|null $where  Condition for deletion
419
   * @return bool
420
   */
421
  public function delete($table, $where = null)
422
  {
423
    $delete = "DELETE FROM `{$table}`" . $this->_where($where);
424
 
425
    /* DEBUG */
426
                // debug($delete);
427
    // $result = true;
428
 
429
    $result = $this->_query($delete);
430
 
431
    return ($result) ? true : false;
432
  }
433
 
434
  /**
435
   * Updates one or more records
436
   *
437
   * @param string|array $tables
438
   *   Table name
439
   * @param array $values
440
   *   Associative array of column-value pairs
441
   * @param array|string $where
442
   *   Only the records matching this condition are updated
443
   * @return string|bool
444
   */
445
  public function update($tables, $updates, $where = null)
446
  {
447
    if (!$tables)
448
    {
449
      throw new InvalidArgumentException('No table specified');
450
    }
451
 
452
    if (is_array($tables))
453
    {
454
      $tables = join(',', $tables);
455
    }
456
 
457
    if (!$updates)
458
    {
459
      throw new InvalidArgumentException('No values specified');
460
    }
461
 
462
    $updates = join(',', $this->_escapeArray($updates));
463
 
464
    $query = "UPDATE {$tables} SET {$updates}" . $this->_where($where);
465
 
466
    /* DEBUG */
467
//    echo "Update:<br>";
468
//    debug($query);
469
 
470
    $_result = $this->_query($query);
471
 
472
    if (!$_result)
473
    {
474
      //debug(mysql_error());
475
      return false;
476
    }
477
 
478
    return $query;
479
  }
480
 
481
  /**
482
   * Inserts a row into a table or updates an existing one
483
   *
484
   * @param string $sTable
485
   *   Table name
486
   * @param array $aValues
487
   *   Associative array of column-value pairs to be updated/inserted
488
   * @param string|array $condition
489
   *   If there are no records matching this condition, a row will be inserted;
490
   *   otherwise matching records are updated
491
   * @return mixed
492
   */
493
  public function updateOrInsert($sTable, $aValues, $condition)
494
  {
495
    $result = $this->select($sTable, '*', $condition);
496
    if (!$result)
497
    {
498
      exit;
499
    }
500
 
501
    //    debug($this->result);
502
    if (count($this->result) > 0)
503
    {
504
      $result = $this->update($sTable, $aValues, $condition);
505
    }
506
    else
507
    {
508
      $result = $this->insert($sTable, $aValues);
509
    }
510
 
511
    return $result;
512
  }
513
 
514
  /**
515
   * @see Database::updateOrInsert()
516
   */
517
  public function insertOrUpdate($sTable, $aValues, $condition)
518
  {
519
    return $this->updateOrInsert($sTable, $aValues, $condition);
520
  }
521
 
522
  public function getResult()
523
  {
524
    return $this->result;
525
  }
526
}
527
?>