Archive

Archive for the ‘MySQL’ Category

Building A PHP Database Connection Class From Scratch (using Singleton & ActiveRecord patterns)

April 8, 2012 11 comments

EH_DESIGN_2012.04.03_PhpMySQLLogo_07Greetings folks.

Tonight I’m studying on how to create a PHP Object-Oriented MySQL database connection class.  This will be reusable and able to be implemented in any database driven web application built with PHP & MySQL.  It uses the ActiveRecord and Singleton design patterns.

I’m currently following this YouTube tutorial: Using OOP PHP to select, update, and insert data in a mysql database. Nick Huot really does a superb job of explaining step-by-step, not only what he is doing, but also why.

(24 hrs later …)

So I am pleased to tell you, dear reader, about the successful PHP OOP Database Connection class I built last night, based on Huot’s tutorials — they really are the best I’ve seen.

I had a lot of debugging to do, but that was simple.  Nick had not provided all the code for PHP validating of variables and such.  I’m sure he would have but he probably ran short on time, so no worries.  The validations I came up with (making sure rows had values and were of type array, for instance), I have provided for you below in hopes it will save some of you some time.  If you choose to implement my code examples you may find that there are still validations I had not accounted for.  I’d love to hear of any bugs that you find, or improvements that you make on this example, or on your own projects derived from this code.

COMPLETE WORKING EXAMPLE:

config.php

<?php  
   // Database Constants
   define("DB_HOST", "localhost");
   define("DB_USER", "root");
   define("DB_PASS", "[your_db_pass]");
   define("DB_NAME", "[your_db_name]");
?>
database.class.php

<?php
require_once('../../viewutilities.php');

class database
{
   private $host;
   private $user;
   private $pass;
   private $dbName;
   
   private static $instance;
   
   private $connection;
   private $results;
   private $numRows; // optional
   
   private function __construct()
   {      
   }
   
   // singleton pattern
   static function getInstance()
   {
      if(!self::$instance)
      {
         self::$instance = new self();
      }
      return self::$instance;
   }
   
   function connect($host, $user, $pass, $dbName)
   {
      $this->host = $host;
      $this->user = $user;
      $this->pass = $pass;
      $this->dbName = $dbName;
      
      $this->connection = mysqli_connect($this->host,
                                         $this->user,
                                         $this->pass,
                                         $this->dbName);
   }
   
   public function doQuery($sql)
   {      
      $this->results = mysqli_query($this->connection, $sql);
      
      if ($this->results && $this->results->num_rows > 0) {
                  
         // NOTE: The line below was giving me this error:
         // "Trying to get property of non-object"
         //$this->numRows = $this->results->num_rows;
         
         $this->numRows = $this->results->num_rows;

      } // end if
   }
   
   public function loadObjectList()
   {
      $obj = "No Results";
            
      if ($this->results)
      {
         $obj = mysqli_fetch_assoc($this->results);
      }
      return $obj;
   }
   
} // end class

?>
table.class.php

<?php
require_once('../../viewutilities.php');

/**
 * Uses Active Record design pattern to implement a class that
 *  closely matches a given database table.  This class contains
 *  methods for binding the class members and methods to CRUD
 *  database management principles.  This type of class is sometimes
 *  referred to a "Table Model", and in many designs it is implemented
 *  as an abstract base class, which is then extended and specialized
 *  as necessary.
 */
class table
{
   protected $id = null;
   protected $table = null;
   
   function __construct()
   {
      
   }   
   
   /**
    * Takes assoc array as param $data.  Dynamically creates class properties
    *  with key as property name and value as its value.
    *
    * @param array $data key value pairs
    * @return
    * @access
    */
   function bind($data)
   {
      //print_r($data);
      foreach ($data as $key=>$value)
      {
         $this->$key = $value;
         //echo $key."--".$value;
      }
   }
   
   function load($id)
   {
      $this->id = $id;
      $dbo = database::getInstance();
      $sql = $this->buildQuery('load');
      
      $dbo->doQuery($sql);

      $row = $dbo->loadObjectList();
      
      if (isset($row) && is_array($row))
      {
         foreach ($row as $key=>$value)
         {
            if ($key == "id")
            {
               continue;
            }
            $this->$key = $value;            
         }               
      }

   }
   
   // write database object contents to database
   function store()
   {
      $dbo = database::getInstance();
      $sql = $this->buildQuery('store');
      $dbo->doQuery($sql);
   }
   
   protected function buildQuery($task)
   {
      $sql = "";
      if ($task == "store")
      {
         // if no id value has been store in this object yet,
         //  add new record to the database, else just update the record.
         if ($this->id == "")
         {
            $keys = "";
            $values = "";
            $classVars = get_class_vars(get_class($this));
            $sql .= "INSERT INTO {$this->table}";
            
            foreach ($classVars as $key=>$value)
            {
               if ($key == "id" || $key == "table")
               {
                  continue;
               }
               
               $keys .= "{$key},";
               $values .= "{$value},";
            }
            
            // NOTE: all substr($keys, 0, -1) does is gets rid of the comma
            // at the on the last array element.
            $sql .= "(".substr($keys, 0, -1).") Values (".substr($values, 0, -1).")";
            
         }else{
            
            $classVars = get_class_vars(get_class($this));
            $sql .= "UPDATE {$this->table} SET ";
            foreach ($classVars as $key=>$value)
            {
               if ($key == "id" || $key == "table")
               {
                  continue;
               }
               
               $sql .= "{$key} = '{$this->$key}'";
               
            }
            $sql .= substr($sql, 0, -2)." WHERE id = {$this->id}";
            
         }         
      }
      elseif ($task == "load")
      {
         $sql = "SELECT * FROM {$this->table} WHERE id = '{$this->id}'";
      }
      return $sql;      
   }
   
   
} // end class

?>
user.class.php

<?php
class user extends table
{
   var $fname = null;
   var $lname = null;
   var $email = null;
   var $table = "user";
}
?>
index.php

<?php
include('./database.class.php');
include('./table.class.php');
include('./user.class.php');

require_once('../../viewutilities.php');

// this has my connection constants
include('./config.php');

$dbo = database::getInstance();
$dbo->connect(DB_HOST, DB_USER, DB_PASS, 'testpatterns');
//$dbo->connect('localhost', 'root', '', 'testPatterns');

$user = new user();
$user->load('1');

echo "Name of user 1: {$user->fname} {$user->lname}<br />";
?>

Now in the interest of time, I’m publishing this post.  Soon I will add commentary and explanations and such, however, experienced Object-Oriented PHP developers will understand how to code fits together without explantion.  The best I can do for the newbies for now is to say if your database exists, has data matching the format the class is looking for , and is running you should be able to put all these files in your PHP Apache web directory (I’m using WAMP and on my system it is in C:\wamp\www; on yours it may be different), and launch index.php to see a first and last name pulled from one row of data, specified by id number, in your MySQL database.

Good luck, and I’d love to hear from you if you have questions, comments, or issues that you ran into.

– CodeSlayer2010

P.S.: Go ahead and comment out the requires and includes for “viewutilities.php”. If you don’t, the code will not work because it will complain that it can’t find this file.

I left it in there because I will be publishing it later on and you may find it useful debugging tool for your PHP scripts. This class is a wrapper for print_r() function and there are all kinds of advantages, the least of which is one line of code vs three with all your pre-tag. But this class and it’s static “debug” method streamline and simplify debugging complex AMP-stack PHP-based web applications. Watch for it soon!

Lynda.com PHP MySQL WAMP Project: Widget Corp – Progress Update – 2012.02.07

February 8, 2012 2 comments

Widget Corp tutorial progress (2012.02.07 - 1)

Hello folks.  I’ve been doing a Lynda.com tutorial called PHP With MySQL Essential Training.  I’ve actually been doing aboutg 2-8 hours per week of the multiple, sequential lessons that are involved in this excellent hands-on tutorial for about a month now.

At this point, I have completed all the basic PHP and MySQL material, and now in Chapter 13, I am creating a content management system from scratch.  I enjoy doing this video tutorial very much, though at times I have to pause to screen for 10 minutes or so to type in code manually from what’s on the screen.  When you subscribe to Lynda.com (around $30/mo) they have subscription options that provide you the example files to save you all the typing.  But, I firmly believe that, at least for me, as a relative newbie who is trying to understand all the mechanics of web application development with PHP & MySQL, the absolute best way to learn is to type everything manually.  In this way you basically link the code concepts together in your mind so that you can now type in various code modules by heart if need be.Image representing lynda.com as depicted in Cr...

At work we are using a Zend MVC-based framework, so I have practical experience in maintenance coding and creating small new features — mostly break-fixes though.  But I’m like a trained monkey — I don’t really know the mechanics and the logic of why to do things, only that somebody told me “if this, then that”.

I’m sure that sufficient for some coders, but sufficient isn’t good enough for me.  My goal is EXCELLENCE!  Therefore, I ask as many questions as I think I need to (after having already done as much research as I could on my own).  There’s no shame in my game.  After I get an answer I write it down in my journal and then I have my own “reference manual” that I can consult if I forget a step or need to recall that information down the road.  I have found that having new information written down actually results in a lesser likelihood of needing to refer to it in writing, and a greater incidence of ingraining in your brain by habit. I’m really happy with my progress on this project which I will refer to from now on as “Widget Corp”.  Basically, when it is all done I will have a highly refactored, efficient, and effective code template/framework for building customized CMS‘s.  I’m pretty psyched about having an actual “web application” that I can display to the world via my website. That image above is a screenshot showing what my app looks like in Firefox 3, locally on my Win XP machine.  Future improvements I intend to look into are:

  1. Figure out why it is bulleting blank lines.  (NOTE: I tried doing echo/exit in the code but no avail so far)
  2. Refactor to be Object Oriented.
  3. Refactor to use MVC.

Anyway. that’s all stuff down the road.  My goal for completion is March31, 2012.  Wish me luck!

Until next time, HAPPY CODING! 🙂

CodeSlayer2010