<?php
/**
* @file : sql.class.php
* @desc : basic mysql functions
* @lmod : 13.05.2007
*/
class sql
{
// database connection handler
var $connection = false;
// query results holder
var $res;
// query stats
var $num_rows;
var $num_flds;
var $num_aff;
var $last_id;
// default fetch row
var $fetch_method = 'object';
/**
* @desc : the constructor, connect to mySQL server
* @param $dbhost : string : database host
* @param $dbuser : string : database user
* @param $dbpass : string : database user pass
* @param $dbname : string : database name
* @return : boolean : connection status
*/
function sql($dbhost=0, $dbuser=0, $dbpass=0, $dbname=0)
{
// get my globals
global $_DBHOST, $_DBUSER, $_DBNAME, $_DBPASS;
// if some of the connection values is missing replace it
$dbhost = $dbhost ? $dbhost : $_DBHOST;
$dbuser = $dbuser ? $dbuser : $_DBUSER;
$dbpass = $dbpass ? $dbpass : $_DBPASS;
$dbname = $dbname ? $dbname : $_DBNAME;
// try to connect
if (!$this->connection = $this->connect($dbhost, $dbuser, $dbpass, $dbname))
{
$this->error('connection error');
return false;
}
return true;
}
/**
* @desc : set default fetch method
* @param $method : string : method name(object, array, assoc, row)
*/
function setFetchMethod($method)
{
$this->fetch_method = $method;
}
/**
* @desc : display error message
* @param $err : sting : error message
*/
function error($error)
{
print $error . (mysql_error() ? ': ' . mysql_error() : '' );
}
/**
* @desc : connect to mySQL server
* @param $dbhost : string : database host
* @param $dbuser : string : database user
* @param $dbpass : string : database user pass
* @param $dbname : string : database name
* @return : boolean : connection status
*/
function connect($dbhost, $dbuser, $dbpass, $dbname)
{
// if there is connection, disconnect first
if ($this->connection) { $this->disconnect(); }
// default database handler
$dbh = false;
// connect to mySQL server and database
if (!$dbh = mysql_connect($dbhost, $dbuser, $dbpass))
{
return false;
}
elseif (mysql_select_db($dbname))
{
return $dbh;
}
return false;
}
/**
* @desc : disconnect from mySQL server
* @return : boolean : is disconnection succesful
*/
function disconnect()
{
// if there isn't connection, just return true;
if (!$this->connection) { return true; }
// disconnect
if (mysql_close($this->connection)) { return true; }
return false;
}
/**
* @desc : makes mySQL query
* @param $query : string : query
* @param $index : int : query index
* @return : boolean : query status
*/
function query($query, $index=0)
{
// query
if (!$this->res[$index] = mysql_query($query, $this->connection))
{
// if query fail show error
$this->error('<strong>invalid query</strong>:<br />' . $query . '<br />');
return false;
}
// statistical information
$this->num_rows[$index] = @mysql_num_rows($this->res[$index]);
$this->num_flds[$index] = @mysql_num_fields($this->res[$index]);
$this->num_aff[$index] = @mysql_affected_rows();
$this->last_id = @mysql_insert_id($this->connection);
return true;
}
/**
* @desc : makes mySQL query and return its result + free this result
* @param $query : string : query
* @param $index : int : query index
* @return $row : boolean : query result
*/
function query1($qry, $index=0, $method=false)
{
$this->query($qry, $index);
$row = $this->getrow($index, false, $method);
if ($row)
{
$this->free_result($index);
}
return $row;
}
/**
* @desc : fetch result
* @param $index : int : result index
* @param $rown : int : which row to fetch (count starts at 0)
* @param $method : string : fetch method (object, array, assoc, row)
* @return $row : : row data
*/
function getrow($index=0, $rown=false, $method=false)
{
// check if there is a result to fetch
if (!$this->getnumrows($index))
{
return false;
}
// check if we seek for row that is invalid for that result
if ($rown !== false && ($row > ($this->getnumrows($index) - 1)) )
{
$this->error('invalid row');
return false;
}
elseif ($rown != false)
{
// seek for selected row number
mysql_data_seek($this->res[$index], $rown);
}
// fetch row depending on fetching method
$method = $method ? $method : $this->fetch_method;
switch ($method)
{
case 'array':
$row = @mysql_fetch_array($this->res[$index]);
break;
case 'assoc':
$row = @mysql_fetch_assoc($this->res[$index]);
break;
case 'row':
$row = @mysql_fetch_row($this->res[$index]);
break;
default:
// 'object' method is the default
$row = @mysql_fetch_object($this->res[$index]);
break;
}
if (!$row || $rown !== false)
{
$this->free_result($index);
}
return $row;
}
/**
* @desc : get the number of selected rows
* @param $index : int : result index
* @return : int : rows number
*/
function getnumrows($index=0)
{
return $this->num_rows[$index];
}
/**
* @desc : get the number of selected fields
* @param $index : int : result index
* @return : int : fields number
*/
function getnumflds($index=0)
{
return $this->num_flds[$index];
}
/**
* @desc : get the number of affected rows
* @param $index : int : result index
* @return : int : affected rows number
*/
function getnumaff($index=0)
{
return $this->num_aff[$index];
}
/**
* @desc : get last insert id, generated by INSERT query
* @return : int : last insert id
*/
function getlastid()
{
return $this->last_id;
}
/**
* @desc : free mysql result from mysql and the class
* @param $index : int : result index
* @return : boolean : status
*/
function free_result($index=0)
{
// unset(delete) all statistical information for the selected result
mysql_free_result($this->res[$index]);
unset($this->num_rows[$index]);
unset($this->num_flds[$index]);
unset($this->num_aff[$index]);
unset($this->res[$index]);
// free mysql result
return true;
}
/**
* @desc : get next free index number
* @return $i : int : next index
*/
function nextindex()
{
for($i=0; $this->num_rows[$i]; $i++);
return $i;
}
/**
* @desc : escape mySQl string
* @param $str : string : original string
* @return : string : escaped string
*/
function escape($str)
{
$str = stripslashes($str);
$str = mysql_real_escape_string($str);
return $str;
}
/**
* @desc : full mysql escape(mainly for search queryies)
* @param $str : string : original string
* @return $str : string : escaped string
*/
function escape_full($str)
{
$str = $this->escape($str);
$str = preg_replace("/(\_|\%)/", '\\\$1', $str);
return $str;
}
/**
* @desc : quote strings(the function arguments)
* @return $str : string : quoted strings
*/
function quote()
{
$str = array();
$args = func_get_args();
foreach ($args as $val)
{
$str[] = '"' . $this->escape($val) . '" ';
}
return join(', ', $str);
}
/**
* @desc : generates where conditions
* @param $cond : contitions
* @return : sql where conditions
*/
function where($cond)
{
if (!$cond)
{
return '';
}
if (is_numeric($cond))
{
return "WHERE id='{$cond}' ";
}
if (is_array($cond))
{
$vals = array();
foreach($cond as $field => $value)
{
$vals[] = is_numeric($field) ? $value : $field . '=' . $this->quote($value);
}
return 'WHERE ' . join(' AND ', $vals) . ' ';
}
return "WHERE {$cond} ";
}
/*
* @desc : generate insert query
* @param $table : string : table name
* @param $values : array : values to insert ([field name] => field value)
* @param $avalues : array : addition values (without '')
* @return : int : last added primary key or false on error
*/
function insert($table, $values, $avalues=0)
{
if (!is_array($values)) { return false; }
// set $flds and $vals param to array
$flds = array();
$vals = array();
// construct query
foreach ($values as $key => $val)
{
$flds[] = $key;
$vals[] = "'{$val}'";
}
// if there is additional values
if (is_array($avalues))
{
foreach ($avalues as $key => $val)
{
$flds[] = $key;
$vals[] = $val;
}
}
// strip commas and whitespace from the end
$flds = join(', ', $flds);
$vals = join(', ', $vals);
// try to make insert query and return last added id
if ($this->query("INSERT INTO {$table} ( {$flds} ) VALUES ( {$vals} )"))
{
return $this->getlastid();
}
return false;
}
/*
* @desc : generate update query
* @param $table : string : table name
* @param $values : array : values to update ([field name] => field value)
* @param $cond : string : conditions for update
* @return : boolean : query status
*/
function update($table, $values, $cond=0)
{
if (!is_array($values)) { return false; }
// add update to the query first
$qry = array();
foreach ((array)$values as $key => $val)
{
$qry[] = is_numeric($key) ? $val : $key . "='{$val}'";
}
// now generate query
return $this->query("UPDATE {$table} SET " . join(', ', $qry) . $this->where($cond));
}
/**
* @desc : set info in $table
* @param $table : string : table name
* @param $values : array : values to set ([field name] => field value)
* @param $key : string : table primary key, can be array with [key] => kval
* @param $kval : string : primary key value
* @return : int : setted datebase record id
*/
function set($table, $values, $key=0, $kval=0)
{
// check ig $key is array [key] => kval
if (is_array($key))
{
// gen. conditions string
$cond = array();
foreach ($key as $kname => $kval)
{
$cond[] = "{$kname}='{$kval}' ";
}
$cond = join('AND ', $cond);
// get first key as primary
reset($key);
$kval = current($key);
$key = key($key);
}
elseif ($kval) // if kval is setted
{
// gen. conditions string
$cond = "{$key}='{$kval}'";
}
// check if there are setted conditions
if ($cond)
{
// check if record with that conditions exists
$this->query("SELECT {$key} FROM {$table} WHERE {$cond} LIMIT 1");
if ($this->getnumrows() > 0)
{
// if there are no values delete record
if (!is_array($values))
{
// delete record
return $this->query("DELETE FROM {$table} WHERE {$cond}");
}
// update record
if ($this->update($table, $values, $cond))
{
return $kval;
}
return 0;
}
}
// insert new record
if ($this->insert($table, $values))
{
return $this->getlastid();
}
return 0;
}
/**
* @desc : delete information from table(s)
* @param $table : string/array : table(s) name(s)
* @param $cond : string/int : condiotions for delete(if some)
* @return : boolean : is deleting succesful
*/
function delete($table, $cond=0)
{
// check if $table is a single table or a list of tables
if (!is_array($table))
{
// delete record(s) from one table
return $this->query("DELETE FROM {$table} ". $this->where($cond));
}
// in case $table is array
// it must contain the list of tables
// from which we must delete record(s)
foreach ($table as $tname)
{
if (!$this->delete($tname, $cond))
{
return false;
}
}
return true;
}
/**
* @desc : make selet query and execute it
* @param $table : string : table
* @param $fields : string : fields to select
* @param $cond : string : select conditions
* @param $limit : string : limit conditions (if 1 return only the first recods
* @param $index : int : query index
* @return : boolean : query status
*/
function select($table, $fields='*', $cond=false, $limit=false, $index=0)
{
$qry = 'SELECT ' . ( is_array($fields) ? join(',', $fields) : $fields ) . " FROM {$table} ";
$qry .= $this->where($cond);
$qry .= $limit ? "LIMIT {$limit} " : '';
if ($limit == 1)
{
return $this->query1($qry, $index);
}
if (!$this->query($qry, $index))
{
return false;
}
return true;
}
/**
* @desc : dumps table(s) structure
* @param $table : string : table to dump
* @param $prefix : string : prefix for table code
* @param $suffix : string : suffix for table code
* @param $type : string : tables TYPE or ENGINE (def: MyISAM)
* @return $str : string : sql codes for table(s) structure
*/
function table_info($table=0, $prefix=0, $suffix=0, $type='MyISAM')
{
// set sring to dumping
$str = '';
if (!$table)
{
// select all tables
$this->query('SHOW TABLES ');
while ($row = $this->getrow(0, false, 'array'))
{
$str .= $this->table_info(current($row), $prefix, $suffix, $type);
}
return $str;
}
elseif (is_array($table))
{
// select listed tables
foreach ($table as $tbl)
{
$str .= $this->table_info(current($row), $prefix, $suffix, $type);
}
return $str;
}
// describe table
$this->query("DESCRIBE {$table}", 1);
// create table query string
$flds = "CREATE TABLE {$table}";
$flds .= "\n(\n";
while ($res = $this->getrow(1))
{
$flds .= "\t" . $res->Field . "\t\t" . strtoupper(trim($res->Type));
$flds .= $res->Null == 'NO' ? ' NOT NULL' : ' ';
$flds .= $res->Default && $res->Default != 'NULL' ? "DEFAULT '{$res->Default}'" : '';
$flds .= $res->Extra ? ' ' . strtoupper($res->Extra) : '';
$flds .= $res->Key == 'PRI' ? ' PRIMARY KEY' : '';
$flds .= ",\n";
}
$flds = rtrim($flds, ",\n");
$flds .= "\n)";
$flds .= $type ? " TYPE={$type}; " : '';
$flds .= "\n";
// add table info to return string
$str .= $prefix ? $prefix : '';
$str .= "\n {$flds}";
$str .= $suffix ? $suffix : '';
$str .= "\n\n";
return $str;
}
/**
* @desc : dumps table(s) content
* @param $table : string : table to dump
* @param $prefix : string : prefix for table code
* @param $suffix : string : suffix for table code
* @param $skip : array : fields to skip
* @return $str : string : sql codes for table(s) content
*/
function table_content($table=0, $prefix=0, $suffix=0, $skip=array())
{
// set sring to dumping
$str = '';
// if there is not table added just show contend from all tables
if (!$table)
{
// select all tables
$this->query('SHOW TABLES ');
while ($row = $this->getrow(0, false, 'array'))
{
$str .= $this->table_content(current($row), $prefix, $suffix, $skip);
}
return $str;
}
elseif (is_array($table))
{
// select form listed tables
foreach ($table as $tbl)
{
$str .= $this->table_content(current($row), $prefix, $suffix, $skip);
}
return $str;
}
// select *(all) from table
$this->query("SELECT * FROM {$table} ", 1);
while($res = $this->getrow(1))
{
// cover result object to array
$res = get_object_vars($res);
// dump table content
$keys = array();
$vals = array();
foreach ($res as $key => $val)
{
// skip this field ?
if (in_array($key, (array) $skip))
{
continue;
}
$keys[] = $key;
$vals[] = "'" . $val . "'";
}
$keys = join(', ', $keys);
$vals = join(', ', $vals);
$str .= $prefix ? $prefix : '';
$str .= "INSERT INTO {$table} ( {$keys} ) VALUES ( {$vals} ) ";
$str .= $suffix ? $suffix : '';
$str .= "\n";
}
return $str;
}
}
?>
|
Download
