<?php
/**
* @Package: FAR-PHP CMS
* @Author: Fanache A. Remus (Birkoff)
* @$Date$
* @Contact: www.farsoft.far-php.ro - [email protected]
* @$Rev$
* @$Id$
*
* MSSQL class
*
**/
// This file can not be accessed independently from index
if(!defined('FAR_ANTIHACK')) { header('HTTP/1.0 403 Forbidden'); header('Status: 403 Forbidden'); die("<br>You don't have access here."); }
class MSSQL_DB {
// initializing the internal variables
// monitoring data
var $sql_tracer = array();
// if true provides information on every function called, otherwise only provides information of queries
var $sql_debug = false;
// connecting
var $conn = NULL;
// variable that holds the results returned
var $result = false;
// db info
var $db_server = '127.0.0.1';
var $db_port = '3306';
var $db_username = 'root';
var $db_password = '';
var $db_name = '';
var $db_encoding = 'utf8';
var $db_time_zone = 'Europe/Bucharest';
var $db_persist = false;
/**
* class instance
*
* @param str $db_server - sql server name
* @param int $db_port - sql server connection port
* @param str $db_username - sql username
* @param str $db_password - sql password
* @param str $db_name - database name
* @param str $db_encoding - encoding data type
* @param bool $db_persist - connection type
*
**/
function MSSQL_DB ($db_server='', $db_port=3306, $db_username='', $db_password='', $db_name='', $db_encoding='', $db_persist=false) {
if ( ! empty($db_server) )
$this->db_server = $db_server;
if ( ! empty($db_port) && is_numeric($db_port) )
$this->db_port = $db_port;
if ( ! empty($db_username) )
$this->db_username = $db_username;
if ( ! empty($db_password) )
$this->db_password = $db_password;
if ( ! empty($db_name) )
$this->db_name = $db_name;
if ( ! empty($db_encoding) )
$this->db_encoding = $db_encoding;
if ( $db_persist === true )
$this->db_persist = $db_persist;
$this->open();
}
/**
* connecting to sql server
* database selection
* encoding type setting
*
* @return resource $conn - false on failure
*
**/
function open() {
if ( $this->connect() === false )
return false;
if ( $this->select_db() === false )
return false;
if ( $this->set_charset() === false )
return false;
return $this->conn;
}
/**
* connecting to the database
*
* @param str $db_server
* @param int $db_port
* @param str $db_username
* @param str $db_password
*
* @return bool
*
**/
function connect($db_server='', $db_port=3306, $db_username='', $db_password='') {
if ( ! empty($db_server) )
$this->db_server = $db_server;
if ( ! empty($db_port) && is_numeric($db_port) )
$this->db_port = $db_port;
if ( ! empty($db_username) )
$this->db_username = $db_username;
if ( ! empty($db_password) )
$this->db_password = $db_password;
// if it is already connected
if( is_resource($this->conn) )
return true;
// Choose the appropriate connect function
if ( $this->db_persist )
$this->conn = mssql_pconnect($this->db_server.':'.$this->db_port, $this->db_username, $this->db_password);
else
$this->conn = mssql_connect($this->db_server.':'.$this->db_port, $this->db_username, $this->db_password);
// Connect to the database server
if(!is_resource($this->conn)) {
if ( $this->sql_debug )
$this->sql_monitor('connect', 'connect', '', $this->conn, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
return false;
} else {
if ( $this->sql_debug )
$this->sql_monitor('connect', 'connect', '', $this->conn, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
return true;
}
}
/**
* database selection
*
* @param str $db_name
*
* @return bool
*
**/
function select_db($db_name='') {
if( empty($db_name) )
$db_name = $this->db_name;
$rez = mssql_select_db($db_name,$this->conn);
if ( $this->sql_debug )
$this->sql_monitor('select_db', 'db_name', $db_name, $rez, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
return $rez;
}
/**
* charset setting used
*
* @param str $db_encoding
*
* @return bool
*
**/
function set_charset($db_encoding='') {
return true; // does not apply to mssql
}
/**
* set time zone
*
* @param str $time_zone
*
* @return bool
*/
function set_time_zone($time_zone='') {
return true; // does not apply to mssql
}
/**
* monitoring data and errors
*
* @param str $function_name - function name that is monitoring
* @param str $param_name - function parameter name
* @param obj $param_val - function parameter value
* @param obj $return_val - value returned
* @param int $line - line number where you can find values
* @param str $file - file name
* @param str $error - reported error
*
**/
function sql_monitor($function_name, $param_name, $param_val, $return_val, $line, $file, $error_nr=0, $error_val=0) {
$this->sql_tracer[] = array(
'function_name' => $function_name,
'param_name' => $param_name,
'param_val' => $param_val,
'return_val' => $return_val,
'line' => $line,
'file' => $file,
'error_nr' => $error_nr,
'error_val' => $error_val,
);
}
/**
* identification number of the current error - mysql_errno
*
* @return int $nr
*
**/
function sql_errno() {
if ( ! is_resource($this->conn) )
return 0;
else
return 0;
}
/**
* last error reported - mysql_error
*
* @return str $error
*
**/
function sql_error() {
if ( ! is_resource($this->conn) )
return 0;
else
return mssql_get_last_message();
}
/**
* making query - mysql_query
*
* @param str $query
*
* @return resource $result
*
**/
function query($query) {
if ( empty($query) )
return false;
$this->result = mssql_query($query,$this->conn);
if ( $this->sql_debug )
$this->sql_monitor('query', 'query', $query, $this->result, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
return $this->result;
}
/**
* parsing the data as array - mysql_fetch_array
*
* @param resource $rezultat
* @param bool $type - true for MYSQL_ASSOC, false for MYSQL_NUM, null for MYSQL_BOTH
*
* @return array $result
*
**/
function fetch_array($rezultat=NULL, $type=true) {
if ( is_resource($rezultat) )
$result = $rezultat;
else
$result = $this->result;
if ( $type == true)
$rez = mssql_fetch_array($result, MYSQL_ASSOC);
elseif ( $type == false)
$rez = mssql_fetch_array($result, MYSQL_NUM);
else
$rez = mssql_fetch_array($result, MYSQL_BOTH);
if ( $this->sql_debug )
$this->sql_monitor('fetch_array', 'result', $result, $rez, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
return $rez;
}
/**
* closing connection - mysql_close
*
* @return bool $rez
*
**/
function close() {
$rez = mssql_close($this->conn);
if ( $this->sql_debug )
$this->sql_monitor('close', '', '', $rez, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
return $rez;
}
/**
* start tranzaction
*
* @return resource
*
**/
function start_tranzaction() {
return $this->query('START TRANSACTION');
}
/**
* rollback tranzaction
*
* @return resource
*
**/
function rollback() {
return $this->query('ROLLBACK');
}
/**
* commit tranzaction
*
* @return resource
*
**/
function commit() {
return $this->query('COMMIT');
}
/**
* number of rows affected - mysql_affected_rows
*
* @return int $rows
*
**/
function affected_rows() {
$rows = mssql_rows_affected($this->conn);
if ( $this->sql_debug )
$this->sql_monitor('affected_rows', '', '', $rows, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
return $rows;
}
/**
* inserted row id - mysql_insert_id
*
* @return int $id
*
**/
function insert_id() {
$result = $this->query("SELECT @@identity");
if (!$result)
$id = false;
$id = $this->result($result, 0);
if ( $this->sql_debug )
$this->sql_monitor('insert_id', '', '', $id, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
return $id;
}
/**
* filter values - mysql_real_escape_string
*
* @param mixed $str
*
* @return mixed $str
*
**/
function real_escape($str) {
if ( is_array($str) )
$rez = array_map('SQL_DB::ms_escape_string', $str);
else
$rez = ms_escape_string($str);
if ( $this->sql_debug )
$this->sql_monitor('real_escape', 'str', $str, $rez, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
return $rez;
}
/*
* filtering data for MSSQL
*
* source: http://stackoverflow.com/questions/574805/how-to-escape-strings-in-mssql-using-php
*
* @param str/int $data
*
* @return str/int $data - filtered
*/
function ms_escape_string($data) {
if ( !isset($data) or empty($data) ) return '';
if ( is_numeric($data) ) return $data;
$non_displayables = array(
'/%0[0-8bcef]/', // url encoded 00-08, 11, 12, 14, 15
'/%1[0-9a-f]/', // url encoded 16-31
'/[\x00-\x08]/', // 00-08
'/\x0b/', // 11
'/\x0c/', // 12
'/[\x0e-\x1f]/' // 14-31
);
foreach ( $non_displayables as $regex )
$data = preg_replace( $regex, '', $data );
$data = str_replace("'", "''", $data );
return $data;
}
/**
* number of rows found - mysql_num_rows
*
* @param resource $rezultat
*
* @return int $result
*
**/
function num_rows($rezultat=NULL) {
if ( ! is_resource($rezultat) )
$rez = $this->result;
else
$rez = $rezultat;
$result = mssql_num_rows($rez);
if ( $this->sql_debug )
$this->sql_monitor('num_rows', 'rez', $rez, $result, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
return $result;
}
/**
* result returned by the query - mysql_result
*
* @param resource $rezultat
* @param int $row
*
* @return str $result
*
**/
function result($rezultat=NULL, $row=0) {
if ( ! is_resource($rezultat) )
$rez = $this->result;
else
$rez = $rezultat;
$result = mssql_result($rez, $row, 0);
if ( $this->sql_debug )
$this->sql_monitor('result', 'rez', $rez, $result, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
return $result;
}
/**
* information about SQL - mysql_info
*
* @return str $info
*
**/
function sql_info() {
return true;
}
/**
* buffer empty sql - mysql_free_result
*
* @param resource $rez
*
* @return bool
*
**/
function free_result($rezultat=NULL) {
if ( ! is_resource($rezultat) )
$rez = $this->result;
else
$rez = $rezultat;
$info = mssql_free_result($rez);
if ( $this->sql_debug )
$this->sql_monitor('free_result', '', '', $info, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
return $info;
}
/**
* Preparation for working with MySQL data
*
* @param array $values - values that need to be changed
* @param bool $tilde - what type of quotes used
*
* @return string $values
*
**/
function prepare_data($values, $tilde = false) {
if (! is_array($values) || count($values) == 0)
return false;
if ($tilde)
$values = count($values) ? "`" . implode("`, `", $values) . "`" : '';
else
$values = count($values) ? "'" . implode("', '", $values) . "'" : '';
return $values;
}
/**
* Filter an array by keys
*
* @param array $array associative array to filter
* @param array $filter list of keys to return (in this order)
*
* @return array a new array containing only those keys and values from $array which exists in $filter too (keep order from $filter)
*
**/
function array_sqlfilter($array, $filter) {
if (! is_array($array) || count($array) == 0)
return false;
if (! is_array($filter) || count($filter) == 0)
return false;
$result = array();
foreach ($filter as $key) {
if (isset($array[$key]))
$result[$key] = $array[$key];
}
return $result;
}
}
?>
|
Download
