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!
|
Achieving true agility is a never-ending effort. We will showcase how you can become agile incrementally, a few practices at the time.Which practices should any agile team strive to adopt? What additional practices should you consider based on your needs to scale? Adopting practices are however made much easier with the right tool support. What about if your tools adapt to your practices? We will take a look at how the Jazz technology can be leveraged to make your process change the behavior of your tools.
FREE! Go There Now!
|
|
|
|
Building a software-as-a-service solution requires addressing a few key technical challenges. In this webcast, we'll focus on the role of IBM Tivoli Directory Server and WebSphere Portlet Factory in creating a Software as a Service solution. We will demonstrate how to use Tivoli Directory Server to prevent the user population of one tenant from accessing the virtual portal and portlet components of another tenant. We will also use the dynamic profile capability of WebSphere Portlet Factory to create multiple highly customized applications from one code base.
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!
|
|
|
|
Secure your Web applications with IBM Rational AppScan Standard Edition V7.7, previously known as Watchfire AppScan. This Web application security testing tool automates vulnerability assessments and scans and tests for common Web application vulnerabilities. Visit IBM developerWorks to download a free trial of IBM Rational AppScan Standard Edition V7.7.
FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference on December 4 at 1:00 pm ET to discuss how Rational Method Composer can help meet your compliance objectives. Get your questions answered!
FREE! Go There Now!
|
|
|
|
Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads.
FREE! Go There Now!
|
|
|
|
Get a free trial download of the latest version of IBM Rational Tester for SOA Quality V7.0.1, a functional and regression testing tool that enables the creation, comprehension, modification and execution of testing GUI-less Web services.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to try the IBM SOA Sandbox for connectivity. The SOA Sandbox for connectivity provides a trial environment with the tooling and components to help you explore how to effectively connect your infrastructure and integrate all of the people, processes and information in your company. Use the hosted sandbox to explore SOA techniques that streamline connecting existing IT assets together, as well as learn how to connect them to new business logic.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to try the IBM SOA Sandbox for people. The SOA Sandbox for people provides a trial environment with the necessary tooling and components required to enable consistent human and process interaction and collaboration, showing how you can improve user experience and business productivity.
FREE! Go There Now!
|
|
|
|
In this webcast, IBM Rational will discuss the importance of Web application security and will share techniques and best practices to introduce application security testing into current QA processes including: understanding common security vulnerabilities and techniques to integrate security testing with defect tracking and remediation systems in an effort to safeguard sensitive online information.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
/ 5 
