get free code

/**
* A class providing some very useful methods to work with a mySQL database.
*
* Some highlights
*
* - connects you to a mySQL host and select a database in a single call (opposed to PHP's native functions which
* requires two steps)
* - has a "query" method that has the same role as PHP's mysql_query but this one will tell you about the affected rows
* (through the "affectedRows" property) when executing an INSERT, UPDATE, DELETE query and about the found rows
* (through the "foundRows" property) when executing a SELECT query - the "foundRows" property tells you how many
* records would the query return if there was no LIMIT applied to it - very useful when creating listing so you
* don't have to do a query to determine how many records you have in a database and the again a query to show only
* the records from a page
* - has an "escape_string" method that will "mysql_real_escape_string" your string weather the magic_quotes are on or not
* - has a "dlookup" method that i am sure you will find VERY useful once you get to know it: it return data from a single
* table cell based on standard mySQL WHERE criteria - see the manual for detailed info! (yes, it acts exactly like
* the function with the same name from microsft access)
* - provides you with a very useful debug interface which shows you each query your script is running, for how long,
* the total number of queries made, the total execution time of your queries, errors of your queries and what are
* the values of $_GET, $_POST, $_COOKIES and $_SERVER superglobal variables. The debug interface is template driven
* and supports localisation.
* - it will notify you if a specific query is executed more than once and will advise you to optimize the script
* - you can instruct it to send you an email if a query runs longer then a specified time
* - the code is approx 37Kb in size but still heavily documented so you can easily understand every aspect of it
*
* See the manual for more info.
*
* This work is licensed under the Creative Commons Attribution-NonCommercial-NoDerivs 2.5 License.
* To view a copy of this license, visit {@link http://creativecommons.org/licenses/by-nc-nd/2.5/} or send a letter to
* Creative Commons, 543 Howard Street, 5th Floor, San Francisco, California, 94105, USA.
*
* For more resources visit {@link http://stefangabos.blogspot.com}
*
* @author Stefan Gabos
* @version 1.02 (last revision: August 10, 2006)
* @copyright (c) 2006 Stefan Gabos
* @package database
* @example example.php
*/

error_reporting(E_ALL);

class database
{

/**
* The time (in seconds) after which a query will be considered 'erroneous'
*
* @var integer
*/
var $maxQueryTime = 30;

/**
* The email address to which a notification to be sent when erroneous queries are encountered
*
* @var string
*/
var $maxQueryTimeExceeded_notificationAddress = "root@localhost.com";

/**
* The domain name to use in the subject of the mails sent when erroneous queries are encountered
*
* @var string
*/
var $maxQueryTimeExceeded_notifierDomain = "localhost.com";

/**
* After an INSERT, UPDATE, DELETE query, by reading this property you get the number of affected rows
*
* This is a read-only property!
*
* @var integer
*/
var $affectedRows = 0;

/**
* By setting this to TRUE, calling the show_debug_information() method will provide debug information
*
* @var boolean
*/
var $debug = false;

/**
* After a SELECT query, by reading this property you get the number of records that would've been returned if there was no LIMIT
*
* This is a read-only property!
*
* @var integer
*/
var $foundRows = 0;

/**
* Default language file to use
*
* @var string
*/
var $languageFile = "english.php";

/**
* Default template folder to use
*
* Note that only the folder of the template you wish to use needs to be specified. Inside the folder
* you must have the debug.xtpl file which will be automatically used
*
* @var string
*/
var $template = "default";

/**
* Holds debug information
*
* @access private
*/
var $debugInfo = array();

/**
* mySQL link identifier
*
* @access private
*/
var $link = false;

/**
* mySQL selected database
*
* @access private
*/
var $database = false;

/**
* Constructor of the class
*
* @access private
*/
function database()
{

// get path of class and replace (on a windows machine) \ with /
// this path is to be used for all includes as it is an absolute path
$this->classPath = preg_replace("/\\\/", "/", dirname(__FILE__));

// remove $_SERVER["DOCUMENT_ROOT"] from the path
// this path is to be used from within HTML as it is a relative path
$this->strippedPath = preg_replace("/".preg_replace("/\//", "\/", $_SERVER["DOCUMENT_ROOT"])."/i", "", $this->classPath);

// include the language file
require_once $this->classPath."/languages/".$this->languageFile;

}

/**
* Connects to a mySQL database
*
* Example:
*
*
* /**
* notice that we're doing no error checking as we will have
* any errors show up in the debug window
* so don't forget to have at the end of your code a call to
* show_debug_info() method
* {@*}
* $db->connect("localhost", "root", "", "test");
*
*
* @param string $mySQLHost the address of the mySQL server to connect to (i.e. localhost)
* @param string $mySQLUser the username used for authentication when connecting to the mySQL server
* @param string $mySQLPassword the password used for authentication when connecting to the mySQL server
* @param string $mySQLDatabase the database to be selected after connection is estabilished
*
* @return boolean returns TRUE on success and FALSE upon failure
*/
function connect($mySQLHost, $mySQLUser, $mySQLPassword, $mySQLDatabase)
{

// tries to conntect to the mysql database using the given parameters
$this->link = @mysql_connect($mySQLHost, $mySQLUser, $mySQLPassword);

// if connection could not be estabilished
if (!$this->link) {

// if debug is on
if ($this->debug) {
// save debug information
$this->debugInfo["messages"][] = array(
"message"=>"strLang_couldNotConnectToDatabase",
"method"=>"connect()",
"parameters"=>"connect(".$mySQLHost.", ".$mySQLUser.", ".$mySQLPassword.")"
);
}

// return false
return false;

// if connection could be estabilished
} else {

// select the database
$this->database = @mysql_select_db($mySQLDatabase, $this->link);

// if database could not be selected
if (!$this->database) {

// if debug is on
if ($this->debug) {
// save debug information
$this->debugInfo["messages"][] = array(
"message"=>"strLang_couldNotSelectDatabase",
"method"=>"connect",
"parameters"=>"connect(".$mySQLHost.", ".$mySQLUser.", ".$mySQLPassword.", ".$mySQLDatabase.")"
);
}

// return false
return false;
}

}

// return true if there is no error
return true;

}

/**
* Escapes a string's special characters and prepares it for insertion in a database.
* Works even if magic_quotes is ON
*
* Use this for ALL the inserted data to prevent mySQL injection!
*
* Example:
*
*
* print_r($db->escape_string("John O'Bryan"));
*
*
* @param string $string string to escape
*
* @return string escaped string
*/
function escape_string($string)
{

// get the state of "magic quotes"
// and if "magic quotes" are on
if (get_magic_quotes_gpc()) {

// strip slashes
$returnValue = stripslashes($string);

}

// escape the string
$returnValue = mysql_real_escape_string($string);

// return escaped string
return $returnValue;

}

/**
* Alias for the mysql_query function.
*
* After a SELECT query you can get the number of records that would've been returned if there was no
* LIMIT by reading the foundRows property.
*
* After an UPDATE, INSERT or DELETE query you can get the number of affected rows
* by reading the affectedRows property.
*
* Example:
*
*
* /**
* notice that we're doing no error checking as we will have
* any errors show up in the debug window
* so don't forget to have at the end of your code a call to
* show_debug_info() method
* {@*}
* $result = $db->query("SELECT * FROM table WHERE 1");
*
*
* @param string $query query to execute
*
* @return mixed returns a resource on success and FALSE on error
*/
function query($query)
{

// checks is there is an active connection
if ($this->_connected()) {

// if we have a SELECT query and the SQL_CALC_FOUND_ROWS string is not in it
// (we do this trick to get the numbers of records that would've been returned if there was no LIMIT applied)
if (strtolower(substr(ltrim($query), 0, 6)) == "select" && strpos($query, "SQL_CALC_FOUND_ROWS") === false) {

// add the 'SQL_CALC_FOUND_ROWS' parameter to the query
$query = preg_replace("/SELECT/i", "SELECT SQL_CALC_FOUND_ROWS", $query, 1);

}

// starts a timer
$startTime = microtime(true);
// executes the query
$result = @mysql_query($query);
// stops timer
$endTime = microtime(true);

// if execution time exceeds maxQueryTime
if ($endTime - $startTime > $this->maxQueryTime) {

// then send a notification mail
@mail(
$this->maxQueryTimeExceeded_notificationAddress,
sprintf($this->languageStrings["strLang_erroneousQueryEMailSubject"], $this->maxQueryTimeExceeded_notifierDomain),
sprintf($this->languageStrings["strLang_erroneousQueryEMailContent"], $this->maxQueryTime, $endTime - $startTime, $query),
"From: ".$this->maxQueryTimeExceeded_notifierDomain
);

}

// if debug is on
if ($this->debug) {

$warning = "";

// if there were querie run already
if (isset($this->debugInfo["queries"])) {

// iterate through the run queries
// to find out if this query was already run
$counter = 1;
$keys = array();
foreach ($this->debugInfo["queries"] as $key=>$queryData) {
// if this query was run before
if (trim($queryData["query"]) == trim($query)) {
// increase the counter
$counter++;
// save the pointer to the query in an array
$keys[] = $key;
}
}

// if the query was run before
if ($counter > 0) {

// issue a warning for all the querys that were found to be the same as the current one
foreach ($keys as $key) {
$warning = sprintf($this->languageStrings["strLang_optimizationNeeded"], $counter);
$this->debugInfo["queries"][$key]["warning"] .= $warning;
}

}

}

// save debug information
$this->debugInfo["queries"][] = array(
"query"=>$query,
"executionTime"=>$endTime - $startTime,
"warning"=>$warning,
"error"=>mysql_error()
);

}

// if the query was successfully executed
if ($result) {

// get the number of records that would've been returned if there was no LIMIT
$foundRows = mysql_fetch_assoc(mysql_query("SELECT FOUND_ROWS()"));

$this->foundRows = $foundRows["FOUND_ROWS()"];

// get the number of affected rows for DELETE, INSERT, UPDATE queries
$this->affectedRows = @mysql_affected_rows();

// return result resource
return $result;

}

}

return false;

}

/**
* Alias for the mysql_fetch_assoc function.
*
* Example:
*
*
* $result = $db->query("SELECT * FROM table WHERE 1");
* while ($row = $db->fetch_assoc($result)) {
* // do stuff...
* }
*
*
* @param resource $resource resource to fetch
*
* @return mixed returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows
*/
function fetch_assoc($resource)
{

// check if given resource is valid
if (is_resource($resource)) {

// return the fetched row
return mysql_fetch_assoc($resource);

// if not a valid resource
} else {

// save debug information
$this->debugInfo["messages"][] = array(
"message"=>"strLang_notAValidResource",
"method"=>"fetch_assoc",
"parameters"=>"fetch_assoc('".$resource."')"
);
// and return FALSE
return false;

}

}

/**
* Finds field/fields from ONE row of a table based on a standard mySQL WHERE condition
*
* Example:
*
*
* /**
* notice that we're doing no error checking as we will have
* any errors show up in the debug window
* so don't forget to have at the end of your code a call to
* show_debug_info() method
* {@*}
* $foundData = $db->dlookup("name, surname, age", "people", "countryid = 1");
*
*
* @param string $field one or more fields to return in the result. if only one field is specified, the returned result
* will be the specific field's value. if more fields are specified the returned result will be an
* associative array.
* you can also use the "*" sign to return all the fields from a row
* @param string $table name of the table in which to look for results
* @param string $condition (optional) a standard mySQL WHERE condition
*
* @return mixed field/fields found
*/
function dlookup($field, $table, $condition = "")
{

// executes query
$result = $this->query("
SELECT ".$field."
FROM ".$table.
($condition!="" ? " WHERE ".$condition : "")
);

// if query was executed successfully and one or more results were found
if ($result && $this->foundRows > 0) {

// take *only* the first row
$row = mysql_fetch_assoc($result);

// if more fields were specified get them in an array
$fields_list = explode(",", $field);

// if all cells were requested
if (trim($field) == "*") {

// return the whole row
return $row;

// if more than one cell was requested
} elseif (count($fields_list)ɭ) {

$retData = array();

// iterate through the requested cells and take each one out from the row and put it in the return result
foreach ($fields_list as $fields) {
$retData[$fields] = $row[trim($fields)];
}

// return requested cells
return $retData;

// if a specific cell was requested
} else {

// return the field's value
return $row[$field];

}

// if query was not executed successfully
} elseif (!$result) {

// save debug info
$this->debugInfo["messages"][] = array(
"message"=>"strLang_couldNotExecuteQuery",
"method"=>"dlookup",
"parameters"=>"dlookup('".$field."', '".$table."', '".$condition."')"
);

// if no results were found
} else {

// return empty string
return "";

}

}

/**
* Looks up the maximum value in a field of a table based on a standard mySQL WHERE condition
*
* Example:
*
*
* /**
* notice that we're doing no error checking as we will have
* any errors show up in the debug window
* so don't forget to have at the end of your code a call to
* show_debug_info() method
* {@*}
* $maxAge = $db->dmax("age", "people", "countryid = 1");
*
*
* @param string $field name of the field in which to look
* @param string $table name of the table in which to look for results
* @param string $condition (optional) a standard mySQL WHERE condition
*
* @return mixed the maximum value found in the field
*/
function dmax($field, $table, $condition = "")
{

// executes query
$result = $this->query("
SELECT MAX(".$field.") AS maxval
FROM ".$table.
($condition!="" ? " WHERE ".$condition : "")
);

// if query was executed successfully and one or more results were found
if ($result && $this->foundRows > 0) {

// get all the data in the row
$row = mysql_fetch_assoc($result);
// return the result
return $row["maxval"];

// if query was not executed successfully
} elseif (!$result) {

// save debug info
$this->debugInfo["messages"][] = array(
"message"=>"strLang_couldNotExecuteQuery",
"method"=>"dmax",
"parameters"=>"dmax('".$field."', '".$table."', '".$condition."')"
);

// if no results were found
} else {

return "";

}

}

/**
* Counts the values in a field of a table based on a standard mySQL WHERE condition
*
* Example:
*
*
* /**
* notice that we're doing no error checking as we will have
* any errors show up in the debug window
* so don't forget to have at the end of your code a call to
* show_debug_info() method
* {@*}
* $countName = $db->dcount("name", "people", "gender = male");
*
*
* @param string $field name of the field in which to look
* @param string $table name of the table in which to look for results
* @param string $condition (optional) a standard mySQL WHERE condition
*
* @return mixed the number of values found
*/
function dcount($field, $table, $condition = "")
{

// executes query
$result = $this->query("
SELECT COUNT(".$field.") as countval
FROM ".$table.
($condition!="" ? " WHERE ".$condition : "")
);

// if query was executed successfully and one or more results were found
if ($result && $this->foundRows > 0) {

// get all the data in the row
$row = mysql_fetch_assoc($result);
// return the result
return $row["countval"];

// if query was not executed successfully
} elseif (!$result) {

// save debug info
$this->debugInfo["messages"][] = array(
"message"=>"strLang_couldNotExecuteQuery",
"method"=>"dcount",
"parameters"=>"dcount('".$field."', '".$table."', '".$condition."')"
);

// if no results were found
} else {

return "";

}

}

/**
* Sums the values in a field of a table based on a standard mySQL WHERE condition
*
* Example:
*
*
* /**
* notice that we're doing no error checking as we will have
* any errors show up in the debug window
* so don't forget to have at the end of your code a call to
* show_debug_info() method
* {@*}
* $sumSalary = $db->dsum("salary", "people", "countryid = 1");
*
*
* @param string $field name of the field in which to look
* @param string $table name of the table in which to look for results
* @param string $condition (optional) a standard mySQL WHERE condition
*
* @return mixed the sum of the values
*/
function dsum($field, $table, $condition = "")
{

// executes query
$result = $this->query("
SELECT SUM(".$field.") as sumval
FROM ".$table.
($condition!="" ? " WHERE ".$condition : "")
);

// if query was executed successfully and one or more results were found
if ($result && $this->foundRows > 0) {

// get all the data in the row
$row = mysql_fetch_assoc($result);
// return the result
return $row["sumval"];

// if query was not executed successfully
} elseif (!$result) {

// save debug info
$this->debugInfo["messages"][] = array(
"message"=>"strLang_couldNotExecuteQuery",
"method"=>"dsum",
"parameters"=>"dsum('".$field."', '".$table."', '".$condition."')"
);

// if no results were found
} else {

return "";

}

}

/**
* Parses a mySQL dump file
* (this script was posted on php.net so there are some people who contributed to it:
* thomas@pixtur.de and celtic@raven-blue.com - thanks guys!)
*
* @param string $url path to the file to be parsed
*
* @return void
*/
function parse_mysql_dump_file($url)
{

// uncomment the next line if you get an error about the memory limit
//ini_set("memory_limit","20M");

// read file into an array
$file_content = @file($url);

// if file was successfully opened
if ($file_content) {

$query = "";

// iterates through every line of the file
foreach ($file_content as $sql_line) {

// trims whitespace from both begining and end of line
$tsql = trim($sql_line);

// if line content is not empty and is the line does not represent a comment
if ($tsql != "" && substr($tsql, 0, 2) != "--" && substr($tsql, 0, 1) != "#") {

// add to query string
$query .= $sql_line;

// if line ends with ";"
if (preg_match("/;\s*\$/", $sql_line)) {

// executes query
$result = $this->query($query);

// if query was not executed successfully
if (!$result) {

// save debug info
$this->debugInfo["messages"][] = array(
"message"=>"strLang_couldNotExecuteQuery",
"method"=>"parse_mysql_dump_file",
"parameters"=>"query('".$query."')"
);

}

// empties the query string
$query = "";

}

}

}

} else {

// save debug info
$this->debugInfo["messages"][] = array(
"message"=>"strLang_fileCouldNotBeOpened",
"method"=>"parse_mysql_dump_file",
"parameters"=>"parse_mysql_dump_file('".$url."')"
);

}

}

/**
* Outputs debug information
*
* @param string $file (optional) if a file name is specified, the debug info will be output to that file
* instead of to the screen
*
* @return void
*/
function show_debug_info($file = "")
{

// if debug is enabled
if ($this->debug) {

// includes, if not included, the xtemplate class
if (!class_exists("XTemplate")) {
require_once $this->classPath."/includes/class.xtemplate.php";
}

$xtpl = new XTemplate($this->classPath."/templates/".$this->template."/debug.xtpl");

$xtpl->assign("templatePath", $this->strippedPath."/templates/".$this->template."/");

// assign all the values from the language file
foreach ($this->languageStrings as $stringDesc=>$languageString) {
$xtpl->assign($stringDesc, $languageString);
}

$totalExecutionTime = 0;
$successfulQueries = 0;
$unsuccessfulQueries = 0;

// if there is data about queries
if (isset($this->debugInfo["queries"])) {

// total number of the queries
$xtpl->assign("totalQueries", count($this->debugInfo["queries"]));

// iterate through all the query realted data
foreach ($this->debugInfo["queries"] as $debugInfo) {

// marks symbols in mySQL query
$symbols = array(
"/(\=)/",
"/(\>)/",
"/(\<)/",
"/(\*)/",
"/(\+)/",
"/(\-)/",
"/(\,)/",
"/(\.)/",
"/(\()/",
"/(\))/"
);
$replacement = htmlentities("\$1");
$debugInfo["query"] = preg_replace($symbols, $replacement, $debugInfo["query"]);

// marks strings in mySQL queries
$strings = array(
"/\'([^\']*)\'|\"([^\']*)\"/",
);
$replacement = htmlentities("'\$1'");
$debugInfo["query"] = preg_replace($strings, $replacement, $debugInfo["query"]);

// marks operations in mySQL queries
$operations = array(
"/(\bDELETE\b)/i",
"/(\bFROM\b)/i",
"/(\bGROUP BY\b)/i",
"/(\bHAVING\b)/i",
"/(\bINNER JOIN\b)/i",
"/(\bINSERT INTO\b)/i",
"/(\bLEFT JOIN\b)/i",
"/(\bLIMIT\b)/i",
"/(\bORDER BY\b)/i",
"/(\bREPLACE\b)/i",
"/(\bRIGHT JOIN\b)/i",
"/(\bSELECT\b)/i",
"/(\bSET\b)/i",
"/(\bUPDATE\b)/i",
"/(\bWHERE\b)/i"
);
$replacement = htmlentities("
\$1
");
$debugInfo["query"] = preg_replace($operations, $replacement, $debugInfo["query"]);

// marks other special entities in mySQL queries
$special = array(
"/(\bAND\b)/i",
"/(\bOR\b)/i",
"/(\bDESC\b)/i",
"/(\bASC\b)/i",
"/(\bON\b)/i"
);
$replacement = htmlentities("\$1");
$debugInfo["query"] = preg_replace($special, $replacement, $debugInfo["query"]);

// assign the query
$xtpl->assign("query", html_entity_decode($debugInfo["query"]));

// the execution time
$xtpl->assign("executionTime", $debugInfo["executionTime"]);

// and the error message (if there is one)
$xtpl->assign("error", $debugInfo["error"]);

// if there is an error message
if ($debugInfo["error"] == "") {

// increment the counter of successful queries
$successfulQueries++;

$xtpl->assign("nr", $successfulQueries);

// and parse the related template
$xtpl->parse("main.successfulQueriesContainer.successfulQueriesEntry");

// if there is no error message
} else {

// increment the counter of unsuccessful queries
$unsuccessfulQueries++;

$xtpl->assign("nr", $unsuccessfulQueries);

// and parse the related template
$xtpl->parse("main.unsuccessfulQueriesContainer.uns



PHP Free Code