diff options
Diffstat (limited to 'php/lib/db.php')
| -rw-r--r-- | php/lib/db.php | 298 |
1 files changed, 298 insertions, 0 deletions
diff --git a/php/lib/db.php b/php/lib/db.php new file mode 100644 index 0000000..3f9435f --- /dev/null +++ b/php/lib/db.php @@ -0,0 +1,298 @@ +<?php +/** + * Minimal wrappers for core PHP mysql_* functions. + * @package mirror + * @subpackage lib + */ + +/** + * Connect to a MySQL database server. + * @param string $host db server, defaults to localhost + * @param string $user db username + * @param string $password db password + * @return resource dbh + */ +function db_connect($host='localhost',$user=null,$password=null) +{ + static $dbh = null; + if (!empty($host) && isset($user) && isset($password)) { + $dbh = @mysql_connect($host,$user,$password); + } + if (is_resource($dbh)) { + return $dbh; + } + else die("Unable to create database connection in db_connect()"); +} + +/** + * Select database. + * @param string $database name of the database to select + * @param resource $dbh valid dbh, null if not defined + * @return bool success of command + */ +function db_select($database,$dbh=null) +{ + if(is_resource($dbh)){ + return @mysql_select_db($database); + }else{ + return @mysql_select_db($database, db_connect()); + } + +} + +/** + * Execute a MySQL query. + * @param string $qry MySQL query + * @param resource $dbh valid dbh + */ +function db_query($qry=null,$dbh=null) +{ + static $result = null; + if(!is_resource($dbh)) $dbh = db_connect(); + if(is_null($qry)) + { + if(is_resource($result)) return $result; + else return false; + } + else + { + $result = @mysql_query($qry,$dbh); + return $result; + } +} + +/** + * Fetch a row as an array from a result. + * @param string $result (default to null) + * @return array + */ +function db_fetch($result=null,$type=MYSQL_BOTH) +{ + return (!is_resource($result))? @mysql_fetch_array(db_query()) : @mysql_fetch_array($result,$type); +} + +/** + * Fetch an array based on a query. + * @param string $query database query + * @param int $type result type + * @param string $col_id if passed it, the values of this column in the result set will be used as the array keys in the returned array + * @return array $list array of database rows + * Example of returned array: + * <code> + * db_get("SELECT * FROM table",MYSQL_ASSOC); + * returns... + * Array + * ( + * [0] => Array + * ( + * [id] => 1 + * [field1] => data1 + * [field2] => data2 + * ) + * + * ) + * </code> + */ +function db_get($query,$type=MYSQL_BOTH,$col_id=NULL) +{ + $res = db_query($query); + $list = array(); + if (is_resource($res) && !is_null($col_id) && ($type == MYSQL_BOTH || $type == MYSQL_ASSOC) && @mysql_num_rows($res) !== 0) { + $col_test = db_fetch($res,$type); + @mysql_data_seek($res, 0); + if (array_key_exists($col_id,$col_test)) { + while ( $buf = db_fetch($res,$type) ) { + $list[$buf[$col_id]] = $buf; + } + return $list; + } + } + while ( $buf = db_fetch($res,$type) ) { + $list[] = $buf; + } + return $list; +} + +/** + * Get all of the fieldnames for the specified table. + * @param string $table name of table to describe + * @return array array of column names, must be an array + */ +function db_fieldnames($table) +{ + $dbh = db_connect(); + $results = db_query("DESCRIBE $table"); + if (is_resource($results)) + { + while ($buf=db_fetch($results)) + { + $field_names[] = $buf[0]; + } + } + else + { + $field_names[] = 0; + } + return $field_names; +} + +/** + * Create a MySQL INSERT statement based on $_POST array generated by form submission. + * <ul> + * <li>does not work with mysql functions (PASSWORD, etc.) because there are forced double quotes</li> + * <li>do not use clean_in() before this, or you'll have double the slashes</li> + * <li>use the function only when it saves you time, not _always_</li> + * <li>form items not set will not be processed (unchecked radios, checkboxes) - handle these manually, or don't use the func</li> + * </ul> + * @param array $vars array of posts + * @param string $table name of the table that fields will be inserted into + * @return string $query resulting MySQL insert string + */ +function db_makeinsert($vars,$table) +{ + $dbh = db_connect(); + $fields = db_fieldnames($table); + foreach ($fields as $field) + { + if (get_magic_quotes_gpc) $vars[$field] = stripslashes($vars[$field]); + $vars[$field] = addslashes($vars[$field]); + if (isset($vars[$field])) + { + isset($q1)?$q1 .= ','.$field:$q1='INSERT INTO '.$table.'('.$field; + isset($q2)?$q2 .= ",'$vars[$field]'":$q2=" VALUES('$vars[$field]'"; + } + } + $q1 .= ')'; + $q2 .= ')'; + $query = $q1.$q2; + return $query; +} + +/** + * Create a MySQL REPLACE statement based on $_POST array generated by form submission. + * <ul> + * <li>does not work with mysql functions (PASSWORD, etc.) because there are forced double quotes</li> + * <li>do not use clean_in() before this, or you'll have double the slashes</li> + * <li>use the function only when it saves you time, not _always_</li> + * <li>form items not set will not be processed (unchecked radios, checkboxes) - handle these manually, or don't use the func</li> + * </ul> + * @param array $vars array of posts + * @param string $table name of the table that fields will be inserted into + * @return string $query resulting MySQL insert string + */ +function db_makereplace($vars,$table) +{ + $dbh = db_connect(); + $fields = db_fieldnames($table); + foreach ($fields as $field) + { + if (get_magic_quotes_gpc) $vars[$field] = stripslashes($vars[$field]); + $vars[$field] = addslashes($vars[$field]); + if (isset($vars[$field])) + { + isset($q1)?$q1 .= ','.$field:$q1='REPLACE INTO '.$table.'('.$field; + isset($q2)?$q2 .= ",'$vars[$field]'":$q2=" VALUES('$vars[$field]'"; + } + } + $q1 .= ')'; + $q2 .= ')'; + $query = $q1.$q2; + return $query; +} + +/** + * Create a MySQL UPDATE statement based on $_POST array generated by form submission. + * <ul> + * <li>does not work with mysql functions (PASSWORD, etc.) because there are forced double quotes</li> + * <li>do not use clean_in() before this, or you'll have double the slashes</li> + * <li>use the function only when it saves you time, not _always_</li> + * <li>form items not set will not be processed (unchecked radios, checkboxes) - handle these manually, or don't use the func</li> + * </ul> + * @param array $vars array of posts + * @param string $table name of the table that fields will be inserted into + * @param string $where where clause, describing which records are to be updated + */ +function db_makeupdate($vars,$table,$where) +{ + $dbh = db_connect(); + $fields = db_fieldnames($table); + foreach ($fields as $field) + { + if (isset($vars[$field])) + { + if (get_magic_quotes_gpc()) $vars[$field] = stripslashes($vars[$field]); + $vars[$field]=addslashes($vars[$field]); + $q1 = isset($q1)?$q1 .= ' ,'.$field."='$vars[$field]'":'UPDATE '.$table.' set '.$field."='$vars[$field]'"; + } + } + $query = $q1.' '.$where; + return $query; +} + +/** + * Since PHP's mysql_insert_id() sometimes throws an error, this is the replacement + * @param resource $dbh optional dbh to get the last inserted id from + * @return int the return value of MySQL's last_insert_id() + */ +function db_insert_id($dbh=null) +{ + if(!is_resource($dbh)) $dbh = db_connect(); + $buf = db_fetch(db_query("SELECT LAST_INSERT_ID()", $dbh)); + return empty($buf[0]) ? false : $buf[0]; +} + +/** + * Determine number of rows in result. + * @param resource $result mysql result + * @return int number of rows in query result + */ +function db_numrows($result=null) +{ + return (!is_resource($result))? @mysql_num_rows(db_query()) : @mysql_num_rows($result); +} + +/** + * Close the db connection. If a dbh is not specified, assume the last opened link. + * @param resource $dbh optional dbh to close + */ +function db_close($dbh=null) +{ + return is_resource($dbh)?@mysql_close($dbh):@mysql_close(); +} + +/** + * Get one record. + * @param string $query query + * @param int $type result type + */ +function db_get_one($query,$type=MYSQL_ASSOC) { + $buf = db_get($query.' LIMIT 1',$type); + return $buf[0]; +} + +/** + * Get an ID based on name. + * @param string $table + * @param string $id_col + * @param string $name_col + * @param string $name + */ +function db_name_to_id($table,$id_col,$name_col,$name) +{ + $buf = db_get_one("SELECT {$id_col} FROM {$table} WHERE {$name_col} = '{$name}'", MYSQL_NUM); + return $buf[0]; +} + +/** + * Sets enum booleans to their opposite + * @param string $table + * @param string $pri + * @param string $col + * @param array $id + * @return int + */ +function db_toggle_bool($table, $pri, $col, $id) +{ + return db_query("UPDATE {$table} SET {$col} = IF({$col} = '1', '0', '1') WHERE {$pri} = {$id}"); +} +?> |