<?
// This file is for abstracting the database layer from the rest of the php.
// Ideally, we might want to use the same functions in this but on another DB, so keep it clean.
// an alternative class method.
class MYSQL_DB{
// MySQL database varibles
var $dbhost;
var $dbname;
var $dbusername;
var $dbuserpassword;
var $link_id;
//var $MYSQL_ERRNO;
//var $MYSQL_ERROR;
// simple function to select the database. 9-2-01
// This only exist to keep the class portable with other db's.
function select_db($dbname=false){
if(!$dbname) $this->dbname = $GLOBALS[dbname];
else $this->dbname = $dbname;
mysql_select_db($this->dbname);
}
// The apex of the DB class.
// Fixed so this only selects a DB when asked, rather than by default. 9-3-01
function connect($dbhost, $dbusername, $dbuserpassword, $dbname=false){
if(!$this->link_id = @mysql_pconnect($dbhost, $dbusername, $dbuserpassword))return false;
if($dbname != false) $this->select_db($dbname);
return true;
}
// The constructor function
function MYSQL_DB(){
$this->dbhost = '$GLOBALS[dbhost]';
$this->dbname = '$GLOBALS[dbname]';
$this->dbusername = '$GLOBALS[dbusername]';
$this->dbuserpassword = '$GLOBALS[dbuserpassword]';
$this->link_id = false;
//$this->connect($this->dbhost, $this->dbusername, $this->dbuserpassword, $this->dbname);
}
// Place assoc_array for the row into numbered return array. 9-2-01
// renamed from fetch_array to fetch_assoc. 9-3-01
function fetch_assoc($db_query){
$result = mysql_query($db_query,$this->link_id);
for($i=0; $query_data = @mysql_fetch_assoc($result); $i++){
$result_array[$i] = $query_data;
}
@mysql_free_result($result);
return $result_array;
}
// This returns a numbered arrays.
// places numbered aray for the row into a parent numbered array.
// renamed, and tuned. 9-3-01
function fetch_numeric($db_query){
$result = mysql_query($db_query,$this->link_id);
for($i=0; $query_data = @mysql_fetch_row($result); $i++){
$result_array[$i] = $query_data;
}
@mysql_free_result($result);
return $result_array;
}
// Returns TRUE (non-zero) or FALSE to indicate whether or not the query succeeded.
// A return value of TRUE means that the query was legal and could be executed by the server.
// It is perfectly possible for a query to succeed but affect no rows or return no rows.
// This function is NOT designed for SELECT statments, but will return a result_set if used.
function query($db_query){
return mysql_query($db_query,$this->link_id);
}
// Returns the number of rows in a result set.
// This command is ONLY valid for SELECT statements.
// Returns false if the query fails.
// Assuming the SELECT query succeeds you can find out how many rows were returned.
function num_rows($db_query){
if(!$result_set = @mysql_query($db_query,$this->link_id)){
//mysql_free_result($result_set);
return false;
}else{
//mysql_free_result($result_set);
return mysql_num_rows($result_set);
}
}
// Returns the number of rows affected by the last INSERT, UPDATE or DELETE query.
// Does not work with SELECT statements; only on statements which modify records.
function affected_rows($db_query){
if(!mysql_query($db_query,$this->link_id))return false;
else return mysql_affected_rows($this->link_id);
}
// Create table
// really gay, but it works.
function create_table($query){
if($this->query($query)) return true;
else return false;
}
// A function to drop the db.
// Use caution here.
function drop_db($dbname){
if(mysql_drop_db("$dbname",$this->link_id))return true;
else return false;
}
// remove the table.
function drop_table($table){
$query = "DROP TABLE $table";
if($result = mysql_query($query,$this->link_id)){
mysql_free_result($result);
return true;
}else{
mysql_free_result($result);
return false;
}
}
// This is a good way to empty a table.
function purge_table($table){
$query = "TRUNCATE TABLE $table";
if(!$result = mysql_query($query,$this->link_id)){
mysql_free_result($result);
return false;
}else{
mysql_free_result($result);
return true;
}
}
// A good way to empty a record.
function purge_record($id, $table){
$query = "DELETE FROM $table WHERE id='$id'";
$query2 = "OPTIMIZE TABLE $table";
if(!$result = mysql_query($query,$this->link_id)){
mysql_free_result($result);
return false;
}elseif(!$result = mysql_query($query2,$this->link_id)){
mysql_free_result($result);
return false;
}else{
mysql_free_result($result);
return true;
}
}
// This uses DB syntax to dump a copy of the db files to the backup path.
// Tar, and gzip the files. 9-3-01
function backup_table($table, $path="/var/db/mysql_backup"){
if(empty($path))return false;
$timestmp = time();
$query = "BACKUP TABLE $table TO '$path'";
if(!$result = mysql_query($query,$this->link_id))return false;
mysql_free_result($result);
if(!exec("tar -cvf $path/$table_$timestmp.tar $path/$table.MYD $path/$table.frm $path/$table.MYI")) return false;
elseif(!exec("gzip $path/$table_$timestmp.tar")) return false;
else return true;
}
// This is a bit tricky
// If the table we are restoring already exist, we need to rename it.
// This needs only the filename minus the extension.
function restore_table($tbl_file, $path="/var/db/mysql_backup"){
// Need a way to extract the table name from the filename.
// This query doesn't work until the above is true.
$query = "RESTORE TABLE $table FROM '$path'";
if(empty($path))return false;
if(!file_exists("$path/$tbl_file.tar.gz")){
clearstatcache();
return false;
}else clearstatcache();
// THE NEXT PART IS TRICKY, AND NOT FINISHED.
// THE FILE IS RENAMED WHEN IT IS TAR/GUNZIP'D, AND THE CHANGE OF NAME HAS TO BE ACCOMIDATED FOR.
if(!exec("gunzip $path/$tbl_file.tar.gz")) return false;
if(!exec("tar -Xvf $path/$tbl_file.tar")) return false;
if(!$result = mysql_query($query,$this->link_id))return false;
mysql_free_result($result);
return true;
}
// Utility functions, no options.
// List the database that are in mysql
function list_dbs(){
if(!$result = mysql_list_dbs($this->link_id))return false;
for($i=0; $query_data = mysql_fetch_array($result); $i++){
$result_array[$i] = $query_data[0];
}
mysql_free_result($result);
return $result_array;
}
// Lists all available tables in a given databaes (using $GLOBALS[dbname])
function list_tables(){
$result=mysql_list_tables($GLOBALS[dbname]);
$i=0;
while (list($val)=mysql_fetch_array($result)){
$result_array[$i] = $val;
$i++;
}
return $result_array;
}
// Function is get the number of entries in all the tables in the passed array. Sould be all tables in
// audiodevil database.
function get_table_info($table_array){
$query = "SHOW TABLE STATUS FROM $GLOBALS[dbname]";
$result_array = $this->fetch_assoc($query);
//$result_array1 = serialize($result_array);
//echo "$result_array1<br>";
return $result_array;
}//eo function
// function to describe the tables
function describe_table($table=""){
if(empty($table)) return false;
$query = "DESCRIBE $table";
return $this->fetch_assoc($query);
}
// renamed this so in future it is portable to other db servers. 9-2-01
function pull_serverstats(){
$query = "SHOW STATUS";
$result = $this->fetch_numeric($query);
while(list( , $val_1) = each($result)) {
$i=0;
while(list( , $val_2) = each($val_1)){
$value_d[$i] = $val_2;
$i++;
}
$return_array[$value_d[0]] = $value_d[1];
}
return $return_array;
}
// a function to randomize an array.
function randoray($array){
srand((double)microtime()*10000000);
$n = count($array);
$i=1;
while($i<=$n){
$r = array_rand($array);
$randarray[$i] = $array[$r];
unset($array[$r]);
$i++;
}//eo while
return $randarray;
}//eofunction
function sorty($array, $op="a"){
switch($op){
case "d": /* d = decending order. */
arsort($array);
break;
case "a": /* a = ascending order. */
asort($array);
break;
case "r": /* r = random order. */
$array = @$this->randoray($array);
break;
}
return $array;
}
}//eo class
?>