diff options
Diffstat (limited to 'php/lib/db.php')
| -rw-r--r-- | php/lib/db.php | 249 |
1 files changed, 60 insertions, 189 deletions
diff --git a/php/lib/db.php b/php/lib/db.php index 3f9435f..6882064 100644 --- a/php/lib/db.php +++ b/php/lib/db.php @@ -5,60 +5,51 @@ * @subpackage lib */ +class DB { + + private $dsn; + private $result; /** * 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 + * @param string $password db password + * @return PDO dbh */ -function db_connect($host='localhost',$user=null,$password=null) +public static function connect($host='localhost',$user=null,$password=null,$database=null) { - static $dbh = null; if (!empty($host) && isset($user) && isset($password)) { - $dbh = @mysql_connect($host,$user,$password); - } - if (is_resource($dbh)) { - return $dbh; + $dsn = "mysql:host={$host}"; + if(!empty($database)) $dsn .= ";database={$database}"; + $options = [PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]; + static::$dbh = new PDO($dsn, $user, $password, $options); } - 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()); + if (static::$dbh instanceof PDO) { + return static::$dbh; } - + die("Unable to create database connection in DB::connect()"); } /** * Execute a MySQL query. * @param string $qry MySQL query - * @param resource $dbh valid dbh */ -function db_query($qry=null,$dbh=null) +public static function query($qry=null, $parameters=[]) { - static $result = null; - if(!is_resource($dbh)) $dbh = db_connect(); + if(!(static::$dbh instanceof PDO)) static::$dbh = static::connect(); if(is_null($qry)) { - if(is_resource($result)) return $result; + if(static::$result instanceof PDOStatement) return static::$result; else return false; } - else - { - $result = @mysql_query($qry,$dbh); - return $result; + if(!empty($parameters)) { + static::$result = static::$dbh->prepare($query); + static::$result->execute($parameters); } + else { + static::$result = static::$dbh->query($qry); + } + return static::$result; } /** @@ -66,198 +57,90 @@ function db_query($qry=null,$dbh=null) * @param string $result (default to null) * @return array */ -function db_fetch($result=null,$type=MYSQL_BOTH) +public static function fetch($result=null,$type=PDO::FETCH_BOTH) { - return (!is_resource($result))? @mysql_fetch_array(db_query()) : @mysql_fetch_array($result,$type); + if (is_null($result) && static::$result instanceof PDOStatement) + $result = static::$result; + if (!$result instanceof PDOStatement) + throw new InvalidArgumentException("Fetch called before query issued"); + return $result->fetch($type); } /** - * Fetch an array based on a query. + * 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); + * DB::get("SELECT * FROM table",PDO::FETCH_ASSOC); * returns... * Array * ( * [0] => Array * ( * [id] => 1 - * [field1] => data1 + * [field1] => data1 * [field2] => data2 * ) * * ) * </code> */ -function db_get($query,$type=MYSQL_BOTH,$col_id=NULL) +public static function get($query,$type=PDO::FETCH_BOTH,$col_id=NULL,$parameters=[]) { - $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); + $res = static::query($query, $parameters); + $list = []; + if ($res instanceof PDOStatement && !is_null($col_id) && ($type === PDO::FETCH_BOTH || $type == PDO::FETCH_ASSOC) && $res->rowCount() !== 0) { + $col_test = static::fetch($res,$type); if (array_key_exists($col_id,$col_test)) { - while ( $buf = db_fetch($res,$type) ) { + $list[$col_test[$col_id]] = $col_test; + while ( $buf = static::fetch($res,$type) ) { $list[$buf[$col_id]] = $buf; } return $list; } } - while ( $buf = db_fetch($res,$type) ) { - $list[] = $buf; + if ($res instanceof PDOStatement) { + $list = $res->fetchAll($type); } 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 + * @param PDO $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) +public static function 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]; + if(!($dbh instanceof PDO)) $dbh = static::connect(); + $buf = $dbh->lastInsertId(); + return empty($buf) ? false : $buf; } /** * Determine number of rows in result. - * @param resource $result mysql result + * @param PDOStatement $result mysql result * @return int number of rows in query result */ -function db_numrows($result=null) +function numrows($result=null) { - return (!is_resource($result))? @mysql_num_rows(db_query()) : @mysql_num_rows($result); + if (is_null($result) && static::$result instanceof PDOStatement) + $result = static::$result; + if (!$result instanceof PDOStatement) + throw new InvalidArgumentException("numrows called before query issued"); + return $result->rowCount(); } /** * 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) +public static function close($dbh=null) { - return is_resource($dbh)?@mysql_close($dbh):@mysql_close(); + return ($dbh instanceof PDO)?$dbh=null:static::$dbh=null; } /** @@ -265,8 +148,8 @@ function db_close($dbh=null) * @param string $query query * @param int $type result type */ -function db_get_one($query,$type=MYSQL_ASSOC) { - $buf = db_get($query.' LIMIT 1',$type); +public static function get_one($query,$type=PDO::FETCH_ASSOC,$parameters=[]) { + $buf = static::get($query.' LIMIT 1',$type,$parameters); return $buf[0]; } @@ -277,22 +160,10 @@ function db_get_one($query,$type=MYSQL_ASSOC) { * @param string $name_col * @param string $name */ -function db_name_to_id($table,$id_col,$name_col,$name) +public static function name_to_id($table,$id_col,$name_col,$name) { - $buf = db_get_one("SELECT {$id_col} FROM {$table} WHERE {$name_col} = '{$name}'", MYSQL_NUM); + $buf = static::get_one("SELECT {$id_col} FROM {$table} WHERE {$name_col} = :name", PDO::FETCH_NUM, [':name' => $name]); 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}"); } -?> |