Location: PHPKode > projects > Katyshop > katyshop_ro_0.3.2.1/classes/common/MysqlTable.php
<?php

/**
 * Product: Katyshop
 * @version 0.3.2.1
 * @author Catalin Hulea - hide@address.com
 * @copyright Copyright (C) 2007 Catalin Hulea
 * @license GNU General Public License version 3
 * 			You can find a copy of GNU GPL v3 at this path: /docs/LICENSE
 * @link https://sourceforge.net/projects/katyshop
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 * 
 */

require_once(dirname(__FILE__) . "/BaseObject.php");

// A table from a database
class MysqlTable extends BaseObject
{
	var $name;

	/**
	 * @var Database
	 */
	var $db;

	var $fields = array();
	var $primaryKeys = array();
	var $insertFields = array();
	var $updateFields = array();
	var $autoincrementFields = array();

	/**
	 * Constructor. Class MysqlTable, a table from a database.
	 *
	 * @param MysqlDatabase $database
	 * @param string $tableName
	 * @return MysqlTable
	 */
	function MysqlTable(&$database, $tableName)
	{
		$this->db = &$database;
		$this->name = $tableName;
	}

	#####################################
	# AUTOMATIC FIELDS AND KEYS			#
	#####################################

	/**
	 * All classes inherited from MysqlTable must implement this method,
	 * that returns an empty object ususally stored in that table.
	 * This method is used by MysqlTable::getRecordByPks() and by MysqlTable::search(),
	 * if you don't implement createLogicObject() you will not be able to use them.
	 * Example:
	 * // Sample function written in TableAddress (extends MysqlTable)
	 * function createLogicObject()
	 * {
	 * 		return new Address();
	 * }
	 *
	 * @return LogicObject
	 */
	function createLogicObject()
	{
		die("MysqlTable::createLogicObject() must be overriden in inherited class");
	}

	/**
	 * Sends a "describe table_name" query in order to find out list of fields from this table,
	 * primary keys, auto increment fields and so on.
	 */
	function readTableInfo()
	{
		$this->fields = array();
		$this->primaryKeys = array();
		$this->insertFields = array();
		$this->updateFields = array();
		$this->autoincrementFields = array();

		$q = "describe {$this->name} ";
		$res = $this->db->query($q);
		while($row = $this->db->fetch_array($res))
		{
			$this->fields[] = $row["Field"];
			if($row["Key"] == "PRI")
			{
				$this->primaryKeys[] = $row["Field"];
				if(strstr($row["Extra"], "auto_increment"))
				{
					// I don't want primary keys with auto_increment
					// to be used in INSERT statements
					$this->autoincrementFields[] = $row["Field"];
				}
				else
				{
					$this->insertFields[] = $row["Field"];
				}
			}
			else
			{
				$this->insertFields[] = $row["Field"];
				$this->updateFields[] = $row["Field"];
			}
		}
	}

	/**
	 * Get the list of all field names from this table
	 */
	function getFields()
	{
		if(empty($this->fields))
			$this->readTableInfo();
		return $this->fields;
	}

	function getPrimaryKeys()
	{
		if(empty($this->primaryKeys))
			$this->readTableInfo();
		return $this->primaryKeys;
	}

	function getAutoincrementFields()
	{
		if(empty($this->fields))
			$this->readTableInfo();
		return $this->autoincrementFields;
	}

	/**
	 * List of fields used for INSERT statement. If this function is not working fine for you,
	 * please override it in the inherited class.
	 *
	 * Example: table persons with fields id (primary key), first_name, last_name
	 * If id field is auto_increment, getInsertFields() will return array("first_name", "last_name").
	 * If id field is not auto_increment, getInsertFields() will return array("id", "first_name", "last_name").
	 *
	 * The result of this function is used by MysqlTable::insertObj().
	 *
	 * @return unknown
	 */
	function getInsertFields()
	{
		if(empty($this->insertFields))
			$this->readTableInfo();
		return $this->insertFields;
	}

	/**
	 * Return array of all field names except primary keys.
	 * The result of this function is used by updateObj().
	 *
	 * @return unknown
	 */
	function getUpdateFields()
	{
		if(empty($this->updateFields))
			$this->readTableInfo();
		return $this->updateFields;
	}

	/**
	 * Override this in the inherited class.
	 * These fields are used by search() function, to use "=" instead of "like" when
	 * searching a value.
	 *
	 * Example:
	 * function getStrictFields()
	 * {
	 * 		return array("id", "id_company");
	 * }
	 *
	 * If the fields in a sample persons table are: id, id_company, first_name, last_name,
	 * you will usually use "=" to search id and id_company and "like" to search first_name, last_name.
	 *
	 *
	 * @return array
	 */
	function getStrictFields()
	{
		return array();
	}


	#####################################
	# ARRAY PARAMETER FUNCTIONS			#
	#####################################

	/**
	 * I advise you not to use this function directly in program; use insertObj() instead.
	 * Sends a simple INSERT query using this table.
	 *
	 * @param array $assocValues - assoc array: keys are field names,
	 * values are values to insert
	 * @return MysqlResult
	 */
	function insert($assocValues)
	{
		$fieldNames = array_keys($assocValues);

		$q = "INSERT INTO " . $this->name . "(";
		for($i = 0; $i < count($fieldNames); $i++)
		{
			if($i > 0)
				$q .= ", ";
			$q .= $fieldNames[$i];
		}

		$q .= ") VALUES (";
		for($i = 0; $i < count($fieldNames); $i++)
		{
			if($i > 0)
				$q .= ", ";
			$q .= "'" . $this->db->escape($assocValues[$fieldNames[$i]]) . "'";
		}
		$q .= ")";
		return $this->db->query($q);
	}

	/**
	 * I advise you not to use this function directly in program; use updateObj() instead.
	 * Sends a simple UPDATE query to this table
	 *
	 * @param array $assocFields - assoc array, keys are field names to update
	 * @param array $assocKeys - [optional] assoc array, keys are field names for WHERE clause
	 * @return MysqlResult
	 */
	function update($assocFields, $assocKeys = array())
	{
		$fieldNames = array_keys($assocFields);
		$keyNames = array_keys($assocKeys);

		$q = "UPDATE " . $this->name . " SET ";

		for($i = 0; $i < count($fieldNames); $i++)
		{
			if($i > 0)
				$q .= ", ";
			$q .= $fieldNames[$i] . " = ";
			$q .= "'" . $this->db->escape($assocFields[$fieldNames[$i]]) . "'";
		}

		if(count($keyNames) > 0)
			$q .= " WHERE ";

		for($i = 0; $i < count($keyNames); $i++)
		{
			if($i > 0)
				$q .= " AND ";
			$q .= $keyNames[$i] . " = ";
			$q .= "'" . $this->db->escape($assocKeys[$keyNames[$i]]) . "'";
		}
		return $this->db->query($q);
	}

	/**
	 * I advise you not to use this function directly in program; use deleteObj() instead.
	 */
	function delete($assocKeys = array())
	{
		$keyNames = array_keys($assocKeys);

		$q = "DELETE FROM " . $this->name;

		if(count($keyNames) > 0)
			$q .= " WHERE ";

		for($i = 0; $i < count($keyNames); $i++)
		{
			if($i > 0)
				$q .= " AND ";
			$q .= $keyNames[$i] . " = ";
			$q .= "'" . $this->db->escape($assocKeys[$keyNames[$i]]) . "'";
		}

		return $this->db->query($q);
	}


	#####################################
	# OBJECT PARAMETER FUNCTIONS		#
	#####################################

	/**
	 * $object is instance of a class inherited from LogicObject
	 *
	 * @param LogicObject $object
	 */
	function insertObj($object)
	{
		$insertFields = $this->getInsertFields();
		$arr = array();
		for($i = 0; $i < count($insertFields); $i++)
		{
			$key = $insertFields[$i];
			$arr[$key] = $object->$key;
		}
		$this->insert($arr);
	}

	/**
	 * $object is instance of a class inherited from LogicObject
	 *
	 * @param LogicObject $object
	 */
	function updateObj($object)
	{
		$updateFields = $this->getUpdateFields();
		$arr = array();
		for($i = 0; $i < count($updateFields); $i++)
		{
			$key = $updateFields[$i];
			$arr[$key] = $object->$key;
		}
		$primaryKeys = $this->getPrimaryKeys();
		$arr2 = array();
		for($i = 0; $i < count($primaryKeys); $i++)
		{
			$key = $primaryKeys[$i];
			$arr2[$key] = $object->$key;
		}
		$this->update($arr, $arr2);
	}

	/**
	 * $object is instance of a class inherited from LogicObject
	 *
	 * @param LogicObject $object
	 */
	function deleteObj($object)
	{
		$primKeys = $this->getPrimaryKeys();
		$arr = array();
		for($i = 0; $i < count($primKeys); $i++)
		{
			$key = $primKeys[$i];
			$arr[$key] = $object->$key;
		}
		$this->delete($arr);
	}

	/**
	 * Get Record by primary keys
	 * Example:
	 * // overriden in inherited class
	 * // Suppose we have table order_products with primary keys id_order and line_number
	 * function getRecordByPks($id_order, $line_number)
	 * {
	 * 		return parent::getRecordByPks(array("id_order" => $id_order, "line_number" => $line_number));
	 * }
	 *
	 * @param assoc_array $params
	 * @return LogicObject
	 */
	function getRecordByPks($params)
	{
		$ret = $this->createLogicObject();
		$pks = $this->getPrimaryKeys();
		$q = "select * from {$this->name}
			where 1 = 1 ";
		for($i = 0; $i < count($pks); $i++)
		{
			$q .= "and {$pks[$i]} = '" . $this->db->escape(@$params[$pks[$i]]) . "' ";
		}
		$res = $this->db->query($q);
		if($row = $this->db->fetch_array($res))
			$ret->copyFromArray($row);
		return $ret;
	}

	/**
	 * This function is using the result from MysqlTable::getStrictFields()
	 * in order to decide on which fields it should use "=" or "like" to perform the search.
	 * Example:
	 * // $list will contain an array of Person objects
	 * $list = $db->tbPersons->search(array("first_name" => "Monica", "city" => "Bucharest"), 0, 10, "first_name", "asc");
	 * // or search from $_GET parameters (useful for pagination)
	 * $list = $db->tbPersons->search($_GET, @$_GET["start"], @$_GET["rows_per_page"], @$_GET["order_by"], @$_GET["order_direction"]);
	 *
	 * $criteria is an associative array with string keys.
	 */
	function search($criteria = array(), $start = 0, $rowsPerPage = 500, $orderBy = "", $orderDirection = "asc")
	{
		if(empty($this->fields))
			$this->readTableInfo();

		$start = intval($start);
		$rowsPerPage = intval($rowsPerPage);
		if($rowsPerPage < 1)
			$rowsPerPage = 10;
		$start = intval($start);
		if(!in_array($orderDirection, array("asc", "desc")))
			$orderDirection = "asc";
		$strictFields = $this->getStrictFields();

		$ret = array();
		$q = "select * from {$this->name}
			where 1 = 1 ";
		foreach ($criteria as $key => $value)
		{
			if(!empty($key) && strval($value) != "" && in_array($key, $this->fields))
			{
				if(!in_array($key, $strictFields))
					$q .= "and $key like '%" . $this->db->escape($value) . "%' ";
				else
					$q .= "and $key = '" . $this->db->escape($value) . "' ";
			}
		}
		if(in_array($orderBy, $this->fields))
			$q .= "order by $orderBy $orderDirection ";
		$q .= "limit $start, $rowsPerPage ";
		$res = $this->db->query($q);
		while($row = $this->db->fetch_array($res))
		{
			$o = $this->createLogicObject();
			$o->copyFromArray($row);
			$ret[] = $o;
		}
		return $ret;
	}

	/**
	 * Use it in conjunction with MysqlTable::search() to make pagination for multiple results.
	 */
	function getCount($criteria = array(), $start = 0, $rowsPerPage = 500, $orderBy = "", $orderDirection = "asc")
	{
		if(empty($this->fields))
			$this->readTableInfo();

		$start = intval($start);
		$rowsPerPage = intval($rowsPerPage);
		$start = intval($start);
		if(!in_array($orderDirection, array("asc", "desc")))
			$orderDirection = "asc";

		$q = "select count(*) as total from {$this->name}
			where 1 = 1 ";
		foreach ($criteria as $key => $value)
		{
			if(!empty($key) && $value != "" && in_array($key, $this->fields))
				$q .= "and $key like '%" . $this->db->escape($value) . "%' ";
		}

		$res = $this->db->query($q);
		$row = $this->db->fetch_array($res);
		return intval($row["total"]);
	}

}
?>
Return current item: Katyshop