MySQL Wrapper Klasse
MySQL wrapper class
One of the older classes, but nonetheless worth to publish as it may be of use. The MySQL
wrapper contains methods to obtain information about the database, tables, as well as normal
queries, queries to objects or arrays and key-value based requests. Multiple instances are
possible, as each object has an own link to its database connection. The instances
automatically connect to the database and throw exceptions (MySqlException) on error.
It is also the base class for a MySQL administration class, which is published on this
site as well.
Dies ist definitiv eine der älteren Klassen, aber dennoch wert, veröffentlicht zu werden.
Der MySQL-Wrapper beinhaltet Methoden, um Informationen über die Datenbank und Tabellen
einzuholen, sowie Queries in Objekte, Arrays und Key-Value Abfragen. Mehrere Instanzen
können unabhängig (gleichzeitig) verwendet werden, da jedes Objekt seine eigne Verbindungsreferenz
hält. Die Instanzen verbinden sich auch selbständig zum und trennen vom MySQL-Server. Bei
Fehlern werden Exceptions geworfen (MySqlException). Diese Klasse ist die Base-Klasse
für eine MySQL-Administrationsklasse, welche auch auf diesen Seiten veröffentlicht ist.
Sample source code
Anwendungsbeispiel
<?php
require_once(__DIR__ . '/swlib/swlib.php');
use sw\MySqlException;
use sw\MySql;
// This example referrs to a table, which was generated usin the query:
//
// SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
// CREATE TABLE IF NOT EXISTS `test` (
// `id` int(11) NOT NULL AUTO_INCREMENT,
// `rid` int(11) NOT NULL,
// PRIMARY KEY (`id`)
// ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
//
//
// (it is just a renamed relation table of on of my projects)
// The database object
$db = new MySql('test', 'root', '', 'localhost');
// Returns information about the database
$r = $db->getStatus();
print '$db->getStatus() = ' . print_r($r, true) . '<br/>';
// Returns information about the status and fields of all tables
$r = $db->getTableStatus();
print '$db->getTableStatus() = ' . print_r($r, true) . '<br/>';
// Returns information about the fields of a table
$r = $db->getTableFields('test');
print '$db->getTableFields() = ' . print_r($r, true) . '<br/>';
// Query an insert, where the fields are defined as array keys and the values
// as the array values. The return value is the id if the just inserted row.
$id = $db->queryInsertInto('test', array('rid' => rand(0, 1000)));
print '$db->queryInsertInto(...) = ' . print_r($id, true) . '<br/>';
// Query the fields of one entry. This method returns directly the content
// if the filter is an ID (numeric). Otherwise it returns an array containing
// the rows with associative values (even if there is only one matching row).
$r = $db->queryFields('test', array('id','rid'), $id);
print '$db->queryFields(' . $id . ') = ' . print_r($r, true) . '<br/>';
// This method works with SQL syntax and returns an array of objects
$r = $db->queryObjects('SELECT * FROM test WHERE id=' . $id);
print '$db->queryObjects(...) = ' . print_r($r, true) . '<br/>';
// This method works with SQL syntax and returns an array of field-arrays
$r = $db->queryArrays('SELECT * FROM test WHERE id=' . $id);
print '$db->queryArrays(...) = ' . print_r($r, true) . '<br/>';
// Change a row
$r = $db->queryUpdateTable('test', array('id' => $id, 'rid' => rand(0, 1000)));
print '$db->queryUpdateTable(...) = ' . print_r($r, true) . '<br/>';
// Delete the row
$r = $db->queryDeleteFrom('test', $id);
?>Output
Ausgabe
~~~~~php ~~~~~
Class source code
Klassen-Quelltext
<?php
/**
* Exception thrown by class MySql and MySqlAdministration
* @gpackage de.atwillys.sw.php.swLib
* @author Stefan Wilhelm
* @copyright Stefan Wilhelm, 2007-2010
* @license GPL
* @version 1.0
*/
namespace sw;
class MySqlException extends LException {
}<?php
/**
* MySQL wrapper, which provides auto connect/disconnect, normal SQL query,
* escaping, and special query methods like queryInsertInto(), queryDeleteFrom(),
* queryUpdate(), query to objects, query information about the database or
* tables - all kind of queries you use regularely.
* @gpackage de.atwillys.sw.php.swLib
* @author Stefan Wilhelm
* @copyright Stefan Wilhelm, 2007-2010
* @license GPL
* @version 1.0
* @uses MySqlException
* @uses Tracer
*/
namespace sw;
class MySql {
/**
* Class configuration
* @staticvar array
*/
private static $config = array(
'server' => 'localhost',
'database' => '',
'user' => '',
'password' => ''
);
/**
* SQL connection link /id
* @var unknown
*/
private $link;
/**
* Server connection, usual localhost
* @var string
*/
private $server;
/**
* SQL server login user
* @var string
*/
private $user;
/**
* SQL login password
* @var string
*/
private $password;
/**
* SQL database
* @var string
*/
private $database;
/**
* Connected flag
* @var bool
*/
private $connected;
/**
* Defines class defaults
* @param string $database
* @param string $user
* @param string $password
* @param string $server
*/
public static final function config($database=null, $user=null, $password=null, $server=null) {
if (!is_null($database))
self::$config['database'] = (empty($database) ? 'localhost' : $database);
if (!is_null($server))
self::$config['server'] = trim($server);
if (!is_null($user))
self::$config['user'] = trim($user);
if (!is_null($password))
self::$config['password'] = $password;
}
/**
* Escapes the value of a mysql query value text (so that no injection
* possible and normal text values do not cause an unexpected error)
* Null is escaped with NULL
* @param string $query_value
* @return string
*/
public static final function escape($query_value) {
if (empty($query_value)) {
return is_null($query_value) ? 'NULL' : '';
} else {
return mysql_real_escape_string($query_value);
}
}
/**
* MySQL wrapper class constructor
* @param string $database
* @param string $user
* @param string $password
* @param string $server
*/
public function __construct($database='', $user='', $password='', $server='') {
Tracer::trace("($database, $user, (pwd), $server)");
$this->server = $server != '' ? $server : self::$config['server'];
$this->user = $user != '' ? $user : self::$config['user'];
$this->password = $password != '' ? $password : self::$config['password'];
$this->database = $database != '' ? $database : self::$config['database'];
$this->connected = false;
$this->link = null;
try {
$this->connect();
} catch (\Exception $e) {
Tracer::traceException($e);
}
}
/**
* MySQL wrapper class destructor
*/
public function __destruct() {
Tracer::trace("Disconnect ...");
$this->disconnect(true);
}
/**
* Server connected inicator
* @return bool
*/
public final function isConnected() {
return $this->connected;
}
/**
* Returns the name of the database
* @return string
*/
public final function getDatabaseName() {
return $this->database;
}
/**
* Connect to server
*/
public final function connect() {
if (!$this->connected) {
Tracer::trace("Connecting");
$this->link = @mysql_connect($this->server, $this->user, $this->password);
if (!$this->link) {
throw new MySqlException("Connecting to database failed");
} else if (!mysql_select_db($this->database)) {
@mysql_close($this->link);
throw new MySqlException("Failed to select database");
} else {
if (function_exists('mysql_set_charset')) {
@mysql_set_charset('utf8', $this->link);
} else {
@mysql_query("SET NAMES 'utf8'", $this->link);
}
$this->connected = true;
}
}
}
/**
* Disconnect MySQL server
*/
public final function disconnect() {
@mysql_close($this->link);
$this->link = null;
$this->connected = false;
}
/**
* Returns the database structure and status as assoc. array.
* @return array
*/
public final function getStatus() {
$database = array(
'name' => $this->getDatabaseName(),
'tables' => array()
);
$tables = $this->getTableStatus();
foreach ($tables as $table) {
$name = $table['name'];
$table['fields'] = $this->getTableFields($name);
$database['tables'][$name] = $table;
}
return $database;
}
/**
* Returns the list of table names in the database
* @return array
*/
public final function getTableNames() {
if (!$this->isConnected()) {
$this->connect();
}
$result = array();
$tables = $this->query("SHOW TABLES FROM " . $this->database);
foreach ($tables as $table) {
$table = reset($table);
$result[] = $table;
}
return $result;
}
/**
* Returns an assoc. array with information about all tables
* @return array
*/
public final function getTableStatus() {
$tables = array();
$r = $this->query("SHOW TABLE STATUS FROM " . $this->getDatabaseName() . ";");
foreach ($r as $table) {
$table = array_change_key_case($table, CASE_LOWER);
$tables[$table['name']] = $table;
}
return $tables;
}
/**
* Returns the field specifications of the database fields
* @param string $table
* @return array
*/
public final function getTableFields($table) {
$result = array();
$table = self::escape($table);
$fields = $this->query("SHOW FULL COLUMNS FROM `$table`");
foreach ($fields as $field) {
$field = array_change_key_case($field, CASE_LOWER);
$result[$field['field']] = $field;
}
return $result;
}
/**
* Returns the resource to the query. The result resource has to be
* set free manually.
* @param string $query
* @return resource
*/
public final function queryResource($query) {
Tracer::trace("query=$query");
if (empty($query)) {
throw new MySqlException("Query failed: no query rule specified.");
} else if (!$this->isConnected()) {
$this->connect();
}
$result = @mysql_query($query, $this->link);
if ($result === false) {
Tracer::trace('MySql ERROR=' . mysql_error());
throw new MySqlException('MySql query failed');
} else {
return $result;
}
}
/**
* Perform query. Returns array of assoc arrays
* @param string $query
* @return array&
*/
public final function & query($query) {
$result = $this->queryResource($query);
$data = array();
if (is_resource($result)) {
while ($row = mysql_fetch_assoc($result)) {
$data[] = $row;
}
@mysql_free_result($result);
}
return $data;
}
/**
* Query SELECT form the table, where all fields specified by the field names
* array are fetched into a structure (assoc. array).
* @param string $table
* @param array $fieldNames
* @param string $idOrFilter
* @param int $numRows
* @param int $fromRow
* @return void
*/
public final function & queryFields($table, $fieldNames=array(), $idOrFilter='', $numRows=0, $fromRow=0) {
$fromRow = intval($fromRow);
$numRows = intval($numRows);
$table = '`' . self::escape(trim($table)) . '`';
if (empty($fieldNames)) {
$fields = '*';
} else if (!is_array($fieldNames)) {
throw new MySqlException('Specified field names must be passed as array');
} else {
$fields = array();
foreach ($fieldNames as $key => $f) {
$fields[] = '`' . self::escape($f) . '`';
}
$fields = implode(',', $fields);
}
$query = "SELECT $fields FROM $table";
if (is_numeric($idOrFilter)) {
$query .= ' WHERE id=' . intval($idOrFilter);
} else if (!empty($idOrFilter)) {
$query .= " WHERE $idOrFilter";
}
if ($numRows > 0) {
$query .= ($fromRow > 0) ? (" LIMIT $fromRow, $numRows") : (" LIMIT $numRows");
}
$r = $this->query($query);
return $r;
}
/**
* Query into a result instance.
* @return array
*/
public final function & queryArrays($query) {
$result = $this->queryResource($query);
$data = array();
while ($row = mysql_fetch_array($result))
$data[] = $row;
@mysql_free_result($result);
return $data;
}
/**
* Query into a result instance.
* @return array
*/
public final function & queryObjects($query, $class=null) {
$result = $this->queryResource($query);
if (!is_null($class) && !class_exists($class) && !swlib::hasClass($class)) {
throw new MySqlException('Class does not exist: :class', array(':class' => $class));
} else {
$data = array();
if (is_null($class)) {
while ($o = mysql_fetch_object($result)) {
$data[] = $o;
}
} else {
while ($o = mysql_fetch_object($result, $class)) {
$data[] = $o;
}
}
@mysql_free_result($result);
return $data;
}
}
/**
* Query UPDATE table, where the SET <key>='<value>' are the array key/value pairs.
* $filter is according to "WHERE id=<id>" or "WHERE name="test" LIMIT 1";
* @param string $table
* @param array $keyValuePairs
* @param string $filter
* @param int $limit
* @return void
*/
public final function queryUpdateTable($table, array $keyValuePairs, $filter='', $limit=1) {
$table = self::escape($table);
$limit = (is_numeric($limit) && $limit > 0) ? " LIMIT " . intval($limit) : '';
if (empty($filter)) {
if (isset($keyValuePairs['id'])) {
$filter = "WHERE id=" . self::escape($keyValuePairs['id']);
unset($keyValuePairs['id']);
} else {
throw new MySqlException('You must set a filter (WHERE anyfield=anyvalue) if you do not have the filed "id"');
}
}
foreach ($keyValuePairs as $key => $value) {
$keyValuePairs[$key] = "`$key`='" . self::escape($value) . "'";
}
$keyValuePairs = implode(',', $keyValuePairs);
$query = "UPDATE `$table` SET $keyValuePairs $filter $limit";
$this->query($query);
}
/**
* Query INSERT INTO table, where the SET <key>='<value>' are the array key/value pairs.
* Returns the complete just inserted row.
* @param string $table
* @param array $keyValuePairs
* @return array
*/
public final function queryInsertInto($table, array $keyValuePairs) {
$table = trim($table);
if (empty($table)) {
throw new MySqlException('Cannot append data in database without a specified table (empty string)');
} else {
$table = '`' . self::escape($table) . '`';
$keys = $values = array();
if (isset($keys['id']) && empty($keys['id'])) {
$keys['id'] = null;
}
foreach ($keyValuePairs as $key => $value) {
if (empty($key)) {
throw new MySqlException('Database field key is empty');
} else {
$keys[] = '`' . self::escape(trim($key)) . '`';
$values[] = "'" . self::escape($value) . "'";
}
}
$keys = implode(',', $keys);
$values = implode(',', $values);
$query = "INSERT INTO $table ($keys) VALUES ($values);";
$this->query($query);
$r = $this->query('SELECT LAST_INSERT_ID();');
if (empty($r)) {
throw new MySqlException('Could not get autoincrement id of insert action');
} else {
$r = reset($r);
return reset($r);
}
}
}
/**
* Query DELETE FROM table. If $idOrFilter is numeric, then the filter
* will be automatically set to "WHERE id=$idOrFilter", if filter is a
* text, then the filter text will be used.
* @param string $table
* @param array $idOrFilter
* @return void
*/
public final function queryDeleteFrom($table, $idOrFilter) {
$idOrFilter = trim($idOrFilter);
if (is_numeric($idOrFilter)) {
$idOrFilter = "id='$idOrFilter'";
} else if (empty($idOrFilter)) {
throw new MySqlException('No id/filter given to indicate the row to delete');
}
$this->query("DELETE FROM $table WHERE $idOrFilter");
}
/**
* Saves the a BLOB in the database in a file in the file system. Write
* the condition like "id=1". The WHERE is added by the method.
* @param string $file
* @param string $table
* @param string $field
* @param string $condition
*/
public final function queryBlobToFile($file, $table, $field, $condition) {
$file = trim($file);
$table = self::escape(trim($table));
$field = self::escape(trim($field));
$condition = str_replace(';', '', $condition); // no multiple commands allowed.
if (file_exists($file)) {
throw new MySqlException('The file to write the BLOB in already exists');
} else if (!is_dir(dirname($file))) {
throw new MySqlException('Parent directory of the file to save does not exist');
} else if (!is_writable(dirname($file))) {
throw new MySqlException('Parent directory of the file to save is not writable for you');
} else {
$query = "SELECT `$field` FROM `$table` WHERE $condition LIMIT 1 INTO DUMPFILE '$file'";
Tracer::trace($query);
$this->query($query);
}
}
/**
* Performs a query to write a BLOB field in the database from a
* existing file in the file system.
* @param string $file
* @param string $table
* @param string $field
*/
public final function queryFileToBlob($file, $table, $field, $condition) {
// All the submethods throw exceptions if something goes wrong.
$file = trim($file);
$table = self::escape(trim($table));
$field = self::escape(trim($field));
$condition = str_replace(';', '', $condition); // no multiple commands allowed.
$data = self::escape(FileSystem::readFile($file));
Tracer::trace("UPDATE `$table` SET `$field`='[BLOB of " . (strlen($data) >> 10) . "kb]' WHERE $condition");
$this->query("UPDATE `$table` SET `$field`='$data' WHERE $condition");
}
}