3 # This program is in the public domain within the United States. Additionally,
4 # we waive copyright and related rights in the work worldwide through the CC0
5 # 1.0 Universal public domain dedication, which can be found at
6 # http://creativecommons.org/publicdomain/zero/1.0/
9 require_once(__DIR__.'/'.'encode.php');
10 require_once(__DIR__.'/'.'format.php');
12 # db_connect() -- connect to a mysql database
16 # database: the name of the database you want to connect to. Defaults to the
17 # second-to-last part of the domain name. eg for foo.example.com it would be
20 # user: username for connecting to the database. Defaults to
21 # $GLOBALS['db_username'] or (if that's not set) "www".
23 # password: password for connecting to the database. Defaults to
24 # $GLOBALS['db_password'] or (if that's not set "".
28 # the database connection handle. You'll only need this if you want to have
29 # multiple databases open at once.
31 function db_enc_sql($str) {
32 return mysqli_real_escape_string(isset($GLOBALS['wfpl_db_handle']) ? $GLOBALS['wfpl_db_handle'] : null, $str);
35 function db_connect($database = 'auto', $user = 'auto', $pass = 'auto', $host = 'localhost', $encoding = 'utf8') {
36 if($database == 'auto') {
37 if(isset($GLOBALS['db_name'])) {
38 $database = $GLOBALS['db_name'];
40 $host = $_SERVER['SERVER_NAME'];
41 $host = explode('.', $host);
43 $database = array_pop($host);
49 if(isset($GLOBALS['db_username'])) {
50 $user = $GLOBALS['db_username'];
57 if(isset($GLOBALS['db_password'])) {
58 $pass = $GLOBALS['db_password'];
64 $GLOBALS['wfpl_db_handle'] = mysqli_connect($host, $user, $pass);
65 if(!$GLOBALS['wfpl_db_handle']) {
66 die('Could not connect to the database: ' . mysqli_error());
69 mysqli_set_charset($GLOBALS['wfpl_db_handle'], $encoding);
71 if(!mysqli_select_db($GLOBALS['wfpl_db_handle'], $database)) {
72 die("Couldn not access database \"$database\": " . mysqli_error($GLOBALS['wfpl_db_handle']));
75 return $GLOBALS['wfpl_db_handle'];
78 # Unless you're doing something unusual like an ALTER TABLE don't call this directly
79 function db_send_query($sql) {
80 #echo("Sending query: " . enc_html($sql) . "<br>\n");
81 $result = mysqli_query($GLOBALS['wfpl_db_handle'], $sql);
83 die(enc_html('DATABASE ERROR: ' . mysqli_error($GLOBALS['wfpl_db_handle']) . ' in the following query: ' . $sql));
89 # All select queries use this to generate the where clause, so they can work
90 # like printf. Currently three % codes are supported:
92 # %% put a % in the output
93 # %i put an integer in the output (strips non-numeric digits, and puts in 0 if blank)
94 # %f put a floating point value in the output (strips non-numeric digits, puts in 0.0 if not valid)
95 # %" output double quotes, surrounding the variable which is encoded to be in there.
96 # %s output encoded to be in double quotes, but don't output the quotes
97 # %$ output argument as-is, no encoding. Make sure you quote everything from the user!
99 # complex example: db_get_rows('mytable', 'id', 'where name=%" or company like "%%%s%%"', $name, $company_partial);
101 function db_printf($str) {
102 $args = func_get_args();
103 $args = array_slice($args, 1);
104 return _db_printf($str, $args);
107 # This function does the work, but takes the parameters in an array
108 function _db_printf($str, $args) {
111 $pos = strpos($str, '%');
112 if($pos === false) { # not found
116 # move everything up to (but not including) % to the output
117 $out .= substr($str, 0, $pos);
119 # grab the character after the %
120 $chr = substr($str, $pos + 1, 1);
122 # remove the stuff we've read from input
123 $str = substr($str, $pos + 2);
126 $out .= '"' . db_enc_sql(array_shift($args)) . '"';
127 } elseif($chr == 's') {
128 $out .= db_enc_sql(array_shift($args));
129 } elseif($chr == 'i') {
130 $int = format_int(array_shift($args));
131 if($int == '') $int = '0';
133 } elseif($chr == 'f') {
134 $arg = array_shift($args);
135 if(is_numeric($arg)) {
136 $arg = sprintf("%f", $arg);
138 $arg = format_decimal($arg);
139 if(strlen($arg) < 1) {
143 } elseif($chr == '$') {
144 $out .= array_shift($args);
155 function db_send_get($table, $columns, $where, $args) {
156 $sql = "SELECT $columns FROM $table";
158 $sql .= ' ' . _db_printf($where, $args);
161 return db_send_query($sql);
165 # if no results: returs []
166 function db_get_rows($table, $columns, $where = '') {
167 $args = func_get_args();
168 $args = array_slice($args, 3);
169 $result = db_send_get($table, $columns, $where, $args);
172 while($row = mysqli_fetch_row($result)) {
176 mysqli_free_result($result);
181 # like db_get_rows, but return array of hashes.
182 # if no results: returs []
183 function db_get_assocs($table, $columns, $where = '') {
184 $args = func_get_args();
185 $args = array_slice($args, 3);
186 $result = db_send_get($table, $columns, $where, $args);
189 while($row = mysqli_fetch_assoc($result)) {
193 mysqli_free_result($result);
198 # if no results: returs []
199 function db_get_column($table, $columns, $where = '') {
200 $args = func_get_args();
201 $args = array_slice($args, 3);
202 $result = db_send_get($table, $columns, $where, $args);
205 while($row = mysqli_fetch_row($result)) {
209 mysqli_free_result($result);
214 # returns first matching row
215 # if no results: returns false
216 function db_get_row($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 = mysqli_fetch_row($result);
223 mysqli_free_result($result);
228 # like db_get_row, but return a hash.
229 # if no results: returns false
230 function db_get_assoc($table, $columns, $where = '') {
231 $args = func_get_args();
232 $args = array_slice($args, 3);
233 $result = db_send_get($table, $columns, $where, $args);
235 $row = mysqli_fetch_assoc($result);
237 mysqli_free_result($result);
242 # if no results: returns false
243 function db_get_value($table, $column, $where = '') {
244 $args = func_get_args();
245 $args = array_slice($args, 3);
246 $result = db_send_get($table, $column, $where, $args);
248 $value = mysqli_fetch_row($result);
249 if($value !== false) {
253 mysqli_free_result($result);
259 function db_count($table, $where = '') {
260 $args = func_get_args();
261 array_splice($args, 1, 0, array('count(*)'));
262 return (int) call_user_func_array('db_get_value', $args);
265 # call either of these ways:
267 # db_insert('people', 'name,company', 'jason', 'widgets ltd');
269 # db_insert('people', 'name,company', array('jason', 'widgets ltd'));
270 function db_insert($table, $columns, $values) {
271 if(!is_array($values)) {
272 $values = func_get_args();
273 $values = array_slice($values, 2);
276 db_insert_ish('INSERT', $table, $columns, $values);
279 # like db_insert() above, but instead of passing columns and data separately,
280 # you can pass one array with the column names as keys and the data as values
281 function db_insert_assoc($table, $data) {
282 $args = func_get_args();
283 $args = array_slice($args, 2);
286 foreach($data as $key => $value) {
290 array_unshift($args, $table, join(',', $columns), $values);
291 call_user_func_array('db_insert', $args);
294 # same as above, except uses the "replace" command instead of "insert"
295 function db_replace($table, $columns, $values) {
296 if(!is_array($values)) {
297 $values = func_get_args();
298 $values = array_slice($values, 2);
301 db_insert_ish('REPLACE', $table, $columns, $values);
304 # return the value mysql made up for the auto_increment field (for the last insert)
305 function db_auto_id() {
306 return mysqli_insert_id($GLOBALS['wfpl_db_handle']);
310 # used to implement db_insert() and db_replace()
311 function db_insert_ish($command, $table, $columns, $values) {
314 foreach($values as $value) {
315 if($sql) $sql .= ',';
316 $sql .= '"' . db_enc_sql($value) . '"';
319 $sql = "$command INTO $table ($columns) values($sql)";
324 # to be consistent with the syntax of the other db functions, $values can be an
325 # array, a single value, or multiple parameters.
327 # as usual the where clause stuff is optional, but it will of course update the
328 # whole table if you leave it off.
332 # # name everybody Bruce
333 # db_update('users', 'name', 'Bruce');
335 # # name user #6 Bruce
336 # db_update('users', 'name', 'Bruce', 'where id=%i', 6);
338 # # update the whole bit for user #6
339 # db_update('users', 'name,email,description', 'Bruce', 'bruce@example.com', 'is a cool guy', 'where id=%i', 6);
341 # # update the whole bit for user #6 (passing data as an array)
342 # $data = array('Bruce', 'bruce@example.com', 'is a cool guy');
343 # db_update('users', 'name,email,description', $data, 'where id=%i', 6);
345 # The prototype is really something like this:
346 # db_update(table, columns, values..., where(optional), where_args...(optional))
347 function db_update($table, $columns, $values) {
348 $args = func_get_args();
349 $args = array_slice($args, 2);
350 $columns = explode(',', $columns);
351 $num_fields = count($columns);
353 if(is_array($values)) {
354 $values = array_values($values);
355 $args = array_slice($args, 1);
357 $values = array_slice($args, 0, $num_fields);
358 $args = array_slice($args, $num_fields);
362 for($i = 0; $i < $num_fields; ++$i) {
366 $sql .= $columns[$i] . ' = "' . db_enc_sql($values[$i]) . '"';
370 $sql = "UPDATE $table SET $sql";
372 # if there's any more arguments
375 $args = array_slice($args, 1);
378 # any left for printf arguments?
380 $sql .= _db_printf($where, $args);
390 # like db_update() above, but instead of passing columns and data separately,
391 # you can pass one array with the column names as keys and the data as values
392 function db_update_assoc($table, $data) {
393 $args = func_get_args();
394 $args = array_slice($args, 2);
397 foreach($data as $key => $value) {
401 array_unshift($args, $values);
402 array_unshift($args, join(',', $columns));
403 array_unshift($args, $table);
404 call_user_func_array('db_update', $args);
407 # pass args for printf-style where clause as usual
408 function db_delete($table, $where = '') {
409 $sql = "DELETE FROM $table";
412 $args = func_get_args();
413 $args = array_slice($args, 2);
415 $sql .= _db_printf($where, $args);
425 define('DB_ORD_MAX', 2000000000);
427 function db_reposition_respace($table, $field, $where = '') {
429 $andand = " && ($where) ";
431 $ids = db_get_column($table, 'id', "where $field != 0 $andand order by $field");
434 # should never happen
437 $inc = floor(DB_ORD_MAX / ($c + 1));
439 foreach($ids as $id) {
440 db_update($table, $field, $cur, 'where id=%i', $id);
445 # this function facilitates letting the user manually sort records (with (int) $field != 0)
447 # 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.
449 # $pretty is used in error messages to refer to the row, it defaults to whatever you pass for $table.
451 # return value is the "ord" value you should set/insert into your database
453 function db_reposition($table, $row_id, $new_pos, $field = 'ord', $pretty = 'same as $table', $where = '', $renumbered_already = false) {
454 if($pretty == 'same as $table') {
458 $andand = " && ($where) ";
461 if($new_pos === 'ignored') {
466 # strategy: calculate $prev_ord and $next_ord. If there's no space between, renumber and recurse
467 if($new_pos == '0') {
468 $row = db_get_row($table, "id,$field", "where $field != 0 $andand order by $field limit 1");
470 list($first_row_id, $first_row_ord) = $row;
471 if($first_row_id == $row_id) {
473 return $first_row_ord;
475 $next_ord = $first_row_ord;
477 # this is the only row, put it in the middle
478 return '' + floor(DB_ORD_MAX / 2);
483 $new_pos = format_int_0($new_pos);
484 $rows = db_get_rows($table, "id,$field", "where $field != 0 $andand order by $field limit %i,2", $new_pos - 1);
486 message("Sorry, couldn't find the $pretty you asked to put this $pretty after. Putting it first instead.");
487 return db_reposition($table, $row_id, '0', $field, $pretty, $where);
489 list($prev_id, $prev_ord) = $rows[0];
490 if($prev_id == $row_id) {
491 # after self? this shouldn't happen
494 if(count($rows) == 1) {
496 $next_ord = DB_ORD_MAX;
498 list($next_id, $next_ord) = $rows[1];
499 if($next_id == $row_id) {
500 # after prev (already there)
506 if($prev_ord + 1 == $next_ord || $prev_ord == $next_ord) { # the latter should never happen
507 if($renumbered_already) {
508 message("Programmer error in $pretty ordering code. Please tell your website administrator.");
509 return '' . rand(2, DB_ORD_MAX - 2); # reasonably unlikely to be the same as some other ord
511 db_reposition_respace($table, $field, $where);
512 return db_reposition($table, $row_id, $new_pos, $field, $pretty, $where, $renumbered_already = true);
514 return $prev_ord + round(($next_ord - $prev_ord) / 2);