PHP Classes

File: mssql.inc.php

Recommend this page to a friend!
  Classes of Fanache A. Remus   SQL class for PHP   mssql.inc.php   Download  
File: mssql.inc.php
Role: Class source
Content type: text/plain
Description: MSSQL class
Class: SQL class for PHP
Access different databases with the same interface
Author: By
Last change: - rename sql class from SQL_DB to sqltype_DB (where sqltype is MYSQL or MYSQLI or SQLITE2 or SQLITE3 or MSSQL)
(this is required if you want to use 2 different sql databases)
(Note: after update to this revision you need to initialize class with specific name, see examples)
- corrected a bug on mysql/mysqli class (bug sql port if is different from 3306)
- add $this->sql_query_monitor (use to monitor query)
(Note: before this revision if you use $this->sql_tracer you get all log from class, include filters and other data and now with $this->sql_query_monitor you can see now only executed queries)
- corrected a bug on all classes on log
(before this update if is to many queries you get an memory error, and now you can avoid this problem by using $this-sql_debug = false;)
- add $this->query_log() (protected function - use $this->sql_query_log instead)
- corrected a bug on $this->real_escape() function
- corrected a bug on $this->free_result() function
- add example_2_db.php (example for using 2 databases of same type)
- add example_from_mysqli_to_sqlite3.php (example for 2 different databases)
- update example.php
- added new function for mysql::set_time_zone
- code review (set all messages and comments on english language)
- bug fixed on MSSQL::real_escape
- added new function array_sqlfilter() (improvement)
- change function name from real_escape_string to real_escape
- change function name from sql_prepare_data to prepare_data
- corect bug on real_escape function
- corect bug on prepare_data function
Date: 9 years ago
Size: 12,445 bytes
 

Contents

Class file image Download
<?php /** * @Package: FAR-PHP CMS * @Author: Fanache A. Remus (Birkoff) * @$Date$ * @Contact: www.farsoft.far-php.ro - contact@far-php.ro * @$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; } } ?>