Example Database Classes

April 22nd, 2008 | by admin | |

This article is pivotal in the design of my CMS. You’ve already been introduced to using a MySQL database class, so this article extends from that by showing you some elements of a class that interacts with this MySQL class. For this example, I’ll be showing you some snippets of a Page Category class, and standard variables that I tend to use (some of which I believe you should ALWAYS include in each of your database tables, including created_by, created_date, modified_by and modified_date)

We start with the class name and variables:

<?php
/*
* @summary: Page_Category class
* @author: Mark Siedle
*/
class Page_Category {

	/*
	* @summary: Public variables (saves bothering about private and get/set methods)
	*/
	var $id = '';
	var $name = '';
	var $seo_name = '';
	var $parent_page_category_id = '';
	var $display_order = '';
	var $menu_item_id = '';
	var $created_by_member_id = '';
	var $created_date = '';
	var $modified_by_member_id = '';
	var $modified_date = '';
	var $recordstatus = '';
...

Simple enough, now we move onto my specially designed constructor:

	/*
	* @summary: Constructor designed to accept an id, which automatically gets the details. This helps cuts down on coding elsewhere.
	* @param: (optional) id
	*/
	function Page_Category($id = '') {
		if($id != '' && $id != 0) $this->db_select($id);
	} // end function

	/*
	* @summary: Select a single database record, based on id
	* @param: id of record to select
	*/
	function db_select($id) {
		global $DB_HOST,$DB_USERNAME,$DB_PASSWORD,$DB_DATABASE;
		$link = new MySQL($DB_HOST, $DB_USERNAME, $DB_PASSWORD, $DB_DATABASE);
		$id = set_safe_data($id);
		//create query
		$sql = "SELECT * ";
		$sql .=	" FROM page_category ";
		$sql .=	" WHERE id = '$id' ";
		$sql .= " LIMIT 1 ";
		//execute query
		$query = $query = $link->query($sql);
		//get query data as array
		$query_row = $query->fetch();
		if(is_array($query_row)){
			foreach($query_row as $key => $value) $this->$key = get_safe_data($value);
		}
	} // end function
...

The comments pretty much tell you everything you need to know. This allows me to automatically get the details (via the db_select method) for a given record, making my front end coding much faster to write.

And lastly (for now), I’ll share my db_delete method:

	/*
	* @summary: Delete a single database record, based on id
	* @param: id of record to select
	*/
	function db_delete($id) {
		global $DB_HOST,$DB_USERNAME,$DB_PASSWORD,$DB_DATABASE;
		$link = new MySQL($DB_HOST, $DB_USERNAME, $DB_PASSWORD, $DB_DATABASE);
		$id = set_safe_data($id);
		//create query
		$sql = "UPDATE page_category ";
		$sql .= " SET recordstatus = 'D' ";
		$sql .= " WHERE id = '$id' ";
		$sql .= " LIMIT 1 ";
		//execute query
		$query = $link->query($sql);
	} // end function

From this, you can write your own functions for all the other methods (insert/update etc). After all, I don’t want to just hand you everything now do I? :-P

This sample does, however, show you how I delete data without actually deleting it, via a recordstatus variable). This allows me to hide information from users, and in the event of someone deleting content they shouldn’t have, data can easily be recovered, since it hasn’t actually done anywhere :-)

You’ll notice that these particular methods don’t use prepared statements. For these particular methods, I have no need to use prepared statements because I cast the id as an integer when sending the id as a parameter to this function (and no user input talks directly to these methods). However, for your other methods (INSERT/UPDATE where user data will be involved), you can checkout my prepared statements article. (But perhaps for your first try just get all functions working, then later look at converting to the prepared statement method (which is far superior in regards to security)

Post a Comment