Tweet
Moderator
<?php
/***********************************************************************
* @author Clemens Krack <ckrack@i-z.de>
* @link [url]http://www.tripdown.de[/url]
* @copyright Clemens Krack 2003
* @since 2003-12-20
* @version $Id sql.class.php, v 1.5, 25.03.2004 11:30:55 rythms Exp $
***********************************************************************
*
* @example
* // form url needed to connect.
* $dsn = "mysql://username:password@host/database_name";
* // create object and run constructor
* $sql = new sql($dsn);
* // check if object could be initialized.
* if ($sql) {
* // connection is ok.
* // more commands can follow here
* } else {
* // connection not established
* }
*
**/
/**
* sql
* basic sql functions
*/
class sql {
/**
* @var resource
* resource id of mysql connection
* @access private
**/
var $_conn;
/**
* @var resource
* resource id of last executed query
* @access private
**/
var $_result;
/**
* @var string
* mysql error information
* @access private
**/
var $_error;
/**
* @var string
* mysql error number
* @access private
**/
var $_errno;
/**
* @var integer
* amount of querys used
* @access public
**/
var $query_count;
/**
* sql::sql()
* constructor
*
* checks and parses string $dsn to mysql connection data
* connects to database and selects table
* returns TRUE if connection is established and database selected
* returns FALSE if an error occured
*
* @param string $dsn
* mysql connection url
*
* @return boolean
**/
function sql($dsn)
{
// check if dsn is correct
if (!preg_match("=mysql://([a-zA-Z0-9_\-]{1,16}):([^@:]+)@([a-zA-Z0-9_\-\.]+)/([a-zA-Z0-9_\-]+)=s", $dsn, $arr)) {
trigger_error("The DSN used to connect to the mysql database is not correct.", E_USER_ERROR);
return FALSE;
}
// parse mysql data
$dsn = parse_url($dsn);
// remove slashes from databasename
$dsn['path'] = str_replace("/", "", $dsn['path']);
// connect to mysql server
if (!$this->_db_connect($dsn['host'], $dsn['user'], $dsn['pass'])) {
trigger_error("mySQL Connection Error <br>\n" . $this->_error, E_USER_ERROR);
return FALSE;
}
// select database
if (!$this->_db_select($dsn['path'])) {
trigger_error("mySQL Database Error <br>\n" . $this->_error, E_USER_ERROR);
return FALSE;
}
return TRUE;
}
/**
* sql::_db_connect()
* @access private
*
* connects to mysql server.
*
*
* @param string $host
* hostname to connect to
*
* @param string $user
* username to connect with
*
* @param string $pass
* password to connect with
*
* @return boolean
* connection established
**/
function _db_connect($host, $user, $pass)
{
$this->_conn = mysql_connect($host, $user, $pass);
// check for errors
if ($this->is_error()) {
return FALSE;
}
return TRUE;
}
/**
* sql::_db_select()
* @access private
*
* selects database
*
* @param string $db
* database name
*
* @return boolean
**/
function _db_select($db)
{
if (!$this->_conn) {
return FALSE;
}
mysql_select_db($db, $this->_conn);
// check for errors
if ($this->is_error()) {
return FALSE;
}
return TRUE;
}
/**
* sql::is_error()
*
* checks if the last mysql operation failed and error information is available.
* sets $error to use in following debugging operations.
*
* @return boolean
* @access public
**/
function is_error()
{
// set class variable which can be used for later debugging
$this->_errno = mysql_errno();
// check if there was an error
if ($this->_errno) {
// set class variable which can be used for later debugging
$this->_error = mysql_error();
// an error occured
return TRUE;
}
// no error occured
return FALSE;
}
/**
* sql::query()
*
* performs a mysql_query using param $query
*
* @param string $query
* query to perform
*
* @return resource|boolean
* returns a resource if the query was a SELECT and one row or more could be selected.
* returns affected rows if the query was UPDATE, DELETE or similiar and succeeded.
* returns FALSE if the query failed or no rows were selected.
*
* @access public
**/
function query($query)
{
// check if connection exists
if (!$this->_conn) {
return FALSE;
}
// execute query and get resource id
$this->_result = mysql_query($query, $this->_conn);
// increment query counter
$this->query_count++;
// check for error
if ($this->is_error()) {
trigger_error("mySQL Query Error <br>\n" . $this->_error, E_USER_ERROR);
return FALSE;
}
// check if resultset could be retrieved ( => query was SELECT)
if ((is_resource($this->_result)) AND mysql_num_rows($this->_result) > 0) {
// one or more rows could be selected, resource can be used
return $this->_result;
}
if (mysql_affected_rows($this->_conn) >= 0) {
// UPDATE/DELETE Query was successfull
return mysql_affected_rows($this->_conn);
}
return FALSE;
}
?>
<?php
/**
* sql::fetch()
*
* returns an array with the results or FALSE if operation fails.
* the returned values are already unescaped!
*
* use var_dump() to view the array structure
*
* @param $query
* query to perform
*
* @return array|boolean
* @access public
**/
function fetch($query)
{
// execute query and check for errors or NULL-results
if (!$this->query($query)) {
return FALSE;
}
// make an array containing the results
$return = array();
while ($row = $this->row()) {
$return[] = $row;
}
return $return;
}
/**
* sql::row()
*
* returns unescaped result array for each resultset.
* works like mysql_fetch_assoc().
* works on the last executed query.
*
* @return array
* @access public
**/
function row()
{
// check if a result exists
if (!is_resource($this->_result)) {
return FALSE;
}
// get the current result row
$row = mysql_fetch_assoc($this->_result);
// unescape values
$row = $this->_unescape($row);
return $row;
}
/**
* sql::insert()
*
* takes an array as insert argument.
* escapes all values.
* inserts values into a given table.
*
* @param string $table
* name of table to insert into
*
* @param array $insert
* array with values and fields to insert.
* structure: (field => value)
*
* @return boolean
* @access public
**/
function insert($table, $insert)
{
// validate insert values
$insert = $this->_escape($insert);
// form query out of array
$fields = array_keys($insert);
$fields = "(" . implode(", ", $fields) . ")";
$values = "('" . implode("','",$insert) . "')";
$query = "INSERT INTO " . $table . " " . $fields . " VALUES " . $values . ";";
// execute query and check for errors
if ($this->query($query)) {
return TRUE;
}
return FALSE;
}
/**
* sql::update()
*
* updates given mysql table $table with given values,
* stored in the array $update and an optional WHERE condition.
*
* @param string $table
* name of the table to update
*
* @param array $update
* array containing fields and their values to update.
* structure: (field => value)
*
* @param string $where
* WHERE condition to use in the query.
*
* @return boolean
* insert sucessfull or not.
*
* @access public
**/
function update($table, $update, $where = FALSE)
{
// validate update values
$update = $this->_escape($update);
// form query out of array
$query = "UPDATE {$table} SET ";
foreach($update AS $field => $value) {
$query = $query . "{$field} = '{$value}', ";
}
// remove trailing "," from query
$lpos = strrpos($query, ",");
$len = strlen($query);
$query = substr($query, 0, $lpos - $len);
// check if a WHERE condition was given and insert it into the query.
$where ? $query = $query . " WHERE " . $where : NULL;
// return result of sql::query (TRUE or FALSE).
return $this->query($query);
}
/**
* sql::free()
* @access public
*
* free's memory after mysql usage
*
* @return boolean
**/
function free()
{
if(mysql_free_result($this->_result)) {
return TRUE;
}
return FALSE;
}
/**
* sql::_escape()
*
* escapes a given string or all fields of given array.
* the returned string may safely be used in mysql querys.
*
* @param mixed $data
* string or array to escape for safe usage in mysql queries.
*
* @return mixed
**/
function _escape($data)
{
// handle arrays
if (is_array($data)) {
foreach ($data as $key => $text) {
$data[$key] = $this->_escape($text);
}
return $data;
}
// remove automatically added slashes. (magic_quotes are bad hmmkay!)
if (get_magic_quotes_gpc()) {
$data = stripslashes($data);
}
// escape string for safe usage
$data = addslashes($data);
return $data;
}
/**
* sql::_unescape()
*
* unescapes a given string or all fields of given array.
* this function removes all slashes, added by sql::escape().
*
* @param mixed $data
* string or array to unescape
*
* @return mixed
**/
function _unescape($data)
{
// handle arrays
if (is_array($data)) {
foreach ($data as $key => $text) {
$data[$key] = $this->_unescape($text);
}
return $data;
}
// remove slashes
$data = stripslashes($data);
return $data;
}
/**
* sql::xml()
*
* fetch array and parse it to XML
*
* format:
* <root>
* <row id="0">
* <field name="field" value="value" />
* </row>
* </root>
*
* @param string $query
* mysql query to execute
*
* @return string
**/
function xml($query) {
$rowcount = 0;
$output = "";
$array = $this->fetch($query);
if (!$array) {
return FALSE;
}
$xml = <<<TEXT
<?xml version="1.0" encoding="ISO-8859-1"?>
TEXT;
$xml .= "\n<root>\n";
while($row = $this->row()){
$rowcount++;
$xml.= "\t<row id=\"{$rowcount}\">\n";
foreach($row as $field => $value) {
$xml .= "\t\t<field name=\"{$field}\">{$value}</field>\n";
}
$xml.="\t</row>\n";
}
$xml .= "</root>";
return $xml;
}
}
?>
Kommentar