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.

PLAIN TEXT
PHP:
  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.