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.

hey again all you generous helpers, i tried writing a test to see if this code really acts like i thought, and well, it doesnt really. my class is: [code] <?php // file singleton_class.php class singleton {     private static $instance;     private $test;          public static function GetInstance()     {         if (empty(self::$instance))         {             self::$instance = new singleton();             echo 'get instance';         }         return self::$instance;     }          public function settest($value)     {         $this->test = $value;     }     public function gettest()     {         return $this->test;     } } ?> [/code] and i have two pages: index.php: [code] <HTML> <BODY> <?php include_once('singleton_class.php'); $single = singleton::GetInstance(); $single->settest("lalala"); echo $single->gettest(); ?> <a href="page2.php"> click </a> </BODY> </HTML> [/code] and page2.php: [code] <HTML> <BODY> <?php include_once('singleton_class.php'); $single = singleton::GetInstance(); echo $single->gettest(); ?> <b> this is page 2 </b> </BODY> </HTML> [/code] in both pages i get the line 'get instance' printed from inside the getinstance() method... which means that the instance is a new one in every page. i thought it will be the same instance, otherwise what is the value of making it static? i suppose i misunderstand something..

Thanks thorpe. thats what i was looking for.

Hey, thanks obsidian, your replies sure help. i still want to clarify something regarding the first question\answer. If i say what you suggest, that is include all my needs in the shared header files, i will be able to define my classes in every one of the variable. but my question was a little different. i want to have a shared 'instance' of that class. like a static\global general class instance that is shared for all pages. i think this is called a single tone in application programming.. is there anyway to implement something like that with php? (this way if my db connection resource is part of that class, i dont need to connect every time as well). p.s. how much posts do you have to post to get to super-guru???

Hello all. i want to develop a php application for my web site. i have decided to take the object-oriented approach since, well , since i like it.. my problem is i am kind of new to web programming and missing some vital information. hope you guys can help me with these: 1. how do i make sure a main class i write will be available in every one of the script pages? do i need to make it a session variable? and save it to the session every time i make a change of the data? or maybe i need to include the loading of the data in every one of the scripts and pages of the application? 2. i guess this is very similar to the first question - how do i open one db-connection at the loading of the application and use it throughout the different pages of my site? is there any reason (security or such) not to keep the connection open, and reopen it with every query? 3. how does php know when a user switches application? by the url? i guess my question is, if i have a link in my site, to a different site, how does it know not to pass the session variables to that site when a user clicks the link? 4. if i define a global variable, does it have the same scope as a session or application variable? i guess i missed the correct documentation since all my questions regard to scope in web application. i learned php from a php manual that contained full reference, but no guide... so if anyone will be willing to point me to the right documentation section i will be very gratefull.. thanks.

never mind... i found the problem. it was a '}' i accidently erased i guess... just like all the different forum posts in this issue say... guess i was to quick in redeeming myself :)

Hey all, i was happily adding some features to a web page, when suddenly i got this message: arse error: parse error, unexpected $ in /homepages/44/d161450496/htdocs/video/play.php on line 629 like every time with scripting errors, i started marking out the chunks of code i have updated (i didn't change anything, just added, so it was easy to remark the new code..) after remarking all the new code i still get this message. i wouldn't be worried about it usually and would have continued searching, but the thing is that my php file has only 626 lines... how is it possible that the error is on a line that doesn't exist? please help me.. [img src=\"style_emoticons/[#EMO_DIR#]/unsure.gif\" style=\"vertical-align:middle\" emoid=\":unsure:\" border=\"0\" alt=\"unsure.gif\" /]

hi all, if i have a php web application that requires logon, and i have a userid for that application. is it possible to write a local php script, that will login to the application and will do actions inside this application without opening the pages in the web-browser? (that my script will emulate a user activating the application from within the browser..) or even better, is it possible to activate locally the functions that are defined in the remote scripts? thanks. bitman