<?php
/**
 * RecordHash Data Access Object class
 * Encapsulates MySQL database operations
 * maintains a connection, and views recordsets in terms of hash arrays for easier manipulation
 * as a result, most optimal for small recordsets
 * also abstracts syntax of insert, update by passing arrays through
 *
 * Hash arrays are two-dimensional (if 1 record, then first dimension has just 1 element)
 *
 * Example usage:
 * $rh = new RH_DAO;
 * $data = $rh->select("select * from table_foo where field_foo= 'bar'");
 * echo $data[0]['id']; //first record, field 'id'
 * $update[0]["staff_name"] = "joe";
 * $update[0]["staff_initials"] = "j";
 * $update[1]["staff_name"] = "fred";
 * $update[1]["staff_initials"] = "f";
 * $rh->insert("table_foo", $update); //insert data; update is similar
 * $rh->delete("dummy_table", "id", 1)); //delete where id=1
 *
 * @Copyright (open source) Vodex (vodex.net) 2004 ABSOLUTELY NO WARRANTY: license at http://www.opensource.org/licenses/lgpl-license.php
 * http://www.vodex.net/php/rh_dao/
 * @version v1.2
 */

class RH_DAO
{
	var $db_username;
	var $db_password;
	var $db_hostname;
	var $db_dbname;
	var $error;

	function RH_DAO ($db_username = "", $db_password = "", $db_hostname = "", $db_dbname = "")
	{
		// change the below to hardcode if you're using the same DB throughout the app
		$this->db_username = $db_username; //"username"
		$this->db_password = $db_password; //"password";
		$this->db_hostname = $db_hostname; //"localhost";
		$this->db_dbname = $db_dbname; //"table_name";
		$db = mysql_connect($this->db_hostname, $this->db_username, $this->db_password);
		mysql_select_db($this->db_dbname);
	}

	function select($sql)
	{
		// performs basic SELECT query
		$sql = trim($sql);

		if ($db_result = mysql_query($sql))
		{
			for($i = 0;$row = mysql_fetch_assoc($db_result);++$i)
			{
				$results[$i] = $row;
			}
			$this->error = null;
			return $results;
		}
		else
		{
			$this->error = "Couldn't perform SELECT!<br />\nMySQL reports: " . mysql_error() . "<br />\nQuery was: $sql<br />\n";
			return false;
		}
	}

	function insert($table, $data)
	{
		// performs INSERT query
		// $table: table to be updated
		// $data: 2D hash array to be inserted
		// example of data:
		// $data[0]["staff_name"] = "joe";
		// $data[0]["staff_initials"] = "j";
		// $data[1]["staff_name"] = "fred";
		// $data[1]["staff_initials"] = "f";
		// returns ID of last record added
		// NOTE if you are inserting more than one record, you only get the first ID
		// so if you want to retrieve several ids, use this method one at a time
		// http://uk.php.net/mysql-insert-id
		$field_names = "(";
		$fields = "";
		// determine the field names by iterating through the first inserted record
		foreach($data[0] as $k => $v)
		{
			$field_names .= "`" . $k . "`, ";
		}
		// iterate through the inserted records, building the SQL query
		foreach ($data as $key => $value)
		{
			$fields .= "(";

			foreach($value as $k => $v)
			{
				$fields .= "'" . $v . "', "; //enclose in quotes by default
			}
			$fields = substr($fields, 0, (strlen($fields)-2)) . "), "; // truncate off the last ", " & add ) ,
		}

		$field_names = substr($field_names, 0, (strlen($field_names)-2)) . ")"; // truncate off the last ", " & add )
		$fields = substr($fields, 0, (strlen($fields)-2)); // truncate off the last ", "
		$sql_insert = "insert into `" . $table . "` " . $field_names . " values " . $fields;

		if (($db_result = mysql_query($sql_insert)))
		{
			$new_id = mysql_insert_id(); //get the last ID
			if (!$new_id)
			{
				// mysql_insert_id not supported
				$new_id = $this->do_sql("SELECT LAST_INSERT_ID();");
			}

			$this->error = null;
			return $new_id;
		}
		else
		{
			$this->error = "Couldn't INSERT data!<br />\nMySQL reports: " . mysql_error() . "<br />\nQuery was: $sql_insert<br />\n";
			return false;
		}
	}

	function update($table, $update_data, $match_field)
	{
		// performs UPDATE query
		// $table:  table to be updated
		// $update_data: 2D hash array of updated fields; as this is SQL, you only need to include fields you want to update
		// $match_field: the field you are matching on when updating (to stop every record being updated)
		// example of $update_data:
		// $data[0]["staff_id"] = "3";
		// $data[0]["staff_name"] = "joe";
		// $data[0]["staff_initials"] = "j";
		// this method only accepts one field set, i.e. can't run more than one UPDATE consecutively
		$field_names = "";
		$fields = "";
		// iterate through the inserted records, building the SQL query
		foreach ($update_data as $key => $value)
		{
			$fields .= "";

			foreach($value as $k => $v)
			{
				$fields .= "`" . $k . "`='" . $v . "', "; //enclose in quotes by default
			}
			$fields = substr($fields, 0, (strlen($fields)-2)) ; // truncate off the last ", "
		}

		$field_names = substr($field_names, 0, (strlen($field_names)-2)); // truncate off the last ", "
		$match_criteria = $update_data[0][$match_field]; //determine the match criteria
		if (is_string($match_criteria))
		{
			// if string, enclose in quotes
			$match_criteria = "'" . $match_criteria . "'";
		}

		$sql = "update `" . $table . "` set" . $fields . " where " . $match_field . "=" . $match_criteria;

		if ($db_result = mysql_query($sql))
		{
			$this->error = null;
			return true;
		}
		else
		{
			$this->error = "Couldn't UPDATE data!<br />\nMySQL reports: " . mysql_error() . "<br />\nQuery was: $sql<br />\n";
			return false;
		}
	}

	function delete($table, $match_field, $record_id)
	{
		// performs DELETE query
		// $table:  table to be deleted from
		// $match_field: the field you are matching on when deleting (to stop every record being updated)
		// $record_id: key to identify record
		$sql = "delete from `" . $table . "` where " . $match_field . " = " . $record_id;
		if ($db_result = mysql_query($sql))
		{
			$this->error = null;
			return true;
		}
		else
		{
			$this->error = "Couldn't DELETE data!<br />\nMySQL reports: " . mysql_error() . "<br />\nQuery was: $sql<br />\n";
			return false;
		}
	}

	function execute($sql)
	{
		// function to perform arbitary SQL
		if ($db_result = mysql_query($sql))
		{
			$this->error = null;
			return true;
		}
		else
		{
			$this->error = "Couldn't execute query!<br />\nMySQL reports: " . mysql_error() . "<br />\nQuery was: $sql<br />\n";
			return false;
		}
	}
}

?>