. require_once(__DIR__ . '/encode.php'); require_once(__DIR__ . '/format.php'); # db_connect() -- connect to a mysql database # # PARAMETERS: # # database: the name of the database you want to connect to. Defaults to the # second-to-last part of the domain name. eg for foo.example.com it would be # "example". # # user: username for connecting to the database. Defaults to # $GLOBALS['db_username'] or (if that's not set) "www". # # password: password for connecting to the database. Defaults to # $GLOBALS['db_password'] or (if that's not set "". # # RETURNS: # # the database connection handle. You'll only need this if you want to have # multiple databases open at once. function db_enc_sql($str) { return mysql_real_escape_string($str, $GLOBALS['wfpl_db_handle'] ? $GLOBALS['wfpl_db_handle'] : null); } function db_connect($database = 'auto', $user = 'auto', $pass = 'auto', $host = 'localhost', $encoding = 'utf8') { if($database == 'auto') { if(isset($GLOBALS['db_name'])) { $database = $GLOBALS['db_name']; } else { $host = $_SERVER['SERVER_NAME']; $host = explode('.', $host); array_pop($host); $database = array_pop($host); unset($host); } } if($user == 'auto') { if(isset($GLOBALS['db_username'])) { $user = $GLOBALS['db_username']; } else { $user = 'www'; } } if($pass == 'auto') { if(isset($GLOBALS['db_password'])) { $pass = $GLOBALS['db_password']; } else { $pass = ''; } } $GLOBALS['wfpl_db_handle'] = mysql_connect($host, $user, $pass); if(!$GLOBALS['wfpl_db_handle']) { die('Could not connect to the database: ' . mysql_error()); } mysql_set_charset($encoding, $GLOBALS['wfpl_db_handle']); if(!mysql_select_db($database, $GLOBALS['wfpl_db_handle'])) { die("Couldn not access database \"$database\": " . mysql_error($GLOBALS['wfpl_db_handle'])); } return $GLOBALS['wfpl_db_handle']; } # Unless you're doing something unusual like an ALTER TABLE don't call this directly function db_send_query($sql) { #echo("Sending query: " . enc_html($sql) . "
\n"); $result = mysql_query($sql, $GLOBALS['wfpl_db_handle']); if(!$result) { die(enc_html('DATABASE ERROR: ' . mysql_error($GLOBALS['wfpl_db_handle']) . ' in the following query: ' . $sql)); } return $result; } # All select queries use this to generate the where clause, so they can work # like printf. Currently three % codes are supported: # # %% put a % in the output # %i put an integer in the output (strips non-numeric digits, and puts in 0 if blank) # %f put a floating point value in the output (strips non-numeric digits, puts in 0.0 if not valid) # %" output double quotes, surrounding the variable which is encoded to be in there. # %s output encoded to be in double quotes, but don't output the quotes # %$ output argument as-is, no encoding. Make sure you quote everything from the user! # # complex example: db_get_rows('mytable', 'id', 'where name=%" or company like "%%%s%%"', $name, $company_partial); function db_printf($str) { $args = func_get_args(); $args = array_slice($args, 1); return _db_printf($str, $args); } # This function does the work, but takes the parameters in an array function _db_printf($str, $args) { $out = ''; while($str) { $pos = strpos($str, '%'); if($pos === false) { # not found # we hit the end. return $out . $str; } # move everything up to (but not including) % to the output $out .= substr($str, 0, $pos); # grab the character after the % $chr = substr($str, $pos + 1, 1); # remove the stuff we've read from input $str = substr($str, $pos + 2); if($chr == '"') { $out .= '"' . db_enc_sql(array_shift($args)) . '"'; } elseif($chr == 's') { $out .= db_enc_sql(array_shift($args)); } elseif($chr == 'i') { $int = format_int(array_shift($args)); if($int == '') $int = '0'; $out .= $int; } elseif($chr == 'f') { $arg = array_shift($args); if(is_numeric($arg)) { $arg = sprintf("%f", $arg); } $arg = format_decimal($arg); if(strlen($arg) < 1) { $arg = '0.0'; } $out .= $arg; } elseif($chr == '$') { $out .= array_shift($args); } else { $out .= $chr; } } return $out; } function db_send_get($table, $columns, $where, $args) { $sql = "SELECT $columns FROM $table"; if($where) { $sql .= ' ' . _db_printf($where, $args); } return db_send_query($sql); } function db_get_rows($table, $columns, $where = '') { $args = func_get_args(); $args = array_slice($args, 3); $result = db_send_get($table, $columns, $where, $args); $rows = array(); while($row = mysql_fetch_row($result)) { $rows[] = $row; } mysql_free_result($result); return $rows; } # like db_get_rows, but return array of hashes. function db_get_assocs($table, $columns, $where = '') { $args = func_get_args(); $args = array_slice($args, 3); $result = db_send_get($table, $columns, $where, $args); $rows = array(); while($row = mysql_fetch_assoc($result)) { $rows[] = $row; } mysql_free_result($result); return $rows; } function db_get_column($table, $columns, $where = '') { $args = func_get_args(); $args = array_slice($args, 3); $result = db_send_get($table, $columns, $where, $args); $column = array(); while($row = mysql_fetch_row($result)) { $column[] = $row[0]; } mysql_free_result($result); return $column; } function db_get_row($table, $columns, $where = '') { $args = func_get_args(); $args = array_slice($args, 3); $result = db_send_get($table, $columns, $where, $args); $row = mysql_fetch_row($result); mysql_free_result($result); return $row; } # like db_get_row, but return a hash. function db_get_assoc($table, $columns, $where = '') { $args = func_get_args(); $args = array_slice($args, 3); $result = db_send_get($table, $columns, $where, $args); $row = mysql_fetch_assoc($result); mysql_free_result($result); return $row; } function db_get_value($table, $columns, $where = '') { $args = func_get_args(); $args = array_slice($args, 3); $result = db_send_get($table, $columns, $where, $args); $value = mysql_fetch_row($result); if($value !== false) { $value = $value[0]; } mysql_free_result($result); return $value; } function db_count($table, $where = '') { $args = func_get_args(); array_splice($args, 1, 0, array('count(*)')); return call_user_func_array('db_get_value', $args); } # call either of these ways: # # db_insert('people', 'name,company', 'jason', 'widgets ltd'); # or # db_insert('people', 'name,company', array('jason', 'widgets ltd')); function db_insert($table, $columns, $values) { if(!is_array($values)) { $values = func_get_args(); $values = array_slice($values, 2); } db_insert_ish('INSERT', $table, $columns, $values); } # like db_insert() above, but instead of passing columns and data separately, # you can pass one array with the column names as keys and the data as values function db_insert_assoc($table, $data) { $args = func_get_args(); $args = array_slice($args, 2); $columns = array(); $values = array(); foreach($data as $key => $value) { $columns[] = $key; $values[] = $value; } array_unshift($args, $table, join(',', $columns), $values); call_user_func_array('db_insert', $args); } # same as above, except uses the "replace" command instead of "insert" function db_replace($table, $columns, $values) { if(!is_array($values)) { $values = func_get_args(); $values = array_slice($values, 2); } db_insert_ish('REPLACE', $table, $columns, $values); } # return the value mysql made up for the auto_increment field (for the last insert) function db_auto_id() { return mysql_insert_id($GLOBALS['wfpl_db_handle']); } # used to implement db_insert() and db_replace() function db_insert_ish($command, $table, $columns, $values) { $sql = ''; foreach($values as $value) { if($sql) $sql .= ','; $sql .= '"' . db_enc_sql($value) . '"'; } $sql = "$command INTO $table ($columns) values($sql)"; db_send_query($sql); } # to be consistent with the syntax of the other db functions, $values can be an # array, a single value, or multiple parameters. # # as usual the where clause stuff is optional, but it will of course update the # whole table if you leave it off. # # examples: # # # name everybody Bruce # db_update('users', 'name', 'Bruce'); # # # name user #6 Bruce # db_update('users', 'name', 'Bruce', 'where id=%i', 6); # # # update the whole bit for user #6 # db_update('users', 'name,email,description', 'Bruce', 'bruce@example.com', 'is a cool guy', 'where id=%i', 6); # # # update the whole bit for user #6 (passing data as an array) # $data = array('Bruce', 'bruce@example.com', 'is a cool guy'); # db_update('users', 'name,email,description', $data, 'where id=%i', 6); # The prototype is really something like this: # db_update(table, columns, values..., where(optional), where_args...(optional)) function db_update($table, $columns, $values) { $args = func_get_args(); $args = array_slice($args, 2); $columns = explode(',', $columns); $num_fields = count($columns); if(is_array($values)) { $values = array_values($values); $args = array_slice($args, 1); } else { $values = array_slice($args, 0, $num_fields); $args = array_slice($args, $num_fields); } $sql = ''; for($i = 0; $i < $num_fields; ++$i) { if($sql != '') { $sql .= ', '; } $sql .= $columns[$i] . ' = "' . db_enc_sql($values[$i]) . '"'; } $sql = "UPDATE $table SET $sql"; # if there's any more arguments if($args) { $where = $args[0]; $args = array_slice($args, 1); $sql .= ' '; # any left for printf arguments? if($args) { $sql .= _db_printf($where, $args); } else { $sql .= $where; } } db_send_query($sql); } # like db_update() above, but instead of passing columns and data separately, # you can pass one array with the column names as keys and the data as values function db_update_assoc($table, $data) { $args = func_get_args(); $args = array_slice($args, 2); $columns = array(); $values = array(); foreach($data as $key => $value) { $columns[] = $key; $values[] = $value; } array_unshift($args, $values); array_unshift($args, join(',', $columns)); array_unshift($args, $table); call_user_func_array('db_update', $args); } # pass args for printf-style where clause as usual function db_delete($table, $where = '') { $sql = "DELETE FROM $table"; if($where) { $sql .= ' '; $args = func_get_args(); $args = array_slice($args, 2); if($args) { $sql .= _db_printf($where, $args); } else { $sql .= $where; } } db_send_query($sql); } define('DB_ORD_MAX', 2000000000); function db_reposition_respace($table, $field, $where = '') { if($where) { $andand = " && ($where) "; } $ids = db_get_column($table, 'id', "where $field != 0 $andand order by $field"); $c = count($ids); if(!$c) { # should never happen return; } $inc = floor(DB_ORD_MAX / ($c + 1)); $cur = $inc; foreach($ids as $id) { db_update($table, $field, $cur, 'where id=%i', $id); $cur += $inc; } } # this function facilitates letting the user manually sort records (with (int) $field != 0) # # 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. # # $pretty is used in error messages to refer to the row, it defaults to whatever you pass for $table. # # return value is the "ord" value you should set/insert into your database function db_reposition($table, $row_id, $new_pos, $field = 'ord', $pretty = 'same as $table', $where = '', $renumbered_already = false) { if($pretty == 'same as $table') { $pretty = $table; } if($where) { $andand = " && ($where) "; } if($new_pos === 'ignored') { # not sorted return '0'; } # strategy: calculate $prev_ord and $next_ord. If there's no space between, renumber and recurse if($new_pos == '0') { $row = db_get_row($table, "id,$field", "where $field != 0 $andand order by $field limit 1"); if($row) { list($first_row_id, $first_row_ord) = $row; if($first_row_id == $row_id) { # already first return $first_row_ord; } $next_ord = $first_row_ord; } else { # this is the only row, put it in the middle return '' + floor(DB_ORD_MAX / 2); } $prev_ord = 0; } else { $new_pos = format_int_0($new_pos); $rows = db_get_rows($table, "id,$field", "where $field != 0 $andand order by $field limit %i,2", $new_pos - 1); if(!$rows) { message("Sorry, couldn't find the $pretty you asked to put this $pretty after. Putting it first instead."); return db_reposition($table, $row_id, '0', $field, $pretty, $where); } else { list($prev_id, $prev_ord) = $rows[0]; if($prev_id == $row_id) { # after self? this shouldn't happen return $prev_ord; } if(count($rows) == 1) { # we should be last $next_ord = DB_ORD_MAX; } else { list($next_id, $next_ord) = $rows[1]; if($next_id == $row_id) { # after prev (already there) return $next_ord; } } } } if($prev_ord + 1 == $next_ord || $prev_ord == $next_ord) { # the latter should never happen if($renumbered_already) { message("Programmer error in $pretty ordering code. Please tell your website administrator."); return '' . rand(2, DB_ORD_MAX - 2); # reasonably unlikely to be the same as some other ord } db_reposition_respace($table, $field, $where); return db_reposition($table, $row_id, $new_pos, $field, $pretty, $where, $renumbered_already = true); } else { return $prev_ord + round(($next_ord - $prev_ord) / 2); } }