Employee Contacts (Part 2) – Building a Data Abstraction Layer with PHP

1. Building the MySQL database.
2. Building a Data Abstraction Layer with PHP
3. Building the Data Access Layer with PHP
4. Building the Business Object Layer with PHP
5. Building the Presentation Layer with PHP & HTML

Part 1 of this series covered building the MySQL database. If you are following along with us, you may want to go back and create the database from part 1. In Part2, we will be building the data abstraction layer. This will allow you to make yor basic database calls from a central location. Back when I created my first PHP application, I was using the PEAR DB class. The tutorial that I read had you make all of the calls from each function. Everything worked fine until I changed to using PEAR MDB2. Some of the calls are different, so I had to go back to all 8 class files and change about 100 functions. This was very time consuming and should have been avoidable. If I would have created my own data abstraction layer that referenced the PEAR DB class, I could have just made a few minor changes there and the rest of the application would have been fine.

Building Your Web Application

At this point you will want to create a new web application. Hopefully if you are reading this, you will know how to do this. But just in case you don’t, see the Microsoft Article on Virtual Directory Setup Instructions and for Apache see Apache Aliases.

Once you have your Directory setup, create a folder inside your web application called data_objects. This is where we will store our data abstraction class.

Define the class – First we need to define our data class. I choose to call mine data_manager. We are also going to define a few private variables that should only be available in this class.

class data_manager{
	// public database object - if you are running php 5 and
	// your hosting provider is, you can change 'var' to 'public'
	var $error_msg;		// holds error messages

	var $host;			// Your MySQL host, may need to add port number as well
	var $user;			// The user name you will be using to connect. Should not be root
	var $pass;			// The password for $user
	var $db;			// The database you are connecting to

	var $conn;			// database link
}

Class Constructor – We need to create our class constructor. Anything inside this method will be executed whenever we instantiate our class. The variables that we defined previously will now be set.

	// class constructor
// if you are using php 5, you could use function __construct instead.
function data_manager(){
	// set up are variables
	$this->host = 'localhost';
	$this->user = 'empmgmt';
	$this->pass = 'empmgmt';
	$this->db 	= 'employeemanagement';
}

Connect to the Database – We will call this method at the presentation layer. This way we can open the database and execute all of our database queries.

/**
* used to connect to the database
*
* @param boolean $persist[optional] determines if the db connection is persistent
* @return boolean
*/
function connect($persist=true){
        // establish our connection
	if( $persist )
		$this->conn = mysql_pconnect($this->host, $this->user, $this->pass);
	else
		$this->conn = mysql_connect($this->host, $this->user, $this->pass);

	// verify that everything worked - check for errors
	if (!$this->conn){
		$this->error_msg = mysql_error();
	        return false;
	}

	// select the database to use
	if (!mysql_select_db($this->db)){
		$this->error_msg = mysql_error();
		return false;
	}

	return true;
}

Close the Database Connection – After executing all of our data access code, we would call this from the presentation layer to close our connection to the database. This frees up the additional system resources that we use when connecting to a database.

/**
* used to disconnect from the database
*/
function disconnect(){
	mysql_close($this->conn);
}

Return all Records – This is called when we want to get more than one row back from our database tables.

/**
* Returns all records from the query provided
*
* @param string $sql
* @return object
*/
function get_all($sql){
	// execute the query
	$results = mysql_query($sql);
	if (!$results){
		$this->error_msg = mysql_error();
		return false;
	}

	return $results;
}

Return one Record – Returns a single row of data.

/**
* Returns a single row
*
* @param string $sql
* @return object
*/
function get_row($sql){
	// execute the query
	$results = mysql_query($sql);
	if (!$results){
		$this->error_msg = mysql_error();
		return false;
	}
	// check to make sure only 1 record is returned.
	// If more than one is returned, you will not get the
	// results you expected
	if (mysql_num_rows($results) > 1){
		$this->error_msg = "Your query returned more than one result";
		return false;
	}

	return $results;
}

Execute DML Statements – Allows us to execute our INSERT, UPDATE, DELETE statements. We will return an integer value that tells us how many records were affected. This method could be called any time you wanted to execute SQL statements that don’t return any records.

/**
* Execute queries against the database. Generally used for
* INSERT, DELETE, UPDATE
* Returns the number of records affected
*
* @param string $sql
* @return integer
*/
function query($sql){
	// initialize the return value
	$return = 0;
	// execute the query
       $results = mysql_query($sql);
	if (!$results){
		$this->error_msg = mysql_error();
		return $return;
	}

	return $results;
}

Add Slashes – This will add backslashes to our strings in order to escape any characters that need to be quoted in our database queries. We will call this from our data access layer whenever we INSERT or UPDATE any string values.

// adds slashes where needed method
function add_slashes($string){
	if (get_magic_quotes_gpc()) {
		return $string;
	}else {
		return addslashes($string);
	}
}

Complete Code

// class providing basic database functionality
class data_manager{
	// public database object - if you are running php 5 and
	// your hosting provider is, you can change 'var' to 'public'
	var $error_msg;		// holds error messages

	var $host;			// Your MySQL host, may need to add port number as well
	var $user;			// The user name you will be using to connect. Should not be root
	var $pass;			// The password for $user
	var $db;			// The database you are connecting to

	var $conn;			// database link

	// class constructor
	// if you are using php 5, you could use function __construct instead.
	function data_manager(){
		// set up are variables
		$this->host = 'localhost';
		$this->user = 'empmgmt';
		$this->pass = 'empmgmt';
		$this->db 	= 'employeemanagement';

		$this->connect(true);
	}

	/**
	 * used to connect to the database
	 *
	 * @param boolean $persist[optional] determines if the db connection is persistant
	 * @return boolean
	 */
	function connect($persist=true){
		// establish our connection
		if( $persist )
			$this->conn = mysql_pconnect($this->host, $this->user, $this->pass);
		else
			$this->conn = mysql_connect($this->host, $this->user, $this->pass);

		// verify that everything worked - check for errors
		if (!$this->conn){
			$this->error_msg = mysql_error();
			return false;
		}

		// select the database to use
		if (!mysql_select_db($this->db)){
			$this->error_msg = mysql_error();
			return false;
		}

		return true;
	}

	/**
	 * used to disconnect from the database
	 */
	function disconnect(){
		mysql_close($this->conn);
	}

	/**
	 * Returns all records from the query provided
	 *
	 * @param string $sql
	 * @return object
	 */
	function get_all($sql){
		// execute the query
		$results = mysql_query($sql);
		if (!$results){
			$this->error_msg = mysql_error();
			return false;
		}

		return $results;
	}

	/**
	 * Returns a single row
	 *
	 * @param string $sql
	 * @return object
	 */
	function get_row($sql){
		// execute the query
		$results = mysql_query($sql);
		if (!$results){
			$this->error_msg = mysql_error();
			return false;
		}

		// check to make sure only 1 record is returned.
		// If more than one is returned, you will not get the
		// results you expectd
		if (mysql_num_rows($results) > 1){
			$this->error_msg = "Your query returned more than one result";
			return false;
		}

		return $results;
	}

	/**
	 * Execute queries against the database. Generally used for
	 * INSERT, DELETE, UPDATE
	 * Returns the number of records affected
	 *
	 * @param string $sql
	 * @return integer
	 */
	function query($sql){
		// initialize the return value
		$return = 0;

		// execute the query
		$results = mysql_query($sql);
		if (!$results){
			$this->error_msg = mysql_error();
			return $return;
		}

		return $results;
	}

	// adds slashes where needed method
	function add_slashes($string){
		if (get_magic_quotes_gpc()) {
			return $string;
		}else {
			return addslashes($string);
		}
	}

}

For a complete MySQL data abstraction layer, check out micahcarrick.com . He provides a fully functional data abstraction class for MySQL that you can immediately put into production.