3 # Copyright (C) 2006 Jason Woofenden
5 # This program is free software: you can redistribute it and/or modify
6 # it under the terms of the GNU General Public License as published by
7 # the Free Software Foundation, either version 3 of the License, or
8 # (at your option) any later version.
10 # This program is distributed in the hope that it will be useful,
11 # but WITHOUT ANY WARRANTY; without even the implied warranty of
12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 # GNU General Public License for more details.
15 # You should have received a copy of the GNU General Public License
16 # along with this program. If not, see <http://www.gnu.org/licenses/>.
19 require_once('code/wfpl/encode.php');
20 require_once('code/wfpl/format.php');
22 # db_connect() -- connect to a mysql database
26 # database: the name of the database you want to connect to. Defaults to the
27 # second-to-last part of the domain name. eg for foo.example.com it would be
30 # user: username for connecting to the database. Defaults to
31 # $GLOBALS['db_username'] or (if that's not set) "www".
33 # password: password for connecting to the database. Defaults to
34 # $GLOBALS['db_password'] or (if that's not set "".
38 # the database connection handle. You'll only need this if you want to have
39 # multiple databases open at once.
41 function db_connect($database = 'auto', $user = 'auto', $pass = 'auto', $host = 'localhost') {
42 if($database == 'auto') {
43 if(isset($GLOBALS['db_name'])) {
44 $database = $GLOBALS['db_name'];
46 $host = $_SERVER['SERVER_NAME'];
47 $host = explode('.', $host);
49 $database = array_pop($host);
55 if(isset($GLOBALS['db_username'])) {
56 $user = $GLOBALS['db_username'];
63 if(isset($GLOBALS['db_password'])) {
64 $pass = $GLOBALS['db_password'];
70 $GLOBALS['wfpl_db_handle'] = mysql_connect($host, $user, $pass);
71 if(!$GLOBALS['wfpl_db_handle']) {
72 die('Could not connect to the database: ' . mysql_error());
75 if(!mysql_select_db($database, $GLOBALS['wfpl_db_handle'])) {
76 die("Couldn not access database \"$database\": " . mysql_error($GLOBALS['wfpl_db_handle']));
79 return $GLOBALS['wfpl_db_handle'];
82 # Unless you're doing something unusual like an ALTER TABLE don't call this directly
83 function db_send_query($sql) {
84 #echo("Sending query: " . enc_html($sql) . "<br>\n");
85 $result = mysql_query($sql, $GLOBALS['wfpl_db_handle']);
87 die(enc_html('DATABASE ERROR: ' . mysql_error($GLOBALS['wfpl_db_handle']) . ' in the following query: ' . $sql));
93 # All select queries use this to generate the where clause, so they can work
94 # like printf. Currently three % codes are supported:
96 # %% put a % in the output
97 # %i put an integer in the output (strips non-numeric digits, and puts in 0 if blank)
98 # %" output double quotes, surrounding the variable which is encoded to be in there.
99 # %s output encoded to be in double quotes, but don't output the quotes
100 # %$ output argument as-is, no encoding. Make sure you quote everything from the user!
102 # complex example: db_get_rows('mytable', 'id', 'where name=%" or company like "%%%s%%"', $name, $company_partial);
104 function db_printf($str) {
105 $args = func_get_args();
106 $args = array_slice($args, 1);
107 return _db_printf($str, $args);
110 # This function does the work, but takes the parameters in an array
111 function _db_printf($str, $args) {
114 $pos = strpos($str, '%');
115 if($pos === false) { # not found
119 # move everything up to (but not including) % to the output
120 $out .= substr($str, 0, $pos);
122 # grab the character after the %
123 $chr = substr($str, $pos + 1, 1);
125 # remove the stuff we've read from input
126 $str = substr($str, $pos + 2);
129 $out .= '"' . enc_sql(array_shift($args)) . '"';
130 } elseif($chr == 's') {
131 $out .= enc_sql(array_shift($args));
132 } elseif($chr == 'i') {
133 $int = format_int(array_shift($args));
134 if($int == '') $int = '0';
136 } elseif($chr == '$') {
137 $out .= array_shift($args);
147 function db_send_get($table, $columns, $where, $args) {
148 $sql = "SELECT $columns FROM $table";
150 $sql .= ' ' . _db_printf($where, $args);
153 return db_send_query($sql);
157 function db_get_rows($table, $columns, $where = '') {
158 $args = func_get_args();
159 $args = array_slice($args, 3);
160 $result = db_send_get($table, $columns, $where, $args);
163 while($row = mysql_fetch_row($result)) {
167 mysql_free_result($result);
172 # like db_get_rows, but return array of hashes.
173 function db_get_assocs($table, $columns, $where = '') {
174 $args = func_get_args();
175 $args = array_slice($args, 3);
176 $result = db_send_get($table, $columns, $where, $args);
179 while($row = mysql_fetch_assoc($result)) {
183 mysql_free_result($result);
188 function db_get_column($table, $columns, $where = '') {
189 $args = func_get_args();
190 $args = array_slice($args, 3);
191 $result = db_send_get($table, $columns, $where, $args);
194 while($row = mysql_fetch_row($result)) {
198 mysql_free_result($result);
203 function db_get_row($table, $columns, $where = '') {
204 $args = func_get_args();
205 $args = array_slice($args, 3);
206 $result = db_send_get($table, $columns, $where, $args);
208 $row = mysql_fetch_row($result);
210 mysql_free_result($result);
215 # like db_get_row, but return a hash.
216 function db_get_assoc($table, $columns, $where = '') {
217 $args = func_get_args();
218 $args = array_slice($args, 3);
219 $result = db_send_get($table, $columns, $where, $args);
221 $row = mysql_fetch_assoc($result);
223 mysql_free_result($result);
228 function db_get_value($table, $columns, $where = '') {
229 $args = func_get_args();
230 $args = array_slice($args, 3);
231 $result = db_send_get($table, $columns, $where, $args);
233 $value = mysql_fetch_row($result);
234 if($value !== false) {
238 mysql_free_result($result);
243 function db_count($table, $where = '') {
244 $args = func_get_args();
245 array_splice($args, 1, 0, array('count(*)'));
246 return call_user_func_array('db_get_value', $args);
249 # call either of these ways:
251 # db_insert('people', 'name,company', 'jason', 'widgets ltd');
253 # db_insert('people', 'name,company', array('jason', 'widgets ltd'));
254 function db_insert($table, $columns, $values) {
255 if(!is_array($values)) {
256 $values = func_get_args();
257 $values = array_slice($values, 2);
260 db_insert_ish('INSERT', $table, $columns, $values);
263 # like db_insert() above, but instead of passing columns and data separately,
264 # you can pass one array with the column names as keys and the data as values
265 function db_insert_assoc($table, $data) {
266 $args = func_get_args();
267 $args = array_slice($args, 2);
270 foreach($data as $key => $value) {
274 array_unshift($args, $table, join(',', $columns), $values);
275 call_user_func_array('db_insert', $args);
278 # same as above, except uses the "replace" command instead of "insert"
279 function db_replace($table, $columns, $values) {
280 if(!is_array($values)) {
281 $values = func_get_args();
282 $values = array_slice($values, 2);
285 db_insert_ish('REPLACE', $table, $columns, $values);
288 # return the value mysql made up for the auto_increment field (for the last insert)
289 function db_auto_id() {
290 return mysql_insert_id($GLOBALS['wfpl_db_handle']);
294 # used to implement db_insert() and db_replace()
295 function db_insert_ish($command, $table, $columns, $values) {
298 foreach($values as $value) {
299 if($sql) $sql .= ',';
300 $sql .= '"' . enc_sql($value) . '"';
303 $sql = "$command INTO $table ($columns) values($sql)";
308 # to be consistent with the syntax of the other db functions, $values can be an
309 # array, a single value, or multiple parameters.
311 # as usual the where clause stuff is optional, but it will of course update the
312 # whole table if you leave it off.
316 # # name everybody Bruce
317 # db_update('users', 'name', 'Bruce');
319 # # name user #6 Bruce
320 # db_update('users', 'name', 'Bruce', 'where id=%i', 6);
322 # # update the whole bit for user #6
323 # db_update('users', 'name,email,description', 'Bruce', 'bruce@example.com', 'is a cool guy', 'where id=%i', 6);
325 # # update the whole bit for user #6 (passing data as an array)
326 # $data = array('Bruce', 'bruce@example.com', 'is a cool guy');
327 # db_update('users', 'name,email,description', $data, 'where id=%i', 6);
329 # The prototype is really something like this:
330 # db_update(table, columns, values..., where(optional), where_args...(optional))
331 function db_update($table, $columns, $values) {
332 $args = func_get_args();
333 $args = array_slice($args, 2);
334 $columns = explode(',', $columns);
335 $num_fields = count($columns);
337 if(is_array($values)) {
338 $values = array_values($values);
339 $args = array_slice($args, 1);
341 $values = array_slice($args, 0, $num_fields);
342 $args = array_slice($args, $num_fields);
346 for($i = 0; $i < $num_fields; ++$i) {
350 $sql .= $columns[$i] . ' = "' . enc_sql($values[$i]) . '"';
354 $sql = "UPDATE $table SET $sql";
356 # if there's any more arguments
359 $args = array_slice($args, 1);
362 # any left for printf arguments?
364 $sql .= _db_printf($where, $args);
374 # like db_update() above, but instead of passing columns and data separately,
375 # you can pass one array with the column names as keys and the data as values
376 function db_update_assoc($table, $data) {
377 $args = func_get_args();
378 $args = array_slice($args, 2);
381 foreach($data as $key => $value) {
385 array_unshift($args, $values);
386 array_unshift($args, join(',', $columns));
387 array_unshift($args, $table);
388 call_user_func_array('db_update', $args);
391 # pass args for printf-style where clause as usual
392 function db_delete($table, $where = '') {
393 $sql = "DELETE FROM $table";
396 $args = func_get_args();
397 $args = array_slice($args, 2);
399 $sql .= _db_printf($where, $args);
409 define('DB_ORD_MAX', 2000000000);
411 function db_reposition_respace($table, $field) {
412 $ids = db_get_column($table, 'id', "where $field != 0 order by $field");
415 # should never happen
418 $inc = floor(DB_ORD_MAX / ($c + 1));
420 foreach($ids as $id) {
421 db_update($table, $field, $cur, 'where id=%i', $id);
426 # this function facilitates letting the user manually sort records (with (int) $field != 0)
428 # When editing a particular row, give the user a pulldown, with 0 -> first, 1 -> second, etc, and pass this integer to db_reposition (3rd parameter). The value "ignored" can be passed, and the row will be given a sort value of 0 and ignored for all sorting.
430 # $pretty is used in error messages to refer to the row, it defaults to whatever you pass for $table.
432 # return value is the "ord" value you should set/insert into your database
434 function db_reposition($table, $row_id, $new_pos, $field = 'ord', $pretty = 'same as $table', $renumbered_already = false) {
435 if($pretty == 'same as $table') {
439 if($new_pos === 'ignored') {
444 # strategy: calculate $prev_ord and $next_ord. If there's no space between, renumber and recurse
445 if($new_pos == '0') {
446 $row = db_get_row($table, "id,$field", "where $field != 0 order by $field limit 1");
448 list($first_row_id, $first_row_ord) = $row;
449 if($first_row_id == $row_id) {
451 return $first_row_ord;
453 $next_ord = $first_row_ord;
455 # this is the only row, put it in the middle
456 return '' + floor(DB_ORD_MAX / 2);
461 $new_pos = format_int_0($new_pos);
462 $rows = db_get_rows($table, "id,$field", "where $field != 0 order by $field limit %i,2", $new_pos - 1);
464 message("Sorry, couldn't find the $pretty you asked to put this $pretty after. Putting it first instead.");
465 return db_reposition($table, $row_id, '0', $field, $pretty);
467 list($prev_id, $prev_ord) = $rows[0];
468 if($prev_id == $row_id) {
469 # after self? this shouldn't happen
472 if(count($rows) == 1) {
474 $next_ord = DB_ORD_MAX;
476 list($next_id, $next_ord) = $rows[1];
477 if($next_id == $row_id) {
478 # after prev (already there)
484 if($prev_ord + 1 == $next_ord || $prev_ord == $next_ord) { # the latter should never happen
485 if($renumbered_already) {
486 message("Programmer error in $pretty ordering code. Please tell your website administrator.");
487 return '' . rand(2, DB_ORD_MAX - 2); # reasonably unlikely to be the same as some other ord
489 db_reposition_respace($table, $field);
490 return db_reposition($table, $row_id, $new_pos, $field, $pretty, $renumbered_already = true);
492 return $prev_ord + round(($next_ord - $prev_ord) / 2);