Location: PHPKode > scripts > Database metadata > classes.mySql.inc.php
<?php
/**
 * File with 3 MySql classes:
 * <ul>
 * <li>imslib_dataReader</li>
 * <li>imslib_dataConnection</li>
 * <li>imslib_dataWriter</li>
 * </ul>
 * These classes form a abstraction layer to make migration to other database easier.
 *
 * First written: 23-08-2005
 *
 * Changelog:
 * xx-08-2005:
 * <ol>
 * 	<li></li>
 * </ol>
 *
 * @author H.F.N. den Boer <hide@address.com>
 * @copyright Copyright 2005 - 2006, IMS - the Netherlands <http://www.denboer-ims.nl>
 * @version 1.0.0
 * @package nl.denboer-ims.imslib
 */

global $imslib_Backbone;

//	First check if access of this page is allowed
if (!isset($imslib_Backbone))
	die("In order to call this file, the backbone must be included first...");

/**
 * Class to execute a SELECT SQL and read the result.
 * Abstraction layer to make migration to other database easier.
 *
 * @package nl.denboer-ims.imslib
 */
class imslib_dataReader
{
	/**
	 * Indicating that there is a next record to read
	 *
	 * @var bool
	 * @access private
	 */
	private $_hasNext;

	/**
	 * Next row to deliver
	 *
	 * @var array
	 * @access private
	 */
	private $_nextRow;

	/**
	 * Dataset
	 *
	 * @var object
	 * @access private
	 */
	private $_dataset;

	/**
	 * Number of rows in resultset
	 *
	 * @var int
	 * @access private
	 */
	private $_numRows;

	/**
	 * Number of columns in resultset
	 *
	 * @var int
	 * @access private
	 */
	private $_numColumns;

	/**
	 * SQL SELECT statement
	 *
	 * @var int
	 * @access private
	 */
	private $_sql;

	/**
	 * Max. nr. of records to fetsh
	 *
	 * @var int
	 * @access private
	 */
	private $_recordsLeft;

	/**
	 * Constructor.
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @param string $sql
	 * @return void
	 */
	public function __construct($sql)
	{
		global $imslib_Backbone;
		if (!isset($sql) || strlen($sql) == 0)
		{
			$this->_numRows = 0;
			$this->_numColumns = 0;
			$this->_hasNext = false;
			return;
		}
//		$this->_sql = $sql;
		$dataSet = null;
		$dataSet = mysql_query($sql);
		if (!is_resource($dataSet))			//	In case no rows are returned because of an error
		{
			trigger_error("\n<br />Error while retrieving dataset\n<br />" . mysql_error().
			"\n<pre>$sql</pre>\n", E_USER_ERROR);
			$this->_hasNext = false;
		}
		else
		{
			$this->_numRows = mysql_num_rows($dataSet);
			$this->_numColumns = mysql_num_fields($dataSet);
			$this->_hasNext = ($this->_numRows > 0);
		}
		$this->_dataset = $dataSet;
		if ($this->_hasNext)
			$this->_nextRow = mysql_fetch_assoc($this->_dataset);
		// or mysql_fetch_array($dataSet, MYSQL_ASSOC) or mysql_fetch_array($dataSet, MYSQL_BOTH);
	}

	/**
	 * Destructor
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @return void
	 */
	public function __destruct()
	{
//		if (isset($this->_dataset) && is_resource($this->_dataset))
//			@mysql_free_result($this->_dataset);
		unset($this->_dataset);
	}

	/**
	 * Clean up resources
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @return void
	 */
	public function dispose()
	{
		$this->__destruct();
	}

	/**
	 * Method to determine if there is a next record to read
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @return bool
	 */
	public function hasNext ()
	{
		return $this->_hasNext;
	}

	/**
	 * Method to get the next record
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @return array
	 */
	public function getNext ()
	{
		if (!isset($this->_dataset) || !is_resource($this->_dataset))
			return null;

		$retVal = $this->_nextRow;
		$this->_nextRow = mysql_fetch_array($this->_dataset, MYSQL_ASSOC);
		if (isset($this->_recordsLeft))
		{
			$this->_recordsLeft--;
			if ($this->_recordsLeft == 0)
				$this->_hasNext = false;
		}
		if (!$this->_nextRow)
			$this->_hasNext = false;

		return $retVal;
	}

	/**
	 * Method to get the number of records
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @return int
	 */
	public function getCount ()
	{
		return $this->_numRows;
	}

	/**
	 * Method to get the number of columns
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @return int
	 */
	public function columnCount ()
	{
		return $this->_numColumns;
	}

	/**
	 * Method to clean PHP strings from a database, to make them suitable for display
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @param string $string2clean
	 * @param bool $convert2entity if true, convert special HTML characters like > to entity codes
	 * @param array $row, optional
	 * @return void
	 */
	public function getCleanDbString ($string2clean, $convert2entity = true, $row = null)
	{
		$retVal = $string2clean;
		if (isset($row))
		{
			if (isset($row[$retVal]))
				$retVal = $row[$retVal];
			else
				return "";

		}
		if (is_string($retVal))
		{
			$retVal = str_replace("~~", ";", trim($retVal));

			//	Strip escapes near characteres like " and $
			//if (!get_magic_quotes_gpc())
				$retVal = stripslashes($retVal);

			//	Convert special HTML characters like > to entity codes
			if ($convert2entity)
				$retVal = htmlspecialchars($retVal);
		}

		return $retVal;
	}

	/**
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @param $header bool
	 * @param $tableParams
	 * @return void
	 */
	public function display($header, $tableParams)
	{
		// find out the number of columns in result
		$column_count = mysql_num_fields($this->_dataset);

		// TABLE form includes optional HTML arguments passed into method
		echo "<table $tableParams >\n";

		// optionally print a bold header at top of table
		if ($header)
		{
		    echo "<tr>";
			for ($column_num = 0; $column_num < $column_count; $column_num++)
			{
				$field_name = mysql_field_name($result_id, $column_num);
				echo "<th>$field_name</th>";
			}
			echo "</tr>\n";
		}
		// print the body of the table
		while ($this->hasNext())
		{
			$row = $this->getNext();
		    echo "<tr style=\"text-align: left;\" valign=\"top\">";
			for ($column_num = 0; $column_num < $column_count; $column_num++)
		        echo "<td>$row[$column_num]</td>\n";
		    echo "</tr>\n";
			echo "</table>\n";
		}
		$this->reset();
	}

	/**
	 * Method to reset the record pointer
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @return void
	 */
	public function reset()
	{
		if ($this->_numRows > 0)
			mysql_data_seek($this->_dataset, 0);	// Go back to top
		$this->_hasNext = ($this->_numRows > 0);
	}

	/**
	 * Method get a array with tables in the database
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @return array
	 */
	static function getTables()
	{
		global $imslib_Backbone;
		$retVal = array();
		$sql = "show tables";
		/* @var $dataReader imslib_dataReader */
		$dataReader = null;
		$imslib_Backbone->getDataSet($sql, $dataReader);
		while ($dataReader->hasNext())
		{
			$row = $dataReader->getNext();
			reset($row);
			$retVal[] = current($row);
		}	//	while
		$dataReader->dispose();
		unset($dataReader);
		return $retVal;
	}
}

/**
 * Class to write data of pass commands to a MySql database
 *
 * @package nl.denboer-ims.imslib
 */
class imslib_dataWriter
{
	/**
	 * Method to clean exec a SQL command and handle it nicely
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @param string $sql
	 * @return int. If the SQL statement is an INSERT, the inserted ID, else the number of affected records.
	 */
	public function execSql($sql)
	{
		global $imslib_Backbone;
		if (!isset($sql))
			return 0;

		$sql = trim($sql);

		if (strlen($sql) == 0)
			return 0;

		$retVal = 0;
		if ($imslib_Backbone->database["connection"]->isOpen())
		{
			if (!mysql_query($sql))
			{
				$_POST["sql"] = $sql;
				trigger_error("\n<br />Error while executing SQL:\n<br />" . mysql_error()  . mysql_info(), E_USER_ERROR);
			}
			if (substr($sql, 0, 6) == "INSERT")
			{
				$retVal = mysql_insert_id();
				if ($retVal <= 0)
				{
					$_POST["sql"] = $sql;
					trigger_error("\n<br />Error while inserting record:\n (id=$retVal)" . mysql_error()  . mysql_info(), E_USER_ERROR);
				}
			}
			else
				$retVal = mysql_affected_rows();
		}

		return $retVal;
	}

	/**
	 * Method to convert parameter to a SQL parameter, ready to insert/update
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @param mixed $par
	 * @param bool $forMySql, optional
	 * @return mixed
	 */
	public function getSqlParameter ($par)
	{
		$retVal = "";
		if (!isset($par))
			$retVal = "null";
		elseif (is_string($par))
		{
			$retVal = str_replace(";", "~~", trim($par));
			if (strlen($retVal) == 0)
				$retVal = "null";
			else
			{
				$retVal = mysql_real_escape_string($retVal);
//			elseif (!$forMySql && !get_magic_quotes_gpc())
//				$retVal = addslashes($retVal);
				$retVal = "'$retVal'";
			}
		}
		elseif (is_numeric($par))
		{
			if ($par == 0)
				$retVal = "null";
			else
				$retVal = $par;
		}
		else
			$retVal = $par;

		return $retVal;
	}

	/**
	 * Method to get a formated MySql parameter from timestamp.
	 * Since validation of the posted value is also a method of this class,
	 * this is the most logical place to put this method ;)
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @param int $timestamp
	 * @param bool $asTime, optional
	 * @return string
	 */
	public function getDateTime ($timestamp, $asTime = true)
	{
		$retVal = "'" . date("Y", $timestamp) . "-" . date("m", $timestamp) .
			"-" . date("d", $timestamp);

		if ($asTime)
			$retVal .= " " . date("H", $timestamp) . ":" . date("i", $timestamp) . ":00";
		$retVal .= "'";

		return $retVal;
	}
}

/**
 * Class to hold a MySql connection
 *
 * @package nl.denboer-ims.imslib
 */
class imslib_dataConnection
{
	/**
	 * Connection to database
	 *
	 * @var object
	 * @access private
	 */
	private $_connection;

	/**
	 * Indicating that the connection is open
	 *
	 * @var bool
	 * @access private
	 */
	private $_isOpen;

	/**
	 * Constructor.
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @param string $sql
	 * @return void
	 */
	public function __construct()
	{
		$this->_isOpen = false;
	}

	/**
	 * Destructor
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @return void
	 */
	public function __destruct()
	{
//		if (isset($this->_dataset) && is_resource($this->_dataset))
//			@mysql_free_result($this->_dataset);
		$this->close();
	}

	/**
	 * Connect to a database
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @return void
	 */
	public function connect($name)
	{
		global $imslib_Backbone;
		$this->close();

		/**
		 * Database config settings
		 */
		require_once("config.database.inc.php");

		if (isset($imslib_Backbone->database["Server"]) &&
			isset($imslib_Backbone->database["Username"]) &&
			isset($imslib_Backbone->database["Password"])
			)
		{
			$this->_connection = mysql_connect($imslib_Backbone->database["Server"],
				$imslib_Backbone->database["Username"], $imslib_Backbone->database["Password"]);
			if (!is_resource($this->_connection))
				trigger_error("MySql connect error " . mysql_error(), E_USER_ERROR);

			if (!mysql_selectdb($imslib_Backbone->database["Catalog"], $this->_connection))
			{
				$this->close();
				trigger_error("MySql selectdb error " . mysql_error(), E_USER_ERROR);
			}
			$this->_isOpen = true;
		}

	//	Make sure the database access vars are not visible for hackers
		unset($imslib_Backbone->database["Server"]);
		unset($imslib_Backbone->database["Username"]);
		unset($imslib_Backbone->database["Password"]);
	}

	/**
	 * Determine if we have a valid and open database connection...
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @return bool
	 */
	public function isOpen ()
	{
		return $this->_isOpen;
	}

	/**
	 * Close database connection...
	 *
	 * @since version 1.0.0
	 * @author H.F.N. den Boer <hide@address.com>
	 * @access public
	 * @return void
	 */
	public function close()
	{
		if ($this->isOpen())
			mysql_close($this->_connection);
		unset($this->_connection);
		$this->_isOpen = false;
	}
}

$imslib_Backbone->database["connection"] = new imslib_dataConnection();
$imslib_Backbone->database["writer"] = new imslib_dataWriter();
?>
Return current item: Database metadata