<?php
/////////////////////////////////////////////////////////////////
/////////////////// Garry`s MySQL Class /////////////////////////
/////////////////////////////////////////////////////////////////
////////////////// 14/12/07 /////////////////////////
// THIS CLASS WRITTEN BY GARRY LACHMAN -> hide@address.com //
/////////////////////////////////////////////////////////////////
require_once('./Settings.class.php');
class MySQL {
// MySQL VARS
var $mysql_link;
var $mysql_host;
var $mysql_username;
var $mysql_password;
var $mysql_database;
var $mysql_link_status = false;
var $mysql_prefix;
// VARS FOR LAST OPERATIONS
var $query_result = Array();
var $records_number;
// CLASS VARS
var $script_log;
var $Settings;
var $inited = false;
function MySQL() {
// LOADING SINGLETON INSTANCE OF SETTINGS
$this->Settings =& Settings::getInstance();
// LOADING SETTINGS FROM SETTINGS CLASS
$this->mysql_host = $this->Settings->getMySQLSettings('hostname');
$this->mysql_username = $this->Settings->getMySQLSettings('username');
$this->mysql_password = $this->Settings->getMySQLSettings('password');
$this->mysql_database = $this->Settings->getMySQLSettings('database');
$this->mysql_prefix = $this->Settings->getMySQLSettings('prefix');
$this->script_log .= "Constractor set settings\n";
$this->inited = true;
$this->script_log .= "Inited set to: " . $this->inited . "\n";
}
// PUBLIC FUNCTIONS
function CustomQuerty($query)
{
$this->CheckConnection();
$this->script_log .= "Query: " . $query . "\n";
$result = mysql_query($query) or die($this->mysql_error_report());
$this->script_log .= "Query executed\n";
return $result;
}
function InsertQuerymassive($table, $insert_fields, $insert_values) {
$this->CheckConnection();
// BUILD SECURED QUERY - FOR SQL INJECTIONS PROTECTION
$query = sprintf("INSERT INTO `%s` (", mysql_escape_string($table));
$i=0;
foreach($insert_fields as $arr) {
if ($i > 0) {
$query .= sprintf(", `%s`", mysql_escape_string($insert_fields[$i]));
} else {
$query .= sprintf("`%s`", mysql_escape_string($insert_fields[$i]));
}
$i++;
}
$i=0;
foreach($insert_values as $arr) {
$j=0;
if ($i == 0)
{
$query .= ") VALUES (";
}
else
{
$query .= ", (";
}
foreach($arr as $arr2)
{
if ($j > 0) {
$query .= sprintf(", '%s'", mysql_escape_string($arr2));
} else {
$query .= sprintf("'%s'", mysql_escape_string($arr2));
}
$i++;
$j++;
}
$query .= ")";
}
$this->script_log .= "Query: " . $query . "\n";
$result = mysql_query($query) or die($this->mysql_error_report());
$this->script_log .= "Query executed\n";
return mysql_insert_id();
}
function InsertQuery($table, $insert_fields, $insert_values) {
$this->CheckConnection();
mysql_query("set character set 'utf8'");
// BUILD SECURED QUERY - FOR SQL INJECTIONS PROTECTION
$query = sprintf("INSERT INTO `%s` (", mysql_escape_string($table));
$i=0;
foreach($insert_fields as $arr) {
if ($i > 0) {
$query .= sprintf(", `%s`", mysql_escape_string($insert_fields[$i]));
} else {
$query .= sprintf("`%s`", mysql_escape_string($insert_fields[$i]));
}
$i++;
}
$query .= ") VALUES (";
$i=0;
foreach($insert_values as $arr) {
if ($i > 0) {
$query .= sprintf(", '%s'", mysql_escape_string($insert_values[$i]));
} else {
$query .= sprintf("'%s'", mysql_escape_string($insert_values[$i]));
}
$i++;
}
$query .= ")";
$this->script_log .= "Query: " . $query . "\n";
$result = mysql_query($query) or die($this->mysql_error_report());
$this->script_log .= "Query executed\n";
return mysql_insert_id();
}
function DeleteQuery($table, $where_field, $where_value, $opr=NULL)
{
$this->CheckConnection();
$query = sprintf("DELETE FROM %s", mysql_escape_string($table));
if (isset($where_field) && isset($where_value)) {
$query .= " WHERE ";
$i=0;
foreach($where_field as $arr) {
if ($i>0) {
$query .= sprintf(" %s `%s`='%s'",mysql_escape_string($opr[$i-1]),
mysql_escape_string($where_field[$i]),
mysql_escape_string($where_value[$i]));
} else {
$query .= sprintf("`%s`='%s'", mysql_escape_string($where_field[$i]),
mysql_escape_string($where_value[$i]));
}
$i++;
}
}
$this->script_log .= "Query: " . $query . "\n";
$result = mysql_query($query) or die($this->mysql_error_report());
$this->script_log .= "Query executed\n";
}
function UpdateQuery($table, $update_fields, $update_values,
$where_field=NULL, $where_value=NULL, $opr=NULL) {
$this->CheckConnection();
// BUILD SECURED QUERY - FOR SQL INJECTIONS PROTECTION
$query = sprintf("UPDATE `%s` SET ", mysql_escape_string($table));
$i=0;
foreach($update_fields as $arr) {
/*if ($i > 0) {
$query .= sprintf(", %s `%s`='%s'", mysql_escape_string($opr[$i-1]),
mysql_escape_string($update_fields[$i]),
mysql_escape_string($update_values[$i]));
} */
if ($i > 0) {
$query .= sprintf(", `%s`='%s'", mysql_escape_string($update_fields[$i]),
mysql_escape_string($update_values[$i]));
} else {
$query .= sprintf("`%s`='%s'", mysql_escape_string($update_fields[$i]),
mysql_escape_string($update_values[$i]));
}
$i++;
}
if (isset($where_field) && isset($where_value)) {
$query .= " WHERE ";
$i=0;
foreach($where_field as $arr) {
if ($i>0) {
$query .= sprintf(" %s `%s`='%s'",mysql_escape_string($opr[$i-1]),
mysql_escape_string($where_field[$i]),
mysql_escape_string($where_value[$i]));
} else {
$query .= sprintf("`%s`='%s'", mysql_escape_string($where_field[$i]),
mysql_escape_string($where_value[$i]));
}
$i++;
}
}
$this->script_log .= "Query: " . $query . "\n";
$result = mysql_query($query) or die($this->mysql_error_report());
$this->script_log .= "Query executed\n";
}
function testCall()
{
$this->query_result = Array();
$this->CheckConnection();
mysql_select_db('listy');
mysql_query('CALL `listy_lastClubs`()') or die($this->mysql_error_report());
}
function CallQuery($name){
$this->query_result = Array();
$this->CheckConnection();
//mysql_query('USE listy');
$query = sprintf("CALL `%s`()",mysql_escape_string($name));
//$query = "CALL `listy_lastClubs`()";
$result = mysql_query($query) or die($this->mysql_error_report());
// or die("Cannot Query")
$this->script_log .= "Query executed\n";
//$this->query_result = mysql_fetch_array($result) or die("Cannot Featch Array.");
$i=0;
while($row = mysql_fetch_assoc($result)) {
$this->query_result[$i] = $row;
$i++;
}
$this->script_log .= "Array fetched \n";
if ($i > 0) {
$this->records_number = mysql_num_rows($result) or die($this->mysql_error_report());
$this->script_log .= "Records Number setted to:" .$this->records_number . "\n";
} else {
$this->records_number = 0 ;
$this->script_log .= "Records Number setted to:" .$this->records_number . "\n";
}
mysql_free_result($result);
return $this->query_result;
}
function SelectQuery($table, $where_field=NULL, $where_value=NULL,
$opr=NULL, $DESC=true, $ORDER_BY="id") {
$this->query_result = Array();
// Example for useing class:
// SelectQuery('links',Array('id','name'),Array(1,'garry'),Array('OR'));
// Return: Array
//
// TODO IN THIS FUNCTION:
// * ADD LIMIT AND START
$this->CheckConnection();
// BUILD SECURED QUERY - FOR SQL INJECTIONS PROTECTION
$query = sprintf("SELECT * FROM `%s`",mysql_escape_string($table));
if (isset($where_field[0])) {
$query .= " WHERE ";
$i=0;
foreach($where_field as $arr) {
if ($i>0) {
$query .= sprintf(" %s `%s`='%s'",mysql_escape_string($opr[$i-1]),
mysql_escape_string($where_field[$i]),
mysql_escape_string($where_value[$i]));
} else {
$query .= sprintf("`%s`='%s'", mysql_escape_string($where_field[$i]),
mysql_escape_string($where_value[$i]));
}
$i++;
}
}
if ($DESC == 'true') {
//$addon = " ORDER BY `id` DESC";
$addon = sprintf(" ORDER BY `%s` DESC", $ORDER_BY);
} else {
//$addon = " ORDER BY `id`";
$addon = sprintf(" ORDER BY `%s`", $ORDER_BY);
}
$query .= $addon;
$this->script_log .= "Query: " . $query . "\n";
$result = mysql_query($query) or die($this->mysql_error_report());
// or die("Cannot Query")
$this->script_log .= "Query executed\n";
//$this->query_result = mysql_fetch_array($result) or die("Cannot Featch Array.");
$i=0;
while($row = mysql_fetch_assoc($result)) {
$this->query_result[$i] = $row;
$i++;
}
//$this->query_result = mysql_fetch_array($result);
/*
$i=0;
foreach (mysql_fetch_assoc($result) as $row)
{
$this->query_result[$i] = $row;
//array_push($this->query_result, $row);
$i++;
}
*/
$this->script_log .= "Array fetched \n";
if ($i > 0) {
$this->records_number = mysql_num_rows($result) or die($this->mysql_error_report());
$this->script_log .= "Records Number setted to:" .$this->records_number . "\n";
} else {
$this->records_number = 0 ;
$this->script_log .= "Records Number setted to:" .$this->records_number . "\n";
}
mysql_free_result($result);
return $this->query_result;
}
function CloseLink() {
if ($this->mysql_link_status == true) {
mysql_close($this->mysql_link) or die($this->mysql_error_report());
$this->script_log .= "Mysql Link Closed\n";
$this->mysql_link_status = false;
} else {
$this->script_log .= "Cannot close MySQL Link, the link dead\n";
}
}
// SETTERS & GETTERS
function getScriptLog() {
return $this->script_log;
}
function getLastResult() {
return $this->query_result;
}
function getLastRecordsNumber() {
return $this->records_number;
}
function getDB() {
return $this->mysql_database;
}
function setDB($database) {
$this->mysql_database = $database;
$this->script_log .= "New DB Setted: " . $this->mysql_database . "\n";
}
// PRIVATE FUNCTIONS
function Connect() {
$this->mysql_link = mysql_connect($this->mysql_host,
$this->mysql_username,
$this->mysql_password, 0, 65536) or die($this->mysql_error_report());
$this->script_log .= "Conncted\n";
mysql_select_db($this->mysql_database) or die($this->mysql_error_report());
$this->script_log .= "Selected db: " . $this->mysql_database . "\n";
$this->mysql_link_status=true;
}
function CheckConnection() {
if ($this->mysql_link == NULL || $this->mysql_link_status == false) {
$this->Connect();
}
mysql_query("set character set 'utf8'");
}
function mysql_error_report() {
echo mysql_error();
}
}
?>