OO class for handeling communication with MSSQL

[bitman]

Hey all,
i am kind of new to php, though old to programming,
and decided to write me a general MSSQL class.

since i am not sure how effective it is, i am posting it here
for sharing, and to get your feedbacks\suggestions\remarks.

it's kind of long, hope you have the patience..
i am posting 4 files.
one is the class itself,
one is a header file for inclusion,
and 2 pages that are used to test the class.
these will help you understand how to work with it.

first, the class:
[code]
<?
// This is a general class to handle db connections to sql server.
class DB_Handler
{

    // ---------------- member definition --------------------------
    private static $instance;   // Singleton Instance
    
    // Connection details
    private $connection;        // Db connection
    private $hostname;            // Hotsname to connect to.
    private $username;            // connection user name.
    private $password;            // connection password.
    private $dbname;            // name of active db.
        
    // Flow details.
    private $query;                // last query.
    private $result;            // last result.
    private $rowdata;            // last row retrieved from query
    private $lastaction;        // a boolean variable that is true if last action succeeded and false if not.
    private $message;            // Last message from class.
    private $sqlmessage;        // Last message from sql.
    
    // Return helper
    private $returnarray;        // A special return variable for function with messages.
    
    // ------------------ Initialization functions -----------------
    
    // singleton pattern static function to create first instance.
    public static function Getinstance($dbname='',  // Database name to connect to
                      $conn='.',                    // Default conenction host
                      $user='sa',               // Default user
                      $pass='sapass')        // Default password
    {
        if (empty(self::$instance))
        {
            self::$instance = new DB_Handler($dbname,$conn,$user,$pass);
        }
        return self::$instance->ReturnValue(self::$instance);
    }
    
    //                    ------ Function end ------
    
    // singleton pattern static function to recreate instance with different connection.
    public static function RefreshInstance($dbname='',  // Database name to connect to
                      $conn='.',                    // Default conenction host
                      $user='sa',                    // Default user
                      $pass='sapass')            // Default password
    {
        self::$instance->DB_Handler($dbname,$conn,$user,$pass);
        
        return self::$instance->ReturnValue(self::$instance);
    }
    
    //                    ------ Function end ------              
    
    // Constructor, creates connection.
    private function DB_Handler($dbname='',            // Database name to connect to.
                      $conn='.',                    // Default conenction host
                      $user='sa',                    // Default user
                      $pass='sapass')            // Default password
    {
        // Trying to connect to sql server.
        if ($this->connection = @mssql_connect($conn,$user,$pass))
        {
            // Successful action.
            $this->lastaction = true;
            
            // Success. Saving connection details.
            $this->hostname = $conn;
            $this->username = $user;
            $this->password = $pass;
            $this->ChangeDB($dbname);        
            
            //Setting message
            $this->message = 'Connection successful';    
        }
        else
        {
            // action failed.
            $this->lastaction = false;
            
            // Saving php error message.
            $tempmessage = $php_errormsg;
            
            // Failed to connect, setting messages.
            $this->message = "Failed to connect to database\r";
            $this->sqlmessage = $this->GetSqlError();
            
            // if no message from sql, get php error.
            if (trim($this->sqlmessage) == '')
            {
                $this->sqlmessage = $tempmessage;
            }
        }        
    }
    
    //                    ------ Function end ------
    
    
    //-------------------------- Private Functions ---------------------
    
    // Function to return values with multiple values.
    // i.e. success bit (true\false), the data if exist, a message
    // from the class and an error message.
    private function ReturnValue($data = null)
    {
        // Checking for assignment of error message and success value.
        if ($this->lastaction)
        {
            $this->returnarray['error'] = null;
            $this->returnarray['bool'] = true;            
        }
        else
        {
            $this->returnarray['error'] = $this->sqlmessage;
            $this->returnarray['bool'] = false;
        }
        
        // Adding data from query (if exists) and message.
        $this->returnarray['data'] = $data;
        $this->returnarray['message'] = $this->message;        
        
        return $this->returnarray;
    }
    
    //                    ------ Function end ------
    
    
    // Retrieving last error message from database.
    private function GetSqlError ($value = '')
    {
       $codequery    = "select @@ERROR as code";                  // Getting error code from sql.
      
       // Logic here is if the connection is good it will work for all queries (cheking only for first).
       // if not, return '' and get the php error.
       if ($coderesult   = @mssql_query($codequery, $this->connection))
       {
               $coderow      = mssql_fetch_array($coderesult);
               $code         = $coderow["code"];                            // Saving code in variable.
              
               // Getting description of current error code.
               $descquery    = "select cast (description as varchar(255)) as errtxt from master.dbo.sysmessages where error = $code ";
               $descresult   = mssql_query($descquery, $this->connection);
               $descrow      = mssql_fetch_array($descresult);
              
               if ($descrow)
               {
                   $text   = $descrow["errtxt"]; // error text (with placeholders)
               }
               else
            {
                  $text   = "Unknown error";
               }
              mssql_free_result($descresult);
                            
              $text = str_replace("'%.*ls'",$value,$text);
              
               return "[".$code."] ". $text;
       }
       else
       {
               return '';
       }
   }
  
    //                    ------ Function end ------
  
   //--------------------------- public functions -----------------------
    
    // Change active database.
    public function ChangeDB($dbname)
    {
        if (trim($dbname) == '')
        {
            $dbname = 'master';
        }
        // Trying to change selected db.
        if (@mssql_select_db('['.$dbname.']'))
        {    
            // Action successful
            $this->lastaction=true;    
                
            // Setting message
            $this->message = "Successfuly connected to database [".$dbname."].";
            
            // Setting new db name in member.
            $this->dbname=$dbname;
        }
        else
        {
            // action failed.
            $this->lastaction=false;    
            
            // Saving php error message.
            $tempmessage = $php_errormsg;
            
            // Getting error message
            $this->sqlmessage = $this->GetSqlError($dbname);
            
            // if no message from sql, get php error.
            if (trim($this->sqlmessage) == '')
            {
                $this->sqlmessage = $tempmessage;
            }
        }        
        
        return $this->ReturnValue();
    }
    
    //                    ------ Function end ------
    
    // Connection interface function.
    public function connect()
    {
        $this->DB_Handler($this->dbname);
    }
    
    //                    ------ Function end ------
    
    
    // Create a query with the active connection.
    public function ExecQuery($query)
    {        
        // Setting member.
        $this->query = $query;
        
        // Checking succussful query.
        if ($this->result = @mssql_query($query, $this->connection))
        {
            // Action successful
            $this->lastaction = true;
            
            // Setting class message.
            $this->message = "Query successful.\r";  
            
        }
        else    
        {
            // Action failed.
            $this->lastaction = false;
            
            // Getting php error.
            $tempmessage = $php_errormsg;
            
            // Getting error message from db.
            $this->sqlmessage = $this->GetSqlError();
            
            // if no message from sql, get php error.
            if (trim($this->sqlmessage) == '')
            {
                $this->sqlmessage = $tempmessage;
            }
            
            // Setting class message.
            $this->message = "Database query failed.\r";
        }        
        
        return $this->ReturnValue();
    }
    
    //                    ------ Function end ------
    
    // Getting last query text.
    public function Getquery()
    {
        return $this->query;
    }
    
    //                    ------ Function end ------
    
    // Getting last message from class.
    public function GetMessage()
    {
        return $this->message;
    }
    
    // Getting last error from sql
    public function GetError()
    {
        return $this->sqlmessage;
    }
    
    // Getting next row of data.
    public function GetRow()
    {
        if ($this->rowdata = @mssql_fetch_array($this->result))
        {
            // Action successful
            $this->lastaction = true;
            
            // Setting class message.
            $this->message = "Row retrieved.\r";  
            
        }
        else    
        {
            // Action failed.
            $this->lastaction = false;
            
            // Getting php error.
            $tempmessage = @$php_errormsg;
            
            // Getting error message from db.
            $this->sqlmessage = $this->GetSqlError();
            
            // if no message from sql, get php error.
            if (trim($this->sqlmessage) == '')
            {
                $this->sqlmessage = $tempmessage;
            }
            
            // Setting class message.
            $this->message = "Failed to retrieve next row.\r";
        }
        return $this->ReturnValue($this->rowdata);
    }
    
    //                    ------ Function end ------
    
    
    // Get number of affected rows
    public function GetNumRows()
    {
        if (strtolower(substr(trim($this->query),0,6)) == 'select')
        {
            if ($value = mssql_num_rows($this->result))
            {
                // Action successful
                $this->lastaction = true;
                
                // Setting message.
                $this->message = "Successfully retrieved number of rows from select.\r";
            }
            else
            {
                // Action failed.
                $this->lastaction = false;
                
                // Getting php error.
                $tempmessage = @$php_errormsg;
                
                // Getting error message from db.
                $this->sqlmessage = $this->GetSqlError();
                
                // if no message from sql, get php error.
                if (trim($this->sqlmessage) == '')
                {
                    $this->sqlmessage = $tempmessage;
                }
                
                // Setting class message.
                $this->message = "Failed retrieving number of selected rows.\r";
            }
        }
        else
        {
            if ($value = mssql_rows_affected($this->connection))
            {
                // Action successful
                $this->lastaction = true;
                
                // Setting message.
                $this->message = "Successfully retrieved number of rows from select.\r";
            }
            else
            {
                // Action failed.
                $this->lastaction = false;
                
                // Getting php error.
                $tempmessage = @$php_errormsg;
                
                // Getting error message from db.
                $this->sqlmessage = $this->GetSqlError();
                
                // if no message from sql, get php error.
                if (trim($this->sqlmessage) == '')
                {
                    $this->sqlmessage = $tempmessage;
                }
                
                // Setting class message.
                $this->message = "Failed retrieving number of selected rows.\r";
            }
        }
        return $this->ReturnValue($value);
    }
    
    
    //                    ------ Function end ------
    
    
    // Get last insert id
    public function GetInsertID()
    {
        $query="SELECT @@IDENTITY as last_insert_id";
                
        // trying identity query
        if ($result = mssql_query($query, $this->connection))
        {    
            // Action successful.
            $this->lastaction=true;
            
            // Setting class message
            $this->message = "Identity column retrieved.\r";
                    
            // retrieving result from successful query.
            $insert = mssql_fetch_array($result);
        }
        else
        {
            // Action failed.
            $this->lastaction = false;
            
            // Getting php error.
            $tempmessage = $php_errormsg;
            
            // Getting error message from db.
            $this->sqlmessage = $this->GetSqlError();
            
            // if no message from sql, get php error.
            if (trim($this->sqlmessage) == '')
            {
                $this->sqlmessage = $tempmessage;
            }
            
            // Setting class message.
            $this->message = "Failed retrieving identity column.\r";
        }
        return $this->ReturnValue($insert['last_insert_id']);
    }
    
    
    //                    ------ Function end ------
    
    
    
}

?>
[/code]

the header file: (header.php)
[code]
<?php

include_once('DB_Handler_class.php');

session_start();
ini_set('track_errors',1);
if (isset($_SESSION['DB']))
{
    $_SESSION['DB']->connect();
}

?>
<HTML>
<HEAD>
<script type="text/javascript">
document.cookie = "TemporaryTestCookie=yes;";
if(document.cookie.indexOf("TemporaryTestCookie=") == -1) {
     alert("This site requires the usage of session cookies.");
     window.location = "Cookies.html";
     }
</script>
</head>
<body>

[/code]

the first page: (index.php)
[code]
<?php
include_once('header.php');
?>
<?php
echo '<h3> checking of DB_Hanlder class: </h3>';


echo '<br /> <b> trying to connect to wrong hostname, no db - with first initialization of singleton class. </b>';

$conntest = DB_Handler::GetInstance("","lala");
if ($conntest['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$conntest['message'];
    echo "<br /> error is : " .$conntest['error'];
    $_SESSION['DB'] = $conntest['data'];
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$conntest['message'];
    echo "<br /> error is : " .$conntest['error'];
}


echo '<br /><br /> <b> trying to connect to right hostname, wrong db. (refresh of singleton class) </b>';

$conntest = DB_Handler::RefreshInstance("nondb");
if ($conntest['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$conntest['message'];
    echo "<br /> error is : " .$conntest['error'];
    $_SESSION['DB'] = $conntest['data'];
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$conntest['message'];
    echo "<br /> error is : " .$conntest['error'];
}

echo '<br /><br /> <b> trying to connect correctly without db(make sure u inserted correct connection parameters) </b>';

$conntest = DB_Handler::RefreshInstance("");
if ($conntest['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$conntest['message'];
    echo "<br /> error is : " .$conntest['error']."<br />";
    $_SESSION['DB'] = $conntest['data'];
    echo '<a href="queries.php"> create test db and tables and check queries. </a>';
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$conntest['message'];
    echo "<br /> error is : " .$conntest['error'];
    echo "please make sure u insert correct information for the sql connection on the second test.";
}


/*
if (!($_SESSION['DB']->ExecQuery("select * from test1")))
{
    echo $_SESSION['DB']->GetMessage();
    echo $_SESSION['DB']->GetError();
}
else
{
    print_r($_SESSION['DB']->GetRow());
}

?>
*/
?>


</BODY>
</HTML>
[/code]

and the second page: (queries.php)
[code]
<?php
include_once('header.php');
?>

<?php
echo '<h2> this is page 2 of DB_Hanlder class tests. </h2>';

// Instance of db connection is supposed to be defined already.
echo '<br /> <b> trying to create a new db. connection supposed to be established. </b>';

$result = $_SESSION['DB']->ExecQuery("CREATE DATABASE [testdb]");
if ($result['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];    
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];
}

echo '<br /><br /> <b> Changing to the new created db. </b>';

$result = $_SESSION['DB']->ChangeDB("testdb");
if ($result['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];    
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];
}

echo '<br /><br /> <b> Creating new table. </b>';

$result = $_SESSION['DB']->ExecQuery("CREATE TABLE [Warner] ([id] [int] IDENTITY (1,1) NOT NULL ,[name] [varchar] (30) NOT NULL , [part] [varchar] (30) NOT NULL)");
if ($result['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];    
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];
}

echo '<br /><br /> <b> Inserting value. </b>';

$result = $_SESSION['DB']->ExecQuery("INSERT INTO warner VALUES('twitty','bird')");
if ($result['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];    
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];
}

echo '<br /><br /> <b> Inserting another value. </b>';

$result = $_SESSION['DB']->ExecQuery("INSERT INTO warner VALUES('twitty','bird')");
if ($result['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];    
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];
}


echo '<br /><br /> <b> Getting insert id. </b>';

$result = $_SESSION['DB']->GetInsertID();
if ($result['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];    
    echo "<br /> insert id is : " .$result['data'];
    $tempid = $result['data'];
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];
}


echo '<br /><br /> <b> selecting data from created table. </b>';

$result = $_SESSION['DB']->ExecQuery("Select * from warner");
if ($result['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];    
    
    echo '<br /> <b> printing data. </b>';
    
    $row = $_SESSION['DB']->GetRow();
    while ($row['bool'])
    {
        echo "<br />returned true: <br />";
        echo "message is : " .$row['message'];
        echo "<br /> error is : " .$row['error'];    
        echo "<br/> rowdata is : ";
        print_r($row['data']);
        $row = $_SESSION['DB']->GetRow();
    }
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];

}


echo '<br /><br /> <b> updating by id from earlier insert. </b>';

$result = $_SESSION['DB']->ExecQuery("update warner set name='sylvester',part='cat' where id=".$tempid);
if ($result['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];    
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];
}

echo '<br /><br /> <b> getting number of rows affected : </b>';

$result = $_SESSION['DB']->GetNumRows();
if ($result['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];    
    echo "<br /> number rows selected: " .$result['data'];
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];
}

echo '<br /><br /> <b> selecting data after update. </b>';

$result = $_SESSION['DB']->ExecQuery("Select * from warner");
if ($result['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];    
    
    echo '<br /> <b> printing data. </b>';
    
    $row = $_SESSION['DB']->GetRow();
    while ($row['bool'])
    {
        echo "<br />returned true: <br />";
        echo "message is : " .$row['message'];
        echo "<br /> error is : " .$row['error'];    
        echo "<br/> rowdata is : ";
        print_r($row['data']);
        $row = $_SESSION['DB']->GetRow();
    }
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];

}

echo '<br /><br /> <b> getting number of rows selected : </b>';

$result = $_SESSION['DB']->GetNumRows();
if ($result['bool'])
{
    echo "returned true: <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];    
    echo "<br /> number rows selected: " .$result['data'];
}
else
{
    echo "returned false : <br />";
    echo "message is : " .$result['message'];
    echo "<br /> error is : " .$result['error'];
}
?>

</BODY>
</HTML>
[/code]

a few remarks of my own:

- im not sure how safe it is to put the db connection data (with password and all) as a session variable.
please your insights on this issue..

- getting errors from sql server is a bit problematic, since the function mssql_get_last_message only returns the last message, as is written in many places. i used a method published in the php documentation (one of the user responses), but this method doesnt include getting the name of the problematic object...
i thought about writing a parser for the query, and get the object names, but decided to postpone it for now.
if anyone has a solution for this it will be most welcome.

- this class only supports one query at a time. u can't stop in the middle of one query,
go to another, and then return to the old one without executing it again.
i didn't bother to support multiple queries, since i believe a well organized code and database prevent
any such needs. also, mssql is sufficiently sofiticated for getting all the needed data in one query.
your thoughts on this matter will of course be very welcome as well.