path: root/php/lib/db.php

<?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}");
}
?>