This MySQL class has been developed to simplify the use of MySQL databases, by enabling the programmer to query a database easily, keeping the code understandable and clear, and keeping the needed amount of lines with code to a minimum, but still provide the same functionality as other classes. It includes error reporting via a logfile or e-mail, useful for debugging purposes.
By : gday
<?php
/*
* MySQL class for the masses; version 2.12 (030722)
*
* Written by:
* Franklin van Velthuizen
* franklin (who doesn't like unsollicited mail, so remove this part) at yoki dot org
* http://www.yoki.org/
*
*
* This class is written and distributed under the GNU General
* Public License, which means that its source code if freely-distributed
* and available to the general public.
* Read about it at http://www.gnu.org/copyleft/gpl.txt
*
* Some quick guidelines:
*
* [::] Before using this class
*
* *DO* check the lines before the beginning of the class, since there are some
* variables there that need some tweaking. This class needs to be included *BEFORE*
* any output is being sent to the browser!!!
*
* [::] Connecting to the database
*
* After including this class into your php source file, you can connect to the database
* like this:
*
* <CODE> MySQL::connect($databasename, $hostname, $username, $password);
*
* After this line of code, the connection to the database is ready to be used.
*
* [::] Querying the database
*
* <CODE> $query = new Query("SELECT foo, bar FROM foo");
*
* The resultset is now stored in $query. You can fetch the rows one by one as an
* Array or as an Object. The code takes Object as the default method, but if you
* wish, you can alter this in the code.
*
* Then, you can fetch the rows in that resultset one by one:
*
* <CODE> while($row = $query->next())
* <CODE> print($row->foo);
*
* .. or, if you wish, you can fetch it as an Array:
*
* <CODE> while($row = $query->next(ROW_AS_ARRAY))
* <CODE> print($row["foo"]);
*
* .. or explicitely state that you wish to have an Object:
*
* <CODE> while($row = $query->next(ROW_AS_OBJECT))
* <CODE> print($row->foo);
*
* Another way to fetch results from a query, is this:
*
* <CODE> $query = new Query("SELECT foo, bar FROM foobar");
* <CODE> $rows = $query->fetchArrays();
*
* After this, $rows will contain ALL results from the executed query.
* Then, you can walk through the results like this:
*
* <CODE> for($i = 0; $i < sizeOf($rows); $i++)
* <CODE> {
* <CODE> extract($rows[$i]);
* <CODE> printf("Foo = %s and Bar = %s<br>\n", $foo, $bar);
* <CODE> }
*
* As you can see, the data returned by the query can be accessed by using the name
* of the corresponding field in the database as a identifying variable name. Some
* people like this kind of approach more.
*
* Another example, but this time an INSERT:
*
* <CODE> $insertedRow = new Query("INSERT INTO foo VALUES (NULL, 'foo', 'bar')");
* <CODE> printf("I inserted a record into the database, which now has ID# %d",
* $insertedRow->getInsertId);
*
* Don't create an object when inserting, because that won't give you the inserted ID.
*
* [::] And now for a more extensive description of the available methods:
*
*
* First the class that extends the base class: Query.
*
* First the public methods:
*
* - public int/resource Query(string query)
* This method takes one parameter: the query. It executes the query, and returns
* a resultset, which can be walked through by the next method (which is next() ;-)).
* One exception: if the query INSERTs something into the database, the result of
* this method will be the identifying id of the INSERTed row.
*
* - public int getInsertId()
* Using this method, you can retrieve the ID generated for an AUTO_INCREMENT column
* by the previous INSERT query. Ofcourse, this value will change after the next
* INSERT query, so be sure to retrieve it in time if needed.
*
* - public array/object next([const method])
* By calling this method, you get an object OR array containing the next row to be
* fetched in return. Use this to safely walk through a resultset, gained from
* executing a query using the default constructor of the Query class. The optional
* parameter defines the type of result you'll get. Either use the values ROW_AS_ARRAY
* or ROW_AS_OBJECT, to respectively get an Array or an Object. The default result will
* be in the form of an Object.
*
*
* The remaining methods are private, and aren't supposed to be called
* from outside this class.
*
* - private object _fetchObjectRow()
* Returns an Object, containing the next resultrow of the resultset.
*
* - private array _fetchArrayRow()
* Returns an Array, containing the next resultrow of the resultset.
*
*
*
* And now for the methods for the MySQL class itself:
*
* - public void connect([string database[, string hostname[, string username[, string password]]]])
* This class method connects to the server, and gives an appropriate response. All
* parameters are optional. If a parameter has not been specified, then
* the standard value will be used, except for the case of the
* database. If that parameter has not been specified, then a specific
* database must be chosen afterwards.
*
* - public boolean close()
* Closes the current connection to the mysql server. Not really useful, but created for
* that one time out of a million someone might need it. ;-)
*
*
* - public boolean dbSelect(string database)
* Selects a database on the current mysql server. If succeeded, true
* is being returned. If the database doesn't exist, the script exits
* with an error.
*
*
` * - public resource getLink()
* Takes no parameter, and just returns the link identifier for the current
* database connection.
*
* The remaining methods are private, and aren't supposed to be called
* from outside this class.
*
* - private array _getDebugInfo()
* Searches for all relevant info for debugging purposes. This method is supposed to be
* called from within _reportError().
*
* - private void _reportError(string errormsg[, int errno[, int response_type]])
* Private method, which handles error reporting and logging.
* response_type can be one of the following:
* 0 - reserved for later use
* 1 - send a brief summary of the error to a file on the server's filesystem
* 2 - send an email out to the address stated as $admin
* All situations print an error to stdout in case of debugging.
*
*/
/*
* $admin is the e-mail address to which possible error reports will be sent
* $logtype defines how/where error reports will be brought into the world
* $logfile is the path to the logfile, if $logtype has been set to 3
* $defaultmethod defines of which type the results returned by query() should be
* $errorPage is the page users should see when an error occurs (USE HTTP URLS!)
*/
$admin = "crap@crap.com";
$logType = 3; //1 = send to mail ($admin), 3 = write to $logfile (other options not supported (yet))
$logFile = "/www/log/mysql-class.log";
$errorPage = "http://www.yoki.org/unavailable.php";
/*
* There should be no real reason to fiddle with the lines of code after THIS line.
*/
$debug = true;
$staticLink = null;
define("ROW_AS_ARRAY", 0);
define("ROW_AS_OBJECT", 1);
$defaultMethod = ROW_AS_OBJECT;
ob_start();
class MySQL
{
var $link, $db, $result, $numrows;
/*
* void connect([string database[, string hostname[, string username[, string password]]]])
*/
function connect($db, $host, $user, $pass)
{
global $logtype, $staticLink;;
$this->link = @mysql_connect($host, $user, $pass)
or @MySQL::_reportError();
if($db)
{
if(!mysql_select_db($db))
@MySQL::_reportError();
$this->db = $db;
}
$staticLink = $this->link;
}
/*
* boolean close()
*/
function close()
{
global $logtype;
$value = @mysql_close($this->link)
or @MySQL::_reportError();
return $value;
}
/*
* boolean dbSelect(string database)
*/
function dbSelect($db)
{
global $logtype;
$value = @mysql_select_db($db)
or @MySQL::_reportError();
return $value;
}
/*
* resource getLink()
*/
function getLink()
{
global $staticLink;
if ($staticLink)
return $staticLink;
else
@MySQL::_reportError("can't get database link");
}
/*
* private array _getDebugInfo()
*/
function _getDebugInfo()
{
$debugInfoArray = debug_backtrace();
$i = 0;
while((realPath($debugInfoArray[$i]["file"]) != realPath($_SERVER["SCRIPT_FILENAME"])) && ($i<sizeOf($debugInfoArray))) $i++;
return $debugInfoArray[$i];
}
/*
* private void _reportError(string errormsg[, int errno[, int response_type]])
*/
function _reportError($txt="", $errno=-1, $type=-1)
{
global $debug, $admin, $logFile, $logType, $errorPage;
ob_clean();
if(!$txt)
{
$txt = mysql_error();
$errno = mysql_errno();
}
$debugInfo = MySQL::_getDebugInfo();
$txt = sprintf("\nFile: %s\nLine: %d\nCalled function: %s\nContained class: %s\nPassed arguments: %s\nReported error: %s", $debugInfo["file"], $debugInfo["line"], $debugInfo["function"], $debugInfo["class"], implode(", ", $debugInfo["args"]), $txt . ($errno==-1 ? "" : " (" . $errno . ")"));
if($type==-1)
$type = $logType;
switch($type)
{
/*
case "0":
if($debug)
error_log("MySQL error reported: " . $txt . " - reported by: " .
"http://" . getenv("HTTP_HOST") . getenv("REQUEST_URI")." - refering page: ".$_SERVER["HTTP_REFERER"], 0);
break;
*/
case "1":
if($debug)
{
$msg = sprintf("MySQL error reported! %s\n\nReported by: http://%s%s\nRefering page: %s", $txt, $_SERVER["HTTP_HOST"], $_SERVER["REQUEST_URI"], $_SERVER["HTTP_REFERER"]);
error_log($msg, 1, $admin, sprintf("From: %s", $_SERVER["HTTP_HOSTS"]));
}
break;
case "3":
if($debug)
{
$stamp = date("[d/M/Y:G:i:s]");
$msg = sprintf("%s %s (%s%s)\r\n\r\n", $stamp, $txt, $_SERVER["HTTP_HOST"], $_SERVER["REQUEST_URI"]);
error_log($msg, 3, $logFile);
}
break;
default:
die("<b>Exiting</b>: wrong call to _reportError()\n");
}
header(sprintf("Location: %s", $errorPage));
/*
die("</td></tr></table>
</td></tr></table>
</td></tr></table>
</td></tr></table>
</td></tr></table>
<br>
<b>Exiting due to an error</b>! " . nl2br($txt) . "\n");
*/
exit;
}
}
class Query extends MySQL
{
/*
* int/resource Query(string query)
*/
function Query($query)
{
global $staticLink, $logtype, $defaultMethod;
if(!$staticLink)
@MySQL::_reportError();
$this->result = @mysql_query($query, $staticLink);
if($this->result)
{
if(ereg("^insert", strtolower($query)))
{
$this->insertId = mysql_insert_id();
return $this->result;
}
elseif(ereg("^select", strtolower($query)))
{
$this->numrows = mysql_num_rows($this->result);
return $this->result;
}
else
return;
}
else
@MySQL::_reportError();
}
/*
* int getInsertId()
*/
function getInsertId(){ return $this->insertId; }
/*
* array/object next([const method])
*/
function next($method = -1)
{
global $defaultMethod;
if($method == -1)
$method = $defaultMethod;
if($method == ROW_AS_OBJECT)
return $this->_fetchObjectRow();
elseif($method == ROW_AS_ARRAY)
return $this->_fetchArrayRow();
}
/*
* object _fetchObjectRow()
*/
function _fetchObjectRow()
{
if(!($row = @mysql_fetch_object($this->result)))
return false;
else
return $row;
}
/*
* array _fetchArrayRow()
*/
function _fetchArrayRow()
{
if(!($row = @mysql_fetch_array($this->result)))
return false;
else
return $row;
}
/*
* array fetchObjects()
*/
function fetchObjects()
{
$obj = Array();
while($val = @mysql_fetch_object($this->result))
{
$obj[] = $val;
}
return $obj;
}
/*
* array fetchArrays()
*/
function fetchArrays()
{
$arr = Array();
while($val = @mysql_fetch_array($this->result))
{
$arr[] = $val;
}
return $arr;
}
/*
* void free()
*/
function free()
{
@mysql_free_result($this->result) or
@MySQL::_reportError();
}
/*
* void reset()
*/
function reset()
{
@mysql_data_seek($this->result, 0) or
@MySQL::_reportError();
}
}
?>
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More Database Code Articles
More By Codewalkers
 developerWorks - FREE Tools!
|
As businesses grow increasingly dependent upon Web applications to provide services to customers, employees and partners, these complex applications become more difficult to secure. Although traditional security solutions protect Internet infrastructure layers, they do not guard against HTTP and HTML attacks. Many organizations that conduct security testing still deploy applications that allow attackers to manipulate their logic and wreak havoc on their business. To mitigate this risk, development and delivery teams must address Web application security throughout the lifecycle, addressing the many layers detailed in this paper.
FREE! Go There Now!
|
|
|
|
Learn how you can extend modern application lifecycle management to IBM System z through the IBM Rational Software Delivery Platform (SDP). The Did you say mainframe? e-kit includes podcasts, webcasts, tutorials, white and red papers, demos, and articles designed to help ease the challenges of modernizing your enterprise. This complimentary kit for mainframe developers is a practical, how-to guide for making the most of an existing development environment, including the skills and infrastructure already in place at an established enterprise.
FREE! Go There Now!
|
|
|
|
WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies.
FREE! Go There Now!
|
|
|
|
Discover how IBM Rational AppScan Standard Edition can help you detext vulnerabilities in your web applications in the Web Application Security eKit. IBM Rational AppScan is a leading suite of automated web application security solutions that scan and test for common Web application vulnerabilities. The new Web Application Security eKit provides you with valuable resources, including white papers, demos, and additional information on the benefits of testing your Web applications.
FREE! Go There Now!
|
|
|
|
This webcast outlines the best practices that must be instituted to gain the maximum benefit from SOA while maintaining high quality of service. Whether you are deploying new applications or managing and monitoring your existing infrastructure, learn how you can ensure high quality of services with SOA based solutions from IBM. All registrants who attend this live Web Seminar will receive complimentary access to a white paper titled “Maintaining QoS in an SOA Environment”.
FREE! Go There Now!
|
|
|
|
Regression testing -- in which code is thoroughly tested to ensure that changes have not produced unexpected results -- is an important part of any development process. But many testing environments neglect the terminal-based applications that still form the backbone of many industries. In this tutorial, you'll learn how the Rational Functional Tester Extension for Terminal-Based Applications works with other Rational Functional Tester to help test terminal-based applications quickly and easily.
FREE! Go There Now!
|
|
|
|
Get a free trial download of the latest version of IBM Rational Method Composer V7.2 which helps you deliver customized yet consistent process guidance to your project teams and IT organization, and includes the latest version of IBM Rational Unified Process (RUP), which has provided process guidance to teams since 1996.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to try the IBM SOA Sandbox for process. The SOA Sandbox for process focuses on providing a trial environment with the necessary tooling and components required to gain a better understanding of business processes and how to best improve existing business processes to derive value quickly.
FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference, to hear how Enterprise Generation Language (EGL) eliminates the need for tedious and error-prone low level coding, so developers can focus on business requirements. EGL extends the Rational software development platform with a simplified programming language that enables developers who have little or no experience with Java, Web technologies or Service Oriented Architecture, to create enterprise-class applications and services quickly and easily. It also allows developers who may have little or no mainframe programming experience to quickly create traditional mainframe components.
FREE! Go There Now!
|
|
|
|
User communities play an important role in communication and collaboration around products, solutions and other areas of special interest to members. Successful communities are able to provide the right mix of content and services to deliver a value proposition that resonates with each audience. Join Tom Inman, VP of Marketing for Information and Platform Solutions as he introduces the new LeverageINFORMATION community. During this webcast, learn about the value provided by the community and how customers and partners derive value from the community in addressing their own technical and business challenges.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
