February 6th, 2007 - by cpradio

For those just joining, it is suggested that you begin with OOP in PHP from a .NET OOP Perspective as this is Part 3 of a series.

This time I am going to talk about the Database class, as every project now-a-days typically stores their information in a database. A good database class can save you time and memory in retrieving results. It should be able to cache previous queries, to recall them later, and it should clear out the results from memory when they are no longer needed.

As with previous articles, it should be stated that the below code will only work using PHP 5.

In this article, I am going to post my code first and then give a break down of the important segments. Again, this is my take on solving a problem for my applications and I am sure there are other approaches, but this is the one I am most pleased with.

  1. class Database
  2. {
  3.   ## CONSTANT VARIABLES
  4.     const DB_TYPES = 'mysql, mysqli';
  5.   ## END CONSTANT VARIABLES
  6.  
  7.   ## PUBLIC VARIABLES
  8.   ## END PUBLIC VARIABLES
  9.  
  10.   ## PRIVATE VARIABLES
  11.     private static $host;
  12.     private static $port;
  13.     private static $database;
  14.     private static $username;
  15.     private static $password;
  16.     private static $type;
  17.     private static $connection;
  18.     private static $savedQueries;
  19.     private static $savedResults;
  20.   ## END PRIVATE VARIABLES
  21.  
  22.   ## CONSTRUCTOR
  23.   ## END CONSTRUCTOR
  24.  
  25.   ## DECONSTRUCTOR
  26.   ## END DECONSTRUCTOR
  27.  
  28.   ## PUBLIC METHODS
  29.     // Initialize the Variables
  30.     // Does not return anything, but acts like a constructor for Static classes
  31.     public static function Initialize($varType, $varHost, $varPort, $varDatabase, $varUsername, $varPassword)
  32.     {
  33.       Error::Initialize();
  34.      
  35.       if (!self::ValidDatabaseTypes($varType))
  36.       {
  37.         Error::LogError("Database Type Invalid", "Database Type must be one of: " . self::DB_TYPES);
  38.       }
  39.      
  40.       self::$host         = $varHost;
  41.       self::$port         = $varPort;
  42.       self::$type         = strtolower($varType);
  43.       self::$database     = $varDatabase;
  44.       self::$password     = $varPassword;
  45.       self::$username     = $varUsername;
  46.       self::$savedQueries = array();
  47.       self::$savedResults = array();
  48.       self::$connection   = self::ConnectToDatabase();
  49.      
  50.       self::SelectTheDatabase();
  51.     }
  52.    
  53.     // DeInitialize the Variables
  54.     // Does not return anything, but acts like a destructor for Static classes
  55.     public static function DeInitialize()
  56.     {
  57.       switch (self::$type)
  58.       {
  59.         case "mysql":
  60.           @mysql_close(self::$connection) or Error::LogError("MySQL Failed to Close", mysql_error(self::$connection));
  61.           break;
  62.         case "mysqli":
  63.           @mysqli_close(self::$connection) or Error::LogError("MySQL Failed to Close", mysqli_error(self::$connection));
  64.           break;
  65.       }
  66.      
  67.       self::$host         = null;
  68.       self::$port         = null;
  69.       self::$type         = null;
  70.       self::$database     = null;
  71.       self::$password     = null;
  72.       self::$username     = null;
  73.       self::$connection   = null;
  74.       self::$savedQueries = null;
  75.       self::$savedResults = null;
  76.       Error::DeInitialize();
  77.     }
  78.    
  79.     // Database Types
  80.     // Returns an array of database types
  81.     public static function DatabaseTypes()
  82.     {
  83.       return split(",", self::DB_TYPES);
  84.     }
  85.    
  86.     // Execute SQL Query
  87.     // Returns the result of the query, which is typically a resource id
  88.     public static function ExecuteQuery($sql, $name)
  89.     {
  90.       if (self::$connection)
  91.       {
  92.         if (strlen(trim($name)) != 0)
  93.         {
  94.           switch (self::$type)
  95.           {
  96.             case "mysql":
  97.               if (!array_key_exists($name, self::$savedQueries))
  98.               {
  99.                 self::$savedQueries[$name] = @mysql_query($sql, self::$connection) or Error::LogError("Query Failed", mysql_error(self::$connection));
  100.               }
  101.               break;
  102.             case "mysqli":
  103.               if (!array_key_exists($name, self::$savedQueries))
  104.               {
  105.                 self::$savedQueries[$name] = @mysqli_query(self::$connection, $sql) or Error::LogError("Query Failed", mysqli_error(self::$connection));
  106.               }
  107.               break;
  108.           }
  109.          
  110.           return self::$savedQueries[$name];
  111.         }
  112.         else
  113.         {
  114.           Error::LogError("Execute Query Name Missing", "The name parameter was empty, please provide a name for the query.");
  115.         }
  116.       }
  117.      
  118.       return null;
  119.     }
  120.    
  121.     // Fetch Results
  122.     // Returns an array of the query results
  123.     public static function FetchResults($name)
  124.     {
  125.       $results = array();
  126.       if (self::$connection)
  127.       {
  128.         if (strlen(trim($name)) != 0 && (array_key_exists($name, self::$savedQueries) || array_key_exists($name, self::$savedResults)))
  129.         {
  130.           if (array_key_exists($name, self::$savedQueries))
  131.           {
  132.             switch (self::$type)
  133.             {
  134.               case "mysql":
  135.                 $row = 0;
  136.                 while ($currentResult = @mysql_fetch_assoc(self::$savedQueries[$name]))
  137.                 {
  138.                   $col = 0;
  139.                   foreach ($currentResult as $key => $value)
  140.                   {
  141.                     $results[$row][$col] = $value;
  142.                     $results[$row][$key] = $value;
  143.                     $col++;
  144.                   }
  145.                  
  146.                   $row++;
  147.                 }
  148.                 break;
  149.               case "mysqli":
  150.                 $row = 0;
  151.                 while ($currentResult = @mysqli_fetch_assoc(self::$savedQueries[$name]))
  152.                 {
  153.                   $col = 0;
  154.                   foreach ($currentResult as $key => $value)
  155.                   {
  156.                     $results[$row][$col] = $value;
  157.                     $results[$row][$key] = $value;
  158.                     $col++;
  159.                   }
  160.                  
  161.                   $row++;
  162.                 }
  163.                 break;
  164.             }
  165.          
  166.             self::$savedResults[$name] = $results;
  167.           }
  168.           else
  169.           {
  170.             $results = self::$savedResults[$name];
  171.           }
  172.         }
  173.         else
  174.         {
  175.           if (strlen(trim($name)) == 0)
  176.           {
  177.             Error::LogError("Fetch Results Name Missing", "The name parameter was empty, the name is required so it knows which results to return.");
  178.           }
  179.           else
  180.           {
  181.             Error::LogError("Fetch Results Name ('{$name}') Not Found", "The name provided did not have any query results associated with it.");
  182.           }
  183.         }
  184.       }
  185.      
  186.       return $results;
  187.     }
  188.    
  189.     // Free SQL Query Results
  190.     // Returns nothing
  191.     public static function FreeResults($name)
  192.     {
  193.       if (self::$connection)
  194.       {
  195.         if (strlen(trim($name)) != 0 && array_key_exists($name, self::$savedQueries))
  196.         {
  197.           switch (self::$type)
  198.           {
  199.               case "mysql":
  200.                 @mysql_free_result(self::$savedQueries[$name]) or Error::LogError("Free Results Error", mysql_error(self::$connection));
  201.                 unset(self::$savedQueries[$name]);
  202.                 break;
  203.             case "mysqli":
  204.               @mysqli_free_result(self::$savedQueries[$name]) or Error::LogError("Free Results Error", mysqli_error(self::$connection));
  205.               unset(self::$savedQueries[$name]);
  206.               break;
  207.           }
  208.         }
  209.         else
  210.         {
  211.           if (strlen(trim($name)) == 0)
  212.           {
  213.             Error::LogError("Free Results Name Missing", "The name parameter was empty, the name is required so it knows which results to free up from memory.");
  214.           }
  215.           else
  216.           {
  217.             Error::LogWarning("Free Results Name ('{$name}') Not Found", "The name provided did not have any query results associated with it.");
  218.           }
  219.         }
  220.       }
  221.     }
  222.    
  223.     // Remove Saved Results
  224.     // Returns nothing
  225.     public static function RemoveSavedResults($name)
  226.     {
  227.       if (strlen(trim($name)) != 0 && array_key_exists($name, self::$savedResults))
  228.       {
  229.         unset(self::$savedResults[$name]);
  230.       }
  231.       else
  232.       {
  233.         if (strlen(trim($name)) == 0)
  234.         {
  235.           Error::LogError("Remove Saved Result Name Missing", "The name parameter was empty, the name is required so it knows which query to remove.");
  236.         }
  237.         else
  238.         {
  239.           Error::LogWarning("Remove Saved Result Name ('{$name}') Not Found", "The name provided was not a saved query.");
  240.         }
  241.       }
  242.     }
  243.    
  244.     // Attempt Connect To Database
  245.     // Returns true or false depending on if the connection failed or succeeded
  246.     public static function AttemptConnectToDatabase($varType, $varHost, $varPort, $varDatabase, $varUsername, $varPassword)
  247.     {
  248.       self::$type       = $varType;
  249.       self::$host       = $varHost;
  250.       self::$port       = $varPort;
  251.       self::$database   = $varDatabase;
  252.       self::$username   = $varUsername;
  253.       self::$password   = $varPassword;
  254.      
  255.       Error::ClearErrors();
  256.       self::$connection = self::ConnectToDatabase();
  257.      
  258.       if (!Error::HasErrors())
  259.       {
  260.         return true;
  261.       }
  262.       else
  263.       {
  264.         return false;
  265.       }
  266.     }
  267.    
  268.     // MySQL Version
  269.     // Returns the mysql version number
  270.     public static function MysqlVersion()
  271.     {
  272.       $version = "";
  273.       if (self::$connection)
  274.       {
  275.         switch (self::$type)
  276.         {
  277.           case "mysql":
  278.             $version = mysql_get_server_info(self::$connection);
  279.             break;
  280.           case "mysqli":
  281.             $version = mysqli_get_server_info(self::$connection);
  282.             break;
  283.         }
  284.       }
  285.      
  286.       return $version;
  287.     }
  288.   ## END PUBLIC METHODS
  289.  
  290.   ## PRIVATE METHODS
  291.     // Connect to Database
  292.     // Returns the database connection resource
  293.     private static function ConnectToDatabase()
  294.     {
  295.       $link = null;
  296.      
  297.       switch (self::$type)
  298.       {
  299.         case "mysql":
  300.           if (strlen(trim(self::$port)) != 0)
  301.           {
  302.             $link = @mysql_connect(self::$host . ":" . self::$port, self::$username, self::$password) or Error::LogError("Database Error", mysql_error());
  303.           }
  304.           else
  305.           {
  306.             $link = @mysql_connect(self::$host, self::$username, self::$password) or Error::LogError("Database Error", mysql_error());
  307.           }
  308.           break;
  309.         case "mysqli":
  310.           $link = @mysqli_connect(self::$host, self::$username, self::$password, self::$database, self::$port) or Error::LogError("Database Error", mysqli_connect_error());
  311.           break;
  312.       }
  313.      
  314.       return $link;
  315.     }
  316.    
  317.     // Select the Database
  318.     // Returns nothing
  319.     private static function SelectTheDatabase()
  320.     {
  321.       switch (self::$type)
  322.       {
  323.         case "mysql":
  324.           @mysql_select_db(self::$database, self::$connection) or Error::LogError("Database Selection", mysql_error(self::$connection));
  325.           break;
  326.       }
  327.     }
  328.    
  329.     // Valid Database Types
  330.     // Returns true or false depending on if the database type is valid
  331.     private static function ValidDatabaseTypes($varType)
  332.     {
  333.       if (strlen($varType) !=0 && strpos(self::DB_TYPES, strtolower($varType)) !== false)
  334.       {
  335.         return true;
  336.       }
  337.      
  338.       return false;
  339.     }
  340.   ## END PRIVATE METHODS
  341.  
  342.   ## PROTECTED METHODS
  343.   ## END PROTECTED METHODS
  344. }

That is the Database class I use in my primary applications, note that it currently only has support for MySQL and MySQLi, but it could be extended to use Postgres and ODBC easily. Considering you never know what applications or platforms you may be writing for, let this be the first lesson. Always, write your classes to work on any platform. If your first project targets only a single platform, that is fine, but make it so it can be extended to fit another platform without having to rewrite significant portions of your original code. Let’s face it, if you had to rewrite significant portions, you would create a new file for each platform because you would not want to go back into previous projects and update the implementation of your classes.

Let’s start out with the constant variable being used.

  1. ## CONSTANT VARIABLES
  2.   const DB_TYPES = 'mysql, mysqli';
  3. ## END CONSTANT VARIABLES

This is where the extensibility starts. Each different platform will be separated by a comma. Below the constants are the private variables for the class, and I will describe those as they are used. Now like the Error class, the Database class has an ‘Initialize()’ method too.

  1. // Initialize the Variables
  2. // Does not return anything, but acts like a constructor for Static classes
  3. public static function Initialize($varType, $varHost, $varPort, $varDatabase, $varUsername, $varPassword)
  4. {
  5.   Error::Initialize();
  6.  
  7.   if (!self::ValidDatabaseTypes($varType))
  8.   {
  9.     Error::LogError("Database Type Invalid", "Database Type must be one of: " . self::DB_TYPES);
  10.   }
  11.  
  12.   self::$host         = $varHost;
  13.   self::$port         = $varPort;
  14.   self::$type         = strtolower($varType);
  15.   self::$database     = $varDatabase;
  16.   self::$password     = $varPassword;
  17.   self::$username     = $varUsername;
  18.   self::$savedQueries = array();
  19.   self::$savedResults = array();
  20.   self::$connection   = self::ConnectToDatabase();
  21.  
  22.   self::SelectTheDatabase();
  23. }

One difference between the Error class’s ‘Initialize()’ method and the Database class’s, is that the Database class requires parameters to be sent. This class needs to know a few things, which is why the parameters are required. It needs to know the type of platform being used, the server name/ip address, the port number, the database name, the username to connect, and the password for the username.

Notice, that I use the Database’s ‘Initialize()’ method to initialize the Error class. Since I know all of my applications will be using a Database, this is a good approach for me, however, if you wish to use the Error class in applications that do not have a Database, then I strongly suggest taking the Error::Initialize() out of the above code and into a more global area.

Next, the Database Type needed to be validated. To do that, we call a private method named ‘ValidDatabaseTypes’ and pass it the type of database (or platform). In this case, it should be either ‘mysql’ or ‘mysqli’.

  1. // Valid Database Types
  2. // Returns true or false depending on if the database type is valid
  3. private static function ValidDatabaseTypes($varType)
  4. {
  5.   if (strlen($varType) !=0 && strpos(self::DB_TYPES, strtolower($varType)) !== false)
  6.   {
  7.     return true;
  8.   }
  9.  
  10.   return false;
  11. }

This code is very basic, it simply looks to find the Database type passed to ‘Initialize()’ inside the constant string ‘DB_TYPES’. If it finds the type, then it will return “true”, otherwise, it will return “false”. If it returns “false”, it will then log an error, using the Error class, stating such.

After the database type the “Initialize” method then stores the information passed into the class variables. The last variable stores the connection reference using the “ConnectToDatabase”.

  1. nnect to Database
  2. // Returns the database connection resource
  3. private static function ConnectToDatabase()
  4. {
  5.   $link = null;
  6.  
  7.   switch (self::$type)
  8.   {
  9.     case "mysql":
  10.       if (strlen(trim(self::$port)) != 0)
  11.       {
  12.         $link = @mysql_connect(self::$host . ":" . self::$port, self::$username, self::$password) or Error::LogError("Database Error", mysql_error());
  13.       }
  14.       else
  15.       {
  16.         $link = @mysql_connect(self::$host, self::$username, self::$password) or Error::LogError("Database Error", mysql_error());
  17.       }
  18.       break;
  19.     case "mysqli":
  20.       $link = @mysqli_connect(self::$host, self::$username, self::$password, self::$database, self::$port) or Error::LogError("Database Error", mysqli_connect_error());
  21.       break;
  22.   }
  23.  
  24.   return $link;
  25. }

The “ConnectToDatabase” method takes the type of database, which was stored in the “Initialize” method into the self::$type variable, and then runs the appropriate connection function in PHP returning the resource identifier to be stored back into the variable.

You may be wondering why I am returning the connection information, instead of just setting it in the “ConnectToDatabase” method. I prefer to have any private methods I write return back the information to my public methods so I am not second guessing where a variable got set. So it is strictly a readability reasoning and definitely a personal preference.

Back to the Database class, I finished off the “Initialize” method by running the “SelectTheDatabse” to select the Database to use.

  1. // Select the Database
  2. // Returns nothing
  3. private static function SelectTheDatabase()
  4. {
  5.   switch (self::$type)
  6.   {
  7.     case "mysql":
  8.       @mysql_select_db(self::$database, self::$connection) or Error::LogError("Database Selection", mysql_error(self::$connection));
  9.       break;
  10.   }
  11. }

Again, this takes the self::$type variable to define which function to run. Notice that the “mysqli” type is not listed in here. This is because with “mysqli” you have the ability to specify the database name in the mysqli_connect function.

Now that the “Initialize” method is out of the way and the private methods associated with it, we can start to focus on executing a query, fetching the results of an executed query, freeing the results of a query, and removing the cached results.

The “ExecuteQuery” which receives two parameters (the query, and a name/identifier for the query). The name/identifier will be used later for fetching the results. In short, it will also help cache the results to be retrieved later.

  1. // Execute SQL Query
  2. // Returns the result of the query, which is typically a resource id
  3. public static function ExecuteQuery($sql, $name)
  4. {
  5.   if (self::$connection)
  6.   {
  7.     if (strlen(trim($name)) != 0)
  8.     {
  9.       switch (self::$type)
  10.       {
  11.         case "mysql":
  12.           if (!array_key_exists($name, self::$savedQueries))
  13.           {
  14.             self::$savedQueries[$name] = @mysql_query($sql, self::$connection) or Error::LogError("Query Failed", mysql_error(self::$connection));
  15.           }
  16.           break;
  17.         case "mysqli":
  18.           if (!array_key_exists($name, self::$savedQueries))
  19.           {
  20.             self::$savedQueries[$name] = @mysqli_query(self::$connection, $sql) or Error::LogError("Query Failed", mysqli_error(self::$connection));
  21.           }
  22.           break;
  23.       }
  24.      
  25.       return self::$savedQueries[$name];
  26.     }
  27.     else
  28.     {
  29.       Error::LogError("Execute Query Name Missing", "The name parameter was empty, please provide a name for the query.");
  30.     }
  31.   }
  32.  
  33.   return null;
  34. }

Before the query can be executed, it needs to ensure the connection to the database succeeded. Following that, there needs to be a name/identifier to store the query resource in. Each query resource will be stored in the self::$savedQueries array. This will save memory resources. Let’s say you wrote a modular application and two modules need to run the same query. No matter which order they run in, when the first module runs the query, it will save the resource identifier of the query into self::$savedQueries and then the second module using the same identifier for the query execution will find that it was already executed and reuse the same query data. Thus, there was only one hit on the database for the query instead of two.

A similar approached was used in the “FetchResults” method too. Notice that only the identifier is passed to this method, as it will pull the query resource out of the self::$savedQueries and build a results array or it will pull the results from self::$savedResults and reuse what it retrieved earlier.

  1. // Fetch Results
  2. // Returns an array of the query results
  3. public static function FetchResults($name)
  4. {
  5.   $results = array();
  6.   if (self::$connection)
  7.   {
  8.     if (strlen(trim($name)) != 0 && (array_key_exists($name, self::$savedQueries) || array_key_exists($name, self::$savedResults)))
  9.     {
  10.       if (array_key_exists($name, self::$savedQueries))
  11.       {
  12.         switch (self::$type)
  13.         {
  14.           case "mysql":
  15.             $row = 0;
  16.             while ($currentResult = @mysql_fetch_assoc(self::$savedQueries[$name]))
  17.             {
  18.               $col = 0;
  19.               foreach ($currentResult as $key => $value)
  20.               {
  21.                 $results[$row][$col] = $value;
  22.                 $results[$row][$key] = $value;
  23.                 $col++;
  24.               }
  25.              
  26.               $row++;
  27.             }
  28.             break;
  29.           case "mysqli":
  30.             $row = 0;
  31.             while ($currentResult = @mysqli_fetch_assoc(self::$savedQueries[$name]))
  32.             {
  33.               $col = 0;
  34.               foreach ($currentResult as $key => $value)
  35.               {
  36.                 $results[$row][$col] = $value;
  37.                 $results[$row][$key] = $value;
  38.                 $col++;
  39.               }
  40.              
  41.               $row++;
  42.             }
  43.             break;
  44.         }
  45.      
  46.         self::$savedResults[$name] = $results;
  47.       }
  48.       else
  49.       {
  50.         $results = self::$savedResults[$name];
  51.       }
  52.     }
  53.     else
  54.     {
  55.       if (strlen(trim($name)) == 0)
  56.       {
  57.         Error::LogError("Fetch Results Name Missing", "The name parameter was empty, the name is required so it knows which results to return.");
  58.       }
  59.       else
  60.       {
  61.         Error::LogError("Fetch Results Name ('{$name}') Not Found", "The name provided did not have any query results associated with it.");
  62.       }
  63.     }
  64.   }
  65.  
  66.   return $results;
  67. }

Just like the “ExecuteQuery” method, you can reuse any previous identifiers so long as they still exist in self::$savedResults. All of the information from each query will be stored in this variable until it is cleared out using “RemoveSavedResults” which will be discussed a bit later. Before we discuss that, I want to point out the necessity of the “FreeResults” method.

MySQL keeps results in memory until requested to free them. This is where the “FreeResults” method comes into play. It is a VERY good idea to clean out the memory space as soon as you can, hence why we are caching the results into an array to be reused. Note, that once you free the results, they will be unretrieveable using the Query Identifier returned by mysql_query and mysqli_query so the only way to reuse the previous information is to get it from “FetchResults”. The “FetchResults” will grab it from self::$savedResults instead of self::$savedQueries.

  1. // Free SQL Query Results
  2. // Returns nothing
  3. public static function FreeResults($name)
  4. {
  5.   if (self::$connection)
  6.   {
  7.     if (strlen(trim($name)) != 0 && array_key_exists($name, self::$savedQueries))
  8.     {
  9.       switch (self::$type)
  10.       {
  11.         case "mysql":
  12.           @mysql_free_result(self::$savedQueries[$name]) or Error::LogError("Free Results Error", mysql_error(self::$connection));
  13.           unset(self::$savedQueries[$name]);
  14.           break;
  15.         case "mysqli":
  16.           @mysqli_free_result(self::$savedQueries[$name]) or Error::LogError("Free Results Error", mysqli_error(self::$connection));
  17.           unset(self::$savedQueries[$name]);
  18.           break;
  19.       }
  20.     }
  21.     else
  22.     {
  23.       if (strlen(trim($name)) == 0)
  24.       {
  25.         Error::LogError("Free Results Name Missing", "The name parameter was empty, the name is required so it knows which results to free up from memory.");
  26.       }
  27.       else
  28.       {
  29.         Error::LogWarning("Free Results Name ('{$name}') Not Found", "The name provided did not have any query results associated with it.");
  30.       }
  31.     }
  32.   }
  33. }

Notice that it ensures the identifier exists in self::$savedQueries and if it does, it clears out the result information from MySQL and it removes itself from self::$savedQueries.

Now that takes care of the memory allocations by MySQL, but what if you are done with the results you fetched from MySQL and no longer want them cached for other pieces of your application? Well, I arranged a method for that purpose too. Enter “RemoveSavedResults”.

  1. // Remove Saved Results
  2. // Returns nothing
  3. public static function RemoveSavedResults($name)
  4. {
  5.   if (strlen(trim($name)) != 0 && array_key_exists($name, self::$savedResults))
  6.   {
  7.     unset(self::$savedResults[$name]);
  8.   }
  9.   else
  10.   {
  11.     if (strlen(trim($name)) == 0)
  12.     {
  13.       Error::LogError("Remove Saved Result Name Missing", "The name parameter was empty, the name is required so it knows which query to remove.");
  14.     }
  15.     else
  16.     {
  17.       Error::LogWarning("Remove Saved Result Name ('{$name}') Not Found", "The name provided was not a saved query.");
  18.     }
  19.   }
  20. }

This method is short and sweet. It checks that the identifier exists in self::$savedResults and then it removes the information stored under that identifier so it is no longer cached for the rest of the application’s execution.

Wrapping it up…
There are a few other public methods in this class, but they are not very important to an application’s design. They are there because I have found use for them within my own applications, and so you can keep them or remove them, it’s up to you. Oh, and one of the MOST important pieces, the “DeInitialize” method. This should be executed at the end of your application’s execution. This way, if you have multiple developers working on your project and one of them forgets to run “RemoveSavedResults” or “FreeResults”, the “DeInitialize” method will ensure the information is cleared out and it ensures the Database connection is closed.

Well this concludes Part 3 of my series, and Part 4 will be focusing on a Design class. The idea behind a Design class, is to give your application a template based structure, so you can support multiple designs. Plus it will allow you to create a modular application, so you are programming pieces of the application rather than the whole. I realize that may sound confusing, but you will have to read my next part to get a firmer understanding.

Update: Sample of Use

  1. $sql = "CREATE TABLE `{$GLOBALS['config']['db_prefix']}Activation` (
  2.             `ID_ACTIVATION` int(11) NOT NULL auto_increment,
  3.             `ID_USER` int(11) NOT NULL,
  4.             `code` varbinary(20) NOT NULL,
  5.             `status` enum('Activated','Not Activated') NOT NULL default 'Not Activated',
  6.             `dateCreated` datetime NOT NULL,
  7.             `createdBy` int(11) NOT NULL,
  8.             `lastUpdated` datetime default NULL,
  9.             `updatedBy` int(11) default NULL,
  10.             PRIMARY KEY  (`ID_ACTIVATION`),
  11.             KEY `ID_USER` (`ID_USER`,`status`)
  12.           ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;";
  13.   Database::ExecuteQuery($sql, "CreateTable");
  14.   Database::FetchResults("CreateTable");
  15.   Database::FreeResults("CreateTable");
  16.   Database::RemoveSavedResults("CreateTable");

14 Responses to “OOP in PHP from a .NET OOP Perspective: The Database Class”

1 Golgotha

Holy crap Matt, that’s a long post!

I’m going to have to print this one and read it tonight before bed. I suck at PHP so hopefully reading this stuff as I doze off to sleep will help me take it all in.

2 BianchiKid

This is a great article but it would be even better if you could include a few lines of code to demonstrate how to instantiate and use the class.

3 cpradio

Sample of use is now provided.

4 Contrid

Thanks for the great post.
This is really extremely useful.

5 Peter Mansen

Great Thank’s.

6 Robert

Just came across these articles today and found them very informative. Thank you for posting them.
I started looking around the net a couple weeks ago for a solution to cache queries using PHP Memcache. It surprised me that there is not much information for incorporating Memcache with a database class.
Your solution here gives me some ideas on how to go about it, but I’m still relatively new to all this. I would be interested in seeing how you would implement something like Memcache.

Anyway, thanks for the info.

7 cpradio

I will try to work on an article about that. Right now, I have two articles in the works, so it will come after those, but I will add it to my list.

8 MrSpooky

I’ve actually got an article on Memcached in the works. It will contain specific examples of use with PHP….

9 Stephen

Matt

Interesting take on static classes. My only criticism would be the limitations it lays upon itself by not being a class instantiation i.e object. php5 is great (and looking at the tech notes for php6 that will be greater). I like using static classes, and a lot of them tend to be refactoring of php4 library functions, or small classes to perform a tightly nit group of functions as a helper class to an object. As far as db abstraction I like the pattern method of main db class as singleton (from abstract factory) with composition of a db results class. No one best way of doing it though!
Look forward to kiosk.

10 cpradio

Stephen, thanks for commenting. Static classes are supposed to be fast as they could lock up threads depending on how the language and the runtime are written.

Unfortunately, there is not very much knowledge (easy to find knowledge) for how PHP handles static methods and classes.

In either case, I would still continuously keep the Database class static, to help ensure each person optimizes their queries to run fast. This will help performance greatly at the possible cost of thread lockouts.

Time will tell though and so will testing. However, from the initial testing I have done, I personally cannot see with the way PHP was written, for it to lockout any threads trying to access a static class, unlike .NET which will lockout any threads if the static method is currently “in use”.

PHP, since it is not a compiled language, it has to compile the scripts at run-time, which in my opinion would mean the run-time execution would be per thread. Now I am sure there can be caching enabled to help stop it from compiling the run-time execution on each request, and that is where it is hazy on if static methods would in fact cause a lockout to another thread if it is “in use”.

Again, thanks for the comment and I would be very interested in seeing the code for the db abstraction with the composition of a db results class.

11 bilicim

thanx for the post,
But i couldn’t find your other posts about session and design class that you mentioned in your first article before. So, are you going to post? Because, I really curious about those :)

12 cpradio

Hi billicim,

I will try to get to those in the upcoming month. I have been out of town a lot over the last month, which is why I haven’t got a chance to write those yet. I will aim for December 12th for the Design class.

13 cycler

Does this really work in a multiuser application?

For example $savedResults is a static variable. Couldn’t it be overwritten by ExecuteQuery calls from other scripts?

Also: Am I free to use this as I please or are there limitations?

14 cpradio

I have used it in a variety of applications, so yes it does work. If you notice $savedResults is indeed a static variable, but that is so it can cache the results, until you free them that is. Notice that the ExecuteQuery method checks to see if the query is in the $savedResults array already (indicating the results are cached) and if it is, then it just returns that result set, otherwise, it will actually hit the database for the information.

There are no limitations to the code, you are free to use it however you please for any application (commercial or personal). Feel free to modify, redistribute, etc.

mulberry sale spyder womens jacket cheap new balance 574 mulberry outlet cheap new balance 574 arcteryx outlet mulberry sale spyder womens jacket mulberry sale spyder womens jacket mulberry outlet mulberry outlet new balance 574

Popular Articles

Top 10 Commentators


Subscribe to this feed! Subscribe by Email!

Random Bits Podcast

You need to download the Flash player from Adobe

Blogs Worth Reading