File Source for db.mysql.php

<?php
/**
* MySQL Database Jet
*
* <pre>
* GloryLands, a Web-Based, Massive Multiplayer Online RPG/Strategy Game
* Copyright (C) 2008-09 John Haralampidis <jïhnys2[at]gmail.cïm>
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*
* For any help/suggestions or troubleshooting you can see the
* project community website at <http://www.glorylands.gr>
* </pre>
*
* @license GNU/GPLv3 GNU General Public License version 3
* @package GloryLands
* @subpackage Database
* @author John Haralampidis <jïhnys2[at]gmail.cïm>
* @copyright Copyright (C) 2007-2008, John Haralampidis
* @version 1.0
*/
/**
* MySQL Database Jet
*
* @package GloryLands
* @subpackage Database
*/
class mysql_jet extends DBJet {
/**
* Connection ID Definition
* @var resource
*/
var $conID;
/**
* Description of current action in case of error
* @var string
*/
var $errPosition;
/**
* Last query's results
* @var resource
*/
var $lastResult;
/**
* Last query's affected rows
* @var int
*/
var $affectedRows;
function num_changed() { return $numRows; }
/**
* Last query's number of rows
* @var int
*/
var $numRows;
function num_rows($resultset=false) {
if ($resultset === false) {
return $this->numRows;
} else {
return mysql_num_rows($resultset);
}
}
/**
* Last query didn't return any results
* @var bool
*/
var $emptyResults;
/**
* The number of queries performed
* @var int
*/
var $totQueries;
/**
* The total time spent on queries
* @var float
*/
var $totTime;
/**
* If we have global debug enabled, this function will store the
* queries being executed
* @var int
*/
var $queryList;
/**
* Initializes MySQL Class
*
* @param string $vdb Database name
* @param string $vhost Database server host
* @param string $vuser Login user name
* @param string $vpwd Login user password
* @param bool $presistent Make a presitent connection with the server
*/
function init($config) {
// Get variables from the config
$vdb = $config['DATABASE'];
$vhost = $config['HOST'];
$vuser = $config['USER'];
$vpwd = $config['PASSWORD'];
$presistent = false;
if (isset($config['PRESISTENT'])) $presistent = $config['PRESISTENT'];
// Connect to SQL
$this->totQueries = 0;
$this->errPosition = "connecting to MySQL";
if ($presistent) {
$id = mysql_pconnect($vhost, $vuser, $vpwd);
} else {
$id = mysql_connect($vhost, $vuser, $vpwd);
}
if (!$id) {
return false;
}
$this->conID = $id;
// Select database
$this->errPosition = "selecting database '{$vdb}'";
if (!mysql_select_db($vdb, $id)) return false;
// Initialize UTF-8
mysql_query("SET CHARACTER SET 'utf8'");
mysql_query("SET SESSION collation_connection ='utf8_general_ci'");
// Initialize variables
$queryList = array();
return true;
}
/**
* Performs a query
*
* @param string $text Query text to execute
* @return bool|resourceReturns the query resultset or false in case of error
*/
function query($text) {
$this->errPosition = "performing query '{$text}'";
$time=microtime(true);
$result = mysql_query($text, $this->conID);
$this->totQueries++;
if (!$result) {
if (defined("GLOB_DEBUG")) $this->queryList[]=array('query'=>$text,'result'=>false,'error'=>$this->get_error_message());
//debug_error($this->get_error_message(),ERR_WARNING);
$this->emptyResults = true;
$this->totTime+=(microtime(true)-$time);
return false;
}
$this->lastResult = $result;
if ((substr(strtoupper($text), 0 ,6) == "SELECT") || (substr(strtoupper($text), 0 ,4) == "SHOW")) {
$this->numRows = mysql_num_rows($result);
$this->emptyResults = ($this->numRows == 0);
if (defined("GLOB_DEBUG")) $this->queryList[]=array('query'=>$text,'result'=>true,'rows'=>$this->numRows);
} else {
$this->affectedRows = mysql_affected_rows();
$this->emptyResults = ($this->affectedRows == 0);
if (defined("GLOB_DEBUG")) $this->queryList[]=array('query'=>$text,'result'=>true,'rows'=>$this->affectedRows);
}
$this->totTime+=(microtime(true)-$time);
return $result;
}
/**
* Return the first value of the first row of the requested query, using a specified resultset
*
* @param resource $result (optional) Resultset obdained from a query() or query-like function
* @return bool|resourceReturns the value or false in case of error
*/
function fetch_value($result=false) {
if (!$result) {
$respond = mysql_fetch_array($this->lastResult, MYSQL_NUM);
} else {
$respond = mysql_fetch_array($result, MYSQL_NUM);
return $respond[0];
}
}
/**
* Release a specific query resultset
*
* @param resource $resultset Resultset obdained from query() function
*/
function free_query($resultset) {
mysql_free_result($resultset);
}
/**
* Return a row from the last queried resultset
*
* @param int $resmode The type of array that is to be fetched. It's a constant and can take the following values: MYSQL_ASSOC, MYSQL_NUM, and the default value of MYSQL_BOTH
* @return bool|resourceReturns the row or false in case of error or end of results
*/
function fetch_array($resmode = MYSQL_BOTH, $resultset = false) {
if (!$resultset) $resultset=$this->lastResult;
if (!$resultset) return false;
return mysql_fetch_array($resultset, $resmode);
}
/**
* Return all the rows from the last queried resultset
*
* @param int $resmode The type of array that is to be fetched. It's a constant and can take the following values: MYSQL_ASSOC, MYSQL_NUM, and the default value of MYSQL_BOTH
* @return bool|resourceReturns the row or false in case of error or end of results
*/
function fetch_array_all($resmode = MYSQL_BOTH, $resultset = false) {
if (!$resultset) $resultset=$this->lastResult;
if (!$resultset) return array();
$res = array();
while ($row = mysql_fetch_array($resultset, $resmode)) {
array_push($res, $row);
}
return $res;
}
/**
* Release the last queried resultset
*/
function free_results() {
if (!mysql_free_result($this->lastResult)) return false;
return true;
}
/**
* Moves the internal row pointer to a new position
*
* @param int $row The row index to jump to
*/
function set_row($row, $resultset=false) {
if (!$resultset) $resultset=$this->lastResult;
if (!mysql_data_seek($resultset, $row)) return false;
return true;
}
/**
* Returns detailed information for the last error occured
*
* @param bool $formatted (optional) TRUE If you want the result to be a pre-formatted HTML response
* @return string An HTML-Formatted error description
*/
function get_error_message($formatted=true) {
if ($formatted) {
return "MySQL error while " . $this->errPosition . " : " . mysql_error() . "";
} else {
return mysql_error();
}
}
/**
* Insert a new row on specified table
*
* @param string $table The table name to add the data
* @param array $data An one-dimensional array that contains the field names (as keys) and the field values to add
* @return bool|resource Returns false in case of error or the resultset of the executed query
*/
function insert($table, $data) {
$vars = ""; $vals = "";
foreach ($data as $name => $value) {
if ($vars != "") $vars .= ", ";
if ($vals != "") $vals .= ", ";
$vars .= "`{$name}`";
$vals .= "'".mysql_real_escape_string($value)."'";
}
return $this->query("INSERT INTO `{$table}` ({$vars}) VALUES ({$vals})");
}
/**
* Replace or insert a new row on specified table
*
* @param string $table The table name to add the data
* @param array $data An one-dimensional array that contains the field names (as keys) and the field values to add
* @return bool|resource Returns false in case of error or the resultset of the executed query
*/
function replace($table, $data) {
$vars = ""; $vals = "";
foreach ($data as $name => $value) {
if ($vars != "") $vars .= ", ";
if ($vals != "") $vals .= ", ";
$vars .= "`{$name}`";
$vals .= "'".mysql_real_escape_string($value)."'";
}
return $this->query("REPLACE INTO `{$table}` ({$vars}) VALUES ({$vals})");
}
/**
* Performs a query and returns true if the results are not empty
*
* @param string $query The query to execute
* @return bool Returns false in case of error or empty resultset, or true otherways
*/
function poll($query) {
$this->errPosition = "performing polling query '{$query}'";
$time=microtime(true);
$result = mysql_query($query, $this->conID);
$this->totQueries++;
if (!$result) {
if (defined("GLOB_DEBUG")) $this->queryList[]=array('query'=>$text,'result'=>false,'error'=>$this->get_error_message());
//debug_error($this->get_error_message(),ERR_WARNING);
$this->totTime+=(microtime(true)-$time);
return false;
} else {
if (defined("GLOB_DEBUG")) $this->queryList[]=array('query'=>$text,'result'=>true,'rows'=>mysql_num_rows($result));
}
$ans = (mysql_num_rows($result)!=0);
mysql_free_result($result);
$this->totTime+=(microtime(true)-$time);
return $ans;
}
/**
* Performs a query and returns the first value of the first row or false in case of error
*
* @param string $query The query to execute
* @return bool|string Returns false in case of error or the first row's first field value
*/
function query_and_get_value($query) {
$this->errPosition = "performing get value query '".htmlspecialchars($query)."'";
$time=microtime(true);
$result = mysql_query($query, $this->conID);
$this->totQueries++;
if (!$result) {
if (defined("GLOB_DEBUG")) $this->queryList[]=array('query'=>$text,'result'=>false,'error'=>$this->get_error_message());
//debug_error($this->get_error_message(),ERR_WARNING);
$this->totTime+=(microtime(true)-$time);
return false;
} else {
if (defined("GLOB_DEBUG")) $this->queryList[]=array('query'=>$text,'result'=>true,'rows'=>mysql_num_rows($result));
}
if (mysql_num_rows($result)==0) return "";
$row=mysql_fetch_array($result, MYSQL_NUM);
mysql_free_result($result);
$this->totTime+=(microtime(true)-$time);
return $row[0];
}
/**
* Update a row on a table. The row to edit is defined by a where clause
*
* @param string $table The table name from which to edit the data
* @param string $where A MySQL WHERE-formatted query part. This is used to identify the item(s) to edit (ex. "`index` = 2")
* @param array $data An one-dimensional array that contains the field names (as keys) and the field values to edit
* @return bool|resource Returns false in case of error or the resultset of the executed query
*/
function update($table, $index_data, $insert_data) {
if (is_array($index_data)) {
$data = '';
foreach ($index_data as $n => $v) {
if ($data!='') $data.=' AND ';
$data.='`'.$n.'` = \''.mysql_real_escape_string($v).'\'';
}
$where=$data;
} else {
$where=$index_data;
}
$q = "";
foreach ($insert_data as $name => $value) {
if ($q != "") $q .= ", ";
$q .= "`{$name}` = '".mysql_real_escape_string($value)."'";
}
return $this->query("UPDATE `{$table}` SET {$q} WHERE {$where}");
}
/**
* Remove one or more rows from a table.
*
* @param string $table The table name from which to edit the data
* @param string $where A MySQL WHERE-formatted query part or an array containing the indexing values
* @return bool|resource Returns false in case of error or the resultset of the executed query
*/
function delete($table, $index_data=false) {
if (is_array($index_data)) {
$data = '';
foreach ($index_data as $n => $v) {
if ($data!='') $data.=' AND ';
if (strstr($v,'%')) {
$data .= "`{$n}` LIKE '".mysql_real_escape_string($v)."'";
} else {
$data .= "`{$n}` = '".mysql_real_escape_string($v)."'";
}
//$data.='`'.$n.'` = \''.mysql_escape_string($v).'\'';
}
$where=$data;
} elseif ($index_data===false) {
$where='';
} else {
$where=$index_data;
}
return $this->query("DELETE FROM `{$table}` WHERE {$where}");
}
/**
* Select one or more rows from a table.
*
* @param string $table The table name from which to edit the data
* @param string $index_data A MySQL WHERE-formatted query part or an array containing the indexing values
* @param string $return_data A MySQL SELECT-formatted query part or an array containing the values to return
* @return bool|resource Returns false in case of error or the resultset of the executed query
*/
function select($table, $index_data=false, $return_data=false, $order_by=false, $sort_order=false) {
if (is_array($return_data)) {
$data = '';
foreach ($return_data as $v) {
if ($data!='') $data.=',';
$data.='`'.$v.'`';
}
$what=$data;
} elseif ($return_data===false) {
$what='*';
} else {
$what=$return_data;
}
if (is_array($index_data)) {
$data = '';
foreach ($index_data as $n => $v) {
if ($data!='') $data.=' AND ';
if (strstr($v,'%')) {
$data .= "`{$n}` LIKE '".mysql_real_escape_string($v)."'";
} else {
$data .= "`{$n}` = '".mysql_real_escape_string($v)."'";
}
//$data.='`'.$n.'` = \''.mysql_escape_string($v).'\'';
}
$where=' WHERE '.$data;
} elseif ($index_data===false) {
$where='';
} else {
$where=' WHERE '.$index_data;
}
$order = '';
if ($order_by !== false) {
$ord = 'ASC';
if ($sort_order !== false) {
$ord = mysql_escape_string($sort_order);
}
$order = ' ORDER BY `'.mysql_real_escape_string($order_by).'` '.$ord;
}
return $this->query("SELECT {$what} FROM `{$table}` {$where}{$order}");
}
/**
* List the database tables
*
* @return bool|array Returns false in case of error or the tables as array
*/
function get_tables() {
// Query
$ans=$this->query("show tables");
if (!$ans) return false;
// Store tables
$tables = array();
while ($row = $this->fetch_array(MYSQL_NUM,$ans)) {
$tables[] = $row[0];
}
// Return result
return $tables;
}
/**
* Show the database structure
*
* @param string $table The table name
* @return bool|array Returns false in case of error or the table columns as associative array (Key = column name)
*/
function get_table_structure($table) {
// Query
$ans=$this->query("show columns from `".$table."`");
if (!$ans) return false;
// Store columns
$columns = array();
while ($row = $this->fetch_array(MYSQL_ASSOC,$ans)) {
$name = $row['Field'];
$columns[$name] = $row;
}
// Return result
return $columns;
}
/**
* Converts a UNIX timestamp into SQL timestamp
*
* @param int $timestamp A UNIX timestamp value
* @return string Returns the value into MySQL timestamp format
*/
function SQLTime($timestamp) {
return date("YmdHis", $timestamp);
}
/**
* Converts a UNIX timestamp into SQL timestamp
*
* @param string $timestamp A MySQL timestamp value
* @return int Returns the value into UNIX timestamp format
*/
function UNIXTime($timestamp) {
$y = substr($timestamp,0,4);
$m = substr($timestamp,4,2);
$d = substr($timestamp,6,2);
$h = substr($timestamp,8,2);
$i = substr($timestamp,10,2);
$s = substr($timestamp,12,2);
return mktime($h, $i, $s, $m, $d, $y);
}
/**
* Execute a SQL script
*
* @param string $file The filename to load and run
* @return bool Returns true if all the queries were successfull or false if one query failed
*/
function run($file) {
// Open file
$f = fopen($file,"r");
if (!$f) return;
// Read file
$incomment = false;
$buffer = '';
while (!feof($f)) {
$row = trim(fgets($f, 4096));
if ((substr($row,0,1)!='#') && (substr($row,0,2)!='--')) {
if (substr($row,0,2) == '/*') $incomment = true;
if (!$incomment) {
$buffer .= $row."\n";
}
if (substr($row,-2) == '*/') $incomment = false;
}
}
fclose($f);
// Count queries
$success = 0;
// Break file queries
$queries = explode(";\n", $buffer);
foreach ($queries as $query) {
if (trim($query)!='') {
if (!$this->query($query)) return false;
}
}
return true;
}
/**
* Visualize the queries being executed for debug purposes
*
* @return string Returns an HTML formatted result with the queries and their status
*/
function getQueries() {
$ans = '<table border="1" width="100%">';
foreach ($this->queryList as $query) {
$ans .= '<tr><td>'.$query['query'].'</td>';
if ($query['result']) {
$ans .= '<td>OK</td>';
$ans .= '<td>Returned/Affected '.$query['rows'].' rows</td>';
} else {
$ans .= '<td colspan="2">'.$query['error'].'</td>';
}
$ans .= '</tr>';
}
$ans .= '</table>';
return $ans;
}
}
?>

	GloryLands Database

[ class tree: GloryLands ] [ index: GloryLands ] [ all elements ]

Source for file db.mysql.php