/mysqldatabase.php
PHP | 653 lines | 238 code | 83 blank | 332 comment | 46 complexity | 3fda13013a224cd742fbc54b9ee39d84 MD5 | raw file
Possible License(s): BSD-3-Clause
- <?php
- /**
- * @package mysql-database
- */
- /**
- * MySQL Database
- *
- * A singleton object which provides convenience methods for interfacing with
- * a MySQL database in PHP 5. You can get the object's instance using the
- * static {@link getInstance()} method. Being a singleton object, this class
- * only supports one open database connection at a time and idealy suited to
- * single-threaded applications. You can read
- * about {@link http://php.net/manual/en/language.oop5.patterns.php the singleton
- * pattern in the PHP manual}.
- *
- * <b>Getting Started</b>
- * <code>
- * $db = MySqlDatabase::getInstance();
- *
- * try {
- * $db->connect('localhost', 'user', 'password', 'database_name');
- * }
- * catch (Exception $e) {
- * die($e->getMessage());
- * }
- * </code>
- *
- * @package mysql-database
- * @author Micah Carrick
- * @copyright (c) 2010 - Micah Carrick
- * @version 2.0
- * @license BSD
- */
- class MySqlDatabase
- {
- /**
- * The MySQL link identifier created by {@link connect()}
- *
- * @var resource
- */
- public $link;
-
- /**
- * @var string
- */
- private $conn_str;
-
- /**
- * @var MySqlDatabase
- */
- private static $instance;
-
- const MYSQL_DATE_FORMAT = 'Y-m-d';
- const MYSQL_TIME_FORMAT = 'H:i:s';
- const MYSQL_DATETIME_FORMAT = 'Y-m-d H:i:s';
-
- const INSERT_GET_AUTO_INCREMENT_ID = 1;
- const INSERT_GET_AFFECTED_ROWS = 2;
-
- /**
- * Constructor
- *
- * Private constructor as part of the singleton pattern implementation.
- */
- private function __construct() {}
- /**
- * Connect
- *
- * Establish a connection to a MySQL database. Returns the MySQL link
- * link identifier or throws an exception if there is an error.
- *
- * <code>
- * // get an instance of the Database singleton
- * $db = MySqlDatabase::getInstance();
- *
- * // connect to a MySQL database (use your own login information)
- * try {
- * $db->connect('localhost', 'user', 'password', 'database_name');
- * }
- * catch (Exception $e) {
- * die($e->getMessage());
- * }
- * </code>
- *
- * @param string
- * @param string
- * @param string
- * @param string
- * @param boolean
- * @return resource
- */
- public function connect($host, $user, $password, $database=false, $persistant=false)
- {
- if ($persistant) {
- $this->link = @mysql_pconnect($host, $user, $password);
- } else {
- $this->link = @mysql_connect($host, $user, $password);
- }
-
- if (!$this->link)
- {
- throw new Exception('Unable to establish database connection: '
- .mysql_error());
- }
- if ($database) $this->useDatabase($database);
-
- $version = mysql_get_server_info();
- $this->conn_str = "'$database' on '$user@$host' (MySQL $version)";
-
- return $this->link;
- }
-
- /**
- * Delete
- *
- * Executes the DELETE statement specified in the query and returns the
- * value from either the PHP {@link mysql_affected_rows()} function. Throws
- * and exception if there is a MySQL error in the query.
- *
- * Note: With MySQL versions prior to 4.1.2, the affected rows on DELETE
- * statements with no WHERE clause is 0. See {@link mysql_affected_rows()}
- * for more information.
- *
- * @param string
- * @return integer
- */
- public function delete($query)
- {
- return $this->updateOrDelete($query);
- }
-
- /**
- * Get Connection String
- *
- * Gets a string representing the connection.
- *
- * <code>
- * echo $db->getConnectionString();
- * // example output: 'test_database' on 'web_user@localhost' (MySQL 5.1.47)
- * </code>
- *
- * @return string
- */
- public function getConnectionString()
- {
- return $this->conn_str;
- }
-
- /**
- * Get Instance
- *
- * Gets the singleton instance for this object. This method should be called
- * statically in order to use the Database object:
- *
- * <code>
- * $db = MySqlDatabase::getInstance();
- * </code>
- *
- * @return MySqlDatabase
- */
- public static function getInstance()
- {
- if (!isset(self::$instance))
- {
- self::$instance = new MySqlDatabase();
- }
-
- return self::$instance;
- }
-
- /**
- * Fetch One From Each Row
- *
- * Convenience method to get a single value from every row in a given
- * query. This is usefull in situations where you know that the result will
- * only have only one column of data and you need that all in a simple
- * array.
- *
- * <code>
- *
- * $query = "SELECT name FROM users";
- * $names = $db->fetchOneFromEachRow($query);
- * echo 'Users: ' . implode(', ', $names);
- * </code>
- *
- * @param string
- * @return array
- */
- public function fetchOneFromEachRow($query)
- {
- $rval = array();
-
- foreach ($this->iterate($query, MySqlResultSet::DATA_NUMERIC_ARRAY) as $row) {
- $rval[] = $row[0];
- }
- return $rval;
- }
-
- /**
- * Fetch One Row
- *
- * Convenience method to get a single row from a given query. This is
- * usefull in situations where you know that the result will only contain
- * one record and therefore do not need to iterate over it.
- *
- * You can
- * optionally specify the type of data to be returned (object or array)
- * using one of the MySqlResultSet Data Constants. The default is
- * {@link MySqlResultSet::DATA_OBJECT}.
- *
- * <code>
- * // get one row of data
- * $query = "SELECT first, last FROM users WHERE user_id = 24 LIMIT 1";
- * $row = $db->fetchOneRow($query);
- * echo $row->foo;
- * echo $row->bar;
- * </code>
- *
- * @param string
- * @param integer
- * @return mixed
- */
- public function fetchOneRow($query, $data_type=MySqlResultSet::DATA_OBJECT)
- {
- $result = new MySqlResultSet($query, $data_type, $this->link);
- $result->rewind();
- $row = $result->current();
- return $row;
- }
-
- /**
- * Fetch One
- *
- * Convenience method to get a single value from a single row. Returns the
- * value if the query returned a record, false if there were no results, or
- * throws an exception if there was an error with the query.
- *
- * <code>
- * // get the number of records in the 'users' table
- * $count = $db->fetchOne("SELECT COUNT(*) FROM users");
- * </code>
- *
- * @param string
- * @return mixed
- */
- public function fetchOne($query)
- {
- $result = new MySqlResultSet($query, MySqlResultSet::DATA_NUMERIC_ARRAY,
- $this->link);
- $result->rewind();
- $row = $result->current();
- if (!$row) return false;
- else return $row[0];
- }
-
- /**
- * Import SQL File
- *
- * Runs the queries defined in an SQL script file. The double-hyphen style
- * comments must have a single space after the hyphens. Hash style comments
- * and C-style comments are also supported.
- *
- * An optional user callback function can be specified to get information
- * about each MySQL statement. The user callback function takes 3
- * parameters: the line number as an integer, the query as a string, and the
- * result of the query as a boolean.
- *
- * <code>
- * function import_sql_callback($line_number, $sql_query, $result)
- * {
- * echo "Line $line_number: $sql_query ";
- * if ($result) echo "(OK)<br/>";
- * else echo "(FAIL)<br/>";
- * }
- * </code>
- *
- * You can optionally specify whether or not to abort importing statements
- * when an SQL error occurs (defaults to 'true') in which case an exception
- * will be thrown for any MySQL error.
- *
- * Returns the number of queries executed from the script or throws an
- * exception if there is an error.
- *
- * <code>
- * // no callback, throw exception on MySQL errors
- * $number = $db->importSqlFile('queries.sql');
- *
- * // callback for each query, skip queries with MySQL errors
- * $number = $db->importSqlFile('queries.sql', 'import_sql_callback', false);
- * </code>
- *
- * TODO: Ensure this works with huge files. Might need to use fopen()
- *
- * @param string
- * @param string
- * @param boolean
- * @return integer
- */
- public function importSqlFile($filename, $callback=false, $abort_on_error=true)
- {
- if ($callback && !is_callable($callback)) {
- throw new Exception("Invalid callback function.");
- }
- $lines = $this->loadFile($filename);
-
- $num_queries = 0;
- $sql_line = 0;
- $sql = '';
- $in_comment = false;
-
- foreach ($lines as $num => $line) {
-
- $line = trim($line);
- $num++;
- if (empty($sql)) $sql_line = $num;
-
- // ignore comments
-
- if ($in_comment) {
- $comment = strpos($line, '*/');
-
- if ($comment !== false) {
- $in_comment = false;
- $line = substr($line, $comment+2);
- } else {
- continue;
- }
-
- } else {
-
- $comment = strpos($line, '/*');
-
- if ($comment !== false) {
-
- if (strpos($line, '*/') === false) {
- $in_comment = true;
- }
-
- $line = substr($line, 0, $comment);
-
- } else {
-
- // single line comments
-
- foreach (array('-- ', '#') as $chars) {
- $comment = strpos($line, $chars);
-
- if ($comment !== false) {
- $line = substr($line, 0, $comment);
- }
- }
- }
- }
- // check if the statement is ready to be queried
-
- $end = strpos($line, ';');
-
- if ($end === false) {
- $sql .= $line;
- } else {
- $sql .= substr($line, 0, $end);
- $result = $this->quickQuery($sql);
- $num_queries++;
-
- if (!$result && $abort_on_error) {
- $file = basename($filename);
- $error = mysql_error($this->link);
- throw new Exception("Error in $file on line $sql_line: $error");
- }
-
- if ($callback) {
- call_user_func($callback, $sql_line, $sql, $result);
- }
-
- $sql = ''; // clear for next statement
-
- }
- }
-
- return $num_queries;
- }
-
- /**
- * Is Connected
- *
- * Determines if there is a connection open to the database.
- *
- * @return boolean
- */
- public function isConnected()
- {
- if (!empty($this->link)) {
- return @mysql_ping($this->link);
- } else {
- return false;
- }
- }
-
- // insertPhpArray
- // insertSqlArray
- // sqlval()
- /**
- * Insert
- *
- * Executes the INSERT statement specified in the query and returns the
- * value from either the PHP {@link mysql_insert_id()} function or the
- * php {@link mysql_affected_rows()} function depending on the value of the
- * $return_type parameter.
- *
- * <code>
- * $db = MySqlDatabase::getInstance();
- * $query = "INSERT INTO foobar (col1, col2) VALUES (1, 2), (2, 3)";
- * $rows = $db->insert($query, MySqlDatabase::INSERT_GET_AFFECTED_ROWS);
- * echo $rows; // output: 2
- * </code>
- *
- *
- * @param string
- * @param integer
- * @return integer
- */
- public function insert($query, $r_type=MySqlDatabase::INSERT_GET_AUTO_INCREMENT_ID)
- {
- $r = $this->query($query);
-
- if ($r_type == MySqlDatabase::INSERT_GET_AFFECTED_ROWS) {
- return @mysql_affected_rows($this->link);
- } else {
- return @mysql_insert_id($this->link);
- }
- }
-
- /**
- * DO NOT USE
- *
- * This was never finished... I don't think. The goal was to take a table
- * name, an array of column names, and an array of values and generate a
- * multiple record insert. You should not use this, but, you could help
- * out and finish or rewrite this method.
- *
- *
- * @param deprecated
- */
- public function smartInsert($table, $columns, $values)
- {
- if (empty($table) || !is_string($table)) {
- throw new Exception('The $table parameter must be specified as a string.');
- }
-
- $table_sql = '`' . @mysql_real_escape_string($table) . '`';
- $query = "INSERT INTO $table_sql ";
-
- // columns
- if (is_string($columns)) {
- $columns = explode(',', $columns);
- }
-
- if (is_array($columns)) {
- foreach ($columns as &$col) {
- if (!is_string($col)) {
- throw new Exception('The $columns parameter must be a string or an array of strings');
- }
- $col = @mysql_real_escape_string($col);
- }
- $column_sql = implode(',', $columns);
- $column_count = count($columns);
- } else {
- throw new Exception('The $columns parameter must be a string or an array of strings.');
- }
-
- try {
- $column_info = array();
-
- foreach ($this->iterate("SHOW COLUMNS FROM $table_sql") as $row) {
- $column_info[] = $row;
- }
- }
- catch (Exception $e) {
- throw new Exception("Could not get column information for table $table_sql.");
- }
-
- $query .= "($column_sql) ";
-
- // values
-
- if (is_array($values)) {
- for ($i=0; $i < count($values); $i++) {
- $info = $column_info[$i];
- $value = $values[i];
-
- // Where the heck did I leave off?
- }
- } else {
- // TODO: if only 1 column, then this will work
- throw new Exception('The $values parameter must be a string or an array.');
- }
-
- if (isset($column_count) && $column_count <> $value_count) {
- throw new Exception("Column count ($column_count) does not match values count ($value_count).");
- }
-
- $query .= "VALUES ($value_sql) ";
- echo $query;
-
- }
-
- /**
- * Iterate Result Set
- *
- * Returns a {@link MySQL_ResultSet} iteratable object for a query. The $type
- * parameter indicates the data being iterated should be an object,
- * a numerically indexed array, an associative array, or an array with
- * both numeric and associative indexes. Defaults to objects.
- *
- * <code>
- * $sql_query = "SELECT col1, col2 FROM table";
- *
- * // iterate as objects
- * foreach ($db->iterate("SELECT col1, col2 FROM table") as $row) {
- * echo $row->col1 . '<br/>';
- * echo $row->col2 . '<br/>';
- * }
- *
- * // iterate as both associative and numerically indexed array
- * foreach ($db->iterate($sql_query, MySQL_Db::DATA_ARRAY) as $row) {
- * echo $row[0] . '<br/>';
- * echo $row['col1'] . '<br/>';
- * }
- * </code>
- *
- * @param string
- * @param integer
- * @return boolean
- */
- public function iterate($sql, $data_type=MySqlResultSet::DATA_OBJECT)
- {
- return new MySqlResultSet($sql, $data_type, $this->link);
- }
-
- /**
- * Load File
- *
- * Loads the specified filename into an array of lines. Throws an exception
- * if there is an error.
- *
- * @param string
- * @return boolean
- */
- private function loadFile($filename)
- {
- if (!file_exists($filename)) {
- throw new Exception("File does not exist: $filename");
- }
-
- $file = @file($filename, FILE_IGNORE_NEW_LINES);
-
- if (!$file) {
- throw new Exception("Could not open $filename");
- }
-
- return $file;
- }
- public function query($query)
- {
- $r = @mysql_query($query, $this->link);
- if (!$r) {
- throw new Exception("Query Error: " . mysql_error());
- }
-
- return $r;
- }
-
- /**
- * Quick Query
- *
- * Executes a MySQL query and returns a boolean value indicating success
- * or failure. This method will close any resources opened from
- * SELECT, SHOW, DESCRIBE, or EXPLAIN statements and would not be very
- * usefull for those types of queries. This method is used internally for
- * importing SQL scripts.
- *
- * @param string
- * @return boolean
- */
- public function quickQuery($query)
- {
- $r = @mysql_query($query, $this->link);
-
- if (!$r) return false;
- if (is_resource($r)) mysql_free_result($r);
- return true;
- }
-
- /**
- * Update
- *
- * Executes the UPDATE statement specified in the query and returns the
- * value from either the PHP {@link mysql_affected_rows()} function. Throws
- * and exception if there is a MySQL error in the query.
- *
- * Note: The number of rows affected include only those in which the new
- * value was not the same as the old value. See {@link mysql_affected_rows()}
- * for more information.
- *
- * @param string
- * @return integer
- */
- public function update($query)
- {
- return $this->updateOrDelete($query);
- }
-
- private function updateOrDelete($query)
- {
- $r = $this->query($query);
- return @mysql_affected_rows($this->link);
- }
-
- /**
- * Use Database
- *
- * Selects the database to use. Throws an exception if there is an error
- * using the specified database.
- *
- * @param string
- * @return integer
- */
- public function useDatabase($database)
- {
- if (!@mysql_select_db($database, $this->link))
- {
- throw new Exception('Unable to select database: ' . mysql_error($this->link));
- }
- }
- }
- ?>