modules/helpers/helpers_database.module

<?php
// $Id: helpers_database.module,v 1.1 2006/07/16 16:26:32 ber Exp $

/**
 * @file
 * This is a library of useful database functions and methods.
 * please insert the link if you have a patch for core to introduce a function from this lib to core.
 */

/**
 * Implementation of hook_help().
 */
function helpers_database_help($section) {
  switch ($section) {
    case 'admin/modules#description':
      return t('A development library for the database. Contains useful functions and methods for database queries.');
  }
}

/**
 * Database functions
 */

/**
 * Run an insert query on the active database.
 *
 * @param $table
 *   The database table on which to run the insert query.
 * @param $fields
 *   An associative array of the values to insert.  The keys are the
 *   fields, and the corresponding values are the values to insert.
 * @return
 *   A   database query result resource, or FALSE if the query was not
 * executed correctly.
 *
 */
function db_insert($table, $fields) {

  $insert_fields = array_keys($fields);
  $insert_values = array_values($fields);

  $params = array();
  foreach ($insert_values as $value) {
    switch(gettype($value)) {
      case 'double':
        $escape = '%f';
        break;
      case 'integer':
        $escape = '%d';
        break;
      case 'string':
        $escape = "'%s'";
        break;
      case 'NULL':
        $escape = 'NULL';
        break;
      default:
        continue;
    }
    $params[] = $escape;
  }

  $sql = 'INSERT INTO {' . $table . '} (' . implode(',', $insert_fields) . ') VALUES (' . implode(',', $params) . ')';

  return db_query($sql, $insert_values);
}

/**
 * Run an update query on the active database.
 *
 * @param $table
 *   The database table on which to run the update query.
 * @param $fields
 *   An associative array of the values to update.  The keys are the
 *   fields, and the corresponding values are the values to update to.
 * @param $where
 *   The where rules for this update query.
 * @param $where_type
 *   Whether to AND or OR the where rules together.
 * @return
 *   A database query result resource, or FALSE if the query was not
 *   executed correctly.
 *
 */
function db_update($table, $fields, $where, $where_type='AND') {

  $update_values = array_values($fields);

  $flat_fields = array();
  foreach ($fields as $field => $value) {
    switch(gettype($value)) {
      case 'integer':
        $escape = '%d';
        break;
      case 'double':
        $escape = '%f';
        break;
      case 'string':
        $escape = "'%s'";
        break;
      case 'NULL':
        $escape = 'NULL';
        break;
      default:
        continue;
    }
    $flat_fields[] = $field . '=' . $escape;
  }

  list($where_string, $where_values) = db_where_clause($where, $where_type);

  $sql = 'UPDATE ' . $table . ' SET ' . implode(',', $flat_fields) . $where_string;

  return db_query($sql, array_merge($update_values, $where_values));
}

/**
 * Run a delete query on the active database.
 *
 * @param $table
 *   The database table on which to run the delete query.
 * @param $where
 *   The where rules for this delete query.
 * @param $where_type
 *   Whether to AND or OR the where rules together.
 * @return
 *   A database query result resource, or FALSE if the query was not
 *   executed correctly.
 *
 */
function db_delete($table, $where, $where_type='AND') {

  list($where_string, $where_values) = db_where_clause($where, $where_type);

  $sql = 'DELETE FROM ' . $table . $where_string;

  return db_query($sql, array_merge($where_string, $where_values));
}

/**
 * Build the WHERE portion of an SQL query, based on the specified values.
 *
 * @param $where
 *   Associative array of rules in the WHERE clause.  If a key in the array
 *   is numeric, the value is taken as a literal rule.  If it is non-numeric,
 *   then it is assumed to be a field name and the corresponding value is the
 *   value that it must hold.
 * @param $where_type
 *   Whether the values of the WHERE clause should be ANDed or ORed together.
 *
 *   As an example, this $where clause would be translated as follows:
 *   $where = array('name'=>'foo', 'type'=>'page', 'created < 1147567877')
 *
 *   WHERE (name='foo') AND ('type'='page') AND (created < 1147567877')
 * @return
 *   An array containing the where clause with sprintf() markers, and
 *   an array of values to substitute for them.
 */
function db_where_clause($where, $where_type='AND', $where_keyword=TRUE) {
  $params = array();
  $args = array();
  foreach ($where as $key => $value) {
    if (is_numeric($key)) {
      $params[] = ' (' . $value . ') ';
    }
    else {
      switch(gettype($value)) {
        case 'double':
          $escape = '%f';
          break;
        case 'integer':
          $escape = '%d';
          break;
        case 'string':
          $escape = "'%s'";
          break;
        case 'NULL':
          $escape = 'NULL';
          break;
        default:
          continue;
      }
      $params[] = ' (' . $key . '=' . $escape . ') ';
      $args[] = $value;
    }
  }

  $return = '';
  if (sizeof($params)) {
    $return = ($where_keyword ? ' WHERE ' : ' ') . implode($where_type, $params);
  }

  return array($return, $args);
}

/**
 * Returns an array with all objects from a query
 * Does not do a rewrite sql for you!
 * Call similar to db_query.
 */
function db_fetch_all_as_objects($query) {
  $results = array();
  $args = func_get_args();
  array_shift($args);
  $res = db_query($query, $args);

  while ($row = db_fetch_object($res)) {
    $results[] = $row;
  }
  return $results;
}

/**
 * Returns an array with all arrays from a query
 * Does not do a rewrite sql for you!
 * Call similar to db_query.
 */
function db_fetch_all_as_arrays($query) {
  $results = array();
  $args = func_get_args();
  array_shift($args);
  $res = db_query($query, $args);

  while ($row = db_fetch_array($res)) {
    $results[] = $row;
  }
  return $results;
}
1	<?php
2	// $Id: helpers_database.module,v 1.1 2006/07/16 16:26:32 ber Exp $
3
4	/**
5	* @file
6	* This is a library of useful database functions and methods.
7	* please insert the link if you have a patch for core to introduce a function from this lib to core.
8	*/
9
10	/**
11	* Implementation of hook_help().
12	*/
13	function helpers_database_help($section) {
14	switch ($section) {
15	case 'admin/modules#description':
16	return t('A development library for the database. Contains useful functions and methods for database queries.');
17	}
18	}
19
20	/**
21	* Database functions
22	*/
23
24	/**
25	* Run an insert query on the active database.
26	*
27	* @param $table
28	* The database table on which to run the insert query.
29	* @param $fields
30	* An associative array of the values to insert. The keys are the
31	* fields, and the corresponding values are the values to insert.
32	* @return
33	* A database query result resource, or FALSE if the query was not
34	* executed correctly.
35	*
36	*/
37	function db_insert($table, $fields) {
38
39	$insert_fields = array_keys($fields);
40	$insert_values = array_values($fields);
41
42	$params = array();
43	foreach ($insert_values as $value) {
44	switch(gettype($value)) {
45	case 'double':
46	$escape = '%f';
47	break;
48	case 'integer':
49	$escape = '%d';
50	break;
51	case 'string':
52	$escape = "'%s'";
53	break;
54	case 'NULL':
55	$escape = 'NULL';
56	break;
57	default:
58	continue;
59	}
60	$params[] = $escape;
61	}
62
63	$sql = 'INSERT INTO {' . $table . '} (' . implode(',', $insert_fields) . ') VALUES (' . implode(',', $params) . ')';
64
65	return db_query($sql, $insert_values);
66	}
67
68	/**
69	* Run an update query on the active database.
70	*
71	* @param $table
72	* The database table on which to run the update query.
73	* @param $fields
74	* An associative array of the values to update. The keys are the
75	* fields, and the corresponding values are the values to update to.
76	* @param $where
77	* The where rules for this update query.
78	* @param $where_type
79	* Whether to AND or OR the where rules together.
80	* @return
81	* A database query result resource, or FALSE if the query was not
82	* executed correctly.
83	*
84	*/
85	function db_update($table, $fields, $where, $where_type='AND') {
86
87	$update_values = array_values($fields);
88
89	$flat_fields = array();
90	foreach ($fields as $field => $value) {
91	switch(gettype($value)) {
92	case 'integer':
93	$escape = '%d';
94	break;
95	case 'double':
96	$escape = '%f';
97	break;
98	case 'string':
99	$escape = "'%s'";
100	break;
101	case 'NULL':
102	$escape = 'NULL';
103	break;
104	default:
105	continue;
106	}
107	$flat_fields[] = $field . '=' . $escape;
108	}
109
110	list($where_string, $where_values) = db_where_clause($where, $where_type);
111
112	$sql = 'UPDATE ' . $table . ' SET ' . implode(',', $flat_fields) . $where_string;
113
114	return db_query($sql, array_merge($update_values, $where_values));
115	}
116
117	/**
118	* Run a delete query on the active database.
119	*
120	* @param $table
121	* The database table on which to run the delete query.
122	* @param $where
123	* The where rules for this delete query.
124	* @param $where_type
125	* Whether to AND or OR the where rules together.
126	* @return
127	* A database query result resource, or FALSE if the query was not
128	* executed correctly.
129	*
130	*/
131	function db_delete($table, $where, $where_type='AND') {
132
133	list($where_string, $where_values) = db_where_clause($where, $where_type);
134
135	$sql = 'DELETE FROM ' . $table . $where_string;
136
137	return db_query($sql, array_merge($where_string, $where_values));
138	}
139
140	/**
141	* Build the WHERE portion of an SQL query, based on the specified values.
142	*
143	* @param $where
144	* Associative array of rules in the WHERE clause. If a key in the array
145	* is numeric, the value is taken as a literal rule. If it is non-numeric,
146	* then it is assumed to be a field name and the corresponding value is the
147	* value that it must hold.
148	* @param $where_type
149	* Whether the values of the WHERE clause should be ANDed or ORed together.
150	*
151	* As an example, this $where clause would be translated as follows:
152	* $where = array('name'=>'foo', 'type'=>'page', 'created < 1147567877')
153	*
154	* WHERE (name='foo') AND ('type'='page') AND (created < 1147567877')
155	* @return
156	* An array containing the where clause with sprintf() markers, and
157	* an array of values to substitute for them.
158	*/
159	function db_where_clause($where, $where_type='AND', $where_keyword=TRUE) {
160	$params = array();
161	$args = array();
162	foreach ($where as $key => $value) {
163	if (is_numeric($key)) {
164	$params[] = ' (' . $value . ') ';
165	}
166	else {
167	switch(gettype($value)) {
168	case 'double':
169	$escape = '%f';
170	break;
171	case 'integer':
172	$escape = '%d';
173	break;
174	case 'string':
175	$escape = "'%s'";
176	break;
177	case 'NULL':
178	$escape = 'NULL';
179	break;
180	default:
181	continue;
182	}
183	$params[] = ' (' . $key . '=' . $escape . ') ';
184	$args[] = $value;
185	}
186	}
187
188	$return = '';
189	if (sizeof($params)) {
190	$return = ($where_keyword ? ' WHERE ' : ' ') . implode($where_type, $params);
191	}
192
193	return array($return, $args);
194	}
195
196	/**
197	* Returns an array with all objects from a query
198	* Does not do a rewrite sql for you!
199	* Call similar to db_query.
200	*/
201	function db_fetch_all_as_objects($query) {
202	$results = array();
203	$args = func_get_args();
204	array_shift($args);
205	$res = db_query($query, $args);
206
207	while ($row = db_fetch_object($res)) {
208	$results[] = $row;
209	}
210	return $results;
211	}
212
213	/**
214	* Returns an array with all arrays from a query
215	* Does not do a rewrite sql for you!
216	* Call similar to db_query.
217	*/
218	function db_fetch_all_as_arrays($query) {
219	$results = array();
220	$args = func_get_args();
221	array_shift($args);
222	$res = db_query($query, $args);
223
224	while ($row = db_fetch_array($res)) {
225	$results[] = $row;
226	}
227	return $results;
228	}