Location: PHPKode > projects > SHOUTcast Management Interface > smi-0.3.4/include/db.inc.php
<?
////////////////////////////////////////////////////////////////////////
/*SMI - SHOUTcast Management Interface
A web based shoutcast server management program
Founding Author: Scott D. Harvanek <hide@address.com>

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License along
with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.*/
////////////////////////////////////////////////////////////////////////

//==================================================================
//
//  Based on "A Simple MySQL Class"
//  By: Matthew Saragusa <hide@address.com>
//
//  Modified for SMI to match MDB2 class functions
//  for easier MySQL database operations and cleaner code
//  By: Kristian Resset <hide@address.com>
//
//===================================================================

require_once('config.php');

// DEFINE VARIABLES

define('DB_SERVER', $dbhost);
define('DB_NAME', $dbname);
define('DB_USERNAME', $dbuser);
define('DB_PASSWORD', $dbpass);



// DEFINE MYSQL DATABASE CLASS 

class MySQLDB {
  var $db, $conn;

  public function __construct($server, $database, $username, $password) {
    $this->conn = mysql_connect($server, $username, $password);
    $this->db = mysql_select_db($database,$this->conn);
  }



//////////////////////////////////////////////////////////////////////////////
//
// MySQLDB->insert(<table>, <insert values array>)
// or
// MySQLDB->insert(<query>)
//
// Insert a record with columns and values from <insert values array>.
// A full regular SQL INSERT query can be passed if desired.
// Returns the value of insert_id if applicable.
//
// Example usage:
// $db->insert('users', array('username' => 'admin', 'password' => 'changeme'));
//
// Equals to this call of the insert function if full query is preferred:
// $db->insert("INSERT INTO users (username, password) VALUES ('admin', 'changeme')");
//////////////////////////////////////////////////////////////////////////////

  public function insert($table, $insert_values='') {
    if (strtoupper(substr(trim($table), 0, 6)) == "INSERT") {
      $sql = $table;
    } else {
      foreach($insert_values as $key=>$value) {
        $keys[] = $key;
        $insertvalues[] = '\''.$value.'\'';
      }
      $keys = implode(',', $keys);
      $insertvalues = implode(',', $insertvalues);
      $sql = "INSERT INTO $table ($keys) VALUES ($insertvalues)";
    }
    $this->sqlordie($sql);
    return mysql_insert_id($this->conn);
  }


//////////////////////////////////////////////////////////////////////////////
//
// MySQLDB->delete(<table> [, <condition array>])
// or
// MySQLDB->delete(<query>)
//
// Deletes records from <table> based on <condition array>, if passed.
// A full regular SQL DELETE query can be passed if desired.
// CAUTION! If <condition array> is omitted, all recods in <table> is deleted.
//
// Example usage:
// $db->delete('users', array('username' => 'admin'));
//
// Equals to this call of the delete function if full query is preferred:
// $db->update("DELETE FROM users WHERE username = 'admin'");
//////////////////////////////////////////////////////////////////////////////

  public function delete($table, $cond_array = '') {
    if (strtoupper(substr(trim($table), 0, 6)) == "DELETE") {
      $sql = $table;
    } else {
      $sql = "DELETE FROM $table";
      if (is_array($cond_array)) {
        $sql .= " WHERE ".$this->conditions($cond_array);
      }
    }
    return $this->sqlordie($sql);
  }



//////////////////////////////////////////////////////////////////////////////
//
// MySQLDB->update(<table>, <update set array> [, <condition array>])
// or
// MySQLDB->update(<query>)
//
// Updates records with <update set array> based on <condition array> if set. 
// A full regular SQL SELECT query can be passed if desired.
// CAUTION! If <condition array> is omitted, all records in <table> is updated.
//
// Example usage:
// $db->update('users', array('level' => 'admin'), array('username' => 'john'));
//
// Equals to this call of the update function if full query is preferred:
// $db->update("UPDATE users SET level = 'admin' WHERE username = 'john'");
//////////////////////////////////////////////////////////////////////////////

public function update($table, $update_array, $cond_array = '') {
  if (strtoupper(substr(trim($table), 0, 6)) == "UPDATE") {
    $sql = $table;
  } else {
    foreach($update_array as $key => $value) {
      $sets[] = $key."='".$value."'";
    }
    $sets = implode(',', $sets);
    $sql = "UPDATE $table SET $sets";
    if (is_array($cond_array)) {
      $sql .= " WHERE ".$this->conditions($cond_array);
    }
  }
  return $this->sqlordie($sql);
}

//////////////////////////////////////////////////////////////////////////////
//
// MySQLDB->getRow(<table>, [<condition array> [, <fields> [, <fetch_type>]]])
// or
// MySQLDB->getRow(<query>)
//
// Returns query result as the first single resulting row based on the passed arguments.
// A full regular SQL SELECT query can be passed if desired.
// If <condition array> is omitted, no WHERE clause is used.
// If no <fields> are given, the query equals 'SELECT * FROM <table>...'
// <fetch_type> defaults to MYSQL_BOTH. Can also be MYSQL_NUM or MYSQL_ASSOC.
//
// Example usage: 
// $row = $db->getRow('users', 'username' => 'admin', 'username, password');
// echo "Username ".$row['username']." has password ".$row[1];
//
// $row = $db->getRow("SELECT username, password FROM users WHERE username = 'admin'",,"MYSQL_NUM");
// echo "Username ".$row[0]." has password ".$row[1];
//////////////////////////////////////////////////////////////////////////////
        
public function getRow($table, $cond_array = '', $fields = '*', $fetch_type = MYSQL_BOTH) {
  if (strtoupper(substr(trim($table), 0, 6)) == "SELECT") {
    $sql = $table;
  } else {
    $sql = "SELECT $fields FROM $table";
    if (is_array($cond_array)) {
      $sql .= " WHERE ".$this->conditions($cond_array);
    }
  }
  $result = $this->sqlordie($sql);
  return mysql_fetch_array($result, $fetch_type);
}



//////////////////////////////////////////////////////////////////////////////
//
// MySQLDB->getRows(<table>, [<condition array> [, <fields> [, <orderfields> [, <ordertype> [, <fetch_type>]]]]])
// or
// MySQLDB->getRows(<query>)
//
// Returns query result as an array of rows based on the passed arguments.
// A full regular SQL SELECT query can be passed if desired.
// If <condition array> is omitted, no WHERE clause is used (all rows selected).
// If <fields> is empty, the query equals 'SELECT * FROM <table>...'
// If <orderfields> is empty, no sorting will be performed. 
// <ordertype> defaults to ASC. Can also be DESC.
// <fetch_type> defaults to MYSQL_BOTH. Can also be MYSQL_NUM or MYSQL_ASSOC.
//
// Example usage:
// $rows = $db->getRows('users', 'level' => 'admin', 'firstname, lastname, username', 'lastname', 'DESC');
// foreach ($rows as $user) {
//   echo $user['lastname'].", ".$user['firstname'].": ".$user[2];
// }
// 
// Equals to this call of the getRows function if full query is preferred:
// $rows = $db->getRows("SELECT firstname, lastname, username FROM users WHERE level = 'admin' ORDER BY lastname DESC");
//////////////////////////////////////////////////////////////////////////////

public function getRows($table, $cond_array = '', $fields = '*', 
                        $orderfields = '', $ordertype = 'ASC', $fetch_type = MYSQL_BOTH) {
  if (strtoupper(substr(trim($table), 0, 6)) == "SELECT") {
    $sql = $table;
  } else {
    $sql = "SELECT $fields FROM $table";
    if (is_array($cond_array)) {
      $sql .= " WHERE ".$this->conditions($cond_array);
    }
    if ($orderfields != '') {
      $sql .= " ORDER BY ".$orderfields." ".$ordertype;
    }
  } 
  $result = $this->sqlordie($sql);
  while($row = mysql_fetch_array($result, $fetch_type)) {
    $records[] = $row;
  }
  if (isset($records)) { 
    return $records;
  }
}



//////////////////////////////////////////////////////////////////////////////
//
// MySQLDB->numRows(<table> [, <condition array>])
// or
// MySQLDB->numRows(<query>)
//
// Returns the number of rows in a query result based on the passed arguments.
// A full regular SQL SELECT query can be passed if desired.
// If <condition array> is omitted, no WHERE clause is used (all rows selected).
//
// Example usage:
// $num = $db->numRows('users', 'level' => 'admin');
// echo "There are ".$num." users which has admin access.";
// 
// Equals to this call of the numRows function if full query is preferred:
// $num = $db->numRows("SELECT * FROM users WHERE level = 'admin'");
//////////////////////////////////////////////////////////////////////////////

public function numRows($table, $cond_array = '', $fields = '*') {
  if (strtoupper(substr(trim($table), 0, 6)) == "SELECT") {
    $sql = $table;
  } else {
    $sql = "SELECT $fields FROM $table";
    if (is_array($cond_array)) {
      $sql .= " WHERE ".$this->conditions($cond_array);
    }
  }
  $result = $this->sqlordie($sql);
  $num = mysql_num_rows($result);
  return $num;
}


//////////////////////////////////////////////////////////////////////////////
//
// MySQLDB->query(<query>)
//
// General simple function to pass any queries directly in SQL.
// The function returns the same value(s) as mysql_query(<query>) would have.
//
// Example usage:
// $users = $db->query("SELECT COUNT(userid) FROM users");
// Would execute the query and return the result to be put in $users
//////////////////////////////////////////////////////////////////////////////
public function query($sql) {
  return $this->sqlordie($sql);
}



/*
/ Private functions used within the class
*/

// Parse the conditions array
private function conditions($multiple) {
  $str = ""; 
  foreach ($multiple as $field => $value) {
    $str .= $field." = '".$value."' AND ";
  }
  return substr($str, 0, -4);	
}

// Perform the actual SQL query
private function sqlordie($sql) {
  $return_result = mysql_query($sql, $this->conn);
  if($return_result) {
    return $return_result;
  } else {
    $this->sql_error($sql);
  }
}

// Error handling
private function sql_error($sql) {
  echo mysql_error($this->conn).'<br>';
  die('error: '. $sql);
}


} // end of class

?> 
Return current item: SHOUTcast Management Interface