PHP Framework I: The Database

11.02.2009 • 20:35 • permalink • Comments (0)

This is the first of a series of posts describing the PHP framework I mentioned in a previous post. I start out by introducing the original first feature of the framework, one that lies at the heart of most websites - database querying. Disclaimer: I'm still learning my way around proper PHP architecture so take this with as many grains of salt as you like.

For any website beyond the most simple, you are bound to be doing a lot of database queries to present the website data. This can quickly grow to become a large mess of database connections, SQL query strings, sanitizing and result sets. When I began work on the project, I hadn't worked with SQL for some time and I couldn't remember any but the most simple queries. To solve this I would wrap SQL queries into PHP objects and functions for easy access. Perhaps inspired a bit by LINQ I decided on a composable set of query functions on an SQL object which can be called thusly:

  1. //(SQL): SELECT name, age FROM users WHERE age = 18;
  2. $result = SQL::select("name","age")->from("users")->where("age","18")->query();
  3.  
  4. //(SQL): INSERT INTO users VALUES ('John', 'Doe', '23');
  5. SQL::insertInto("users")->values("John","Doe","23")->query();

$result is a standard MySQL result set as returned by the mysql_query function. Alternatively, you can fetch a row of the result set with the query:

  1. $row = SQL::select("name","age")->from("users")->where("age","18")-> queryRow();
  2. $name = $row['name'];
  3. $age = $row['age'];

Each method (except the query methods) returns an object which aggregates the query data. If you have an editor which supports intellisense this will allow you to easily compose many different SQL queries without having to remember the exact syntax. While it is infeasible for me to support all possible SQL queries the most common are supported and the model can easily be expanded. Below you can see a simple example of how a method on the InnerHtml class can decorate a query with the FROM clause:

  1. function from($table){
  2.     $this->sql .= " FROM ". $table;
  3.     return $this;
  4. }

What we have now is a simple model for constructing SQL queries but it gives us something even more powerful. We are now free to incapsulate all the details about such issues as connection management and parameter sanitization. Management of the connection to the database (MySQL in this case, but it should be simple to change due to the isolation) is provided by the singleton class DatabaseConnection.

  1. class DatabaseConnection{
  2.     private static $dc = null;
  3.     private static $connection = null;
  4.    
  5.     private function __construct(){
  6.         self::$connection = mysql_connect(Settings::$dbPath, Settings::$loadUser,Settings::$loadPass);
  7.         if (!self::$connection)
  8.         {
  9.           die('Could not connect: ' . mysql_error());
  10.         }
  11.         mysql_select_db(Settings::$dbName, self::$connection);
  12.     }
  13.    
  14.     static function get(){
  15.         if(self::$connection == null){
  16.             self::$dc = new DatabaseConnection();
  17.         }
  18.         return self::$connection;
  19.     }
  20.    
  21.     //Call this at the end of each page using DB calls
  22.     static function close(){
  23.         if(self::$connection != null){
  24.             mysql_close(self::$connection);
  25.             self::$connection = null;
  26.         }
  27.     }
  28. }

This code is used by the query methods of the SQL query object.

  1. function query(){
  2.     DatabaseConnection::get();
  3.     return mysql_query($this->sql);
  4. }

To dispose the connection the close function must be called at the bottom of every page (using DB queries). The result of the above code is that as long as you can remember to include the DatabaseConnection::close() call at the bottom of each page you never have to think about connections again. With regards to sanitizing the input this is just a question of cleaning any function parameters before they are added to the inner SQL string.

  1. //Params: Arbitrary number of field-value pairs
  2. function where(){
  3.     $firstWhere = true;
  4.     for ($i = 0; $i < func_num_args(); $i += 2) {
  5.         $field = func_get_arg($i);
  6.  
  7.         //Cleaning the input values
  8.         $val = Database::cleanInputString(func_get_arg($i+1));
  9.         if ($firstWhere){
  10.             $this->sql .= " WHERE ".$field." = '".$val."'";
  11.             $firstWhere = false;
  12.         }else{
  13.             $this->sql .= " AND ".$field." = '".$val."'";
  14.         }
  15.     }
  16.     return $this;
  17. }
  18.  
  19. ...
  20.  
  21. class Database{
  22.     static function cleanInputString($string){
  23.         if (get_magic_quotes_gpc())
  24.         {
  25.             $string = stripslashes($string);
  26.         }
  27.         $string = mysql_real_escape_string($string, DatabaseConnection::get());
  28.         return $string;
  29.     }
  30. }

I hope that I didn't miss anything critical in that sanitizing function since I don't have web security 100% down in PHP yet. The only problem (or maybe not?) with relying on the database classes to handle input cleaning is that if you end up constructing your own SQL strings for the more advanced queries this code can't handle, you must manually clean all the input. I find that this code has really made database operations much easier but you don't have to take my word for it - go check it out yourself. The file will be expanded to contain the other features of the framework as I get it cleaned up.

Comments

Name:

Comment (no HTML):