WebSVN - PHPX - Diff - Rev 29 and 34 - /trunk/Db/database.class.php


<?php

require_once __DIR__ . '/../global.inc';

/* NOTE: Obsolete with autoloader */
//require_once 'Zend/Registry.php';
//require_once 'Zend/Db/Table.php';

/**
 * @property-read array $lastId The last generated ID
 * @property-read array $result The result of the last query
 *
 * @author rvejseli, tlahn
 */
class Database
{
  private $db_host = DB_HOST;
  private $db_user = DB_USER;
  private $db_pass = DB_PASS;
  private $db_name = DB_NAME;

  private $connection = false;
  private $_result = array();

  /**
   * The last inserted ID
   *
   * @var int
   */
  private $_lastId;

  /* Anlegen der Instanz */
  private static $instance = NULL;

  /* Konstruktor private, damit die Klasse nur aus sich selbst heraus instanziiert werden kann. */
  private function __construct()
  {
    $this->connect();
    $this->_resetLastID();
  }

  /**
   * Returns a singleton Database instance
   * @return Database
   */
  public static function getInstance()
  {
    if (self::$instance === NULL)
    {
      self::$instance = new self;
    }

    return self::$instance;
  }

  /* Klonen per 'clone()' von aussen verbieten. */
  private function __clone() {}

  /**
   * Universal getter
   *
   * @param string $name
   */
  public function __get($name)
  {
    switch ($name)
    {
      case 'lastId':
      case 'result':
        return $this->{"_$name"};
    }
  }

  /**
   * Connects to the Database
   */
  public function connect()
  {
    if (!$this->connection)
    {
      $myconn = @mysql_connect($this->db_host, $this->db_user, $this->db_pass);
      if ($myconn)
      {
        $seldb = @mysql_select_db($this->db_name, $myconn);
        if ($seldb)
        {
          $this->connection = true;
          return true;
        }
        else
        {
          return false;
        }
      }
      else
      {
        return false;
      }
    }
    else
    {
      return true;
    }
  }

  /**
   * Resets the last ID to a default value, indicating that no rows where inserted
   */
  private function _resetLastID()
  {
    $this->_lastId = -1;
  }

  public function setDatabase($name)
  {
    if ($this->connection)
    {
      if (@mysql_close())
      {
        $this->connection = false;
        $this->_result = null;
        $this->db_name = $name;
        $this->connect();
      }
    }
  }

  /**
   * Makes an encoding-safe database query
   *
   * @param string $query
   * @param string encoding
   *   The encoding in which the query result should be returned from the DBMS.
   *   The default is 'utf8'.
   * @return resource|null
   *   The query result if successful, <code>null</code> otherwise
   */
  private function _query($query, $encoding = 'utf8')
  {
    $this->_resetLastID();

    if (mysql_query("SET CHARACTER SET $encoding"))
    {
      if (DEBUG > 1)
      {
        echo "<pre>$query</pre>";
      }

      return @mysql_query($query);
    }
    else
    {
      /* DEBUG */
      echo mysql_errno() . ':' . mysql_error();
      exit;
    }

    return null;
  }

  private function _tableExists($table)
  {
    $tablesInDb = $this->_query("SHOW TABLES FROM {$this->db_name} LIKE '{$table}'");
    if ($tablesInDb)
    {
      if (mysql_num_rows($tablesInDb) == 1)
      {
        return true;
      }
      else
      {
        return false;
      }
    }
  }
 
  /**
   * Determines if an array is associative (does not have a '0' key)
   *
   * @param array $a
   * @return boolean
   *   <code>true</code> if <var>$a</var> is associative,
   *   <code>false</code> otherwise
   */
  private function _isAssociativeArray(array $a)
  {
    return !array_key_exists(0, $a);
  }

  /**
   * Escapes an associative array so that its string representation can be used
   * in a query.
   *
   * NOTE: Intentionally does not check whether the array actually is associative!
   *
   * @param array &$array
   *   The array to be escaped
   * @return array
   *   The escaped array
   */
  private function _escapeArray(array &$array)
  {
    foreach ($array as $column => &$value)
    {
      if (is_string($value))
      {
        $value = mysql_real_escape_string($value);
      }

      $value = "`" . mysql_real_escape_string($column) . "`='{$value}'";
    }
   
    return $array;
  }
 
  /**
   * Constructs the WHERE part of a query
   *
   * @param string|array $where Condition
   * @return string
   */
  private function _where($where)
  {
    if (!is_null($where))
    {
      if (is_array($where))
      {
        if (count($where) < 1)
        {
          return '';
        }
       
        if ($this->_isAssociativeArray($where))
        {
          $this->_escapeArray($where);
        }

        $where = '(' . join(') AND (', $where) . ')';
      }

      return ' WHERE ' . $where;
    }
   
    return '';
  }
 
  /**
   * Selects data from one or more tables; the resulting records are stored
   * in the <code>result</code> property.
   *
   * @param string|array[string] $tables Table(s) to select from
   * @param string|array[string] $columns Column(s) to select from (optional)
   * @param string|array $where Condition (optional)
   * @param string $order Sort order (optional)
   * @param string $limit Limit (optional)
   * @return bool
   * @throws <code>Exception</code> if the query fails
   */
  public function select($tables, $columns = null, $where = null, $order = null, $limit = null)
  {
    $this->_result = array();

    if (is_null($columns))
    {
      $columns = array('*');
    }
   
    if (!is_array($columns))
    {
      $columns = array($columns);
    }

    $columns = join(',', $columns);
   
    if (!is_array($tables))
    {
      $tables = array($tables);
    }

    $tables = join(',', $tables);

    $q = "SELECT $columns FROM $tables" . $this->_where($where);

    if (!is_null($order))
    {
      $q .= " ORDER BY $order";
    }

    if (!is_null($limit))
    {
      $q .= " LIMIT $limit";
    }
   
    if (DEBUG > 1)
    {
      echo "<pre>$q</pre>";
    }

    /* DEBUG */
//    debug($q);

    $_result = $this->_query($q);
    if ($_result)
    {
      $this->numResults = mysql_num_rows($_result);
      for ($i = 0, $len = $this->numResults; $i < $len; ++$i)
      {
        $_results = mysql_fetch_array($_result);

        /* DEBUG */
        //        debug($_results);

        $keys = array_keys($_results);
        foreach ($keys as $key)
        {
          if (!is_int($key))
          {
            if (mysql_num_rows($_result) > 0)
            {
              $this->_result[$i][$key] = $_results[$key];
            }
            else if (mysql_num_rows($_result) < 1)
            {
              $this->_result = null;
            }
          }
        }
      }

      return $q;
    }
    else
    {
      throw new Exception(mysql_error() . ". Query: " . $q);
    }
  }

  /**
   * Inserts a record into a table.<p>The AUTO_INCREMENT value of the inserted
   * row, if any (> 0), is stored in the {@link $lastId} property of
   * the <code>Database</code> instance.</p>
   *
   * @param string $table
   *   Table name
   * @param array|string $values
   *   Associative array of column-value pairs, indexed array,
   *   or comma-separated list of values.  If <var>$values</var> is not
   *   an associative array, <var>$cols</var> must be passed if the
   *   values are not in column order (see below).
   * @param array|string $cols
   *   Indexed array, or comma-separated list of column names.
   *   Needs only be passed if <var>$values</var> is not an associative array
   *   and the values are not in column order (default: <code>null</code>);
   *   is ignored otherwise.  <strong>You SHOULD NOT rely on column order.</strong>
   * @return bool
   *   <code>true</code> if successful, <code>false</code> otherwise
   */
  public function insert($table, $values, $cols = null)
  {
    if ($cols != null)
    {
      $cols = ' ('
      . (is_array($cols)
      ? join(',', array_map(create_function('$s', 'return "`$s`";'), $cols))
      : $cols) . ')';
    }
    else
    {
      $cols = '';
    }

    /* DEBUG */
    //          debug($values);

    if ($this->_isAssociativeArray($values))
    {
      $this->_escapeArray($values);

      $cols = '';
      $values = 'SET ' . join(', ', $values);
    }
    else
    {
      foreach ($values as &$value)
      {
        if (is_string($value))
        {
          $value = "'" . mysql_real_escape_string($value) . "'";
        }
      }
       
      $values = ' VALUES (' . join(', ', $values) . ')';
    }

    $insert = "INSERT INTO `{$table}` {$cols} {$values}";

    /* DEBUG */
//    echo "Insert:<br>";
//    debug($insert);

    $ins = $this->_query($insert);
    $this->_lastId = mysql_insert_id();

    if ($ins)
    {
      return true;
    }
    else
    {
      return false;
    }
  }

  /**
   * Deletes records from a table
   *
   * @param string $table
   * @param string|array|null $where  Condition for deletion
   * @return bool
   */
  public function delete($table, $where = null)
  {
    $delete = "DELETE FROM `{$table}`" . $this->_where($where);

    /* DEBUG */
                // debug($delete);
    // $result = true;

    $result = $this->_query($delete);

    return ($result) ? true : false;
  }

  /**
   * Updates one or more records
   *
   * @param string|array $tables
   *   Table name
   * @param array $values
   *   Associative array of column-value pairs
   * @param array|string $where
   *   Only the records matching this condition are updated
   * @return string|bool
   */
  public function update($tables, $updates, $where = null)
  {
    if (!$tables)
    {
      throw new InvalidArgumentException('No table specified');
    }
     
    if (is_array($tables))
    {
      $tables = join(',', $tables);
    }
     
    if (!$updates)
    {
      throw new InvalidArgumentException('No values specified');
    }
     
    $updates = join(',', $this->_escapeArray($updates));
         
    $query = "UPDATE {$tables} SET {$updates}" . $this->_where($where);
   
    /* DEBUG */
//    echo "Update:<br>";
//    debug($query);
   
    $_result = $this->_query($query);

    if (!$_result)
    {
      //debug(mysql_error());
      return false;
    }

    return $query;
  }

  /**
   * Inserts a row into a table or updates an existing one
   *
   * @param string $sTable
   *   Table name
   * @param array $aValues
   *   Associative array of column-value pairs to be updated/inserted
   * @param string|array $condition
   *   If there are no records matching this condition, a row will be inserted;
   *   otherwise matching records are updated
   * @return mixed
   */
  public function updateOrInsert($sTable, $aValues, $condition)
  {
    $result = $this->select($sTable, '*', $condition);
    if (!$result)
    {
      exit;
    }

    //    debug($this->result);
    if (count($this->result) > 0)
    {
      $result = $this->update($sTable, $aValues, $condition);
    }
    else
    {
      $result = $this->insert($sTable, $aValues);
    }

    return $result;
  }

  /**
   * @see Database::updateOrInsert()
   */
  public function insertOrUpdate($sTable, $aValues, $condition)
  {
    return $this->updateOrInsert($sTable, $aValues, $condition);
  }

  public function getResult()
  {
    return $this->result;
  }
}
?>

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

Subversion Repositories PHPX

(root)/trunk/Db/database.class.php - Rev 29 → 34