PHP MSSQL Class
This code is still beta, use at your own risk.
<?php
/**
All code is Copyright 2009 by Ashwin Surajbali (http://www.redinkdesign.net).
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.
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 can view a copy of the GNU General Public Licsense at
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
**/
class redDB {
private $__db_host;
private $__db_username;
private $__db_password;
private $__db_name;
private $__db_handle;
private $__sql;
private $__table_name;
private $__result;
function __construct($host, $username, $password, $db_name, $db_handle = ''){
if (empty($db_handle)){
$this->__db_host = $host;
$this->__db_username = $username;
$this->__db_password = $password;
$this->__db_name = $db_name;
$this->connect();
}else{
$this->__db_handle = $db_handle;
}
}
/**
* Initialize our table object
*
* @example $this->init('users_table');
*
* @param string $table
* @return object
*/
public function init($table = ''){
if(empty($table)){
$this->throw_error(__METHOD__, __LINE__, 'No table name specified.');
}
$this->__table_name = $table;
$ar_fields = $this->get_table_fields();
if (!is_array($ar_fields)) {
$this->throw_error(__METHOD__, __LINE__, "ERROR: Table '{$this->__table_name}' has no fields.");
}
foreach ($ar_fields as $field){
switch ($field->type){
case "INTEGER":
case "TINYINT":
case "SMALLINT":
case "MEDIUMINT":
$this->{$field->name} = 0;
break;
case "NUMERIC":
case "FLOAT":
case "DECIMAL":
$this->{$field->name} = 0.00;
break;
default:
$this->{$field->name} = '';
break;
}
if (!empty($field->def)) {
$this->{$field->name} = $field->def;
}
}
return $this;
}
/**
* Alias for @link init
*
* @param string $table
* @return object
*/
public function init_table($table){
return $this->init($table);
}
/**
* Returns a single record as a table object depending on
* constraint passed in
*
* @example $this->get_single_record("id = 22");
*
* @param string $constraint
* @return object
*/
public function get_single_record($constraint = ''){
if (empty($this->__table_name)){
$this->throw_error(__METHOD__, __LINE__, "You must initialize a table before retrieving a record.");
}
if (empty($constraint)){
$this->throw_error(__METHOD__, __LINE__, "No constraint entered.");
}
$this->__sql = "select top 1 * from {$this->__table_name} where {$constraint}";
return $this->get_single_result();
}
/**
* Alias for @link get_single_record
*
* @param string $constraint
* @return object
*/
public function get($constraint){
return $this->get_single_record($constraint);
}
/**
* Inserts a new record or updates if it already exists
* Use this for quick updates/inserts
*
* For custom updates, use @link update
*
* @return boolean
*/
public function save(){
$duplicate_sql = '';
$this->__sql = "insert into {$this->__table_name} values (";
foreach ($this as $key => $val){
if (substr($key, 0, 2) == '__'){
continue;
}
$this->__sql .= "'{$this->escape($val)}'" . ',';
}
$this->__sql = trim($this->__sql, ',');
$this->__sql .= ")";
return $this->execute_query($this->__sql);
}
public function insert(){
return $this->save();
}
/**
* Updates an existing record depending on custom constraints
* Use this for complex updates instead of @link save
*
* @param string $where_constraint
* @return boolean
*/
public function update($where_constraint = ''){
if (empty($where_constraint)){
$this->throw_error(__METHOD__, __LINE__, 'Missing WHERE constraint. eg. ID = 222');
}
$this->__sql = "update {$this->__table_name} set ";
foreach ($this as $key => $val){
if (substr($key, 0, 2) == '__'){
continue;
}
$this->__sql .= "{$key} = '{$this->escape($val)}'" . ',';
}
$this->__sql = trim($this->__sql, ',');
$this->__sql .= ' where ' . $where_constraint;
return $this->execute_query($this->__sql);
}
/**
* Executes a query
*
* @param string $query
* @return boolean
*/
public function execute_query($query){
$this->__sql = $query;
if(!$this->query($this->__sql)){
$this->throw_error(__METHOD__, __LINE__);
}
return true;
}
/**
* Send out a query and return the first field of the first result row
* eg. select count(*) as count from blah; only the value of count will be returned
*
* @param string $query
* @return string return value of query
*/
public function get_query_value($query){
if (empty($query)) $this->throw_error(__METHOD__, __LINE__, 'Query missing.');
$this->__sql = $query;
$this->__result = $this->query($this->__sql);
if (!$this->__result){
$this->throw_error(__METHOD__, __LINE__);
}
$ar_result = mssql_fetch_array($this->__result);
if (!empty($ar_result[0])){
return $ar_result[0];
}else{
return false;
}
}
/**
* Get data from the initialized table depending on constraint, limit and order
* Returns results in an array of objects
*
* @param string $constraint - optional
* @param string $limit - optional
* @param string $order_by - optional
* @return array of objects
*/
public function get_data($constraint = '', $limit = '', $order_by = ''){
if (empty($this->__table_name)) $this->throw_error(__METHOD__, __LINE__, 'Table missing.');
$this->__sql = "select " . (!empty($limit)? " top {$this->escape($limit)}" : '') . " * from {$this->__table_name}" . (!empty($constraint)? " where {$constraint}" : '') . (!empty($order_by)? " order by {$this->escape($order_by)}" : '');
$this->__result = $this->query($this->__sql);
if (!$this->__result){
$this->throw_error(__METHOD__, __LINE__);
}
$ar_results = array();
while($obj = mssql_fetch_object($this->__result)){
$ar_results[] = $obj;
}
if (is_array($ar_results)){
return $ar_results;
}else{
return false;
}
}
/**
* Runs a query and returns results in an array of objects
*
* @param string $query
* @return array
*/
public function get_query_data($query){
if (empty($query)) $this->throw_error(__METHOD__, __LINE__, "Query missing.");
$this->__sql = $query;
$this->__result = $this->query($this->__sql);
if(!$this->__result){
$this->throw_error(__METHOD__, __LINE__);
}
$ar_results = array();
while ($obj = mssql_fetch_object($this->__result)){
$ar_results[] = $obj;
}
if (is_array($ar_results)){
return $ar_results;
}else{
return false;
}
}
/**
* Escapes special characters in a string for use in a SQL statement
*
* @param string $string
* @return string
*/
public function escape($string){
return str_replace("'", "''", $string);
}
/**
* Gets the sql statement that was executed
*
* @return string
*/
public function get_sql(){
return $this->__sql;
}
/**
* Gets the number of rows in a result
*
* @return int
*/
public function get_num_rows(){
return mssql_num_rows($this->__result);
}
/**
* Gets the number of affected rows in a previous MySQL operation
* Returns an integer greater than zero which indicated the number of rows affected or retrieved.
* Zero indicates that no records where updated for an UPDATE statement,
* no rows matched the WHERE clause in the query or that no query has yet been executed.
* -1 indicates that the query returned an error.
*
* @return int
*/
public function get_affected_rows(){
return mssql_rows_affected($this->__db_handle);
}
private function connect(){
if (!$this->__db_handle = mssql_connect($this->__db_host, $this->__db_username, $this->__db_password)){
$this->throw_error(__METHOD__, __LINE__, 'Could not connect to SQL server.');
}
if (!mssql_select_db($this->__db_name, $this->__db_handle)){
$this->throw_error(__METHOD__, __LINE__, 'Could not select database ' . $this->__db_name);
}
}
private function get_single_result(){
$this->__result = $this->query($this->__sql);
if (!$this->__result){
$this->throw_error(__METHOD__, __LINE__);
}
$ob_record = mssql_fetch_object($this->__result);
if (is_object($ob_record)){
foreach($ob_record as $key => $value){
$this->{$key} = $value;
}
return $this;
}else{
return false;
}
}
private function get_table_fields(){
$this->__sql = "select top 1 * from {$this->escape($this->__table_name)}";
$this->__result = mssql_query($this->__sql, $this->__db_handle);
if (!$this->__result){
$this->throw_error(__METHOD__, __LINE__,'Could not fetch table fields.');
}
for ($i = 0; $i < mssql_num_fields($this->__result); $i++){
$field = mssql_fetch_field($this->__result, $i);
$ar_fields[$field->name]->name = $field->name;
$ar_fields[$field->name]->type = strtoupper($field->type);
$ar_fields[$field->name]->max_length = $field->max_length;
$ar_fields[$field->name]->column_source = $field->column_source;
$ar_fields[$field->name]->numeric = $field->numeric;
}
if (count($ar_fields) > 0){
return $ar_fields;
}else{
$this->throw_error(__METHOD__, __LINE__, "No columns found for table {$this->__table_name}.");
}
}
private function query($query){
$this->__result = mssql_query($query, $this->__db_handle);
return $this->__result;
}
private function throw_error($function_name, $line_number, $error_msg = ''){
die('<font style="color: red; font-weight: bold;">-- Error --</font><br />' . $function_name . ' at line ' . $line_number . '<br />' . (!empty($error_msg)? "<br />Message: $error_msg" : "Invalid Query: {$this->__sql}"));
}
}
?>
All code is Copyright 2009 by Ashwin Surajbali (http://www.redinkdesign.net).
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.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of ERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You can view a copy of the GNU General Public Licsense at http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
No comments yet.