MySQL Abstraction Class

<?php

/*

Database abstraction class
Version 0.3

TODO for v1.0:
- Add functionality to create custom modifiers without having to hard-code them into class
- Eliminate the need to write SQL queries in asset definition
- Make class work with different database engines (like Propel does)

*/

class asset
{
	public function __construct($def, $db = null)
	{
		$this->db = is_null($db) ? $GLOBALS['db'] : $db;
		
		// list of elements
		$this->elements = $def['elements'];
		
		// sql queries
		$this->list_sql = $def['list_sql'];
		$this->edit_sql = $def['edit_sql'];
		$this->save_sql = $def['save_sql'];
		$this->add_sql = $def['add_sql'];
		$this->delete_sql = $def['delete_sql'];
		
		// primary key element
		$this->param_element = $def['primaryKey'];
	}
	
	// this goes through list of elements and formats it based on parameters
	// (array) $values
	private function parseValues($values = array())
	{
		$return = array();
		
		// loop through elements
		foreach ($this->elements as $n => $element)
		{
			$value = $values[$n];
			
			if (is_array($element['modifiers']))
			{
				foreach($element['modifiers'] as $pn => $pv)
				{
					// do something with $value based on params
					switch ($pn)
					{
						case 'date_format':
							$value = date($pv, $value);
							break;
						
						case 'toUnixTime':
							$value = strtotime($value);
							break;
						
						case 'encode':
							if ($pv === 'html')
							{
								$value = htmlspecialchars($value);
							} else {
								$value = urlencode($value);
							}
							break;
					}
				}
			}
				
			$return[$n] = $value;
		}
		
		return $return;
	}
	
	// get value from database by primary key
	// (string) $pkValue; (bool) $skipParce
	private function getOne($pkValue, $skipParce)
	{
		// fill in primary key value
		$q = preg_replace('~%param%~i', mysql_real_escape_string($pkValue), $this->edit_sql);
		$s = $this->db->query($q);
		
		return $skipParce ? $s[0] : $this->parseValues($s[0]);
	}
	
	// add new record
	// (array) $values
	public function add($values)
	{
		$sql = array();
		$sql_left = array();
		$sql_right = array();
		
		foreach ($this->elements as $n => $element)
		{
			$value = isset($values[$n]) ? stripslashes($values[$n]) : $element['default'];
			$sql[] = array($element['sql_path'], $value);
		}
		
		if (!empty($sql))
		{
			foreach($sql as $n => $v)
			{
				array_push($sql_left, "`" . $v[0] . "`");
				array_push($sql_right, "'" . mysql_real_escape_string($v[1]) . "'");
			}
			
			$search = array();
			$search[] = '~%sql_left%~i';
			$search[] = '~%sql_right%~i';
			
			$replace = array();
			$replace[] = implode(',', $sql_left);
			$replace[] = implode(',', $sql_right);
			
			$q = preg_replace($search, $replace, $this->add_sql);
			$this->db->query($q);
			
			return true;
		}
		
		return false;
	}
	
	// save values
	// (array) $values; (string) $pkValue
	public function save($values, $pkValue)
	{
		$sql = array();
		$sql_left = array();
		$sql_right = array();
		
		foreach ($this->elements as $n => $element)
		{
			if (isset($values[$n]))
			{
				$value = stripslashes($values[$n]);
				$sql[] = array($element['sql_path'], $value);
			}
		}
		
		$sql_upd = '';
		$i = 0;
		$total = count($sql) - 1;
		
		if ($total > -1)
		{
			foreach($sql as $n => $v)
			{
				$sql_upd .= "`" . $v[0] . "`='" . mysql_real_escape_string($v[1]) . "'";
				if ($i < $total) $sql_upd .= ",";
				$i++;
			}
			
			if(!empty($sql_upd))
			{
				$search = array();
				$search[] = '~%update_sql%~i';
				$search[] = '~%param%~i';
				
				$replace = array();
				$replace[] = $sql_upd;
				$replace[] = mysql_real_escape_string($pkValue); // primary key value
				
				// save
				$q = preg_replace($search, $replace, $this->save_sql);
				$this->db->query($q);
				
				return true;
			}
		}
		
		return false;
	}
	
	// delete record
	// (array|string) $sel
	public function delete($sel)
	{
		// sel could be an array of primary key values or string if you want to delete just one record
		if (is_array($sel) && count($sel) > 0)
		{
			$p = $sel;
		} else {
			$p = array(0 => $sel);
		}
		
		$q = preg_replace('~%param%~i', implode(',', $p), $this->delete_sql);
		$this->db->query($q);
		
		return true;
	}
	
	// get records. if second parameter is not an array - assume user wants to get one record by primary key
	// (bool) $skipParce; (string|array) $limit; (string) $order_by; (string) $order_how
	public function get($skipParce = false, $limit = array(), $order_by = '', $order_how = '')
	{
		// make sure $skipParce is boolean
		$skipParce = (bool) $skipParce;
		
		// check if we want to display one record or not
		if (is_array($limit))
		{
			$dList = array();
			
			foreach ($this->elements as $n => $element)
			{
				$dList[] = array('name' => $n, 'sql_path' => $element['sql_path']);
			}
			
			// what do we sort by? we need correct sql paths to columns
			$order_by_sql = $this->elements[$order_by]['sql_path'];
			
			// there is only asc and desc ordering
			$order_how = $order_how === 'desc' ? 'desc' : 'asc';
			
			// check if user wants to limit amount of records
			$limit_sql = '';
			if (isset($limit[0]) && (int) $limit[0] > 0)
			{
				$limit_sql = 'limit ' . (int) $limit[0];
				if (isset($limit[1]) && (int) $limit[1] > 0)
				{
					$limit_sql .= ', ' . (int) $limit[1];
				}
			}
			
			$search = array();
			$search[] = '~%sql_val%~i';
			$search[] = '~%sql_order%~i';
			$search[] = '~%sql_limit%~i';
			
			$replace = array();
			$replace[] = '*';
			$replace[] = empty($order_by_sql) ? '' : "order by `" . $order_by_sql . "` " . $order_how;
			$replace[] = $limit_sql;
			
			$query = preg_replace($search, $replace, $this->list_sql);
			$val = $this->db->query($query);
			
			if (!$skipParce)
			{
				foreach ($val as $n => $v)
				{
					$val[$n] = $this->parseValues($v);
				}
			}
			
			return $val;
		} else {
			// return single record
			return $this->getOne($limit, $skipParce);
		}
	}
}

// example usage:

include_once('class.mysql.php');

$db_conf = array(
	'host'	=>	'localhost',
	'user'	=>	'undefine_tester',
	'pass'	=>	'tester',
	'db'	=>	'undefine_tester',
	'pers'	=>	false // persistent connection?
);
$db = new mysql($db_conf);

$table = 'tester';
$tester_definition = array(
	'primaryKey'	=>	'id', // primary key column
	
	'elements'	=> array(
		/*
			please use the following format for element definitions:
			
			'unique name of element'	=>	array(
				'default'	=>	'default value (if not specified upon adding new record)',
				'sql_path'	=>	'column name in table',
				'modifiers'	=>	array(
					'encode'	=>	'html',
					'encode'	=>	'url'
				)
			)
		*/
		
		'id'	=>	array(
			'sql_path'	=>	'id'
		),
		
		'date'	=>	array(
			'default'	=>	time(),
			'sql_path'	=>	'date',
			'modifiers'	=>	array(
				'date_format'	=>	'F j, Y, g:i a'
			)
		),
		
		'title'	=> array(
			'sql_path'	=>	'title'
		)
	),
	
	// templated sql queries
	'list_sql'	=>	"select %sql_val% from `" . $table . "` %sql_order% %sql_limit%", // get all values
	'edit_sql'	=>	"select * from `" . $table . "` where `" . $param . "`='%param%' limit 1", // get single value
	'save_sql'	=>	"update `" . $table . "` set %update_sql% where `" . $param . "`='%param%' limit 1", // save
	'add_sql'	=>	"insert into `" . $table . "` (%sql_left%) values (%sql_right%)", // add
	'delete_sql'	=>	"delete from `" . $table . "` where `" . $param . "` in (%param%)" // delete
);

$tester_asset = new asset($tester_definition, $db);

$test_add = array(
	'title'	=>	'test'
);
$tester_asset->add($test_add);

$list = $tester_asset->get();
print_r($list);

?>

PHPBuilder
Policies

• Legal Notices
• Licensing
• Permissions
• Privacy Policy
• Advertise

PHP Developer
Topics

• Architecture
• Databases
• HTML/CSS/JavaScript
• Functions
• Tools

PHP Developer
Resources

• E-mail Offers
• Careers
• Forums
• Code Snippet Library

PHP Developer
References

• Getting Started
• News Archive
• Setup and Installation
• Site Operation
• Tips and Quickies
• Tricks and Hacks